SQL: Differences between OleDB Vs SQLClient?

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, so like you I couldn’t find anything to back that up. 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 that 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. It still didn’t work, of course, but the better error messages provided enough new information that I was able to fix the problem.

odbcclient and oledbclient are using native ODBC driver or OleDb provider. These are bridge for some data source that does not ship the managed provider.
sqlclient is using the “managed ADO.net provider for SQL Server”, which is written in managed code.

For performance aspect and many other aspects, the recommendation is to use the “sqlclient” since there are less switching between managed code and native code.

System.Data.SQLClient

Can only be used for connecting to SQL Server 2000 and later. But you will get optimal performance when connecting to those databases.

System.Data.OledbClient

Can be used to connected to SQL 6.5

OLEDBClient gives you ability to connect to other database like ORALCE or Access. But for working with SQL Server you will get better performance using SQLClient.

Note: For connecting to ORALCE Microsoft also has ORACLEClient.

System.Data.ODBCClient provides connection to legacy databases ( e.g. MS Access 97) using ODBC drivers.

Question:I have a VB.net 2005 application that will use both Access and SQL server databases. I have DSN names for both access and sql server database. I used OleDb.OleDbConnection to open the Access database and it works fine. I am trying to make it work with sql server database. Do I have to have to have separate source codes using Sqlclient.SqlConnection and its related name spaces? Can I use odbc.OdbcConnection name space for both Access and SQl server database? I don’t want to keep 2 separate source codes.

Answer: Yes. You make it work by using the DbProviderFactory and instead of declaring them as OLEDb.OLEDbConnection or SqlClient.SqlConnection you declare the variables using DbConnection and DbCommand and DbDataAdapter.

That is what the DbProviderFactory feature in ADO.NET is for. It allows you to work with OleDb or ODBC or SQL.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s