Which .NET data provider?

Question

I am new to database programming, so I'd like help getting on the right track. I have read that there are Microsoft-defined and third-party data providers for data access. MSDN has information on data providers for SQL Server, OLE DB, ODBC, Oracle, as well as the EntityClient provider (Entity Framework).

Which data provider is today's hottest, most-widely used model?

Which one is the future?

Also, I have seen Linq to SQL tutorials, but what category does L2S fall into?

Answer 1

Linq 2 Sql and Entity client providers also uses a managed drivers at the back end. Managed drivers are efficient than ODBC and OLE DB drivers supposedly. There exists managed providers for most of the databases out there.

Answer 2

Linq to SQL is an ORM, an Object Relationship Mapper. Entity Framework is also an ORM, and while it might seem that Entity Framework is a natural progression of L2S, they were actually developed in parallell and are quite different under the covers. For example L2S will not work with any other database other than MSSQL, where as the entity framework will work with most databases.

The entity framework allows you to use your own data provider, so the provider is going to rely heavily on which database you want to use and whether or not there is a provider available for it. If you have not yet chosen a database, you will need to weigh up the pros and cons first of each first (and then probably go with MSSQL because it's just easier if you're developing with .NET).

Given your preference form MSSQL then your database design will have a large impact on whether or not you wish to use an ORM. Linq to SQL L2S is still well used in the development community but I'm unsure as to it's future. Linq to Entities (or the entity framework) is relatively new, but as of 4.0 I believe it is much more business ready and well supported. You can also consider NHibernate if you like the open source (and hard work) option. It is harder work to get set up, but often worth the effort for complex domains.

Answer 3

Depends on the database you plan to use.

• Officially Microsoft is pushing Entity Framework as the new hotness for data access. In theory you can use EF with Oracle, MySQL, and Postgre.
• Last I heard Microsoft wasn't planning to do active development on Linq to SQL. However, as is, it's quite good as a light weight ORM layer on top of SQL Server and worth exploring (depending on your project needs).
• There's also a SqlClient if you need lower level connection to your SQL Server. It can be a viable option in some situations. It is definitely not the new hotness, but it will probably help you (in general) to know how ADO works before you start working with abstractions like EF.

I can't speak to OLE DB or ODBC.

Answer 4

Actually, it depends on what task you are solving, your experience in this or that database. If you wish to use ORM you have many options here: LINQ 2 SQL, Entity Framework, NHibernate, DataObjects and others. LINQ 2 SQl works only with SQL Server, Entity Framework can work with different providers, but really I do not know any official provider from Oracle, so, as for me, I wouldn't trust any third party one. NHibernate is truly cross-database solution, more mature than previous two. But it has its own drawbacks.Anr rememeber ORM is not the only choice. You can use ADO.NET with a lot of providers avaliable. This assumes that you write SQL code by yourself and control all the connectivity and transaction stuff, but it also gives you more freedom, because this layer is lower than ORM.