.net – IdbConnection vs. SqlConnection

.netdesign-decisionsvendor-neutrality

When I write an app, I use the System.Data interfaces (IDbConnection, IDbCommand, IDataReader, IDbDataParameter, etc…). I do this to reduce vendor dependencies. Unless, I'm doing a simple test app, it just seems like the ethical thing to do when consulting.

However, it seems like all the code I see uses the System.Data.SqlClient namespace classes or other vendor specific classes. In magazines and books it's easy to chalk this up to Microsoft influence and their marketing spin to program only against SQLServer. But it seams like almost all the .NET code I see uses the SQLServer specific classes.

I realize the vendor specific classes have more functionality, for example adding a parameter to a SqlCommand object is one method, where as adding it to an IDbCommand is an irritating 4+ lines of code. But then again; writing a little helper class for these limitations is pretty simple.

I've also wondered if programming against the interfaces when SQLServer is the current target client is over-engineering since it is not required immediately. But I don't think it is since the cost of programming against the interfaces is so low, where as reducing vendor dependency provides such a huge benefit.

Do you use vendor specific data classes or the interfaces?

EDIT: To summarize some of the answers below, and throw in some thought's I had while reading them.

Possible pitfalls to using interfaces for vendor neutrality:

  • Vendor specific keywords embedded in
    your SELECT statements (all my ins,
    upd, & del's are in procs, so that's
    not a problem)
  • Binding directly the
    database would probably cause
    issues.
  • Unless your connection
    instantiation is centralized, the
    vendor specific class will need to
    be called anyway.

Positive reasons to use interfaces :

  • In my experience the ability (even
    if not exercised) to move to a
    different vendor has always been
    appreciated by the customer.
  • Use interfaces in reusable code libraries

Best Solution

One thing to take into consideration is the actual chance that you will ever switch databases. In most cases, this will never happen. And Even if it does, it will be a major rewrite, even if you use classes that are database neutral. In this case, it's probably just best to use whichever is more feature rich and will help you get the project done quicker.

That being said, I think that in most instances, you should use a layer you create yourself, above the actual .Net API, so that if you ever have to change which classes you have to use then it won't be so much of a problem. Even if you stay on the same database, you never know when you'll have to switch the way you access the database. Anybody who migrated from ASP to ASP.Net (ADODB vs. ADO.Net) can tell you how much of a pain this is.

So I think the best solution is to use the more feature rich database specific API, and build you own layer on top of it, so that you can easily swap it out if necessary.

Related Question