MSDN Documentation

Data Access with ADO.NET

ADO.NET is a set of .NET Framework classes that expose data access services to the .NET Framework developer. ADO.NET provides a rich set of components for creating distributed, data-sharing applications. It is an intermediary between a data source and the application, via the ADO.NET Provider model.

The core components of ADO.NET for data access revolve around establishing connections, executing commands, and processing the results. This section will guide you through the fundamental steps involved in interacting with data sources using ADO.NET.

1. Establishing a Connection

Before you can interact with any data source, you need to establish a connection. ADO.NET uses provider-specific connection objects for this purpose. For example, you would use SqlConnection for SQL Server, OracleConnection for Oracle, and so on.

using System.Data.SqlClient;

// ...

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Connection is now open and ready to be used
} // Connection is automatically closed here due to 'using' statement

2. Executing Commands

Once a connection is established, you can execute commands against the data source. This typically involves SQL statements for querying, inserting, updating, or deleting data. ADO.NET provides command objects like SqlCommand, OracleCommand, etc.

a) Retrieving Data with DataReader

The DataReader is an efficient way to retrieve a forward-only, read-only stream of data from a data source. It's ideal for scenarios where you need to process data row by row without loading the entire result set into memory.

using System.Data.SqlClient;

// ... (connection established as above)

string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";
using (SqlCommand command = new SqlCommand(query, connection))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}, Contact: {reader["ContactName"]}");
        }
    }
}

b) Executing Non-Query Commands

For operations that don't return a result set, such as INSERT, UPDATE, DELETE, or executing stored procedures, you can use ExecuteNonQuery(). This method returns the number of rows affected by the command.

using System.Data.SqlClient;

// ... (connection established as above)

string updateQuery = "UPDATE Products SET UnitPrice = UnitPrice * 1.10 WHERE CategoryID = 1";
using (SqlCommand command = new SqlCommand(updateQuery, connection))
{
    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"{rowsAffected} rows updated.");
}

3. Using DataSets and DataTables

For more complex data manipulation scenarios, or when you need to work with data offline, ADO.NET provides DataSet and DataTable objects. These are in-memory caches of data that can hold multiple tables and their relationships.

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

// ... (connection established as above)

string query = "SELECT * FROM Orders";
using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
{
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, "Orders"); // Fill the DataSet with data into a table named "Orders"

    // Access data from the DataTable
    DataTable ordersTable = dataSet.Tables["Orders"];
    foreach (DataRow row in ordersTable.Rows)
    {
        Console.WriteLine($"Order ID: {row["OrderID"]}, Customer ID: {row["CustomerID"]}");
    }
}

4. Transactions

Ensuring data integrity is crucial. ADO.NET supports transactions, which allow you to group multiple database operations into a single unit of work. If any operation within the transaction fails, the entire transaction can be rolled back, leaving the database in its original state.

using System.Data.SqlClient;

// ... (connection established as above)

connection.Open();
SqlTransaction transaction = null;

try
{
    transaction = connection.BeginTransaction();

    // First operation
    string sql1 = "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1";
    using (SqlCommand command1 = new SqlCommand(sql1, connection, transaction))
    {
        command1.ExecuteNonQuery();
    }

    // Second operation
    string sql2 = "UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2";
    using (SqlCommand command2 = new SqlCommand(sql2, connection, transaction))
    {
        command2.ExecuteNonQuery();
    }

    transaction.Commit(); // Commit the transaction if all operations succeed
    Console.WriteLine("Transaction committed successfully.");
}
catch (Exception ex)
{
    if (transaction != null)
    {
        transaction.Rollback(); // Roll back the transaction if an error occurs
        Console.WriteLine($"Transaction rolled back: {ex.Message}");
    }
}
finally
{
    if (connection.State == ConnectionState.Open)
    {
        connection.Close();
    }
}

This overview covers the basic building blocks of data access with ADO.NET. Explore the following sections for deeper dives into specific providers, data binding, and advanced techniques.