Writing DB Classes for the web

Since this is a tip, I am making this as short as possible. In essence, this tip utilizes facts from the ASP.NET page life cycle model, garbage collection, and the concept of delegation.

Let's assume you are writing a class that does some database access to find, for e.g.
(a) if an employee actually exists in your organization, and/or
(b) if an employee has access to some department archives, etc.

You'd probably write a helper class where (a) and (b) become two methods of that class.

Suppose you are trying to do both the activities with the same connection object (which is private to your helper class) you'd need two extra method: one which opens a connection to the database, and the other which closes the connection. This approach relies on creating two additional methods.

Alternatively, people tend to traditionally open the connection, do the data access, close the connection; this is done for both operations (a) & (b). Here the system spends time opening the connection, and closing it, each time some method which does data access is called.

But fortunately using those three simple concepts in the beginning, you can now write classes which need only a single connection to do everything. The connection closes automatically, and opens automatically (at the time of constructing).

Here is a simple code example of how to achieve this:

using System.Web.UI

public class EmployeeHelper
{
	public EmployeeHelper(string EmployeeID)
	{
		Page CurrentPage = HttpContext.Current.Handler as Page;
		if(CurrentPage != null)
		{
			//opening connection
			cxn = new SqlConnection(ConfigurationManager.ConnectionStrings["con1"].ConnectionString);
			cmd = cxn.CreateCommand();
			cxn.Open();

			//wiring to the page's unload event here
			CurrentPage.Unload += new System.EventHandler(CurrentPage_Unload);			
		}
		else
		{
			//you need some other way to open and close 
			//	database connections!!!
		}
	}

	SqlConnection cxn;
	SqlCommand cmd;

	void CurrentPage_Unload(object sender, EventArgs e)
	{
		//close connection here
		cxn.Close();
		cxn.Dispose();
	}

	//class methods
	public bool AssertValidEmployee()
	{
		//call backend proc here
		//	no need of opening connection
	}

	public bool AssertDepartmentAccess(int DeptID)
	{
		//call the required backend proc here
		//	no need of opening connection

	}
}