Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Optimizing Your Application - Part 2

By Dinesh Priyankara,

My article Optimize your application spoke about web-based application and this is the time to speak about windows-based application. Couple of questions had been arisen regarding first article and I hope that all your questions have been answered now.

Obviously we cannot apply caching to windows-based application. But we can use a collection object to store all data what we need. The ideal one is HashTable that is a dictionary type collection (implemented with IDictionary interface) that associates a key to a value and it is optimized dictionary for fast retrieval (Read more about HashTable) .The below code show you the way of doing it.

public class Common
{
	public Common()
	{
	}
		
	//Hash table to keep combo items
	static Hashtable dropdownItems = Hashtable.Synchronized(new Hashtable());
		
	//Add dropdownItems
	public static void AddDropDownItems(string name, object item)
	{
		dropdownItems.Add(name, items);
	}
		
	//Get dropdownItems
	public static object GetDropDownItems(string name)
	{
		if (dropdownItems.Contains(name))
			return dropdownItems[name];
		else
			return null;
	}
}

You can see that this is a simple class named Common that holds data for drop-down lists in HashTable named dropdownItems. There are two static methods that are used to add drop-down list data to HashTable and get data for drop-down list. Now let's see the way of using it.

Let's say that we have a windows form that has drop-down list for employee's names. The natural way of loading employee data into drop-down list is, make a connection to the SQL Server and fill data to the data set, and bind the data table to the drop-down list. Your code might looks like this.

SqlConnection sqlConnection = new SqlConnection("Server=(local);Database=Northwind;Uid=sa;Pwd=");
sqlConnection.Open();
						
SqlCommand sqlCommand = new SqlCommand("SELECT EmployeeID, LastName FROM Employees", sqlConnection);
DataSet dataSet = new DataSet();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
						
sqlDataAdapter.Fill(dataSet, "Employees");
DropDownListEmployees.DataSource = dataSet.Tables["Employees"];

In order to stop the re-querying for same data, let's store data in HashTable by using Common class we created. Here is the code for it.

Common.AddDropDownItems("Employees", dataSet.Tables["Employees"]);

The method AddDropDownItems accepts two parameters: name and item. The parameter "name" is used as key for HashTable and "item" is the value for the key. You can see that we send employee's data to the HashTable with the key named "Employees". That's all. If same data is needed again (for same win-form or different win-form), you can simply get data from HashTable without re-querying the database. Here is the complete code.

//Get data from hashtable
object dropdownItems = Common.GetDropDownItems("Employees");

//If data is not available in hashtable
if (dropdownItems==null)
{
	SqlConnection sqlConnection = new SqlConnection("Server=(local);Database=Northwind;Uid=sa;Pwd=");
	sqlConnection.Open();
			
	SqlCommand sqlCommand = new SqlCommand("SELECT EmployeeID, LastName FROM Employees", sqlConnection);
	DataSet dataSet = new DataSet();
	SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
			
	sqlDataAdapter.Fill(dataSet, "Employees");
	DropDownListEmployees.DataSource = dataSet.Tables["Employees"];
	
	//Save data in hashtable for future reference
	Common.AddDropDownItems("Employees", dataSet.Tables["Employees"]);
}
else
	DropDownListEmployees.DataSource = dropdownItems;
	
DropDownListEmployees.DisplayMember= "LastName";
DropDownListEmployees.ValueMember = "EmployeeID";

How simple is this? Again, I must say that do not apply this scenario for data that are frequently modified because then you need to remove old data from HashTable and add new data to the HashTable. I think that this is ideal for data that loads into to drop-down. Ok, it is up to you to decide to what type of data should be applied this scenario. Do it and see. If you have questions with this, please let me know. Enjoy.

Total article views: 6925 | Views in the last 30 days: 0
 
Related Articles
FORUM

Reusing SQLCommand

How to use 1 SQLCommand for many set of parameter values

FORUM

Reporting services - datasets

Datasets

FORUM

DataSet

DataSet and List

FORUM

Dataset doubt

Dataset doubt

Tags
miscellaneous    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones