Choosing a Data Provider

As you saw earlier, many data providers are 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? When choosing a data provider, the main criteria of selection is performance and multiple data source connectivity.

In brief, to work with SQL Server 7 or later databases, the 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 the Sql data provider provides classes that help you to convert from native SQL Server data types to the .NET data types, and vice versa.

OleDb data provider uses native OLE DB providers to access OLE DB data sources. OleDb data providers are useful because some database providers don't implement ODBC drivers to connect to a database through ODBC.

The Odbc data provider provides a way to work with different kinds of data sources through ODBC drivers. ODBC is an older data access technology, but many applications still use ODBC to access data sources. ODBC data providers provide a way to access ODBC data sources through ODBC drivers and ODBC Administration. 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 a generic class that can access multiple data sources 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, you also install the ODBC driver for these databases. So, you can write a generic application that accesses these data sources based on the ODBC Data Source Name (DSN). You can also pass the driver and data source information in the application itself, but the user has to only create a DSN from ODBC, and the application can use that DSN as the connection string in the application for the Odbc data provider.