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.
Can only be used for connecting to SQL Server 2000 and later. But you will get optimal performance when connecting to those databases.
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.