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:
Positive reasons to use interfaces :
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With