Best Data Access Layer for .NET

Question

Which is the best data access layer for .net projects. I am using SQL Server 2005 with C#.

Answer 1

I have experience with NHibernate and Microsoft's Entity Framework. I would certainly recommend NHibernate as EF is still in development phase with a lot of bugs and constraints.

Answer 2

I still use The J2EE DAO pattern http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html. And then I use an ORM to do the dirty work. And something more lowlevel when perfomance is needed.

Answer 3

This would depend on your requirements. There is no "best" Data Access Layer (DAL) per-sé.

For small-medium size projects, I would recommend LINQ to SQL. There is a small learning curve but it is very straightforward and less "clunky" than the DataSet designer.

For large-scale Enterprise applications, then you may want to consider an Object Relationship Mapping (ORM) tool. NHibernate is a .NET port of the well-known popular Java ORM tool Hibernate, and gets high ratings. However there is a learning curve. Other options include .NET Entity Framework from Microsoft.

A breakdown of available .NET ORM tools is available here: http://www.theserverside.net/news/thread.tss?thread_id=29914

Of course there is always the option of learning some enterprise patterns and rolling your own. There is a good book by Martin Fowler specifically on Enterprise Patterns at Amazon. I recommend looking specifically at the Data Mapper and Repository patterns.

Answer 4

I found NHibenate with castle-ActiveRecord really good. One of the many reasons for this is that nevermind everything else you can actually test your model:

http://gojko.net/2008/05/07/castle-demo-app-activerecord-basics-and-unit-testing/

Answer 5

Second the recommendation for LINQ to SQL ... - been trying it out with the ASP.NET Dynamic Data and well impressed