This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".
As you've seen earlier there are many data providers available in ADO.NET. There may be cases when you can access a data source using more than one data provider. Now the question arises: which one is better? What are the selection criteria? The main criterion of selection is performance and multiple data source connectivity.
In brief, to work with SQL server 7 or later and MSDE databases, Sql data provider is the best choice. The Sql data provider bypasses many layers and directly connects to the internal layer of the infrastructure. Not only that but Sql data providers provide classes that help you to convert from native SQL server data typed to the .NET data types, and vice versa.
OleDb data providers are useful when you want to access OLEDB data sources such as MS-Access, XML, text, and other data sources. Because of the COM nature of the OLEDB API, it reduces many layers in comparison to ODBC data providers. This is also useful when you don't have an ODBC driver for a data source and have OLEDB provider.
The ODBC data providers utilize the existing way to work with data sources. ODBC is an older data access technology. Many applications still use ODBC to access data sources. ODBC data providers provide a way to access ODBC data source through ODBC drives and ODBC Admin. Database venders generally provide the ODBC drivers. Using ODBC you can access any data source for which you have an ODBC driver installed.
In general, ODBC data provider connectivity is faster than OLE-DB because of OLE DB's COM nature, which is very "chatty." Sql data provider is faster than ODBC to work with Sql server databases. But ODBC is useful when you need to write generic classes that can access multiple data source through ODBC for example, say you're writing an application that can work with multiple back-end servers including SQL server, Oracle, Access, and MySql. When you install these back- end servers, it also installs the ODBC driver for these databases. So you can write a generic application that can access these data source based on ODBC DSN. You can also pass the driver and data source information in the application itself, but the user has to create a DSN from ODBC, and the application can use that DSN as the connection string in the application for the ODBC data provider.
Adding Data Provider Namespace References
Data provider is defined in a separate namespace. The namespaces for the OleDb, Sql, and ODBC data providers are System.Data.OleDb, System.Data.Sqlclient, and Microsoft.Data.Odbc, respectively. Before using data adapter classes, you must add a reference of the correct namespace to your project. For example, add the following line for the Ole Db data provider:
using System.Data.OleDb;
Add the following line for the Sql data provider:
using System.Data.SqlClient;
And add the following line for the ODBC data provider:
using Microsoft.Data.Odbc;
Conclusion
Hope this article would have helped you in understanding How Do I Choose a Data Provider? See my other articles on the website on ADO.NET.
 | This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET. |