IdbConnection vs. SqlConnection

Question

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

Answer 1

I use the vendor specific classes where I'm doing direct db work (unless I'm told it's a possibility we may end up using a different database - number of times that has happened: once).

However if I end up factoring off some non-db specific code into separate classes, I'll often make the method parameters interfaces - especially if I think it will be useful in other projects, and I'm not relying on any of the specific features of the direct access class.

Basically, to paraphrase Einstein: Make the solution as simple as possible, but no simpler.

Answer 2

There are differences to the SQL you're going to have to give the classes, depending on the type of database engine you're talking to, so even if you manage to write all your code to use the interfaces, you still need to write multiple sets of SQL.

Or, you can do what we have done, write our own layer that takes care of all the syntax we use, which isn't all the syntax provided by the different engines, but enough to manage to write one SQL that gets properly rewritten before execution.

Basically we've created a function syntax where we prefix the names of the functions with SQL::, which lets our code identify the special functions that needs to be rewritten. These are then parsed out, and rewritten properly, even so far as to swap around argument order if necessary.

Small things like the name of the function that returns the current server date and time can be done, but also bigger things, like how to select the first N rows of a query.

Additionally, parameters for SQL Server is written as @name, where OleDb uses positional (just add a ? where you want the parameter), and our code handles those differences as well.

It pays off in the sense that we don't worry much about the SQL that needs to be written.

Answer 3

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.

Answer 4

I was writing something similar to what lassevk said, but he beat me to it.

Additionally, you have to talk to a real database at some point. You might be able to get away wrapping most of your dataaccess with interface code, and that's a good idea. But eventually if you're using SQL Server you'll want to create a real instance of SqlClient.SqlConnection. Same for Access, Oracle, or MySQL.

Answer 5

You should try to write your code database agnostic. Maybe you don't find it useful right now but you will probably take advantage of it in the future.

Answer 6

Take a look at Microsoft's Patterns and Practices group's Enterprise library. The data access code they have there shows some good implementations of provider independence.

http://msdn.microsoft.com/en-us/library/cc467894.aspx