What are the pros and cons of OleDB versus SQLClient?
Some context: one of the systems I'm working on is a .net 2.0 web application. VB.net for the front end, and SQL Server 2005 for the backend. For a variety of reasons that have been lost to time, the original designer decided to use the .Net OleDB connection rather than the SQLClient connection.
After a few years of development, this particular system is on the cusp of crossing the line from "beta" into "1.0" status. One of the things we've been talking about at this point is moving to the SQLClient connection. While I'm aware that it's best practice to be using it, and that it's the only way to get at the fancier features in SQL Server 2005 (which we aren't using, obviously) what are the advantages of using the one over the other? Any hidden gotchas I should know about? And can anyone point me at some benchmarks showing relative speeds? (I hear that the SQLClient is supposed to be faster, but I've never seen any numbers to back that up.)
Thanks, all.
Answer
OleDb is more generic. If you ever move to a different database type in the future there's a good chance it'll have an Ole driver and you won't have to change as much code.
On the other hand, the Sql Server native driver is supposed to be faster as you said, and it has nicer parameter support (parameters can use names and don't have to be in order).
In my personal experience, I've never noticed the speed difference; I also couldn't find anything to back up the claim. I suspect the performance advantage is real, but that you'd have to process millions of records before you could start to measure it.
What I did notice made a meaningful difference were the error messages. I was having trouble with an old OleDb app, and I switched it to SqlClient out of desperation. Of course, it still didn't work, but the better error messages provided enough new information I was able to fix the problem.