ADO.NET Data Access Concepts

This document provides a conceptual overview of data access within the .NET Framework using ADO.NET. ADO.NET is a set of .NET Framework classes that expose data access services to the .NET programmer. It is an integral part of the .NET Framework, providing consistent access to data sources like SQL Server, Oracle, XML, and OLE DB data sources as well as file systems.

The ADO.NET Data Provider Model

ADO.NET provides a data provider model that offers a consistent interface for accessing various data sources. Each data provider includes a set of classes that implement a common interface, allowing developers to write data access code that can be easily adapted to different back-end systems. The core components of a data provider include:

• Connection: Establishes a connection to a data source.
• Command: Represents a SQL statement or stored procedure to be executed against a data source.
• DataReader: Provides forward-only, read-only access to data.
• DataAdapter: Bridges the gap between a DataSet and a data source, populating a DataSet and handling updates.

Core ADO.NET Objects

DataSet

The DataSet is a collection of DataTable objects, representing a set of data entirely in memory. It is designed for disconnected data scenarios, allowing applications to retrieve data, modify it, and then send the changes back to the data source.

• DataTable: Represents a single table of data.
• DataRow: Represents a single row within a DataTable.
• DataColumn: Represents a column within a DataTable.

DataReader

The DataReader object provides a highly efficient way to retrieve data from a data source. It offers a forward-only, read-only stream of data. This means you can read data row by row, but you cannot navigate backward or modify the data directly through the reader.

// Example of using SqlDataReader
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"]}");
        }
    }
}

Command

The Command object is used to execute commands against a data source, such as SQL statements or stored procedures. It can return data, execute DML (Data Manipulation Language) statements, or call stored procedures.

Disconnected Data Access

ADO.NET excels in disconnected data access scenarios. This approach allows an application to retrieve data from a data source, close the connection, manipulate the data in memory (e.g., in a DataSet), and then reopen the connection to update the data source with the changes.

Benefits of Disconnected Data

• Scalability: Reduces the load on the data source by minimizing the time connections are open.
• Performance: Applications can perform operations on data in memory, which is often faster than constant database interaction.
• Responsiveness: The UI remains responsive as long as the connection is not being held open for long periods.

Transactions

ADO.NET supports transactions, ensuring data integrity by allowing a series of operations to be treated as a single unit of work. If any operation within the transaction fails, all operations can be rolled back to their original state.

// Example of using a transaction
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction();
    SqlCommand command = connection.CreateCommand();
    command.Transaction = transaction;

    try
    {
        command.CommandText = "UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE CategoryID = 1";
        command.ExecuteNonQuery();

        command.CommandText = "INSERT INTO AuditLog (Message) VALUES ('Price increase applied')";
        command.ExecuteNonQuery();

        transaction.Commit();
        Console.WriteLine("Transaction committed successfully.");
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        Console.WriteLine($"Transaction rolled back. Error: {ex.Message}");
    }
}

Understanding these core concepts is crucial for building robust and efficient data-driven applications with ADO.NET.