C#. ADO .NET. Data providers (providers). ADO .NET namespaces

Introduction to ADO .NET. Data providers (providers). ADO .NET namespaces

Search other resources:


1. General information about ADO .NET. Data providers

The Microsoft .NET platform includes a number of namespaces that provide facilities for interacting with both local and remote databases. The classes hosted in these namespaces form the basis of a database access technology called ADO .NET (ActiveX Data Object for .NET). These ADO .NET tools provide access to various data providers. Each provider is programmed to interact with a specific database management system (DBMS). That is, there is no single universal set of classes (types) that interact with different DBMS.

Using a separate provider to access the corresponding DBMS allows you:

  • program a specific data provider to access any unique specific DBMS;
  • provide a direct connection of a specific data provider to the DBMS mechanism without the use of additional inter-level mapping tools.

For each specific provider (data provider) the following is defined:

  • namespace with corresponding types (classes) directly interacting with this provider;
  • a namespace containing basic functionality that interacts with any data provider.


2. The list of data provider objects. Table

In ADO .NET terminology, it is customary to define the concept of a data object based on its base class. For example, an instance of the DbConnection class is considered a Connection object, while an instance of the DbCommand class is commonly referred to as a Command object.

The table below shows the main objects of the ADO .NET data providers.



Base class Interface The purpose
Connection DbConnection IDbConnection Provides connection to the data source and disconnection from it, access to transaction objects
Command DbCommand IDbCommand Implements an SQL query or a stored procedure. Also provides access to a specific data provider’s data reader object
DataReader DbDataReader

IDataReader, IDataRecord

Represents read-only data access. Data is read in one direction with a server-side cursor
DataAdapter DbDataAdapter

IDataAdapter, IDbDataAdapter

Provides data exchange between the data store and the calling process using commands to select, insert, modify, and delete information
Parameter DbParameter

IDataParameter, IDbDataParameter

Implements a named parameter in a parameterized query
Transaction DbTransaction IDbTransaction Encapsulates transaction in database

From the classes (interfaces) of the above main objects, the classes of specific providers that have the prefix of the corresponding DBMS are inherited.

For example, the SqlConnection, OracleConnection, OdbcConnection, and OledbConnection classes are inherited from the DbConnection class. And from the DbCommand class, the SqlCommand, OracleCommand, OdbcCommand, OledbCommand classes are inherited.

Figure 1 shows the interaction of a client application with a database through a data provider.

C#. ADO .NET. Interaction of the client application with the database

Figure 1. Interaction of the client application with the database

The client application can be any type of application: console application, Windows Forms type application, WPF application, ASP .NET web page, WCF service, .NET code library, and so on.


3. Data providers. The list. Namespaces

The .NET Framework package includes a set of tools for working with various Microsoft and non-Microsoft data providers (particularly Oracle).

The table below shows the data providers supported in the .NET Framework. For each supplier, specify:

  • namespaces to include when used;
  • the names of the assemblies containing the implementation.
Data provider Namespace Assembly
Microsoft SQL Server System.Data.OleDb System.Data.Dll
Microsoft SQL Server Mobile System.Data.SqlClient System.Data.Dll
ODBC System.Data.SqlServerCe System.Data.SqlServerCe.dll
OLE DB System.Data.Odbc System.Data.Dll
Oracle Client System.Data.OracleClient System.Data.OracleClient.dll

Below is a brief description of each provider.

3.1. Microsoft SQL Server. The Microsoft SQL Server Data Provider provides direct access to SQL Server type databases. All the necessary tools for working with this provider are located in the System.Data.SqlClient namespace. Accordingly, the connection string for this namespace in C# will be as follows:

using System.DataSqlClient;

3.2. OLE DB (Object Linking and Embedding, Database).

The OLE DB Data Provider has the same basic functionality as Microsoft SQL Server. However, this provider uses an additional layer of interaction with the database, which results in a performance penalty compared to Microsoft SQL Server. The OLE DB provider must be used if there is a need to interact with a Microsoft Access database.

This provider comes in the System.Data.OleDb namespace. Accordingly, the connection string of the OLE DB provider will be the following:

using  System.Data.OleDb;

3.3. ODBC (Open DataBase Connectivity). It is a programming interface for various developers to create applications that access data without using a specific provider. To use the ODBC tools, you need to include the System.Data.Odbc namespace

using System.Data.Odbc;

3.4. Oracle Client.

The Oracle Client Data Provider is designed to interact with Oracle databases. There are two possible varieties of assemblies for this provider type. The first assembly from Microsoft, located in the System.Data.OracleClient.dll namespace. The second one is from Oracle, which can be downloaded from the Oracle official website www.oracle.com.

3.5. Third Party ADO .NET Data Providers

 ADO .NET technology allows you to connect third-party providers to access any type of DBMS. These systems can be either commercial or open source. Examples of such providers are SQLite, IBM DB2, MySQL, PostgreSQL, TurboDB, Sybase, and others.


4. Additional namespaces. The list

The Microsoft .NET Framework Library contains additional namespaces that are used when programming ADO .NET related tasks. These namespaces include the following:

  • Microsoft.SqlServer.Server – contains types for the operation of the CLR and SQL Server 2005 integration service;
  • System.Data – defines the basic ADO .NET types used by all providers. The namespace contains base classes and interfaces that represent the autonomous (disconnected) level (mode) of interaction with the database;
  • System.Data.Common – Contains types to be shared by all ADO.NET providers. This also includes general abstract classes;
  • System.Data.Sql – contains types (classes) that define Microsoft SQL Server objects that are installed in the current local network;
  • System.Data.SqlTypes – contains types used by Microsoft SQL Server, as well as support for CLR types;
  • System.Data.EntityClient – contains types that provide the functionality of the entity client layer. These types represent the Entity Framework concept for an ADO.NET data provider.


Related topics