HomeASP.NET Testing the StudentDB Class for ASP.NET 2....
Testing the StudentDB Class for ASP.NET 2.0
In the first article of this series we created the database table, the Student class and sketched the structure of the StudentDB class. In this article, we start developing the methods of the StudentDB class and testing it from an ASP.NET web page.
Contributed by Michael Youssef Rating: / 6 September 10, 2007
In particular, we will develop the GetStudent() and GetStudentsInCollection() methods. I advice you to read my articles about ADO.NET before you continue reading this article if you are not familiar with ADO.NET.
We start by creating the stored procedures needed by the GetStudent() and GetStudentsInCollection() methods. Run the following T-SQL code in your SQL Server Management Studio:
USE School GO CREATE PROCEDURE GetStudent @StudentID INT AS SELECT StudentID, FirstName, LastName, DateOfBirth, AdmissionDate, Major, Active FROM Students WHERE StudentID = @StudentID GO CREATE PROCEDURE GetAllStudents AS SELECT StudentID, FirstName, LastName, DateOfBirth, AdmissionDate, Major, Active FROM Students
This T-SQL code simply creates two stored procedures. The first one is GetStudent, which accepts a parameter of type INT as the Student ID and returns a student record based on that ID. The second stored procedure, GetAllStudents, returns all the records of the Students table.
Note that I have selected all the columns of the table using their individual column names and not using the asterisk (*). I like to do that because, if for some reason I have modified the structure of my table by adding more columns, I would not break my code. It depends on your situation and your business logic, but it's a good practice to use column names instead of the asterisk (*). Let's create the GetStudent() method.
The GetStudent() method is a special method because it's passed a student id, and its return value is a Student object that represents a record in the database that pertains to the passed student id value. Recall that in the first article we discussed why we need to use a Student class to represent a record of the Students table. The code of this method will be as follows (but only for now because we will enhance it later):
public static Student GetStudent(int studentId){ 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 = studentId;
Student student; connection.Open(); using (SqlDataReader reader = command.ExecuteReader (CommandBehavior.SingleRow)){ reader.Read(); student = new Student((int)reader["StudentID"], reader ["FirstName"].ToString(),reader["LastName"].ToString(), (DateTime)reader["DateOfBirth"], (DateTime)reader ["AdmissionDate"], reader["major"].ToString(),(bool)reader ["active"]); } return student; } } catch(SqlException ex){ throw new ApplicationException("A database error has occurred."); } catch (Exception ex){ throw new ApplicationException("An error has occurred."); } }
Let's talk about what we have done in this method.
First of all, we put the database access code inside a try/catch block. The first catch block handles SqlException objects and and the second catch block handles any other exception. You must place more specific catch blocks before the more general catch block, which catches the base class for all exceptions (the Exception class), as we did in the method above.
In the try block we created a SqlConnection object with a using block to guarantee that it will be closed after the execution of the using block is complete or in case of an exception. We then created the SqlCommand object and passed the name of the stored procedure we want to execute, along with the connection object to use as parameters to the constructor. We have to tell the SqCommand object that we want to execute a stored procedure. We do that by assigning the enumeration value CommandType.StoredProcedure to the property command.CommandType.
After that we added the stored procedure's only parameter by using the SqlCommand.Parameters.Add() method, which accepts a SqlParameter object and adds the parameter to the command. The Add() method adds the SqlParameter object to the SqlCommand's SqlParameterCollection object. There are six versions of this method you can use; we will use other overloads soon.
We used the indexer on the SqlCommand.Parameters, yes the SqlParameterCollection of the SqlCommand, to access the parameter StudentID and assign its value through the Value property. We assigned the value that's passed to the GetStudent() method as the value for the parameter, as you can see. Then we opened the connection and executed the command using a SqlDataReader object.
Note that we passed the CommandBehavior.SingleRow enumeration value to the SqlCommand.ExecuteReader() method. The CommandBehavior enumeration defines the behavior of the SqlCommand and the SqlDataReader that is used to retrieve the data; in our case the CommandBehavior.SingleRow optimizes the SqlDataReader to retrieve only a single row. We are retrieving a single student record from the database and it makes sense to use this enumeration value.
This single row is available after calling the SqlDataReader.Read() method. At this point we have used the values of the fields to create a Student object by passing those values that we get through the SqlDataReader object to the Student's object constructor. These will be returned as a result of calling this method, as follows:
When you run the page you will find that two student records have been retrieved from the database and printed on the page as in the following screen shot:
We simply pass the Student ID to the static method StudentDB.GetStudent() and assign the returned object to a local variable, of type Student, and then print out the values of its properties through the use of the Response.Write() method.
As our next step, let's create the GetAllStudentsInCollection() method.
For the GetAllStudentsInCollection() method we are going to use Generics. Please read an article about C# generics in case you are not familiar with this concept. Generics are used widely with collections, for example the generic type List<T> which creates a strongly typed List collection object through passing the class type that the collection will store between angle brackets, like List<Student> . Now we can call the Add() method of that generic collection, which accepts a parameter of type Student.
The generic collection class List<T> defines a type parameter (the T) between angle brackets. This is a placeholder for any type that will be stored in the List collection. So when we create an object of type List<Student> at runtime, the Student type will replace, and be used instead of, the T type parameter. The List<T> looks something like the following:
public class List<T>{ public void Add(T item){ // code to add T to the List } }
So at runtime the T, the type parameter, is replaced with the Student type when we create an instance of that collection in our code, maybe like this:
List<Student> students = new List<Student>();
I hope you get the idea; if not, please consult other resources because Generics can't be covered in only one section or even one article. So let's see the code of the GetAllStudentsInCollection() method.
public static List<Student> GetAllStudentsInCollection(){ try{ using (SqlConnection connection = new SqlConnection (connString)){ SqlCommand command = new SqlCommand("GetAllStudents", connection); command.CommandType = CommandType.StoredProcedure;
List<Student> students = new List<Student>();
connection.Open(); using (SqlDataReader reader = command.ExecuteReader()){ while (reader.Read()){ Student student; student = new Student((int)reader["StudentID"], reader ["FirstName"].ToString(), reader["LastName"].ToString(), (DateTime)reader["DateOfBirth"], (DateTime)reader ["AdmissionDate"], reader["major"].ToString(), (bool)reader ["active"]); students.Add(student); } } return students; } } catch (SqlException ex){ throw new ApplicationException("A database error has occurred."); } catch (Exception ex){ throw new ApplicationException("An error has occurred."); } }
The only thing that you need to understand is the statement List<Student> students = new List<Student>(); which creates the generic List type to store objects of type Student. The rest of the code should be familiar; we simply open a connection to execute the command and return a SqlDataReader object. Using the SqlDataReader object we pass the values of the fields of the current returned database table's row to the Student object's constructor to initialize a new Student object. Then we add it to our Generic List collection and finally we return that collection to the caller, which now has all the student records from the database, as a Student object inside a collection object.
Here is the code needed in the Page_Load() event handler to test the method.
Don't forget to add a reference to the namespace that contains the generic collection classes.
using System.Collections.Generic;
When you run the page you will get the result shown in the next screenshot.
As you can see, we have used a foreach statement in the same way we would use it with a strongly typed collection of our own. In case you didn't know, you can create a strongly typed collection without using generics, but it requires writing a lot of code. With generics we managed to do the same thing without writing any code. Note that we have created a local generic List collection variable that matches the return type of the GetAllStudentsInCollection method in order to use it.
SqlDataReader returns the fields into an object data type, but our values are of types int, string, DateTime and bool. So we have to convert the field's data type from object to the appropriate data type, which hurts performance. There is a better technique that we can use. Below you'll see the modified versions of the GetAllStudents() and GetStudent() that use this technique.
public static Student GetStudent(int studentId){ 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 = studentId;
Student student; connection.Open(); using (SqlDataReader reader = command.ExecuteReader (CommandBehavior.SingleRow)){ reader.Read(); student = new Student(reader.GetInt32(0), reader.GetString(1), reader.GetString(2),reader.GetDateTime(3), reader.GetDateTime(4), reader.GetString(5), reader.GetBoolean (6)); } return student; } } catch(SqlException ex){ throw new ApplicationException("A database error has occurred."); } catch (Exception ex){ throw new ApplicationException("An error has occurred."); } }
public static List<Student> GetAllStudentsInCollection(){ try{ using (SqlConnection connection = new SqlConnection (connString)){ SqlCommand command = new SqlCommand("GetAllStudents", connection); command.CommandType = CommandType.StoredProcedure;
List<Student> students = new List<Student>();
connection.Open(); using (SqlDataReader reader = command.ExecuteReader()){ while (reader.Read()){ Student student; student = new Student(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 (SqlException ex){ throw new ApplicationException("A database error has occurred."); } catch (Exception ex){ throw new ApplicationException("An error has occurred."); } }
We have used the SqlDataReader.GetInt32(), GetString(), GetDateTime() and GetBoolean() methods to retrieve the value of each field in the correct data type without performing data type conversion operations, which would hurt performance when we have a table with 100,000 records or so. Those Get() methods accept the column ordinal and return the data in the appropriate data type. Note that in the GetAllStudentsInCollection() method we have used the return value of the SqlDataReader.GetOrdinal() method. The method accepts the name of the column and returns its ordinal as a value to the parameter of the strongly typed SqlDataReader.Get() method in order to refer to the column by name instead of by column ordinal.
Doing this eliminates confusion, but it also hurts performance a little bit. The SqlDataReader object now has to search the columns for that column name instead of just retrieving the value in column number 0 or column number 3 and so on.
So the point is, if you know that you are retrieving a field that has an INT database data type, then use the SqlDataReader.GetInt32() to return that field. The same rule applies for all data types.
ASP.NET 
Improving the StudentDataAccess Class for ASP.NET 2.0
