I'm reading this very interesting book on Entity framework (EF) which is an Object Relational Mapper(ORM) offering from Microsoft. Our of curiosity, I started to backtrack all the offerings from Microsoft which is used to connect to SQL Server database. Microsoft uses its proprietary ADO.NET data provider technology to conect to database in all its offerings.
Almost a decade ago, I had used Microsoft application blocks in my project for connecting to SQL Server database. To use it I used to reference the MS Application block DLLs in my project directly. Since then so many other things have also come up. Objective of all of them is same - reduce the repeatitive code developers write to do DB operations. This problem is more prominent every time when you start a new project from scratch.
So I started investigating all the options that will be available to me whenever I start a new project from scratch. How these offerings stack against each other? Are they same or they are a gradual enhancement of the same age old ADO.NET technology with EF being the latest offering? Here are the options I tried to explore:
System.Data.SqlClient namespace (by referring System.Data.dll - Available since .NET Framework 1.x)Microsoft.ApplicationBlocks.Data.dll assembly in your project and executing DB queries.All these options can be a point of confusion once you enter into ADO.NET world. It will be helpful if anyone can throw a birds eye view on these offerings.
Off hand - you should write off Microsoft.ApplicationBlocks.Data and Enterprise Library.
LINQ to SQL supports 1 to 1 mapping of database tables, views, sprocs and functions available in Microsoft SQL Server. It's good for a RAD (rapid application development) scenario. I believe MS still supports it, but I don't think they are doing active development on it. I haven't used it or seen it used on any projects I've been on in years.
As you mentioned Entity Framework is an ORM and its much more powerful than LINQ to SQL. The following SO post has a lot of information on the differences.
Another option to consider is Dapper. Dapper is a micro-orm and very fast. Here is some good analysis of Dapper vs. ADO.NET in this SO post: Why should one use Dapper ? Also can anyone comment on Dapper Vs ADO.NET Pros and Cons
Personally, I use Entity Framework or Dapper when I'm building an application. If it's a very large application with complex mapping requirements - then I normally opt for Entity Framework. Otherwise, I stick with Dapper.
I may use straight ADO.NET if I am building a service or some utility that doesn't require any sort of mappings.
Read this EntityFramework VS pure Ado.Net