Data Access Layer design patterns

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.

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.

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.