ADO = Active Data Objects

Understanding ADO.NET Providers

ADO.NET supports multiple data providers, each of which is optimized to interact with a specific DBMS.

  1. DbConnection : Provides the ability to connect to and disconnect from the data store. Connection objects also provide access to a related transaction object.
  2. DbCommand : Represents a SQL query or a stored procedure. Command objects also provide access to the provider’s data reader object.
  3. DbDataReader : Provides forward-only, read-only access to data using a server-side cursor.
  4. DbDataAdapter : Transfers DataSets between the caller and the data store. Data adapters contain a connection and a set of four internal command objects used to select, insert, update, and delete information from the data store.
  5. DbParameter : Represents a named parameter within a parameterized query.
  6. DbTransaction : Encapsulates a database transaction.

ADO.NET Data Providers

Microsoft SQL Server : Microsoft.Data.SqlClient

ODBC : System.Data.Odbc

OLE DB (Windows only) : System.Data.OleDb

MySQL : Mysql.Data

The Types of the System.Data Namespace

Of all the ADO.NET namespaces, System.Data is the lowest common denominator. This namespace

contains types that are shared among all ADO.NET data providers, regardless of the underlying

data store.

  1. Constraint : Represents a constraint for a given DataColumn object
  2. DataColumn : Represents a single column within a DataTable object
  3. DataRelation : Represents a parent-child relationship between two DataTable objects
  4. DataRow : Represents a single row within a DataTable object
  5. DataSet : Represents an in-memory cache of data consisting of any number of interrelated DataTable objects
  6. DataTable : Represents a tabular block of in-memory data
  7. DataTableReader : Allows you to treat a DataTable as a firehose cursor (forward-only, read-only data access)
  8. DataView : Represents a customized view of a DataTable for sorting, filtering, searching, editing, and navigation
  9. IDataAdapter : Defines the core behavior of a data adapter object
  10. IDataParameter : Defines the core behavior of a parameter object
  11. IDataReader : Defines the core behavior of a data reader object
  12. IDbCommand : Defines the core behavior of a command object
  13. IDbDataAdapter : Extends IDataAdapter to provide additional functionality of a data adapter object
  14. IDbTransaction : Defines the core behavior of a transaction object

The Role of the IDbConnection Interface

This interface defines a set of members used to configure a connection to a specific data store. It also allows you to obtain the data provider’s transaction object.

The Role of the IDbTransaction Interface

The overloaded BeginTransaction() method defined by IDbConnection provides access to the provider’s

transaction object.

The Role of the IDbCommand Interface

IDbCommand interface, which will be implemented by a data provider’s command object. Like other data access object models, command objects allow programmatic manipulation of SQL statements, stored procedures, and parameterized queries.

The Role of the IDbDataParameter and IDataParameter Interfaces

The Parameters property of IDbCommand returns a strongly typed collection that implements IDataParameterCollection. This interface provides access to a set of IDbDataParameter-compliant class types (e.g., parameter objects).

IDbDataParameter extends the IDataParameter interface to obtain the some additional behavior.

The Role of the IDbDataParameter and IDataParameter Interfaces

You use data adapters to push and pull DataSets to and from a given data store. The IDbDataAdapter interface defines the following set of properties that you can use to maintain the SQL statements for the related select, insert, update, and delete operations.

IDataAdapter interface defines the key function of a data adapter type: the ability to transfer DataSets between the caller and underlying data store using the Fill() and Update() methods.

The Role of the IDataReader and IDataRecord Interfaces

IDataReader, which represents the common behaviors supported by a given data reader object. When you obtain an IDataReader-compatible type from an ADO.NET data provider, you can iterate over the result set in a forward-only, read-only manner.

IDataReader extends IDataRecord, which defines many members that allow you to extract a strongly typed value from the stream, rather than casting the generic System.Object retrieved from the data reader’s overloaded indexer method.

Abstracting Data Providers Using Interfaces

public static void OpenConnection(IDbConnection cn)
 {
     // Open the incoming connection for the caller.
     connection.Open();
 }
 
enum DataProviderEnum
 {
     SqlServer,
#if PC
OleDb,
     #endif
     Odbc,
     None
 }

<PropertyGroup>
         ...
        <DefineConstants>PC</DefineConstants>
</PropertyGroup>

static void Main(string[] args)
 {
SetUp(DataProviderEnum.MySql);
 }
private static void SetUp(DataProviderEnum mySql)
 {
     IDbConnection myConnection = GetConnection(mySql);
     // Open,use, and close connection
 }
public static IDbConnection GetConnection(DataProviderEnum dP) => dP switch
 {
     DataProviderEnum.MySql => new MySqlConnection(),
     _ => null
 };

The ADO.NET Data Provider Factory Model

The .NET data provider factory pattern allows you to build a single code base using generalized data access

types.