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;
}