Starting to construct a data access layer. Things to consider?

Question

Our organisation uses inline sql. We have been tasked with providing a suitable data access layer and are weighing up the pro's and cons of which way to go...

• Datasets
• ADO.net
• Linq
• Entity framework
• Subsonic
• Other?

Some tutorials and articles I have been using for reference:

• http://www.asp.net/(S(pdfrohu0ajmwt445fanvj2r3))/learn/data-access/tutorial-01-vb.aspx
• http://www.simple-talk.com/dotnet/.net-framework/designing-a-data-access-layer-in-linq-to-sql/
• http://msdn.microsoft.com/en-us/magazine/cc188750.aspx
• http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx
• http://www.subsonicproject.com/

I'm extremely torn, and finding it very difficult to make a decision on which way to go. Our site is a series of 2 internal portals and a public web site. We are using vs2008 sp1 and framework version 3.5.

Please can you give me advise on what factors to consider and any pro's and cons you have faced with your data access layer.

PS. I found the subsonic web site's information a bit shallow, and a lot of emphasis seems to be placed on how 'cool' it and it's developers are. I'd much prefer a spoken explanation to their demo video than the music? Does anyone else agree?

Answer 1

I would suggest Entity Framework 4 if you can go with .NET 4.0. It's a good ORM that will only get better as time goes on. You can write your queries, inserts etc. using Linq, which gives you a syntax that is reasonably close to the inline SQL you are using today. Obviously, it has MS behind it so you can expect lots of information and skilled developers to be available now and into the future.

If you have to stay with .NET 3.5, I would look at NHibernate. It is more mature than EF4 overall but lacks full support for Linq right now. Although you will be able to use Linq for relatively simple queries, you will have to go to the somewhat less SQL-like criteria API for more complex queries. I would also use Fluent NHibernate to allow in-code configuration of the mapping. It has strong community support and should have much better Linq support in the very near future. It also has the advantage of being open source.

Either of these ORMs will take care of mapping the objects in your system to the database. This will allow you to focus more of your effort on building the application logic. For that reason, I would not suggest using ADO.Net/Datasets. Linq2Sql is pretty good for what it does but is never going to be as strong as Nhibernate or EF4. Linq2Sql always looked like a first effort from MS and seems to be relegated to playing second fiddle to Entity Framework.