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;
    
        try
        {
    
            //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
            cnnSql.Open();
    
            //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.