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.