Data Providers

A data provider is a set of classes the manages and handles the connection to a data source such as SQL Server. The .NET Framework provides you with different data providers not just for SQL Server. For example, you can utilize the OLE DB and ODBC information connect to associate with other data sources, for example, Microsoft Access, MySql or even Oracle.

The following are some of the data providers provided by .NET. There are currently three data providers supported by .NET.

Provider Description
Sql Server Provides access to an SQL Server database.
OLE DB Provider Provides access to any data source that has an OLE DB driver such as MySQL,
ODBC Provider Provides access to any data source that has an ODBC driver.

Please note that another provider, Oracle Provider, has been considered deprecated since Microsoft recommends the use of ODP.NET instead. More data providers are offered by third party companies. Each data provider provides different classes that you can use to access your database. For example, the data provider for SQL Server consists of classes such as SqlConnectionSqlCommandSqlDataReader, and SqlDataAdapter. The data provider for OLE DB consists of classes such as OleDbConnectionOleDbCommandOleDbDataReader, and OleDbDataAdapter.The classes for the diverse suppliers acquire from a typical arrangement of classes and actualize a typical arrangement of interfaces to give reliable usefulness paying little respect to the supplier.

Data Provider
Sql Server OLE DB ODBC
Connection SqlConnection OleDbConnection OdbcConnection
Command SqlCommand OleDbCommand OdbcConnection
DataReader SqlDataReader OleDbDataReader OdbcDataReader
DataAdapter SqlDataAdapter OleDbDataAdapter OleDbDataAdapter

Each data provider is located in its own namespace. For example, the SQL Provider is found inside System.Data.SqlClient and the OLE DB provider is found inside System.Data.OleDb.

Data Provider Namespace
Sql Server System.Data.SqlClient
OLE DB System.Data.OleDb
ODBC System.Data.Odbc

The classes inside these namespaces implement a common interface. Consider the SqlConnection and OleDbConnection for example. They both implement the interface IDbConnection. If you look at this interface, you will see the common properties and methods of the Connection classes for all data providers.

Note that the following lessons are mostly concepts and present little code examples. This is OK as we will discuss how to use them in a working application after we have tackled this classes. You don’t have to memorize each of them. In fact, you can skip the section and proceed to create an ADO.NET application. You can then go back here whenever these classes are encountered on those lessons.