ADO.net Data Providers

ADO.NET is an object-oriented set of libraries that allows you to interact with data sources.  Commonly, the data source is a data base, but it could also be a text file, an Excel spread sheet, or an XML file.  For the purposes of this tutorial, we will look at ADO.NET as a way to interact with a data base.
As you are probably aware, there are many different types of data bases available.  For example, there is Microsoft SQL Server, Microsoft Access, Oracle, Borland Interbase, and IBM DB2, just to name a few.  To further refine the scope of this tutorial, all of the examples will use SQL Server. 
The examples will use the Northwind database.  This tutorial is specifically for ADO.NET.

Data Providers

We know that ADO.NET allows us to interact with different types of data sources and different types of data bases.  However, there isn't a single set of classes that allow you to accomplish this universally.  Since different data sources expose different protocols, we need a way to communicate with the right data source using the right protocol.  Some older data sources use the ODBC protocol, many newer data sources use the OleDb protocol, and there are more data sources every day that allow you to communicate with them directly through .NET ADO.NET class libraries. 

ADO.NET provides a relatively common way to interact with data sources, but comes in different sets of libraries for each way you can talk to a data source.  These libraries are called Data Providers and are usually named for the protocol or data source type they allow you to interact with.  Table 1 lists some well known data providers, the API prefix they use, and the type of data source they allow you to interact with.

Table 1.  ADO.NET Data Providers are class libraries that allow a common way to interact with specific data sources or protocols.  The library APIs have prefixes that indicate which provider they support.

Provider Name	API Prefix	Data Source Description
ODBC Data Provider	Odbc	Data Sources with an ODBC interface.  Normally older data bases.
OleDb Data Provider	OleDb	Data Sources that expose an OleDb interface, i.e. Access or Excel.
Oracle Data Provider	Oracle	For Oracle Data Bases.
SQL Data Provider	Sql	For interacting with Microsoft SQL Server.
Borland Data Provider	Bdp	Generic access to many data bases such as Interbase, SQL Server, IBM DB2, and Oracle.

An example may help you to understand the meaning of the API prefix.  One of the first ADO.NET objects you'll learn about is the connection object, which allows you to establish a connection to a data source.  If we were using the OleDb Data Provider to connect to a data source that exposes an OleDb interface, we would use a connection object named OleDbConnection.  Similarly, the connection object name would be prefixed with Odbc or Sql for an OdbcConnection object on an Odbc data source or a SqlConnection object on a SQL Server data base, respectively.