This is the second part of an article on encapsulating your data access into a single class. Read the first part here. This article assumes you know your way around an ASP.NET application at least in a basic sense.

Caching the data for better performance

Modern servers, even the cheap ones, generally have a ton of memory, much of which goes unused. The data that you’re sucking out of the database and serving to hundreds, maybe thousands of users over and over might not change much, but it does take a bit of time to search for it and extract it from the database. That means reading data off of a hard drive and dragging it through the database’s process, then piping it through drivers to .NET so you can display it. Why not store it in memory? It’s considerably faster to retrieve the data from memory.

The System.Web.Caching.Cache class provides us with a powerful means to keep objects in memory to be quickly retrieved and used throughout our application.

Part of the power in this class is its ability to decide when the object in memory is no longer needed. It makes this decision based on the type of cache dependency that you choose. You can base the dependency on changes to a file or directory. That’s great, but it’s a problem for us because our data isn’t coming from a file (not in the literal sense, anyway).

We can also kill off our cached items after a certain amount of time has passed or if the object hasn’t been accessed for a certain amount of time.

All of these methods make data caching difficult, because our data might be changed in the mean time by another page. Obviously we want only the current data to be served to people visiting our site. The key to maintaining this data integrity while caching our data is to only access the database via our data class.

To cache the data, we’ll add a few lines of code to our constructor, and to our Update() and Delete() methods. We don’t have to add any code to the Create() method because at that point, we have no idea if the data will be retrieved by some other page.

Cached objects are organized and retrieved by a key, much in the same way that you find the right portion of a query string or getting a column by name from a DataReader. We’ll name our cached customer objects “UberCustomer” plus the primary key of the record we retrieve. So for example, if my customer record’s CustomerID column has a value of 216, the cached object will be named “UberCustomer216.”

We’ll start by adding a single “if” statement to our constructor, checking to see if the cached object exists. If it does, we’ll load those values into our properties. If it doesn’t exist, we’ll get the data from the database and insert it into the cache. The new code looks like this:

C#

public Customer(int CustomerID)
{
	HttpContext context = HttpContext.Current;
	if ((context.Cache["UberCustomer" + CustomerID] == null))
	{
		SqlConnection objConnection = new SqlConnection(_ConnectionString);
		objConnection.Open();
		SqlCommand objCommand = new SqlCommand("SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, Phone, SignUpDate WHERE CustomerID = @CustomerID", objConnection);
		objCommand.Parameters.Add("@CustomerID", CustomerID);
		SqlDataReader objReader = objCommand.ExecuteReader();
		if (objReader.Read())
		{
			_CustomerID = objReader.GetInt32(0);
			_LastName = objReader.GetString(1);
			_FirstName = objReader.GetString(2);
			_Address = objReader.GetString(3);
			_City = objReader.GetString(4);
			_State = objReader.GetString(5);
			_Zip = objReader.GetString(6);
			_Phone = objReader.GetString(7);
			_SignUpDate = objReader.GetDateTime(8);
		}
		else PopulateDefault();
		objReader.Close();
		objConnection.Close();
		context.Cache.Insert("UberCustomer" + _CustomerID.ToString(), this, null, DateTime.Now.AddSeconds(60), new TimeSpan(0));
	}
	else
	{
		Customer objCustomer = (Customer)context.Cache["UberCustomer" + CustomerID];
		_CustomerID = objCustomer.CustomerID;
		_LastName = objCustomer.LastName;
		_FirstName = objCustomer.FirstName;
		_Address = objCustomer.Address;
		_City = objCustomer.City;
		_State = objCustomer.State;
		_Zip = objCustomer.Zip;
		_Phone = objCustomer.Phone;
		_SignUpDate = objCustomer.SignUpDate;
	}
}

VB .NET

Public Sub New(CustomerID As Integer)
   Dim context As HttpContext = HttpContext.Current
   If context.Cache(("UberCustomer" + CustomerID)) Is Nothing Then
      Dim objConnection As New SqlConnection(_ConnectionString)
      objConnection.Open()
      Dim objCommand As New SqlCommand("SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, Phone, SignUpDate WHERE CustomerID = @CustomerID", objConnection)
      objCommand.Parameters.Add("@CustomerID", CustomerID)
      Dim objReader As SqlDataReader = objCommand.ExecuteReader()
      If objReader.Read() Then
         _CustomerID = objReader.GetInt32(0)
         _LastName = objReader.GetString(1)
         _FirstName = objReader.GetString(2)
         _Address = objReader.GetString(3)
         _City = objReader.GetString(4)
         _State = objReader.GetString(5)
         _Zip = objReader.GetString(6)
         _Phone = objReader.GetString(7)
         _SignUpDate = objReader.GetDateTime(8)
      Else
         PopulateDefault()
      End If
      objReader.Close()
      objConnection.Close()
      context.Cache.Insert("UberCustomer" + _CustomerID.ToString(), Me, Nothing, DateTime.Now.AddSeconds(60), New TimeSpan(0))
   Else
      Dim objCustomer As Customer = CType(context.Cache(("UberCustomer" + CustomerID)), Customer)
      _CustomerID = objCustomer.CustomerID
      _LastName = objCustomer.LastName
      _FirstName = objCustomer.FirstName
      _Address = objCustomer.Address
      _City = objCustomer.City
      _State = objCustomer.State
      _Zip = objCustomer.Zip
      _Phone = objCustomer.Phone
      _SignUpDate = objCustomer.SignUpDate
   End If
End Sub

We start the new version of the constructor by checking to see if there’s an existing cache object that corresponds to the record we’re looking for. Because our class has absolutely no clue that it’s being used in a Web application, we first create an HttpContext object to reference, in this case, HttpContext.Current.

If there is no cached object, everything proceeds as before, save for the very last line. We call the Insert method of the cache object, which takes a number of parameters. (There are a number of overloads for the Insert method, but this one offers the most control for our purposes. Consult the .NET documentation for more information on the other overloads.)

context.Cache.Insert("UberCustomer" + _CustomerID.ToString(), this, null, DateTime.Now.AddSeconds(60), new TimeSpan(0));

The first parameter is a string to name the cache entry. As we mentioned earlier, it’s a combination of “UberCustomer” and the CustomerID value. The second parameter is “this” (or “Me” in VB), which is the instance of the class itself. That means that all of the values assigned to the class’ properties are stored in the cache. The third parameter is for a CacheDependency object, and in our case we’re using null (Nothing in VB) because we’re not tying any dependency to the cached object.

The fourth parameter is the time at which we want the cached object to be removed from memory, an absolute expiration time. The fifth parameter is a sliding expiration time expressed as the TimeSpan that passes without the cached object being accessed. That means an object could live indefinitely if it’s accessed over and over. Since we’ve already set an absolute expiration, we must set this to a new TimeSpan object that indicates zero time.

These are values that you have to experiment with to decide how much memory you want to use. If you write a number of different data classes similar to this one, you may want to store a value in web.config that indicates the number of seconds (or minutes, hours, whatever you wish), so you can change the setting all from one place.

If the object has been cached, it’s easy enough to retrieve those values and assign them to our private class members. We create a new Customer object and fill it with the cached version. We have to cast the object to the Customer type, because the type returned by the Cache object is of the type System.Object.

This is a point of confusion for some developers, because we’re creating an instance of the class from within the class, then assigning its properties to the private members of the class instance we’re working in. Getting this cached data will save a lot of trips to the database, but we need to devise a way to make sure that we always have current data. If another user loads the data and changes it by calling the Update() method, or deletes it with the Delete() method, we have to remove the cached data so it isn’t loaded, and is instead loaded from the database. This is accomplished with a private method that uses the Cache’s Remove method:

C#

private void DeleteCache()
{
	if (HttpContext.Current.Cache["UberCustomer" + _CustomerID] != null)
		HttpContext.Current.Cache.Remove("UberCustomer" + _CustomerID);
}

VB .NET

Private Sub DeleteCache()
   If Not (HttpContext.Current.Cache(("UberCustomer" + _CustomerID)) Is Nothing) Then
      HttpContext.Current.Cache.Remove(("UberCustomer" + _CustomerID))
   End If
End Sub

Again, we reference the HttpContect.Current object. First we see if the object exists, and if it does, we call the Remove method which looks for the object by its key name.

We’ll need to call the DeleteCache() method from both the Update() and Delete() methods. It’s as simple as adding one line to both of the methods:

C#

DeleteCache();

VB .NET

DeleteCache()

As long as we access the database through this class only, we will always have current data. The only way the data is cached is if the constructor is called. If that data is changed with the Update() or Delete() methods, the cached data is removed, so the next call to the constructor for the same record must go back to the database.

Getting more than one record at a time

This model for a data class might be great for getting one record, but what happens when you need to get a group of records? Fortunately we can group these data objects together in an ArrayList, a structure that is often less complicated and less work for .NET to create and maintain compared to a DataTable or DataSet, which internally must create many row and columns objects.

Continuing on with our example, let’s say that we frequently need to look up groups of customers by their Zip code. We’ll add a static (shared) method to the class that takes a single parameter, the Zip code, and search the database for matching records. The method will return an ArrayList of Customer objects. The ArrayList class is in the System.Collections namespace, so we need to add a using statement (Imports in VB) to the top of our class file.

C#

public static ArrayList GetCustomersByZip(string Zip)
{
	SqlConnection objConnection = new SqlConnection("server=(local);database=test;Integrated Security=SSPI");
	objConnection.Open();
	SqlCommand objCommand = new SqlCommand("SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, Phone, SignUpDate WHERE Zip = @Zip ORDER BY LastName, FirstName ", objConnection);
	objCommand.Parameters.Add("@Zip", Zip);
	SqlDataReader objReader = objCommand.ExecuteReader();
	// create the ArrayList that the method will return
	ArrayList alList = new ArrayList();
	while (objReader.Read())
	{
		// create a new customer object
		Customer objCustomer = new Customer();
		// assign the database values to the object's properties
		objCustomer.CustomerID = objReader.GetInt32(0);
		objCustomer.LastName = objReader.GetString(1);
		objCustomer.FirstName = objReader.GetString(2);
		objCustomer.Address = objReader.GetString(3);
		objCustomer.City = objReader.GetString(4);
		objCustomer.State = objReader.GetString(5);
		objCustomer.Zip = objReader.GetString(6);
		objCustomer.Phone = objReader.GetString(7);
		objCustomer.SignUpDate = objReader.GetDateTime(8);
		// add the customer object to the ArrayList
		alList.Add(objCustomer);
	}
	objReader.Close();
	objConnection.Close();
	// return the finished ArrayList with customer objects
	return alList;
}

VB .NET

Public Shared Function GetCustomersByZip(Zip As String) As ArrayList
   Dim objConnection As New SqlConnection("server=(local);database=test;Integrated Security=SSPI")
   objConnection.Open()
   Dim objCommand As New SqlCommand("SELECT CustomerID, LastName, FirstName, Address, City, State, Zip, Phone, SignUpDate WHERE Zip = @Zip ORDER BY LastName, FirstName", objConnection)
   objCommand.Parameters.Add("@Zip", Zip)
   Dim objReader As SqlDataReader = objCommand.ExecuteReader()
   ' create the ArrayList that the method will return
   Dim alList As New ArrayList()
   While objReader.Read()
      ' create a new customer object
      Dim objCustomer As New Customer()
      ' assign the database values to the object's properties
      objCustomer.CustomerID = objReader.GetInt32(0)
      objCustomer.LastName = objReader.GetString(1)
      objCustomer.FirstName = objReader.GetString(2)
      objCustomer.Address = objReader.GetString(3)
      objCustomer.City = objReader.GetString(4)
      objCustomer.State = objReader.GetString(5)
      objCustomer.Zip = objReader.GetString(6)
      objCustomer.Phone = objReader.GetString(7)
      objCustomer.SignUpDate = objReader.GetDateTime(8)
      ' add the customer object to the ArrayList
      alList.Add(objCustomer)
   End While
   objReader.Close()
   objConnection.Close()
   ' return the finished ArrayList with customer objects
   Return alList
End Function

At first glance, this new method looks a lot like our constructor. The first difference comes in that we’re using a static (shared) method. We can call these methods without instantiating the class. To look up customers in the 44114 Zip code, for example, we’d need only one line in our calling code:

ArrayList alList44114 = Customer.GetCustomersByZip(“44114”);

The next difference is in our SQL statement. This time we’re looking for records where the Zip is matched to the parameter we’ve passed in. Since the Zip column of the database is not our primary key and may not be unique, we may get several records.

Just after we execute the DataReader, we create an ArrayList object. This will be the container for our Customer objects. Using a while loop, we go through each record returned by the database using the DataReader's Read() method, creating a Customer object each time, assigning the database values to the object’s properties, then adding that Customer object to the ArrayList. When we’re done and we’ve cleaned up our connection, we return the ArrayList populated with Customer objects.

There are a few other changes we need to make. First, our CustomerID property can’t be read only, because we need to assign data to it. We revise it to include the “set” portion of the property:

C#

public int CustomerID
{
	get {return _CustomerID;}
	set {_CustomerID = value;}
}

VB .NET

Public Property CustomerID() As Integer
   Get
      Return _CustomerID
   End Get
   Set
      _CustomerID = value
   End Set
End Property

The other change is that our static method doesn’t know anything about the string _ConnectionString, because the rest of the class hasn’t been instantiated. We’ve included the string here right in the code, but a better practice is to store it elsewhere, perhaps in web.config, instead of hard-coding it.

The big surprise for a lot of people is that your wonderful new ArrayList can be bound to a Repeater control, and you can access the properties of the Customer objects just as if you bound a DataReader or DataTable. That’s because the ArrayList implements the IEnumerable interface, just as the DataReader and DataTable. As long as your ArrayList contains all of the same objects, in this case Customer objects, there’s nothing more to do. Your Repeater’s ItemTemplate might look something like this:

<ItemTemplate>
	<p><%# DataBinder.Eval(Container.DataItem,"LastName") %>,
	<%# DataBinder.Eval(Container.DataItem,"FirstName") %></p>
</ItemTemplate>

You can cache these result sets as well, by putting the finished ArrayList into the cache using a name like “UberCustomerList44114” in this case. However, you’ll have to add more plumbing to the Update() and Delete() methods, as well as the Create() method to remove any customer ArrayLists being cached if the Zip matches. Otherwise, the cached ArrayList wouldn’t have a new record (or would include a deleted record) of a customer with a 44114 Zip.

©2010, POP World Media, LLC. All rights reserved
Legal, privacy, terms of service