Improving the StudentDataAccess Class for ASP.NET 2.0

If you read my article Testing the StudentDB Class for ASP.NET 2.0, you saw that we used C# generics as the return type of the method GetAllStudentsInCollection() to return a strongly typed collection of Student objects. The method looks like this:

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.");
  }
}

Using generics is very easy and saves time, but you might be new to this technique; you might have not created a custom collection class by hand before. I want to show you what we will have to do without generics. If you are not familiar with generics please consult other articles because it's an important subject for developing .NET applications.

In the next section I will show you an alternative to using generics. We will create a class called StudentDataAccessCollection that we will use to store objects of type StudentDataAccess which represent records in the Students table. So you might retrieve all the records from the Students table, use them to create StudentDataAccess objects, and add those objects to an instance of type StudentDataAccessCollection. If you do that you will be able to execute code that uses a foreach statement -- for example, to iterate through the StudentDataAccess objects in the StudentDataAccessCollection and use their properties as we are going to see soon.

Creating the StudentData AccessCollection class

In the next section we are going to create a method called GetAllStudents() which returns all the rows of the Students table as objects of type StudentDataAccess in a collection object, which we will create in this section, of type StudentDataAccessCollection. The method signature looks like this

 public static StudentDataAccessCollection GetAllStudents()

Let's create the StudentDataAccessCollection class. Right click on the App_Code folder, click on Add new Item menu option, select a class template, name it StudentDataAccessCollection.cs and click on OK. Replace the auto-generated code of the class file with the following code.

using System;
using System.Collections;


public class StudentDataAccessCollection{
  private ArrayList list;

  public ArrayList List{
    get { return list; }
  }

  public StudentDataAccessCollection(){
    this.list = new ArrayList();
  }

  public void Add(StudentDataAccess student){
    this.list.Add(student);
  }

  public void Remove(StudentDataAccess student){
    this.list.Remove(student);
  }

  public int Count{
    get { return this.list.Count; }
  }

  public StudentDataAccess this[int index]{
    get { return (StudentDataAccess)this.list[index]; }
  }

  public IEnumerator GetEnumerator(){
    return list.GetEnumerator();
  }
}

You might think that we can write the signature of the GetAllStudents() method to return a .NET Collection object as follows, instead of creating our own collection class.

 public static ArrayList GetAllStudents()

Yes, you can do that, but the problem is that the ArrayList.Add() method accepts the general object data type as a parameter, so it can contain any data type. We need a way to have a collection object with an Add() method that accepts the data type we are working with. To make this possible we have defined a private ArrayList that will be used for storing our objects.

We have defined the ArrayList object as private and created methods, like Add(), as public with the data type we want to work with, in this case the StudentDataAccess type, as the parameter. Inside those public methods we pass the StudentDataAccess object as a value to the ArrayList.Add() method. Using this technique we guarantee that the StudentDataAccessCollection.Add() method will add only objects of type StudentDataAccess to the inner private ArrayList object. Also using this technique you can write code that is strongly typed, as we are going to see in the testing page.

The same principle applies to the Remove() method, the property Count and the indexer. We simply choose what we want to provide as part of our collection's functionality by implementing public methods and properties, that use the private ArrayList methods and properties, for storing and retrieving our strongly typed objects.

To use a foreach statement with a StudentDataAccessCollection object you must provide a public method with the following signature:

public IEnumerator GetEnumerator()

It's used by the foreach statement to iterate over the objects of the collection. we simply called the ArrayList.GetEnumerator() to return the enumerator for us. We didn't do anything new other than use the private ArrayList object's methods and properties. Create a new class file in the App_Code folder, name it StudentDataAccessCollection.cs and replace the class template code with the above class definition.

Let's write the GetAllStudents() method and test it.

Creating the GetAllStudents() method

As we have agreed, this method returns an object of type StudentDataAccessCollection so let's see how we can do that. The following is the code for this method.

public static StudentDataAccessCollection GetAllStudents(){
  try{
    using (SqlConnection connection = new SqlConnection(connString)){
      SqlCommand command = new SqlCommand("GetAllStudentsIDs", connection);
      command.CommandType = CommandType.StoredProcedure;

      StudentDataAccessCollection students = new StudentDataAccessCollection();

      connection.Open();
      using (SqlDataReader reader = command.ExecuteReader()){
        while (reader.Read()){
          students.Add(new StudentDataAccess(reader.GetInt32(0)));
        }
      }
      return students;
    }
  }
  catch (Exception ex){
    throw new ApplicationException("An error has occurred.");
  }
}

The method uses a SqlConnection and a SqlCommand object to execute the stored procedure named GetAllStudentsIDs, which returns only the StudentID column from the database. Note that we are not retrieving all the columns; we use the returned StudentID column's values to create the StudentDataAccess objects. As you know, the StudentDataAccess constructor accepts the studentId and communicates with the database in order to populate its fields with the values returned from the database. We accomplish this by using the following code:

while (reader.Read()){
  students.Add(new StudentDataAccess(reader.GetInt32(0)));
}

We simply use the SqlDataReader.Read() method to read the StudentID column's values from the Students table, and use these values as arguments to the StudentDataAccess constructor's parameter to create the StudentDataAccess objects. Those objects are added to the StudentDataAccess collection object using the Add() method as shown above. After that we return that collection object to the caller.

Note that the SqlDataReader.Read() method returns true if there is an available row to read, which means that its inner state is placed before the first row. To read that first row you need to call the method and test for its return value. As long as there is a next row to read it, it returns true; otherwise it returns false. Let's write code to test the method.

Testing the GetAllStudents() method

Replace the code that you have written in the Page_Load() event handler with the following:

StudentDataAccessCollection students = StudentDataAccess.GetAllStudents();
foreach (StudentDataAccess student in students){
  Response.Write("<b>Student ID:<b/> " + student.StudentId +
"<br />" +
"<b>Student First Name:<b/> " + student.FirstName + "<br />" +
"<b>Student Last Name:<b/> " + student.LastName + "<br />" +
"<b>Student Date Of Birth:<b/> " + student.DateOfBirth + "<br />"
+
"<b>Student Admission Date:<b/> " + student.AdmissionDate +
"<br />" +
"<b>Student Major:<b/> " + student.Major + "<br />" +
"<b>Student Active:<b/> " + student.Active + "<br />"
  );
  Response.Write("<br />");
}

Run the page and you will get all the records of the Students table.

We assigned the return object to calling the GetAllStudents() method to an object called students of type StudentDataAccessCollection, then we used a foreach statement to iterate over the StudentDataAccess objects of that students collection and printed out the values of the fields. As you can see, we have accessed each object of the StudentDataAccessCollection in a strongly typed syntax as in student.FirstName and student.LastName.

Note that to return these StudentDataAccess objects we have communicated with the database many times. The first communication happened when we retrieved the StudentID column's values and the other four communications (because we  have four records in the database) took place when we passed the StudentID column's values to the StudentDataAccess constructor to populate the object's fields from the database. This is fine for our example, but imagine that you are executing this code against a database with thousands of records; it would slow down the application's performance. To solve this problem, move on to the next section to see how we handle this situation.

Modifying the application to retrieve all the columns

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();