All the time i seem to come across example code on sites and various thing. Unfortunatly a large number of people tend to think that this is just how things are done in the real world. So time and time again i seem to come across projects where the same db connection code (as an example) is copied and pasted around all the pages in a project. This of course is just causing a mainantice problem for later.

A quick example of what i am talking about is below.

SqlConnection conDotNet = new SqlConnection(args);  

string sSQL = "Select sub_category_id, sub_category_text  
	from Sub_Category"; 

SqlCommand cmd = new SqlCommand(sSQL, conDotNet);  
conDotNet.Open();  
SqlDataReader dtrCat = cmd.ExecuteReader();  

catlist.DataSource = dtrCat;  
catlist.DataBind();

When i first started working in c# it was based around both windows forms and asp.net. On of the first steps that i did was to create a class lib that could be shared between the projects and all future projects for both windows forms and asp.net application. Later on it has also been applied to windows services and other automated systems that run automatically.

The very first thing I ended up creating because i knew that i was going to be using it *a lot* was a data base connection class called DBConn. It supports the following methods and properties.

interface IDBConn
    {
        void Dispose();
        int Execute(System.Data.IDbCommand Sql);
        int Execute(System.Data.SqlClient.SqlCommand Sql);
        int Execute(string sql);
        DataRow ExecuteDataRow(System.Data.IDbCommand Sql);
        DataRow ExecuteDataRow(System.Data.SqlClient.SqlCommand Sql);
        DataRow ExecuteDataRow(string Sql);
        DataSet ExecuteDataSet(System.Data.IDbCommand Sql);
        DataSet ExecuteDataSet(System.Data.SqlClient.SqlCommand Sql);
        DataSet ExecuteDataSet(string Sql);
        DataTable ExecuteDataTable(System.Data.IDbCommand Sql);
        DataTable ExecuteDataTable(System.Data.SqlClient.SqlCommand Sql);
        DataTable ExecuteDataTable(string Sql);
        IDataReader ExecuteReader(System.Data.IDbCommand Sql);
        SqlDataReader ExecuteReader(System.Data.SqlClient.SqlCommand Sql);
        object ExecuteScalar(System.Data.IDbCommand Sql);
        object ExecuteScalar(System.Data.SqlClient.SqlCommand Sql);
        object ExecuteScalar(string sql);
        System.Data.SqlClient.SqlConnection SqlConnection { get; }
    }

The implementation of the class is pretty straight forward. There is also some thread safe locking involved in the functions to make them work a little better in a mutli threaded enviroment. However the real trick is what todo with the class at the page level.

I use the current solution layout in asp.net c# for every project i work on.

• The main web site project
• A class lib specific to the web site project.
• Multiple shared class lib's between projects.

The DBConn in this case is stored in the shared project lib. Along with some other treats to speed up development. It will also contine the a BasePage, BaseMasterPage, BaseUserControl, BaseHandler class's which are going to be inherited by App specific classes in the project class lib. Which are called AppPage, AppMasterPage etc.. Each type of page / component in the main project file then inherits the app specific classes.

So this forms a nice code layout in the following layer's. Using Default.aspx as a starting example.

Default -> AppPage -> BasePage -> System.Web.UI.Page

Each of these base classes contain a basic level of code for connecting to database's using a default connection string which is stored in the web config. The nice part about this is that the functionality can be changed in any of the layer's at any time. So as you get more page specific you can extend the functionality that you like per project.

This is where the likes of DBConn comes in. Since it's already setup and working at the most base class. We can access it at the page level while writting minimum code. So once a project is setup in this way i can simply render things onto a webpage in the following way all over my project (give that repList is a repeater control)

repList.DataSource = DBConn.Execute("SELECT * FROM TableX ORDER BY CreatedOn DESC");
repList.DataBind();

with this sort of setup you can imaging just how quickly it is possible to create new pages in you project to extend functionality. Allowing more time to focus on layout and functionality rather than fighting with lots and lots of the same code repeating all over your projects.