Data Access Layer design patterns

Question

I have to design a Data Access Layer with .NET that probably will use more than one database management system (Mysql and Sql Server) with the same relational design.

Basically, it has to be simple to switch from one database to another so I would like you to recommend me some web-sites or books that has been useful for you, with common design patterns or information in general to implement this kind of data access layer.

Thank you.

Answer 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.

Answer 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.