When should I be using Odbc, OleDb, SQLClient? What are the trade-offs

Question

I am starting off with a SQLServer database. So it would seem that I should use System.Data.SqlClient namespace. But, there is a chance that we might shut down our SqlServer database and go to MySql or Oracle. For this reason, I am coming up with a set of standards on how our .Net apps will communicate with the database, so as to make it easier to migrate to a different database system in the future if we needed to do so.

So here are the standards:

1. Use an ORM if possible (eg NHibernate) (No LINQ as it only supports SqlServer, but what about Entity framework and its support for Oracle and MySql?)
2. If ORM is an over-kill, then use parameterized SQL queries.
3. Use stored procedures only for long running or complex actions that need to be performed on the database.

Which brings me to my main question at hand. Which namespace should I be using to code my DAL?

It looks to me that the choice is between System.Data.ODBC and System.Data.OleDB:

• What are the trade-offs?
• Is one preferred over the other?
• What are your thoughts about the first 3 standards?

Toitoiboid

Answer 1

System.Data.SQLClient

Connects to SQL Server 2000 and later only, but you will get optimal performance when connecting to those databases.

System.Data.OledbClient

Connects to SQL 6.5

OLEDBClient gives you ability to connect to other database like ORACLE or Access. But for working with SQL Server you will get better performance using SQLClient.

Note: For connecting to ORACLE, Microsoft also has ORACLEClient.

System.Data.ODBCClient

Connects to legacy databases only, using ODBC drivers. (E.g. MS Access 97.)

Original source

Cryptocrystalline

Answer 2

You want to use the SQL Server driver. I understand what you are trying to do but the way you would accomplish supporting multiple databases is by inserting another layer of abstraction. You can do this many ways. But you put the database specific code at the edge of your class hierarchy. Therefore, each class can get the benefits of database specific functionality but the higher level callers don't know or care what database is being used underneath. As far as ORMs, I prefer LLBLGen, but this is just my preference.

Also, just to clarify, LINQ is not specific to SQL Server. That is LINQ-to-SQL. LINQ is a querying technology that you can use in LINQ-to-SQL, LINQ-to-Entities, LINQ-to-objects, and even LLBLGen supports LINQ.

Craigcraighead

Answer 3

No matter whether you use SQLClient or Odbc for now, if you use stored procedures or other database-specific features, you'll have to rewrite those if you change database engines.

Coeducation

Answer 4

I'd just use SqlClient and re-write/re-generate the DAL if it changed.

Unless you're going to implement and test on multiple platforms right now, I'm not sure the extra effort right now is a big deal or any less than the effort to redo the DAL, and the fact that you've got a DAL at all means you've got everything in one place for a later change anyway.

Lengel

Answer 5

If you've got any inkling that you'll be swapping databases (or supporting multiple backends) then an ORM is the way to go. Otherwise, you'll still have to refactor/rewrite a lot of your DAL in order to support the change. If your app is small, it won't be bad, but anything substantial and you'll be hurting.

Lymphoid

Answer 6

You will find that using SQL Server the SqlClient is far quicker and easier to develop with than OleDB and ODBC - unless it is extremely likely that you will need to support multiple platforms you will find that the benefits outweigh the risks that you will need to rewrite your DAL.

Additionally, using OleDB / ODBC is only 1 way of maintaining platform independence - you may find it more effective to have multiple implementations of your DAL, each using a client native to the platform being used.

Airmail

Answer 7

I've heard it said that unless it's a key feature, you shouldn't worry too much about maintaining platform independence. That said,

SQLClient will give you native access and should be more performant (it doesn't have to do any abstractions/translations).

The only thing you have to change to get OLEDB working vs. ODBC is your connection string. OLEDB has a little bit more client-side intelligence so it offers better performance.

Mistral