General

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

Forgotten Windows Password Recovery and the "no tables found" error.

by JBrooks 4. January 2010 17:59

A friend of mine brought over his laptop because he couldn't remember his new Windows Vista login password. I said I would work on it and worst case I would have to clear everything and reinstall Vista.

Googling around I found that there were tools for clearing the password. I downloaded and burned to a CD the tool here: here at pogostick.net and later Ophcrack found here. Both are free if you are just clearing a password.

Obviously, I first cleared all of the BIOS based passwords (he did remember these). Then I tried to run the 2 tools listed above. The first one couldn't mount the hard drive and the second gave the error "no tables found".

So I Googled for this error. Found 1 comment that said something about not having a drive for the controller card... I see that this is Linux, so maybe the drive isn't there for my friends laptop's controller card, after all, it is a cheap eMachine. (I'm not a Linux guy so I'm not even sure how this works.) So I pop the hard drive out of his laptop and put it in my laptop.

I then booted and tried the Ophcrack CD and it worked. Case closed.

Tags:

General

Release Manager for Subversion

by JBrooks 27. June 2009 06:36

Getting close to being done my release manager.

I got the video done, it can be seen here: http://www.ReleaseManager.com

Any comments would be appreciated!

Tags:

Development | General