Constructing a DAL in C#


I’ve been asked many times why someone might write their own DAL given that there exist so many frameworks that can provide this functionality already.

And the truth is – you may or may not need to.

There are a lot of available DAL’s and frameworks out there that can provide many of the common features of database platform and type abstraction, but you (as I did) may have certain needs that aren’t fulfilled by these frameworks.

Examples of some custom tasks that aren’t readily covered by some frameworks might be:

  • Parsing and insertion of custom data in SQL statements
  • Syntax abstraction in SQL statements
  • Auto-population of application specific data (e.g. DAL knowledge of some schema or application mechanisms)
    While any of the above, or other, tasks can likely be accomplished with most frameworks through some level of effort, the number one reason in my mind to write your own DAL at some point is to gain a deeper understanding of what is happening beneath the covers when using a DAL – either through a framework or perhaps an existing for a project you are working on.
    Gaining a thorough understanding of the tasks required to be abstracted and performed by the DAL on your behalf help developers to know how to effectively use a DAL, as well as increase their own knowledge in this area.

And that is what this post is about – the creation of a simple DAL as a learning tool. I’ll talk about some of the work we’ve put into a DAL for a project I’ve worked on and some of the useful features that you may want consider to make your life a bit easier should you choose to write your own DAL.

I thought that giving some sample code of connecting to a SQL and Oracle database from the same client would serve as a nice reference point to start out discussion.

Here are the current hoops we have to jump through to read some simple data and get a DataTable back.

string sqlConnectionString = "Data Source=localhost;Initial Catalog=DALDatabase;Integrated Security=SSPI";
string oraConnectionString = "Data Source=localhost;User Id=DALDatabase;Password=password;Integrated Security=no;";

string sqlCommandText =
    "SELECT Department.Name " +
    "FROM Employee " +
    " INNER JOIN EmployeeToDepartment ON Employee.ID = EmployeeToDepartment.EmployeeID " +
    " INNER JOIN Department ON EmployeeToDepartment.DepartmentID = Department.ID " +
    "WHERE " +
    "Employee.LastName = @LastName";

string oraCommandText =
    "SELECT * " +
    "FROM Employee " +
    " INNER JOIN EmployeeToDepartment ON Employee.ID = EmployeeToDepartment.EmployeeID " +
    " INNER JOIN Department ON EmployeeToDepartment.DepartmentID = Department.ID " +
    "WHERE " +
    "Employee.LastName = :LastName";

//SQL
DataSet ds = new DataSet();
SqlConnection sqlConn = new SqlConnection(sqlConnectionString);
SqlCommand sqlCmd = new SqlCommand(sqlCommandText, sqlConn);
SqlParameter param = new SqlParameter("LastName", SqlDbType.NVarChar, 32);
param.Value = "Adams";
sqlCmd.Parameters.Add(param);
SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
sqlDA.Fill(ds);
DataTable dt = ds.Tables[0];

//Oracle

...
      One of the first steps to tackle is to design a way to abstractly create the different types of objects we will need such as a Connection, Command, etc.
    So let’s get started…

     

    For our purposes we will create a set of database specific providers to mange the creation of objects specific to that database. We will also abstract the creation of these database specific providers through the use a factory pattern to create the concrete implementations of our database specific providers.

     

    Abstracting the Database Specific Providers

    We’ll start by defining an abstract class to represent the set of services that each database provider will need to implement

    internal abstract class BaseDBProvider
    {
        // Connections
        internal abstract IDbConnection CreateConnection(
            string connectionString);
    
        // Commands
        internal abstract IDbCommand CreateCommand(IDbConnection conn);
        internal abstract IDbCommand CreateCommand(IDbConnection conn,
            string commandText);
    
        // Parameters
        internal abstract IDbDataParameter CreateParameter(
            string name, object value);
        internal abstract IDbDataParameter CreateParameter(
            string name, DALDbType dataType, object value);
        internal abstract IDbDataParameter CreateParameter
            (string name, DALDbType dataType, int size, object value);
    
        internal abstract IDbDataAdapter CreateDataAdapter();
        internal abstract IDbDataAdapter CreateDataAdapter(IDbCommand cmd);
    
        ...
    }

     

    Our base class is fairly simple at this point defining a way to create a connection which accepts a provider specific connection string to connect to the database with and returns a IDbConnection interface. The IDbConnection interface is defined within the ADO.NET framework and all ADO.NET based database connection objects must implement it.

    By returning the interface we can separate the implementation details from the set of common services that each connection object provides.

    Similarly, we have a set of methods that allow the construction of command objects, which accept an IDbConnection and optionally the SQL command text to execute. Both these methods return an IDbCommand interface, again defined by Microsoft, which all database specific ADO.NET commands must implement. This again separates the caller from the provider specific objects.

    The next step is to provide concrete implementations of the BaseDBProvider class for each database platform we want to support. Below is an example for SQL Server.

    internal class SQLDbProvider : BaseDBProvider
    {
        internal SQLDbProvider() { }
    
        internal override IDbConnection CreateConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }
    
        internal override IDbCommand CreateCommand(IDbConnection conn,
            string commandText)
        {
            if (!(conn is SqlConnection))
                throw new ArgumentException("SqlConnection required", "conn");
    
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = commandText;
            return cmd;
        }
    
        internal override IDbDataAdapter CreateDataAdapter(IDbCommand cmd)
        {
            return new SqlDataAdapter(cmd as SqlCommand);
        }
    
        internal override IDbDataParameter CreateParameter(string name, DALDbType dataType, int size, object value)
        {
            SqlParameter p = new SqlParameter(name, GetDataTypeFor(dataType), size);
            p.Value = value;
            return p;
        }
    
        ...
    
        private SqlDbType GetDataTypeFor(DALDbType dbType)
        {
            SqlDbType ret = SqlDbType.NVarChar;
            switch (dbType)
            {
                case DALDbType.DateTime:
                    ret = SqlDbType.DateTime;
                    break;
                case DALDbType.Int:
                    ret = SqlDbType.Int;
                    break;
                case DALDbType.NText:
                    ret = SqlDbType.NText;
                    break;
                case DALDbType.NVarChar:
                    ret = SqlDbType.NVarChar;
                    break;
                default:
                    throw new NotSupportedException("Data type not supported");
            }
            return ret;
        }
    }

     

    One thing of interest to note is the way the SqlCommand gets created. We use the CreateCommand() method of the IDbCommand interface which at this point is implemented on a SqlConnection.

    The same would be done for each database platform you intend to support.

    internal class OracleDbProvider : BaseDBProvider
    {
        ...
    }


    We also defined a set of database neutral types that can be used across providers and mapped to whatever database specific types we desire. For instance we can have a DALDbType.Bit map to a SQL Server ‘Bit’ or ‘TinyInt’ or ‘Char’ if we wanted. This opens up additional flexibility in mapping logical types to the physical types for each database platform.

    public enum DALDbType
    {
        DateTime,
        Int,
        NVarChar,
        NText,
    
        ...
    }

     

    Implementing the Provider Factory

    At this point we have encapsulated all the database specific code in each provider class, but we haven’t solved how to not specifically instantiate these providers from our client code.

    To do this we will use the concept of a class factory. This is a pattern that allows a caller to create concrete implementations of objects by providing some identification of what type of object to create without the need for referencing the type directly.

    Here’s the implementation of the class factory that will create and return concrete providers for our specified database.

    public enum SupportedDatabases { SQLServer, Oracle }
    
    internal static class DbProviderFactory
    {
        public static BaseDBProvider GetDbProvider(SupportedDatabases databaseType)
        {
            BaseDBProvider provider = null;
            switch (databaseType)
            {
                case SupportedDatabases.SQLServer:
                    provider = new SQLDbProvider();
                    break;
    
                case SupportedDatabases.Oracle:
                    provider = new OracleDbProvider();
                    break;
    
                default:
                    break;
            }
            return provider;
        }
    }

     

    We define an enumeration that contains identifiers for the different databases we will support in our DAL and then through the use of the factory, we specify which type of database we would like a provider for and the factory creates the correct database specific provider for us.

    The key to the decoupling process is that the factory returns the abstract BaseDBProvider class to the caller. This allows the caller to use the same object and methods regardless of the type of database being used.

     

    Simplifying Usage

    One important goal for me when creating a DAL is to have it simplify the way I use and interact with the database. By creating a DAL on top of what ADO.NET provides we can start to simplify patterns of usage that normally occurs.

    For instance, instead of setting up command parameters using the typical Create/Set/Add pattern:

    SqlCommand cmd = new SqlCommand(cmdText, conn);
    SqlParameter param = new SqlParameter("LastName", SqlDbType.NVarChar, 32);
    param.Value = "Smith";
    cmd.Parameters.Add(param);


    We can simplify this into a single method, cleaning up the caller code as well as (in my opinion) making the code more readable.

    DALCommand cmd = new DALCommand(conn, cmdText);
    cmd.AddParameter("LastName", DALDbType.NVarChar, 32, "Smith");

     

    Other simplifications that I would include in a DAL is the use of DataAdapters to retrieve DataTable and DataSet objects. Wouldn’t it be nice to have a command object that supports ExecuteDataTable() or ExecuteDataSet() methods?

    Seems like I am very often working with DataTable’s, and using ADO.NET to retrieve these seems a bit more work than it should otherwise be. I would love to be able to just do something like this to simplify my client code:

    DataTable dt = cmd.ExecuteDataTable();

     

    Summary

    To recap what we accomplished:

    • We now have a basic DAL framework to abstract the use of database specific objects to more abstract ones. No more dealing with XXXCommand or XXXXConnection per database platform.
    • We’ve also managed to abstract ourselves from database specific types as well as added the flexibility to represent data in any way we want on the database platform (Ex: Bit ==> TinyInt, etc.)
    • We’ve simplified usage of the DAL so we can write less code and let it be more readable.
    Additional Features

    While we haven’t yet completely abstracted ourselves from the differences in databases completely – most notably the connection string and SQL syntax (parameter syntax differences, etc) – at this point I will leave the implementation of these to the reader.

    On projects I’ve worked on we have successfully abstracted away the connection strings as well as a lot of the SQL syntax of queries across the providers we are using.

    If there’s any interest in these techniques, drop me some comments and perhaps I’ll do a follow-up post.

    By the way, here’s the relevant portion of the client code from the beginning of the post with the DAL improvements we’ve implemented compared to the original:

    ADO.NET:

    DataSet ds = new DataSet();
    SqlCommand cmd = new SqlCommand(cmdText, conn);
    SqlParameter param = new SqlParameter("LastName", SqlDbType.NVarChar, 32);
    param.Value = "Smith";
    cmd.Parameters.Add(param);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(ds);
    DataTable dt = ds.Tables[0];


    DAL:

    DALCommand cmd = new DALCommand(conn, cmdText);
    cmd.AddParameter("LastName", DALDbType.NVarChar, 32, "Smith");
    DataTable dt = cmd.ExecuteDataTable();


    Seems like a winner to me!

    Technorati Tags: ,,,,
    Digg This
    Advertisements

    One thought on “Constructing a DAL in C#

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s