Global DataSet Caching
page 1 of 2
Published: 29 Apr 2004
Unedited - Community Contributed
Abstract
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.
by Phil Winstanley
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 30901/ 43

Creating Cache Items that are Dependant on your Stored Procedure Parameters

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;

 

}

 


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





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


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 4/17/2014 5:24:01 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search