MSDN Documentation

Connected Data Access with ADO.NET

Connected data access in ADO.NET refers to a programming model where your application maintains an active connection to a data source (like a database) throughout the entire process of retrieving, manipulating, and persisting data. This contrasts with disconnected data access, where the connection is opened only to fetch or update data and then closed.

When to Use Connected Data Access

Connected data access is typically preferred for scenarios where:

Key Components for Connected Access

The primary classes used for connected data access reside in the System.Data namespace and provider-specific namespaces (e.g., System.Data.SqlClient for SQL Server, System.Data.OleDb for OLE DB data sources).

1. Connection Objects

The Connection object represents a unique session with a data source. It is responsible for establishing and managing the connection.

A typical connection string specifies the server address, database name, and authentication details.

2. Command Objects

The Command object represents a Transact-SQL statement or stored procedure to be executed against a data source.

Commands can be used to execute DML (Data Manipulation Language) statements like SELECT, INSERT, UPDATE, DELETE, and also DDL (Data Definition Language) statements.

3. DataReader Objects

The DataReader object provides a forward-only, read-only stream of data from the data source. It is highly efficient for reading large amounts of data sequentially.

You obtain a DataReader by executing a Command with the ExecuteReader() method.

Example: Executing a SELECT Query

C# Example


using System;
using System.Data;
using System.Data.SqlClient; // Or your specific provider

public class ConnectedDataAccessExample
{
    public static void ReadProducts(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connection opened successfully.");

                string query = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice > @minPrice";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    // Add parameter to prevent SQL injection
                    command.Parameters.AddWithValue("@minPrice", 50.00);

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            Console.WriteLine("Products with UnitPrice > 50.00:");
                            Console.WriteLine("------------------------------------");
                            while (reader.Read())
                            {
                                Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["UnitPrice"]}");
                            }
                            Console.WriteLine("------------------------------------");
                        }
                        else
                        {
                            Console.WriteLine("No products found matching the criteria.");
                        }
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"SQL Error: {ex.Message}");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"General Error: {ex.Message}");
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    Console.WriteLine("Connection closed.");
                }
            }
        }
    }

    public static void Main(string[] args)
    {
        // Replace with your actual connection string
        string connStr = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
        ReadProducts(connStr);
    }
}

Example: Executing an INSERT Statement

C# Example


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

public class InsertExample
{
    public static int AddNewCustomer(string connectionString, string customerName, string city)
    {
        int newCustomerId = -1;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                string query = "INSERT INTO Customers (CustomerName, City) VALUES (@name, @city); SELECT SCOPE_IDENTITY();";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@name", customerName);
                    command.Parameters.AddWithValue("@city", city);

                    // ExecuteScalar returns the first column of the first row in the result set
                    object result = command.ExecuteScalar();
                    if (result != null && result != DBNull.Value)
                    {
                        newCustomerId = Convert.ToInt32(result);
                        Console.WriteLine($"New customer added with ID: {newCustomerId}");
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"SQL Error: {ex.Message}");
            }
            finally
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                }
            }
        }
        return newCustomerId;
    }

    public static void Main(string[] args)
    {
        string connStr = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
        AddNewCustomer(connStr, "Acme Corporation", "Metropolis");
    }
}

Benefits of Connected Data Access

Considerations

Connected data access is a fundamental pattern in ADO.NET, providing direct and immediate interaction with your data sources.