Tag Archives: Database

I’ve been pondering something that was raised in passing earlier this week and that is the relationship between a DAL (Data Abstraction Layer) and the DAAB (Data Access Application Block).

It was briefly mentioned in a conversation that I had that the DAAB provides the functionality of a DAL because the developer doesn’t need to worry about the back end database that is being used. I suppose to some extent that is true. However, I don’t believe that it fully functions as a DAL.

To my mind a DAL abstracts the access of data away from the rest of the application. Most people seem to restrict this view to data being held in a database.  But databases are not the only repository of data. Data can be held in plain text files, CSV files, XML files and many other formats. It doesn’t need to arrive by file, it could be data from a service or other mechanism.

If you treat sources of data as being more than a database then the DAAB is not a suitable substitute for building a DAL.

Also the DAAB has some limitations in that it cannot translate the SQL itself. For example the flavour of SQL in Oracle has differences to the flavour of SQL in SQL Server. This means that any SQL code will have to be translated. One possible solution is to ensure that everything is done through stored procedures. Then all that the DAAB needs is the stored procedure’s name and the values for the parameters.

But what of passing stored procedure names and parameters to the DAAB? Wouldn’t they need to be known in the business layer? Surely the business layer should know absolutely nothing about the database? Absolutely, the business layer should not be concerning itself at all with the database. It shouldn’t know about stored procedure names, parameters or anything else, even if the DAAB takes care of figuring out the details under the hood from information picked up from the config file. The Business Layer should just need to know there is a DAL and a method on the DAL can be called and some results come back. How the DAL does anything is of no concern to the business layer.

A quick and dirty test, in my opinion, is to look out for any classes from the System.Data namespace in the business or presentation layer to determine if the DAL is well designed.

In my mind the DAAB is just a tool that can be used to make the creation and maintenance of a DAL easier when dealing with databases. It makes it easy to change the location of databases as the development process moves along from the developers’ machines, to continuous integration, test, pre-live and finally live (or what ever your process calls for). The argument that the DAAB makes it easy to swap out one type of database for another isn’t something that is actually going to be done all that often. From what I’ve seen, companies generally run two systems concurrently until the old one is discontinued. Rarely do they ever actually update the old system to use the new database and when they do it is usually via some form of orchestration system so the old system doesn’t need to be changed in any great way. If it isn’t broke don’t fix it.

Tags: dal daab data abstraction layer data access application block sql

In my last post about the DAL I presented a very basic DAL based on a static class. This is fine for small applications, but for larger applications the limitations will soon start to show themselves.

In this post I’ll convert the DAL to a series of singletons so that each singleton operates on a different part of the database.

First of all, I should explain what a singleton is. A singleton is a class that has only one instance. The way that single instance is generated is controlled from the class itself.

When some code wants to use the singleton it calls a static property called Instance to get the one and only instance of the class. The property checks to see if there is already an instance available and if not creates it. This instance is stored in a private static field on the class. To ensure that only one instance of the class is ever created

The following is the DAL with just the parts that make it a singleton

    public class InvoiceDal
    {
        /// <summary>
        /// A private field to hold the only instance of this class
        /// </summary>
        private static InvoiceDal instance;

        /// <summary>
        /// A private constructor so that the only thing that can
        /// construct this class is itself
        /// </summary>
        private InvoiceDal()
        {
            // Do initialisation stuff here.
        }

        /// <summary>
        /// A public property that retrieves the only instance of the
        /// class
        /// </summary>
        public static InvoiceDal Instance
        {
            get
            {
                if (instance == null)
                    instance = new InvoiceDal();
                return instance;
            }
        }
    }

Now the methods from the original static DAL (see previous post) can be carried over almost as they are. It is just the static keyword that has to be removed.

But, the point of this post is to refactor the old static DAL into a number of DAL classes where each class represents some component. The part given already represents the invoicing system. The next part will be for the stock inventory system.

It should, hopefully, be obvious that part of the DAL given already will be needed again. The lazy way is to just copy and paste the methods into a new DAL class. This is wasteful and the poor sod that has to maintain the code will not thank you. In practice what tends to happen is that, in your absence, you will be the butt of all jokes and your name will be dragged through the mud. This is something you don’t want to happen to you.

So, instead an abstract base class will be created which contains the common parts of the DAL. Certain decisions need to be made about goes in the base class. For example, is the base class responsible for figuring out what connection string to use? If you are only going to connect to one database then it certainly does seem to be a good choice. What if you need to (or expect to) connect to multiple databases? In those situations the derived class may be the best place to figure out the connection string. For this example, I’ll assume that multiple database are in use, but all from the same type of database system (e.g. Different databases running on SQL Server). I’ll come on to different database systems later in the series.

The abstract base class looks like this:

public abstract class DalBase
{
    private static string connectionString;

    protected DalBase()
    {
    }

    /// <summary>
    /// Builds the command object with an appropriate connection and sets
    /// the stored procedure name.
    /// </summary>
    /// <param name="storedProcedureName">The name of the stored
    /// procedure</param>
    /// <returns>The command object</returns>
    protected SqlCommand BuildCommand(string storedProcedureName)
    {
        // Create a connection to the database.
        SqlConnection connection = new SqlConnection(connectionString);

        // Create the command object - The named stored procedure
        // will be called when needed.
        SqlCommand result = new SqlCommand(storedProcedureName,
            connection);
        result.CommandType = CommandType.StoredProcedure;
        return result;
    }

    /// <summary>
    /// Builds a DataAdapter that can be used for retrieving the results
    /// of queries
    /// </summary>
    /// <param name="storedProcedureName">The name of the stored
    /// procedure</param>
    /// <returns>A data adapter</returns>
    protected SqlDataAdapter BuildBasicQuery(string storedProcedureName)
    {
        SqlCommand cmd = BuildCommand(storedProcedureName);

        // Set up the data adapter to use the command already setup.
        SqlDataAdapter result = new SqlDataAdapter(cmd);
        return result;
    }
}

And the derived class looks like this:

public class InvoiceDal : DalBase
{

    /// <summary>
    /// A private field to hold the only instance of this class
    /// </summary>
    private static InvoiceDal instance;

    /// <summary>
    /// A private constructor so that the only thing that can
    /// construct this class is itself
    /// </summary>
    private InvoiceDal()
    {
        connectionString =
            ConfigurationManager.AppSettings["InvoiceConnectionString"];
    }

    /// <summary>
    /// A public property that retrieves the only instance of the class
    /// </summary>
    public static InvoiceDal Instance
    {
        get
        {
            if (instance == null)
                instance = new InvoiceDal();
            return instance;
        }
    }

    /// <summary>
    /// A sample public method. There are no parameters, it simply calls
    /// a stored procedure that retrieves all the products
    /// </summary>
    /// <returns>A DataTable containing the product data</returns>
    public DataTable GetAllProducts()
    {
        SqlDataAdapter dataAdapter = BuildBasicQuery("GetAllProducts");

        // Get the result set from the database and return it
        DataTable result = new DataTable();
        dataAdapter.Fill(result);
        return result;
    }

    /// <summary>
    /// A sample public method. It takes one parameter which is passed
    /// to the database
    /// </summary>
    /// <param name="invoiceNumber">A number which identifies the
    /// invoice</param>
    /// <returns>A dataset containing the details of the required
    /// invoice</returns>
    public DataSet GetInvoice(int invoiceNumber)
    {
        SqlDataAdapter dataAdapter = BuildBasicQuery("GetInvoice");
        dataAdapter.SelectCommand.Parameters.AddWithValue(
            "@invoiceNumber", invoiceNumber);

        DataSet result = new DataSet();
        dataAdapter.Fill(result);
        return result;
    }

    /// <summary>
    /// A sample public method. Creates an invoice in the database and
    /// returns the invoice number to the calling code.
    /// </summary>
    /// <param name="customerId">The id of the customer</param>
    /// <param name="billingAddressId">The id of the billing
    /// address</param>
    /// <param name="date">The date of the invoice</param>
    /// <returns>The invoice number</returns>
    public int CreateInvoice(int customerId, int billingAddressId,
        DateTime date)
    {
        SqlCommand cmd = BuildCommand("CreateInvoice");
        cmd.Parameters.AddWithValue("@customerId", customerId);
        cmd.Parameters.AddWithValue("@billingAddressId",
            billingAddressId);
        cmd.Parameters.AddWithValue("@date", date);

        cmd.Connection.Open();
        int result = (int)cmd.ExecuteScalar();
        cmd.Connection.Close();
        return result;
    }

}

It is now possible to create more of these derived classes for various parts of the application all sharing the same common code in the base class.

Tags: dal c# singleton .net ado.net