Connected Data Access with ADO.NET

Connected data access in ADO.NET refers to a programming model where your application maintains an open connection to the data source for the duration of an operation. This model is ideal for scenarios where you need to perform real-time operations, such as inserting, updating, or deleting data, or when you need to read data and act upon it immediately.

The Core Components

The connected data access model primarily revolves around the following ADO.NET objects:

Connection: Represents an open connection to a data source. Different data providers expose their own connection objects (e.g., SqlConnection for SQL Server, OleDbConnection for OLE DB).
Command: Represents a Transact-SQL statement or stored procedure to execute against a data source. Like connections, commands are provider-specific (e.g., SqlCommand, OleDbCommand).
DataReader: Provides a forward-only, read-only stream of data from the data source. It is highly efficient for reading large result sets quickly. The primary object here is DataReader (e.g., SqlDataReader, OleDbDataReader).

How Connected Data Access Works

The typical workflow for connected data access involves these steps:

Establish a Connection: Create an instance of a Connection object and open it, providing the necessary connection string.
Create a Command: Create an instance of a Command object, associate it with the connection, and set its CommandText property to the SQL statement or stored procedure you want to execute.
Execute the Command:
- For queries that return data, use the ExecuteReader() method to obtain a DataReader.
- For queries that don't return data (e.g., INSERT, UPDATE, DELETE), use the ExecuteNonQuery() method.
- For queries that return a single value, use the ExecuteScalar() method.
Process Data (if applicable): If a DataReader was obtained, iterate through its rows using a loop (e.g., while (reader.Read())) and access column values.
Close the Connection: Crucially, close the connection when you are finished to release resources. This can be done explicitly with the Close() method or implicitly by using a using statement (recommended for ensuring proper disposal).

Example: Retrieving Data

Here's a simplified C# example demonstrating connected data access to retrieve data using SqlConnection and SqlDataReader:


using System;
using System.Data.SqlClient;

public class ConnectedDataAccessExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string query = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";

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

                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // Add parameter to prevent SQL injection
                    command.Parameters.AddWithValue("@City", "London");

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine($"CustomerID: {reader["CustomerID"]}, CompanyName: {reader["CompanyName"]}");
                            }
                        }
                        else
                        {
                            Console.WriteLine("No rows found.");
                        }
                    }
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error: {e.Message}");
            }
        }
        Console.WriteLine("Connection closed.");
    }
}

Key Considerations:

Resource Management: Always ensure that connections and other disposable ADO.NET objects are properly closed and disposed of. The using statement is the preferred way to handle this.
Performance: Connected data access is generally faster for single operations or when immediate feedback is needed because it avoids the overhead of creating and populating a DataSet. However, holding connections open for extended periods can tie up database resources.
Concurrency: In a connected model, the data might change between the time you read it and the time you try to update it. Applications need to handle potential concurrency issues.
Error Handling: Implement robust error handling to manage potential exceptions during connection, command execution, or data retrieval.

When to Use Connected Data Access:

Performing single INSERT, UPDATE, or DELETE operations.
Executing stored procedures that perform actions without needing to return large datasets.
Reading a small number of records very quickly.
When you need the most up-to-date data and can tolerate potential concurrency conflicts.

For more complex scenarios involving large amounts of data or when you need to work with data offline, the disconnected data access model using DataAdapters and DataSets might be more suitable.