(EDIT: Made it a community wiki as it is more suited to a collaborative format)
There are a plethora of ways to access SQL Server and other databases from .Net. All have their pros and cons and it will never be a simple question of which is "best" - the answer will always be "it depends".
However, I am looking for a comparison at a high level of the different approaches and frameworks in the context of different levels of systems. For example, I would imagine that for a quick-and-dirty Web 2.0 application the answer would be very different from an in-house Enterprise-level CRUD application.
I am aware that there are numerous questions on SO dealing with subsets of this question but think it would be useful to try to build a summary comparison. I will endeavour to update the question with corrections and clarifications as we go.
So far, this is my understanding at a high level - but I am sure it is wrong...
I am primarily focusing on the Microsoft approaches to keep this focused.
ADO.Net Entity Framework
- Database agnostic
- Good because it allows swapping backends in and out
- Bad because it can hit performance and DB vendors are not too happy about it
- Seems to be MS's preferred route for the future
- Complicated to learn (though, see 267357)
- It is accessed through Ling-to-Entities so provides ORM, thus allowing abstraction in your code
Linq to SQL
- Uncertain future (see Is Linq to SQL truly dead?)
- Easy to learn (?)
- Only works with MS SQL
- See also Pros and cons of Linq
"Standard" ADO.Net
- No ORM
- No abstraction so you are back to "roll your own" and play with dynamically generated SQL
- Direct access, allows potentially better performance
- This ties in to the age-old debate of whether to focus on objects or relational data, to which the answer of course is "it depends on where the bulk of the work is" and since that is an unanswerable question hopefully we don't have to go in to that too much. IMHO, if your application is primarily manipulating large amounts of data, it does not make sense to abstract it too much into objects in the front-end code, you are better of using stored procedures and dynamic SQL to do as much of the work as possible on the back-end. Whereas, if you primarily have user interaction which causes database interaction at the level of tens or hundreds of rows then ORM makes complete sense. So, I guess my argument for good old-fashioned ADO.Net would be in the case where you manipulate and modify large datasets, in which case you will benefit from the direct access to the backend.
- Another case, of course, is where you have to access a legacy database that is already guarded by sprocs.
ASP.Net Data Source Controls
Are these something altogether different or just a layer over standard ADO.Net? - Would you really use these if you had a DAL or if you implemented Linq or Entities?
NHibernate
- Seems to be a very powerful and powerful ORM?
- Open source
Some other relevant links;
NHibernate or Linq-to-SQL
Entity Framework vs LINQ to SQL