Abstracting Your Dalc

by Randall 3/23/2007 3:24:00 PM
For quite some time now I've been using a Base DALC from which all of my object-specific dalcs inherit.  I found it tedious to code the same old ADO.Net commands for every CRUD operation I need to implement.

For example, when I want to load a list of foo objects, in my foo dalc I simply do something like this:


    public FooList FooList_Get(Guid id) {
        SortedList paramList = new SortedList();
        paramList.Add("uiFooListId", uiFooListId);
        SqlDataReader drdDb = base.GetDataReader("some stored proc name", SortedList);
        //enumerate your reader
        //close it

My base dalc call methods looks like this:

    /// <summary>
    /// Executes a stored and returns the results as a SqlDataReader.
    /// Paramlist is the parameters in form of (key=paramName;value=paramValue).
    /// <summary>
    /// <param name="storedProcName"></param>
    /// <param name="paramList"></param>
    /// <returns></returns>
    /// <remarks>Notice that a sorted list is used instead of an actual parameter collection.
    /// This is done in case the database provider ever changes, you won't break any inherited classes.
    /// </remarks>
    public SqlDataReader OpenDataReader(string storedProcName, SortedList paramList)
        SqlConnection cnnSql = null;
        SqlCommand cmdSql = null;
        SqlDataReader drdSql = null;
        IDictionaryEnumerator enmParam = null;
            //set-up the connection object
            cnnSql = new SqlConnection(this.ConnectionString);
            //set-up the command object
            cmdSql = new SqlCommand();
            cmdSql.CommandText = storedProcName;
            cmdSql.Connection = cnnSql;
            cmdSql.CommandTimeout = 3000;
            cmdSql.CommandType = CommandType.StoredProcedure;
            //add the parameters
            enmParam = paramList.GetEnumerator();
            while (enmParam.MoveNext())
                cmdSql.Parameters.Add(new SqlParameter((string)enmParam.Key, enmParam.Value));
            //open the connection
            //get the reader
            drdSql = cmdSql.ExecuteReader(CommandBehavior.CloseConnection);
            return drdSql;
        catch (Exception ex)
            throw ex;

Can you see the benefits of doing this?  I initially did it because, well, I'm lazy and I don't like repeating the same code everywhere.  However, while reviewing someone's code yesterday, another reason appeared for encapsulating your ADO.Net commands.

I recently inherited an application from a developer who left our company.  I needed to track some logic within the code - specifically the order in which stored procedures were called within the application.  I had to search through the dalc and place a debug statement within each call.  Had the dalc operations been encapsulated into a base class, I would have had to place debug statements in only one location - yup, you guessed it, the base dalc.

So, it turns out that being lazy can also be a good practice.

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: , , ,

Powered by BlogEngine.NET
Theme by Mads Kristensen

About the author

Name of author Randall Sexton
Currently a .Net developer for Bechtel Corporation in Oak Ridge, TN.

E-mail me Send mail


<<  July 2015  >>

View posts in large calendar


    Recent comments

    Don't show



    Logo Credit

    My logo was taken from CodingHorror.
    Jeff Atwood © Copyright 2007


    The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

    © Copyright 2015

    Sign in