Data Access Layer Architecture

Data Access Layer Architecture

by JBrooks 23. November 2010 10:59

I wanted to document how we do our DAL.  First, performance is important so we use ADO.NET instead of the Entity Framework, NHibernate, etc.   Second, in our main project we have 1,743 stored procs  and counting, so a stored proc centered approach is necessary. 

So over the years I have reduced it down so that the following is possible:

 
protected void Page_Load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
        {
            this.GridView1.DataSource = cApp.DB.getIPAddresses();
            this.GridView1.DataBind();
        }
    }
}

Normally you would have a business layer in there, but you get the idea from this sample.  Encapsulated in that one call is the logic to get the connection string, open and close connections, log exceptions, etc. 

Here is how access to the method works.  We have a central class called cApp that contains a lot of stuff having to do with the application in general.  One of those items is the database object.

 
using System.Configuration;
 
namespace SampleApp
{
    public sealed class cApp
    {
 
        static readonly SampleApp.cDB _cDB = new SampleApp.cDB(
            ConfigurationManager.ConnectionStrings["SampleAppConnString"].ToString());
        
        public static cDB DB
        {
            get
            {
                return _cDB;
            }
        }
       
 
        static cApp()
        { }
    }
}

 

The thing to note here is that there is only every one database object, it is created in a thread safe way, and it stays around for the full length of the application.  Since the applications that I develop are almost always data driven applications I didn’t think it made much sense to create,destroy and garbage collect a data object for every request.  Nothing performs faster than the work that you don’t do.

So what does this cDB class look like?   Here is a sample with only a few methods.

 
using System;
using System.Data;
using System.Data.SqlClient;
 
namespace SampleApp
{
    public class cDB : DBLib.cDBBase
    {
        public cDB(string ConnString)
            : base(ConnString)
        { }
 
        public cDB()
            : base()
        { }
 
 
        public DataTable getIPAddresses()
        {
            SqlCommand cmd = GetNewCmd("dbo.getIPAddresses");
            return GetDataTable(cmd);
        }
 
        public void saveIPAddresses(Int32 id, String UserName, String IPAddress, String editBy)
        {
            SqlCommand cmd = GetNewCmd("dbo.saveIPAddresses");
 
            cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 150).Value = UserName;
            cmd.Parameters.Add("@IPAddress", SqlDbType.VarChar, 50).Value = IPAddress;
            cmd.Parameters.Add("@editBy", SqlDbType.VarChar, 150).Value = editBy;
 
            ExecuteNonQuery(cmd);
        }
 
        public DataTable getExceptions(DateTime exTime)
        {
            SqlCommand cmd = GetNewCmd("dbo.getExceptions");
            cmd.Parameters.Add("@exTime", SqlDbType.DateTime).Value = exTime;
 
            return GetDataTable(cmd);
        }
 
 
    }
}

We follow the pattern where the method name and parameters are the same as the stored procs.   I had previously blog about how I automatically generate the code for these methods based on the stored procs HERE , which really helps when there are a lot of parameters. 

Most of the work is found in the class that the cDB class inherits from, DBLib.cDBBase.  It is a class I use in every project and it has methods for getting DataTables, Integers, GUIDs, etc.  It will log exceptions to a table and then fail over to a text file if needed.  

You can download a sample application that has the full source code including cDBBase HERE

Tags:

Development | General