David Hayden [MVP C#]

After several discussions recently, I am suspecting a number of people are using the old SqlHelper and the new Enterprise Library Data Access Application Block as their data access layer, but I don't believe this is the original intent of the Patterns and Practices Group.

I think of SqlHelper and DAAB as the pipes in my house. I hide them behind the wall and stick a "sink", "shower", or "toilet" on the end of them depending on my needs.  Using them as a DAL is like running around the house and drinking from the hose bib :)

When I use the DAAB, I wrap it in a DbHelper class that implements IDbHelper.  Except for a few changes, there is usually a 1:1 ratio between the public "interface" on the DAAB and my IDbHelper interface:

public class DbHelper : IDbHelper
{
    // Enterprise Library DAAB - Hidden
    private readonly Database _database = null;

    public DbHelper()
    {
        _database = DatabaseFactory.CreateDatabase();
    }

    #region IDbHelper Members

    public IDataReader ExecuteReader(CommandType commandType, string storedProcedureName)
    {
        return _database.ExecuteReader(commandType, storedProcedureName);
    }

    #endregion
}

One could argue if one really needs to go through all this work of wrapping up the DAAB in a helper class. I do it for a few reasons:

• It gains me a bit more flexibility so that I can add additional public methods via the interface not directly supported by DAAB.
• I can easily replace the DAAB with a different solution without requiring much effort.
• The IDbHelper Interface gives me a bit more fine grained testability.

Now from here at the minimum you would have a class that uses IDbHelper ( there should be some value add to this class and not just an empty shell around IDbHelper ):

public class SqlDataSource : IDataSource
{
    private readonly IDbHelper _helper = null;

    public SqlDataSource(IDbHelper helper)
    {
        _helper = helper;
    }

    #region IDataSource Members

    public IDataReader GetReader(string spName)
    {
        return _helper.ExecuteReader(CommandType.StoredProcedure, spName);
    }

    #endregion
}

And you could continue this further if again you are looking for some more granularity in functionality and loose coupling ( or you could remove the intermediate step of SqlDataSource and add the IDbHelper to this class ):

public class CustomerDataSource : ICustomerDataSource
{
    private readonly IDataSource _dataSource = null;
    
    public CustomerDataSource(IDataSource dataSource)
    {
        _dataSource = dataSource;
    }
    
    public IDataReader GetCustomers()
    {
        // Do some stuff
        IDataReader dr = _dataSource.GetReader("GetCustomers");
        // Do some stuff
        
        return dr;
    }
}

This might all get consumed into a business class using, for example, the ActiveRecord Pattern, if you like using classes and have minimal business rules:

public class Customer
{
    // ...
    
    public static List<Customer> GetCustomers()
    {
        ICustomerDataSource source = ...
        
        // Return customers...
    }
}

A lot of the classes here are subject to one's development style, opinion, and application needs. Some developers would remove some of the classes and some would rather return a disconnected object ( like DataSet or Entity ) to the business layer rather than an open DataReader.  Many developers smirk at the whole idea of the ActiveRecord Pattern as the idea of combining business rules and data access is crazy. Some people prefer creating custom dataproviders using the built-in DataProvider model in the .NET 2.0 Framework. Many would grab the nearest O/R Mapper to avoid the process :)

My point isn't to argue or defend different architectures, but to merely point out that SqlHelper or DAAB is a helper class. It is the pipes IMHO.  It is low on the food chain and needs to be very much disposable and replaceable.  When SqlHelper or Enterprise Library 2.0 DAAB starts to define or limit your data access layer, you are probably using it wrong :)