ADO.NET

ADO.NET is a set of .NET Framework classes that expose data access services to .NET programmers. ADO.NET provides a rich set of components for creating distributed data-driven applications. It allows applications to retrieve data from and manipulate data in data sources. ADO.NET works with relational and non-relational data sources, making the data accessible from dissimilar data sources. By using ADO.NET, developers can build components that interact with data sources such as SQL Server, Oracle, or XML files.

Introduction to ADO.NET

ADO.NET is the foundational technology in the .NET ecosystem for data access. It's a set of classes that allows you to interact with data sources, including relational databases, XML files, and other data sources. It evolved from earlier Microsoft data access technologies like ADO (ActiveX Data Objects) but is completely rewritten for the .NET Framework, offering improved performance and a managed code environment.

Core Components of ADO.NET

ADO.NET is built around several key objects that work together to facilitate data access:

• Connection Objects: Establish a connection to a data source. Different providers have different connection objects (e.g., SqlConnection for SQL Server, OracleConnection for Oracle).
• Command Objects: Represent SQL statements or stored procedures to be executed against a data source.
• DataReader Objects: Provide a forward-only, read-only stream of data from a data source, offering efficient access to data.
• DataAdapter Objects: Act as a bridge between a DataSet and a data source. They retrieve data from the source and populate the DataSet, and also manage updates back to the source.
• DataSet Objects: An in-memory representation of data. It's a collection of DataTable objects, allowing for complex data structures and disconnected data manipulation.
• DataTable Objects: Represent a single table of data in memory, complete with rows and columns.

Data Providers

ADO.NET uses a provider model, where each type of data source has a corresponding provider. These providers are implemented as .NET assemblies and include classes for connecting, commanding, and retrieving data. Common providers include:

• System.Data.SqlClient (for SQL Server)
• System.Data.OleDb (for OLE DB data sources like Access)
• System.Data.Odbc (for ODBC data sources)
• System.Data.OracleClient (for Oracle databases, though the managed provider is now deprecated in favor of Oracle's official .NET drivers)

Connected Data Access

This model involves maintaining an active connection to the data source throughout the data access operation. It's typically used when you need to perform operations that require an open connection, such as executing a single query or transaction.

Example: Executing a Query

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

public class ConnectedExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Your_Connection_String_Here";
        string query = "SELECT CustomerID, CompanyName FROM Customers;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection opened successfully.");

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error: {e.Message}");
            }
        }
    }
}

Disconnected Data Access

In this model, you retrieve data and then close the connection. Operations are performed on an in-memory copy of the data (like a DataSet), and the connection is reopened only when you need to update the data source.

This approach is highly beneficial for applications that need to:

• Reduce server load by keeping connections open for shorter durations.
• Handle data in the client application without requiring a constant connection.
• Allow multiple users to work with data concurrently without blocking each other's connections.

Datasets and DataTables

The DataSet object is a core component of ADO.NET's disconnected data access. It acts as an in-memory cache that can hold multiple tables, relationships, and constraints. A DataSet is composed of DataTable objects, each representing a single table of data with columns and rows.

Key Features of DataSet:

• In-memory cache: Stores data independently of the data source.
• Multiple tables: Can hold several related tables.
• Relationships: Defines parent-child relationships between tables.
• Constraints: Enforces data integrity (e.g., unique keys).
• Row States: Rows can have states like Added, Modified, Deleted, or Unchanged, which are crucial for updates.

DataAdapters

DataAdapter objects are the primary mechanism for populating a DataSet and synchronizing it with a data source. They expose four core properties:

• SelectCommand: Retrieves records from the data source.
• InsertCommand: Executes an INSERT statement to add new records.
• UpdateCommand: Executes an UPDATE statement to modify existing records.
• DeleteCommand: Executes a DELETE statement to remove records.

When you call the Fill method of a DataAdapter, it executes the SelectCommand and populates the DataSet. When you call the Update method, it examines the row states in the DataSet and executes the appropriate InsertCommand, UpdateCommand, or DeleteCommand for each modified row.

Executing Commands

Command objects are used to execute SQL statements or stored procedures against a data source. They can be used to perform data manipulation language (DML) operations like INSERT, UPDATE, DELETE, or data definition language (DDL) operations like CREATE TABLE.

Methods for Executing Commands:

• ExecuteNonQuery(): Returns the number of rows affected by the command (useful for INSERT, UPDATE, DELETE).
• ExecuteReader(): Returns a DataReader object (for SELECT statements).
• ExecuteScalar(): Returns the value of the first column of the first row in the result set (useful for aggregate functions like COUNT, SUM).

Transactions

ADO.NET provides support for database transactions to ensure data integrity. You can use the BeginTransaction() method of the Connection object to start a transaction. Once started, all commands executed within that transaction share the same connection and are committed or rolled back together.

using (SqlTransaction transaction = connection.BeginTransaction())
{
    try
    {
        // Execute commands within the transaction
        SqlCommand command1 = new SqlCommand("UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;", connection, transaction);
        command1.ExecuteNonQuery();

        SqlCommand command2 = new SqlCommand("UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;", connection, transaction);
        command2.ExecuteNonQuery();

        // If all commands succeed, commit the transaction
        transaction.Commit();
        Console.WriteLine("Transaction committed successfully.");
    }
    catch (Exception ex)
    {
        // If any command fails, roll back the transaction
        transaction.Rollback();
        Console.WriteLine($"Transaction failed: {ex.Message}. Rolled back.");
    }
}

Error Handling

When executing commands or processing data, errors can occur. ADO.NET raises exceptions, typically SqlException (for SQL Server) or general DbException, which contain information about the error. It's crucial to wrap your data access code in try-catch blocks to handle these exceptions gracefully, ensuring that resources are released and the application remains stable.

Performance Considerations

• Use SqlDataReader for forward-only, read-only access to minimize memory usage and improve performance over DataSet when appropriate.
• Only select the columns you need.
• Use parameterized queries to prevent SQL injection and improve query plan caching.
• Optimize your SQL queries and database schema.
• Manage connection pooling effectively.
• Use using statements to ensure proper disposal of connection and command objects.

API Reference

This overview covers the fundamental aspects of ADO.NET. Exploring specific provider documentation and advanced features will further enhance your data access capabilities.