A generic loading of data in a DropDownList using caching mechanism

• Download source code - 17.5 Kb

Introduction

When I was trying to learn how to load data into DropDownList from database, initially as novice learner I had to search a lot. Once I have loaded DropDownList with data, there were many implementation issue whether to use DataReader or DataSet. Once done that, there was a question of performance and to provide additional I-text to user in DropDown as “Select Item”. For doing all above, I have to make a around trip to many .NET sites. So I decided to write article giving the entire features which were required while developing any application. I have used the existing database ‘NorthWind’. So you don’t have to create one.

DataAccessLayer

I will start with DataAccessLayer where one can make DataAccess class (DataAccessLayer.cs). This class contains constructors for connection string, SQL query statement etc. as per the requirement of any application. In this class, I have also defined properties ConnectionString and SelectStmOnCat. One can set and get these property from client end. Basically in our case, client is a presentation layer where one can declare the property values and also access them. The code below just gives you highlight of the above features. But I haven’t included this in my code.

DataAccessLayer.DataAccess objDac=new DataAccess();
objDac.ConnectionString=strConn;
objDac.SelectStmOnCat=strSelectCat;

In order to show how one can make use a parameterized constructor, I have used below declaration in presentation layer i.e. DropDown.aspx.cs.

DataAccessLayer.DataAccess objDac=new DataAccess(strConn,strSelectCat);

public class DataAccess
{
    //Private member
    private string strConn;
    private string strSelectCat;
    public DataAccess()
    {
      //
      // TODO: Add constructor logic here
      //Initialization 
      strConn=null;
      strSelectCat=null;
    }
    public DataAccess(string _strConn,string _strSelectCat)
    {
      //parameterized constructor;
      strConn=_strConn;
      strSelectCat=_strSelectCat;
    }
    //set connectionstring property 
    //this has been done just to show how property can be defined 
    
    public string ConnectionString
    {
      get
      {
        return strConn;
      }
      set
      {
        strConn=value;
      }
    
    }
}

Now let's look at the method below:

public DataTable LoadCategoryInDropDownList()
//PreCondition: Take Connection Object, SQL Query.
//PostCondition: Return DataTable containing Category list.
//Process: Fill Datatable with category from NorthWind _db

This method have three logical blocks. So we start with try statement. In this, I have opened a connection with database SQL Server. As you will observe, I have tried to made this process more generic and reusable. I have used “Using statement”. This “Using” statement ensures that once DataAdapter object is made, it will be closed once the required process get completed. It won’t wait for Garbage collector to do so. I have used this so as to enhance the performance in terms of memory.

using(SqlDataAdapter objAdpOnCat=new SqlDataAdapter(strSelectCat,objConn)) 

try
{
  objConn.Open();
  //Using helps to dispose object as soon as 
  //required process gets complete
  using(SqlDataAdapter objAdpOnCat=new SqlDataAdapter (strSelectCat,objConn))
  {
    objAdpOnCat.Fill(tblCategory);
  }
}

I skip the catch block as it is self explanatory.

catch(SqlException e)
{
  throw new Exception("Invalid Connection Error Occured  "+e.Message);
}

Going further down the block, we can see:

finally
{
  if(objConn.State.ToString()=="Open")
  {
    objConn.Dispose();
    objConn.Close();
          
  }
}

In finally block, I have closed the connection object for memory management. It’s a best practice to do so. I hope till now that you are with me.

Complete DataAccessLayer.cs code:

namespace DataAccessLayer
{
  /// <SUMMARY>

  /// Summary description for DataAccessLayer.

  /// This is a generic code for loading data from any database..

  /// One can also use OleDb for more generalized coding....

  /// </SUMMARY>

  public class DataAccess

  {
    //Private member

    private string strConn;

    private string strSelectCat;
    public DataAccess()

    {
      // TODO: Add constructor logic here

      //Initialization 

      strConn=null;

      strSelectCat=null;

    }

    public DataAccess(string _strConn,string _strSelectCat)

    {

      //parameterized constructor;

      strConn=_strConn;//Connection String

      strSelectCat=_strSelectCat;//Sql query statement

    }

    //set connectionstring property 

    //this has been done just to show how property can be defined 
    public string ConnectionString
    {
      get  { return strConn; }
      set  { strConn=value; }
    }
    public string SelectStmOnCat
    {
      get { return strSelectCat; }
      set {    strSelectCat=value;  }
    }

    //PreCondition:Take Connection Object, Sql Query

    //PostCondition:Return DataTable containing Category list

    //Process:Fill Datatable with category from Northwind _db

    public DataTable LoadCategoryInDropDownList()

    {
      SqlConnection objConn=new SqlConnection(strConn);

      DataTable tblCategory =new DataTable();

      try

      {

        objConn.Open();

        //Using helps to dispose object as soon as 

        //required process gets complete

        using(SqlDataAdapter objAdpOnCat=new SqlDataAdapter(strSelectCat,objConn))

        {
          objAdpOnCat.Fill(tblCategory);

        }
      }

      catch(SqlException e)

      {

        throw new Exception("Invalid Connection Error Occured  "+e.Message);

      }

      finally

      {

        if(objConn.State.ToString()=="Open")

        {

          objConn.Dispose();

          objConn.Close();

        }

      }

      return tblCategory;

    }
  }

}

Configuration Settings

I have declared and defined connection string in Web.confiq file so that the same connection string can be used globally across the application. One can also encrypt this connection string and decrypt it .Let's not discuss this right now.

Did you learn anything interesting/fun/annoying while writing the code? Did you do anything particularly clever or wild or zany?

<appSettings>
  <!--   User application and configured property settings go here.-->     
  <!--   Example: <add key="settingName" value="settingValue"/gt -->
  <add key="DSNConn" value="SERVER=ITL232;DATABASE=Northwind;UID=sa;PWD=sa" />

</appSettings>

One can extract connection string using below line of code:

protected static string strConn=
   System.Configuration.ConfigurationSettings.AppSettings["DSNConn"];

Caching: A performance

Now let's look at the code declared in DropDown.aspx.cs file.

using System.Web.Caching;

In order to make use of caching mechanism, include System.Web.Caching in the code. Caching is very useful mechanism one can use enhance the performance of application. I have declared

DataTable tblCategories = (DataTable) Cache["Categories"];

Initially I have assigned a cached table name “Categories” to tblCategories. Now comes the very interesting concepts to follow. It always happens that we leave a current aspx page and gets directed to next aspx page. Sometimes it happens that one has to revisit the same page again with the same data in a page front-end block. For example, I have DropDownList loaded with data from database. Now I leave this page and jump to the next page. It might happen that user wants to come to that page with the dropdown again. In this case, we won’t load data from database but from cache memory instead. So we avoided calling important resources and round trip of opening new connection to database. Thereby we gain a performance rise and through output in our application.

Looking into the code, we find that if cached table is null we load cache with DataTable for an hour from database ‘NorthWind’. Each time the page loads, it checks DataTable in cache memory, if data is there in cache memory it will fetch from there.

Cache.Insert("Categories", tblCategories, null, DateTime.Now.AddHours(1), 
      Cache.NoSlidingExpiration);

Here, you can specify an expiration date in terms of absolute time interval or in terms of interval of time since the last access. Absolute Expiration of 1 hour: DateTime.Now.AddHours(1)

public void Insert ( System.String key , System.Object value , 
       System.Web.Caching.CacheDependency dependencies , 
       System.DateTime absoluteExpiration , 
       System.TimeSpan slidingExpiration , 
       System.Web.Caching.CacheItemPriority priority , 
       System.Web.Caching.CacheItemRemovedCallback onRemoveCallback )
    Member of System.Web.Caching.Cache

Tips: Inserts an object into the System.Web.Caching.Cache object with dependencies, expiration and priority policies, and a delegate you can use to notify your application when the inserted item is removed from the cache.

For the above input parameters refer MSDN.

Here is FetchCategory which loads DropDownList with data.

public void FetchCategory()
  {
    //---------------- 
      
    DataAccessLayer.DataAccess objDac=new DataAccess(strConn,strSelectCat);
      
    DataTable tblCategories = (DataTable) Cache["Categories"];

    if (tblCategories == null) 
    {
      tblCategories = new DataTable();
      tblCategories=objDac.LoadCategoryInDropDownList();
      // It inserts new row in filled datatable.
      //This new row contains static data for user 
      //instruction Text such as" Select the Item"
      DataRow dr=tblCategories.NewRow();
      dr["CategoryID"]=0;
      dr["CategoryName"]="--Select Item--";
      tblCategories.Rows.InsertAt(dr,0);
      //Cache The DataTable in a Cache Memory for duration of one hour...
      Cache.Insert("Categories", tblCategories, null, 
             DateTime.Now.AddHours(1), Cache.NoSlidingExpiration);
    }
      
    //---------------------
  }

Adding Static Instruction text in DropDownList

Now let's look at how we add static Instruction text once dropdown is loaded with data from database. I have created new row in DataTable and inserted at index level zero. Hope this is self explanatory so far.

DataRow dr=tblCategories.NewRow();
dr["CategoryID"]=0;
dr["CategoryName"]="--Select Item--";
tblCategories.Rows.InsertAt(dr,0);

Conclusion

My main objective was to make a learner more accustomed with object oriented way of programming. It’s very important to take performance into consideration when multiple users are connected to the application so as to reduce a bottleneck. This is my first article, hope I have fulfilled some of the expectation of the learner. Any suggestions and criticisms are welcome.