OleDbConnection vs SqlConnection

Hi,

How much of a performance difference is there between connecting to SQL Server 2000 using OleDbConnection or using SqlConnection?

The reason I'm asking is I am taking on the task of updating an older program that uses a Access Database to use SQL Server, but it has a Database Utility class that uses OleDbConnection. I'm just debating whether it would be worthwhile to upgrade the class to use the SQL objects rather than Oledb. Program does a lot of update and insert of invidual records, and a few select statements that usually return from 1 to 2000 records up to a maximum of 50,000 records

ThanksConsider this:

OleDb is a wrapper for all the native drivers, combined so that one interface may be used for any... OleDb ends up applying datatype conversions, etc, and communicates those conversions to SQL

The native sql client drivers do not have to go through this wrapper interface. They are SQL-ready.

I don't know about specific benchmarks, but this simple observation keeps me from using it when I know I'm accessing a SQL database.

However, the .NET framework allows you to abstract the specific nature of the data client through the use of interfaces. If you are worried about having to change databases during the lifespan of an app, utilizing these interfaces instead of the actual objects keeps your code maintainable.
C#:

SqlConnection connection = new SqlConnection();
IDbCommand cmd = connection.CreateCommand();
IDataParameter param = cmd.CreateParameter();

Using this simple pattern, the only thing you would have to change besides special parameters (like text, image, etc) is the connection object and DataAdapters. YOu could abstract those away with a factory interface.

As a matter of fact, I have written a data provider independant database client with a SqlFactory built in. Find it at http://brandon.gobigfishgo.com under 'Open Source'

I think you should look at a very informative newsletter about these interfaces at
http://www.bytefx.com/newsletter.aspx
You have to 'register' or whatever to actually see it, but it will be a lot more informative than I can be here.|||Really depends on the load to decide whether it would be worth it. SqlClient namespace classes implement a TDSParser which means they deal directly with the stream coming back from SQL; whereas OLEDB stuff runs through a more generic filter.

In your position I would look at the effort to convert, versus the actual chance that the application would be under such a stressful mode that users would experience the slight difference in performance.