It’s long been a problem on websites, when you’re just trying to bind up a drop down list, or repeater with some content that is not likely to change by the second, that you’re hitting the database server with requests that in reality are not needed.

 

Caching is sometimes seen as a hard subject to master, hopefully this small article will be able to allay some fears that people have over implementing Data Caching, I will not discuss the issues surrounding the risk taken of stale data, that is for another day.

 

In this Article I will use C# and the Intrinsic Caching functionality built in to ASP.NET.

 

What this method revolves around is storing the Parameters for your stored procedures inside a HashTable object.

 

Creating our Parameter Storage area.

 

Hashtable Parameters = new Hashtable();

Parameters.Add("Phil","Rocks");

Parameters.Add("Steve","Smells");

Parameters.Add("Alex",3);

 

 

There we have the Parameters that we’ll use to execute against a stored procedure in our Database, lovely I think you will agree (or else).

 

Now we need something to put our Data in, I was thinking a Dataset, so here’s one.

 

DataSet PhilsDataSet = new DataSet();

 

Let’s create a method to get our Data from the Database and in to the DataSet.

 

public void FillDataSet(DataSet Ds, string DataMember, string StoredProcedure, Hashtable Parameters)

{

 

       //Set up a Connection Object

       SqlConnection Conn = new SqlConnection("someconnectionstring");

 

       //Set up a Command Object

       SqlCommand Comm = new SqlCommand(StoredProcedure,Conn);

       Comm.CommandType = CommandType.StoredProcedure;

 

       //Add the Parameters to the Command Object

       Comm = ConvertToParameters(Comm,Parameters);

 

       //Set up a SqlDataAdapter to do some Jiggery Pokery (technical term) with the SqlServer Results Set

       SqlDataAdapter Da = new SqlDataAdapter();

 

       //Fill the DataSet Table with the Results

       Da.Fill(Ds,DataMember);

 

}

 

Notice we pass the Hashtable on to another method which automagically (yes another technical term) adds them in to the Command object.

 

 

public SqlCommand ConvertToParameters(SqlCommand Comm, Hashtable Parameters)

{

 

       // Add the Parameters that are left in the hashtable

       foreach (object key in Parameters.Keys)

       {

             

 

              // If the type of the key is an ArrayList then were going to send its contents as a Comma seperated list as the parameter to the command

              if (Parameters[key].GetType() == typeof(ArrayList))

              {

                     ArrayList innerArray = (ArrayList)Parameters[key];

                     string values = null;

                     foreach (object innerItem in innerArray)

                     {

                           if (values == null)

                           { // its the first item

                                  values = innerItem.ToString();

                           }

                           else

                           { // otherwise stick a comma on

                                  values += "," + innerItem.ToString();

                            }

                     }

 

                     if (values != null)

                     {

                           Comm.Parameters.Add("@" + key.ToString(), values);

                     }

 

              }

              else

              {

                     Comm.Parameters.Add("@" + key.ToString(), Parameters[key]);

              }

 

      

       }

 

       return Comm;

 

}

 

Ok so now we can fill a DataTable in a DataSet from our method using the Parameters collection (Hashtable).

 

What this doesn’t have in is the Caching we’re actually looking for, so what we’ll do is include this now. First we need a method that will take the Hashtable and turn it in to a Key that is unique for the Parameters and values within it.

 

public string CriteriaString(Hashtable Parameters)

{

 

       StringBuilder CacheKey = new StringBuilder();

 

       if (Parameters != null)

       {

 

              ArrayList MagicArray = new ArrayList();

 

              foreach (object key in Parameters.Keys)

              {

                     MagicArray.Add(key);

              }

 

              MagicArray.Sort();

 

              foreach(String Key in MagicArray)

              {

 

                     //We handle Arrays in a special way, we CSV them

                     if (Parameters[Key].GetType() == typeof(ArrayList))

                     {

                           ArrayList innerArray = (ArrayList)Parameters[Key];

                           innerArray.Sort();

                           string values = null;

                          

                           //Itterate through the items

                           foreach (object innerItem in innerArray)

                           {

                                  if (values == null)

                                  { // its the first item

                                         values = innerItem.ToString();

                                  }

                                  else

                                  { // otherwise stick a comma on

                                         values += "," + innerItem.ToString();

                                  }

                           }

 

                           //Lets add it to the StringBuilder

                           if (values != null)

                           {

                                  CacheKey.Append(Key.ToString().ToLower() + "=" + values + ";");

                           }

 

                     }

                     else // It's a normal key/value par so we treat it normally

                     {

                           CacheKey.Append(Key.ToString().ToLower() + "=" + Parameters[Key].ToString().ToLower() + ";");

                     }

              }

 

       }

 

       return CacheKey.ToString();

 

}

 

“Whoopie” I hear you cry. Well, now we can use the string that this will create as a unique key for our Caching.

 

Here’s a modified version of the Fill method: -

 

public void FillDataSet(DataSet Ds, string DataMember, string StoredProcedure, Hashtable Parameters)

{

 

       //Add the Table to the DataSet if it's not there

       if (Ds.Tables[DataMember] == null)

       {

              Ds.Tables.Add(DataMember);

       }

       else

       {

              //Clear the Current Rows

              Ds.Tables[DataMember].Rows.Clear();

       }

 

       if (Cache[DataMember + ":" + CriteriaString(Parameters)] != null)

       {

 

              //Retreive the Cached DataSet

              DataTable Cached = ((DataTable)Cache[DataMember + ":" + CriteriaString(Parameters)]);

 

              //Add the Cached Rows

              foreach(DataRow Dr in Cached.Rows)

              {

                     Ds.Tables[DataMember].ImportRow(Dr);

              }

 

       }

       else

       {

 

              //Set up a Connection Object

              SqlConnection Conn = new SqlConnection("someconnectionstring");

 

              //Set up a Command Object

              SqlCommand Comm = new SqlCommand(StoredProcedure,Conn);

              Comm.CommandType = CommandType.StoredProcedure;

 

              //Add the Parameters to the Command Object

              Comm = ConvertToParameters(Comm,Parameters);

 

              //Set up a SqlDataAdapter to do some Jiggery Pokery (technical term) with the SqlServer Results Set

              SqlDataAdapter Da = new SqlDataAdapter();

 

              //Fill the DataSet Table with the Results

              Da.Fill(Ds,DataMember);

 

              //Add the DataSet to the Cache

              Cache.Add(DataMember + ":" + CriteriaString(Parameters),Ds.Tables[DataMember],null,DateTime.Now.AddMinutes(30),System.TimeSpan.Zero,System.Web.Caching.CacheItemPriority.Normal,null);

 

       }

 

}

 

There you have it, Cached data with the parameters passed in as the key for your cache items.