Building the StudentDataAccess Class for ASP.NET 2.0 - Creating the Constructor
(Page 3 of 4 )
We need to create one constructor for our StudentDataAccess class. The constructor accepts a studentId value as int and initializes the object from the database Students table, using the studentId value that is passed to it and by searching the table for a matching record. If it didn't find a matching row it sets the studentId field to -1. Let's see the code.
public StudentDataAccess(int studentId){
this.studentId = studentId;
if (this.studentId != -1){
try{
using (SqlConnection connection = new SqlConnection
(connString)){
SqlCommand command = new SqlCommand("GetStudent",
connection);
command.CommandType = CommandType.StoredProcedure;
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@StudentID";
command.Parameters.Add(parameter);
command.Parameters["@StudentID"].Value = this.studentId;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader
(CommandBehavior.SingleRow)){
if (reader.Read()){
this.firstName = reader.GetString(reader.GetOrdinal
("FirstName"));
this.lastName = reader.GetString(reader.GetOrdinal
("LastName"));
this.dateOfBirth = reader.GetDateTime
(reader.GetOrdinal("DateOfBirth"));
this.admissionDate = reader.GetDateTime
(reader.GetOrdinal("AdmissionDate"));
this.major = reader.GetString(reader.GetOrdinal
("Major"));
this.active = reader.GetBoolean(reader.GetOrdinal
("Active"));
}
else
{
this.studentId = -1;
}
}
}
}
catch (Exception ex){
this.studentId = -1;
throw new ApplicationException("An error has occurred.");
}
}
}
What we have done in the constructor is that we have assigned the value of the studentId parameter to the private field studentId. Now we can use the studentId field to execute the GetStudent stored procedure and return a record from the Students table. The if statement tests if the studentId field contains a valid value; 1 is not a valid value (this will make sense later when we discuss other methods). If the field does contain a valid value we simply execute the database stored procedure and use the returned row to initialize the object's state (the object properties). We have created a connection object, in a using block to ensure that it will be properly closed when the execution of the using block completes -- or in case of an exception, we have created the command needed.
The SqlCommand object's constructor is passed the name of the stored procedure, or the T-SQL statements (like SELECT * FROM Students WHERE StudentID = @StudentID) if you want to do that, and the SqlConnection object that is used to communicate with the database. You define, to the SqlCommand object, that you want to execute a stored procedure using the SqlCommand.CommandType property which accepts values of the CommandType enumeration. In this case, we have used the enumeration value CommandType.StoredProcedure.
Then we have created a SqlParameter object, which is an object that represents a T-SQL parameter associated with the command we are executing, and assigned its name and value through the SqlParameter.ParameterName and SqlParameter.Value properties. Note how we have assigned those properties. we have assigned the Value property, after adding the SqlParameter object to the SqlCommand.Parameters collection, and by using the Parameters collection's indexer.
After that we simply opened the connection using the Open() method and called the SqCommand.ExecuteReader() method, which returns a SqlDataReader object. We know that we are going to get back only one student record and because of that we have passed the enumeration value CommandBehavior.SingleRow to the SqCommand.ExecuteReader(), which optimizes the command for retrieving a single row.
Inside the using block we have used the SqlDataReader.Read() method to read the returned row and to assign the returned fields to the StudentDataAccess fields. Note that we have used the Strongly Typed Get methods of the SqlDataReader object to return the values of the table fields in the appropriate data types. Please refer to my ADO.NET articles about SqlConnection, SqlCommand, SqlParameter and SqlDataReader objects for more information.
We have returned the values of the fields of the single record from the SqlDataReader object using the following syntax:
this.firstName = reader.GetString(reader.GetOrdinal
("FirstName"));
this.lastName = reader.GetString(reader.GetOrdinal("LastName"));
this.dateOfBirth = reader.GetDateTime(reader.GetOrdinal("DateOfBirth"));
this.admissionDate = reader.GetDateTime(reader.GetOrdinal
("AdmissionDate"));
this.major = reader.GetString(reader.GetOrdinal("Major"));
this.active = reader.GetBoolean(reader.GetOrdinal("Active"));
In case you don't know, the SqlDataReader object returns the field's value in an object data type and you have to convert it to an appropriate data type. But you can use the strongly typed Get() methods to return the values in an appropriate data type. Also note that we have used the SqlDataReader.GetOrdinal() method to return the column ordinal. We needed to do this because the strongly typed get() methods accept only column ordinals, not column names.
Note that if the SqlDataReader.Read() method didn't find a line to read, or in case of an exception, we assign the value -1 to the studentId field and this means that the object has not been initialized from the database table. Let's write code to test the constructor we have just written.
Next: Testing the Constructor >>
More ASP.NET Articles
More By Michael Youssef
