Global DataSet Caching
page 2 of 2
by Phil Winstanley
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 16031/ 27

Caching the Data

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.


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-02-26 2:06:11 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search