Introduction:

When I was trying to learn how to load data into dropdownList from database, initially as novice learner I have 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 feature one required while developing any application. I have used existing Database ‘Northwind’. So you don’t have to create one.

Flow of Logic

Here is DropDown.aspx.cs file description

private void Page_Load(object sender, System.EventArgs e)
		{
			// Put user code to initialize the page here
			if(!IsPostBack)
			{
				FetchCategory();//Load Dropdown with data
			}
			else
			{
			//add any process if required on PostBack Condition
			}
		}

public void FetchCategory()
		{
			//Here is DataAccess class under namespace DataAccesslayer

			
			DataAccessLayer.DataAccess objDac=new DataAccess(strConn,strSelectCat);
			//In order to send parameter through property 
//use
// DataAccessLayer.DataAccess objDac=new DataAccess();
	//objDac.ConnectionString=strConn;
	//objDac.SelectStmOnCat=strSelectCat;

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

			if (tblCategories == null) 
			{
				tblCategories = new DataTable();
//Load data from Database
				tblCategories=objDac.LoadCategoryInDropDownList();
//now add static Instruction text into Datatable
				// 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);
			}
			
			//---------------------
			

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 tabled named “Categories” to tblCategories.

Now comes very interesting concepts to follow. It always happen we leave a current aspx page and gets directed to next aspx page. Sometimes it happens 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 next page. It might happen user wants to come to that page again one with dropdown. 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 page load it checks datatable in cache memory, if data is there in cache memory it will fetch from there.

Include directive of this class in main form:

This class takes two parameter connection string and Sql query string
The method inside this class load DataTable with data from Database and return DataTable .

Here is DataAccessLayer.cs file

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;
//Connection String 
			strConn=_strConn;
//Sql Query String 
			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;
			}
		
		}
}
//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;
		
		
		}

	}
}