(EDIT: I 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 Stack Overflow dealing with subsets of this question, but I 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 database 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 LINQ to Entities so provides ORM, thus allowing abstraction in your code