Difference Between SQL Connection and Oledb Connection

Difference between Sql Connection and OLEDB Connection

The advantage of using OleDbConnection is flexibility. You can change your database (for instance, move to Oracle)and not have to change your code.

If you using SQLServer as backend then use SQLConnection for better performance.

check with this link http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/fadb2742-d75a-49fb-a839-b2d4a9183998/

OleDbConnection : You can connect to any database, which you have provide for that.

Tip: use Universal Data Link File

What are the pros and cons of OleDB versus 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; 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.

C# SQL connection ODBC or OLEDB

Please check this Microsoft documentation:
https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ado-net-code-examples

As you can see, you are using SQLCLient, not OleDb or ODBC.

SQLClient is an specific data provider for SQL Server. Also, you can check this link to learn a bit more about OLE DB and ODBC, and their differences:
what is the difference between OLE DB and ODBC data sources?

what is the difference between OLE DB and ODBC data sources?

According to ADO: ActiveX Data Objects, a book by Jason T. Roff, published by O'Reilly Media in 2001 (excellent diagram here), he says precisely what MOZILLA said.

(directly from page 7 of that book)

• ODBC provides access only to relational databases

• OLE DB provides the following features
• Access to data regardless of its format or location
• Full access to ODBC data sources and ODBC drivers

So it would seem that OLE DB interacts with SQL-based datasources THRU the ODBC driver layer.

I'm not 100% sure this image is correct. The two connections I'm not certain about are ADO.NET thru ADO C-api, and OLE DB thru ODBC to SQL-based data source (because in this diagram the author doesn't put OLE DB's access thru ODBC, which I believe is a mistake).

What is difference in adodb and oledb?

Adodb (ActiveX Data Objects DB) is an API layer over OLE DB. It works well with MS-based databases such as Sql Server, providing a consistent API and optimizations. That being said you can use ADODB to connect with non-MS data sources as well but that would mean that you will require an OLEDB/ODBC Provider for the data source.

In simpler terms, to connect to any data source you need a driver. Here are a couple of common scenarios to think of:

1. ADODB for Data Source that has ODBC Driver Only - ADODB uses OLEDB Provider for ODBC which loads ODBC Driver which then connects to Data Source.
2. ADODB for Data Source with OLEDB Driver (like SQL Server) - ADODB uses OLEDB Provider for SQL Server to talk directly with DB.

Oledb (Object Linking and Embedding DB) is a standard format supported by a large number of dbs, so you can connect to oracle, db2 etc. using Oledb. You can also use OLEDB directly to connect to Sql Server but the API is messier as compared to a adodb connection which is optimized to work with Sql Server and MS Access.

ADO.Net is a .Net based db connection "architecture". In ADO.Net there's a library for Oledb - System.Data.OledbClient. Adodb has been replaced/upgraded and ADO.Net now uses the System.Data.SqlClient library for MS-based databases/data providers.

OleDB connection string for SQL Server in a C# program

This works as expected on my side. From the error message I strongly suspect that you are using the SqlConnection class instead of the OleDbConnection (Of course you need to use all the other classes provided by OleDb like OleDbCommand, OleDbDataReader etc...)

  string connStr = "Provider=SQLOLEDB;Data Source=<servername>;Initial Catalog=<dbname>;Integrated Security=SSPI";
  using(OleDbConnection cnn = new OleDbConnection(connStr))
  {
     ....
  }

---

Related Topics