Improving the StudentDataAccess Class for ASP.NET 2.0 - Modifying the application to retrieve all the columns
(Page 5 of 5 )
We need to modify our code to retrieve all the columns, not only the StudentID column. To do that we need to create another stored procedure that returns all the columns and we also need to create a new constructor for the StudentDataAccess class that accepts values for all the fields. Finally we need to add a new method that does the work of populating the objects and adding them to the collection.
Execute the following CREATE PROCEDURE statement in SQL Server Management Studio:
USE School
GO
CREATE PROCEDURE GetAllStudents
AS
SELECT StudentID, FirstName, LastName, DateOfBirth,
AdmissionDate, Major, Active
FROM Students
GO
The GetAllStudents stored procedure simply returns all the columns of the Students table, and it's always good to retrieve the columns by their name. Although we are selecting all the columns it's not advisable to use the (*). Here's the issue: if you use the (*) and want to modify your table by adding one or more columns, you would break your database access code which uses SqlParameter objects.
Now add the following constructor to the StudentDataAccess class:
public StudentDataAccess(int studentId, string firstName, string
lastName, DateTime dateOfBirth, DateTime admissionDate, string
major, bool active){
this.studentId = studentId;
this.firstName = firstName;
this.lastName = lastName;
this.dateOfBirth = dateOfBirth;
this.admissionDate = admissionDate;
this.major = major;
this.active = active;
}
This constructor initializes the object without communicating to the database. It simply assigns the values that are passed to it to the private fields. Those values will come from the SqlDataReader object created in the next method.
Add the following method to the StudentDataAccess class:
public static StudentDataAccessCollection GetAllStudentsData(){
try{
using (SqlConnection connection = new SqlConnection
(connString)){
SqlCommand command = new SqlCommand("GetAllStudents",
connection);
command.CommandType = CommandType.StoredProcedure;
StudentDataAccessCollection students = new
StudentDataAccessCollection();
connection.Open();
using (SqlDataReader reader = command.ExecuteReader()){
while (reader.Read()){
StudentDataAccess student = new StudentDataAccess
(reader.GetInt32(reader.GetOrdinal("StudentID")),
reader.GetString(reader.GetOrdinal("FirstName")),
reader.GetString(reader.GetOrdinal("LastName")),
reader.GetDateTime(reader.GetOrdinal("DateOfBirth")),
reader.GetDateTime(reader.GetOrdinal("AdmissionDate")),
reader.GetString(reader.GetOrdinal("Major")),
reader.GetBoolean(reader.GetOrdinal("Active")));
students.Add(student);
}
}
return students;
}
}
catch (Exception ex){
throw new ApplicationException("An error has occurred.");
}
}
When you run the page you will get the same result that we got using the GetAllStudents() method. The difference between the GetAllStudents() and GetAllStudentsData() methods is that the GetAllStudentsData() method uses only one database communication to populate StudentDataAccess objects and add them to the collection. The GetAllStudents() method, on the other hand, uses many database communications (actually five database communications for populating four StudentDataAccess objects) to populate the objects. It does this because it calls the StudentDataAccess constructor overload that accepts the studentId value, opens a connection to execute the stored procedure and returns the fields to populate the object. And the GetAllStudents() method will continue to do that as long as there are records in the Students database, which would hurt the performance of both the web site and the database server if there are thousands of records.
To test the GetAllStudentsData() method simply replace the call to the GetAllStudents() method by the call to the GetAllStudentsData() method in the test page, as done in the next statement, and run the page. You will get the same results as before but with less database communications.
StudentDataAccessCollection students =
StudentDataAccess.GetAllStudentsData();
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
