Data Access Layer design patterns

Solution 1:

I recommend Patterns of Enterprise Application Architecture by Martin Fowler.

A list of the patterns is also on his website

The DataMapper pattern is also relevant.

Solution 2:

I like using interface based Db access. Every db provider for Ado.net implements basic interfaces, and when you use them your code may look like this:

public static IDbConnection GetConnection(string connectionName)
{
  ConnectionStringSettings ConnectString = ConfigurationManager.ConnectionStrings[connectionName];
  DbProviderFactory Factory = DbProviderFactories.GetFactory(ConnectString.ProviderName);
  IDbConnection Connection = Factory.CreateConnection();
  Connection.ConnectionString = ConnectString.ConnectionString;
  return Connection;
}

Then, when you need to communicate with db:

public static DataTable Dummy()
{
  using (IDbConnection Connection = GetConnection("SiteSqlServer"))
  {
    IDbCommand Command = Connection.CreateCommand();
    Command.CommandText = "DummyCommand";
    Command.CommandType = CommandType.StoredProcedure;

    Connection.Open();

    using (IDataReader reader = Command.ExecuteReader())
    {
      DataTable Result = new DataTable();
      Result.Load(reader);
      return Result;
    }
  }
}

With this technique you can create fully db independent DAL. Of course for some complex scenarios this is not enough. But mostly this will do the work, and you don't needed various external libs.

Solution 3:

The easiest solution would be to use an ORM. Check out LLBLGen. Using the Adapter Model you can switch between data providers while using the same business objects. It can generate code for both MySql and Sql Server.

Solution 4:

In general, I second John Nolan's recommendation of Patterns of Enterprise Application Architecture.

More specifically, I would always recommend that you hide your Data Access Layer behind an interface and use Dependency Injection to inject a particular Data Access Component into your Domain Logic at run-time.

You can use a Dependency Injection Container or do it manually.

On the technology side I would recommend Microsoft's Entity Framework, since your data access needs seem to be constrained to relational databases. The Entity Framework is Microsoft's official OR/M and it has providers for many different RDBMSs, as well as LINQ support.