Microsoft Docs

ADO.NET Data Provider Model

The ADO.NET data provider model is a set of classes that expose data-access services for a data source. ADO.NET providers are designed to be lightweight, efficient, and scalable. They are implemented as managed assemblies and provide a consistent programming interface for interacting with various data sources, including relational databases, XML files, and flat files.

Each data provider typically includes a set of core classes that represent the fundamental components of data access:

Key Components of the Data Provider Model

1. Connection Objects

Connection objects are responsible for establishing and managing the connection to a data source. Each data provider has its own specific connection class (e.g., SqlConnection for SQL Server, OracleConnection for Oracle). These objects typically support methods for opening and closing the connection, as well as properties to manage connection strings and transaction scopes.


using System.Data.SqlClient;

// Example of opening a SQL Server connection
string connectionString = "Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    Console.WriteLine("Connection opened successfully.");
    // Perform database operations here
    connection.Close();
}

2. Command Objects

Command objects allow you to execute SQL statements or stored procedures against the data source. Similar to connection objects, each provider has its own command class (e.g., SqlCommand, OracleCommand). Command objects can be configured with a command text (the SQL query or procedure name) and a connection object. They also support the addition of parameters to prevent SQL injection and improve performance.


using System.Data.SqlClient;

// Example of executing a command
string connectionString = "...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Customers WHERE City = @City", connection);
    command.Parameters.AddWithValue("@City", "London");

    int customerCount = (int)command.ExecuteScalar();
    Console.WriteLine($"Number of customers in London: {customerCount}");
}

3. DataReader Objects

DataReader objects provide a fast, forward-only, read-only stream of data. This is the most efficient way to retrieve large amounts of data when you don't need to cache it in memory. Each data provider implements its own DataReader (e.g., SqlDataReader, OracleDataReader).


using System.Data.SqlClient;

// Example of using a DataReader
string connectionString = "...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers", connection);
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
        }
    }
}
Note: DataReaders are ideal for scenarios where you need to process data as it's retrieved, such as streaming data to a web page or performing calculations on large result sets without loading everything into memory.

4. DataAdapter Objects

DataAdapter objects are used to fill a DataSet or DataTable with data and to resolve changes made to the data back to the data source. They manage the interaction between a DataSet (an in-memory cache of data) and a data source. Common DataAdapter classes include SqlDataAdapter and OracleDataAdapter.


using System.Data.SqlClient;
using System.Data;

// Example of using a DataAdapter
string connectionString = "...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", connection);
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, "Customers");

    // Access data from the DataSet
    foreach (DataRow row in dataSet.Tables["Customers"].Rows)
    {
        Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
    }
}
Important: While DataAdapters offer flexibility for disconnected data scenarios, they can consume more memory than DataReaders due to the in-memory caching provided by DataSets.

5. Parameter Objects

Parameters are crucial for secure and efficient command execution. They allow you to pass values into SQL statements or stored procedures, preventing SQL injection vulnerabilities and enabling the database engine to cache query plans. Each command object has a Parameters collection where you add Parameter objects.


using System.Data.SqlClient;

// Example of using Parameters
string connectionString = "...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)", connection);

    SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.VarChar, 50);
    nameParam.Value = "Chai";
    command.Parameters.Add(nameParam);

    SqlParameter priceParam = new SqlParameter("@Price", SqlDbType.Money);
    priceParam.Value = 18.00;
    command.Parameters.Add(priceParam);

    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"{rowsAffected} row(s) inserted.");
}

Choosing the Right Provider

ADO.NET supports various data providers out-of-the-box. The most common ones include:

For specific data sources, you may need to install third-party data providers. The core ADO.NET concepts and interfaces remain consistent across different providers, making it easier to switch data sources with minimal code changes.