Data Access Layer design patterns

Question

Hello, 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've found ADO.NET to be very useful for this. It has all the features you need to create a data access layer independent of the database you use.

Answer 2

NHibernate is designed to handle this kind of scenario if you are willing to learn it

Answer 3

I recommend Patterns of Enterprise Application Architecture by Martin Fowler.

A list of the patters is also on his website

The DataMapper pattern should be of relevant interest.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

It really depends on the size of your layer and the type of product your developing. If it is fairly well contained then ADO.NET will probably be ideal. If it is a bigger DAL layer, and its greenfield development of multitargeted dbms it's best to use an ORM tool. They are fast, efficient and mature products, and can quickly enable the retargetting to another db, simply by changing a single parameter. Writing static ADO, is something that is passing into legacy.

There are several ORM tools which can do the job, all work slightlty differently, and depend on your budget, size of your team etc. They can either work by having to write a mapping class like NHibernate, or work through reflection, i.e attribute markup.

For free, i.e. open source, if your skint, NHibernate is ideal. I'm using this at the moment, to build the DAL layer, for a large enteprise product. Its excellent, but take some time to master. With NHibernate you define mapping classes, which when executed generate the db model for you. It supports stored procedures. The downside is that it takes some time to learn, especially around mapping complex data correctly. Its excellent. It has a huge bundle of samples and other projects floating about thats used it. Check out Koders.com.

If you have a some budget, then LLBLGen is ideal. It is strongly typed and also supports stored procedures.

If have some of the data model already available, then TierDeveloper is ideal. Its essentially free, and works by developing a set of classes from your database model. The only downside is the mapper for mysql is 3rd party. It is a enteprise class product that has been made free to support ncache, and its a possible approach.

If you are desperate to stick with MS, they are moving towards ORM, and have a product called ADO.NET Entity Framework. Functionally it is not as complete as the tools defined above. Its about 3 generations behind in maturity. Its available in vs 2008 sp1. The connector for mysql would be a cost.

Also, you could use LINQ. It will also target mysql, if you would also need the connector.

Ideally, your best bet is with ORM. If you can't support open source, and have budget, then get

Hope that helps.