ADO.NET Data Access Concepts

Microsoft Developer Network

ADO.NET is a set of .NET Framework classes that expose data access services to. NET programmers. ADO.NET is composed of two main parts: the DataSet and the Data Provider.

The ADO.NET architecture allows you to build application components that efficiently interact with data sources, such as relational databases, at the same time that you build services that manage data manipulation and distribution.

The DataSet Object Model

The DataSet object represents a collection of tables, relationships, and constraints for data within an application. It is an in-memory representation of data, allowing you to work with data independently of the data source.

Key Components of a DataSet:

The DataSet is designed to work with data from multiple sources, including SQL Server, Oracle, and XML. It can also be used to manipulate data that has not been retrieved from a data source, such as data generated programmatically.

The Data Provider Model

A Data Provider is a set of .NET Framework classes that plug into ADO.NET to expose data manipulation capabilities for a data source. Each Data Provider for a given data source provides a set of objects that expose the commands, connections, and data readers that are specific to that data source.

The common .NET data providers include:

Core Provider Objects:

Typical ADO.NET Workflow

A common workflow for interacting with a database using ADO.NET involves the following steps:

  1. Establish a Connection: Create a connection object (e.g., SqlConnection) and open it.
  2. Create a Command: Instantiate a command object (e.g., SqlCommand) with a SQL statement or stored procedure name and associate it with the connection.
  3. Execute the Command:
    • For queries that return data, use a DataReader to read the results row by row, or use a DataAdapter to fill a DataSet.
    • For commands that do not return data (e.g., INSERT, UPDATE, DELETE), use the ExecuteNonQuery() method.
  4. Process Results: If data was retrieved, process it as needed.
  5. Close the Connection: Close the connection to release resources. It is best practice to use using statements to ensure connections are properly closed and disposed of.

Example: Retrieving Data

Here's a simplified C# example of retrieving data using a DataReader:


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

public class DataRetriever
{
    public void GetData(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string sql = "SELECT CustomerID, CompanyName FROM Customers";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                    }
                }
            }
        }
    }
}

Disconnected vs. Connected Scenarios

ADO.NET supports both connected and disconnected data access scenarios:

Related Topics: