Click here to Skip to main content
Click here to Skip to main content
Go to top

A generic loading of data in a DropDownList using caching mechanism

, 6 Apr 2005
Rate this:
Please Sign up or sign in to vote.
This article shows you how to make use of DataAccessLayer and caching.

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
{
  /// <span class="code-SummaryComment"><SUMMARY>
</span>

  /// Summary description for DataAccessLayer.

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

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

  /// <span class="code-SummaryComment"></SUMMARY>
</span>

  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>
  <!--<span class="code-comment">   User application and configured property settings go here.--></span>     
  <!--<span class="code-comment">   Example: <add key="settingName" value="settingValue"/gt --></span>
  <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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

santosh poojari
Technical Lead
India India
Whatsup-->Exploring--> MVC/HTML5/Javascript & Virtualization.......!
www.santoshpoojari.blogspot.com

Comments and Discussions

 
GeneralThread Synchronization PinmemberAshaman12-Apr-05 1:42 
GeneralRe: Thread Synchronization PinmemberGroucho Marx13-Apr-05 5:35 
GeneralRe: Thread Synchronization PinmemberAshaman13-Apr-05 8:14 
GeneralRe: Thread Synchronization PinmemberGroucho Marx13-Apr-05 9:02 
GeneralRe: Thread Synchronization PinmemberAshaman14-Apr-05 2:29 
GeneralBadly formatted. PinmemberDennis Nusser6-Apr-05 4:26 
GeneralRe: Badly formatted. Pinmembersantosh poojari7-Apr-05 0:56 
GeneralRe: Badly formatted. PinmemberDennis Nusser7-Apr-05 4:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 6 Apr 2005
Article Copyright 2005 by santosh poojari
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid