.NET data base access: Newest technology, comparison (2011)

In fact, I have several questions, which I hope, can be answered objectively.

1. Which standard not obsolete techniques to ac开发者_StackOverflowcess databases are available in .NET? I know the following:

   • ADO.NET
     • using DbDataReader
     • using DataSet, DataTable, etc.
     • Inside ASP as SqlDataSource
   • EF
   • LINQ to SQL
   • NHibernate (whcih in fact is 3rd party but seems to be some kind of a standard solution)

2. Which of them are limited to certain DB providers? As far as I know, ADO.NET supports most DB types and LINQ to SQL only supports MS SQL 2005 and later.

3. Are there restrictions regarding specific .NET versions? E.g. LINQ requires .NET 3.5 etc.

4. Which of them is suitable to provide simple DTO which can be simply written as code, containing the DB data, not neccessarily bound to the DB schema? The goal is to be able to write classes, which don't need to represent the table layout of the DB. For example: A class representing the results of several tables. I'D also appreciate a solution which can be diffed easily e.g. in a versioning system.

5. Which of them shouldn't be used with certain other techniques? Are there known limitations? I don't want to start a war over performance etc. here, but I can imagine, that certain techniques won't work together or would be extremely inefficent or complicated.

---

First of all ADO.NET is the common denominator, all these techniques eventually uses ado.net to talk with the database. for the other questions:

• Linq To sql is limited to MSSSQL, EF needs some special driver, NH talk to almost ever DB :)
• Having DTO is not an OR/M related question, anyway NH allow embedding DTO in the query, and LINQ to SQL/EF too since having LINQ allow you to select new MyDTO...
• In term of performance there is no war to open, it's almost always a design matter.

If you are interested in investigating more, this site can be helpful.