ADO.NET Data Access Patterns

Understanding common strategies for interacting with data using ADO.NET.

Introduction

ADO.NET provides a robust set of classes for accessing and manipulating data in a .NET application. Choosing the right data access pattern is crucial for building efficient, scalable, and maintainable applications. This document outlines some of the most common and effective patterns used with ADO.NET.

1. Connected Data Access Pattern (Forward-Only, Read-Only)

This is the simplest pattern, where you establish a connection to the data source, execute a command, and process the results row by row. The connection remains open for the duration of the operation. This pattern is ideal for scenarios where you only need to read data sequentially and don't require it to be held in memory after processing.

When to Use:

• Reading large amounts of data that don't need to be manipulated or stored.
• Performing quick lookups or generating reports where each row is processed and discarded.
• Scenarios where minimizing memory usage is critical.

Key Classes:

• DbConnection (e.g., SqlConnection, OleDbConnection)
• DbCommand (e.g., SqlCommand, OleDbCommand)
• DbDataReader (e.g., SqlDataReader, OleDbDataReader)

Example:

using System.Data.SqlClient;

// ...

using (SqlConnection connection = new SqlConnection("your_connection_string"))
{
    connection.Open();
    string query = "SELECT CustomerID, CompanyName 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"]}");
            }
        }
    }
}
            

2. Disconnected Data Access Pattern

This is perhaps the most common and flexible pattern. It involves retrieving data from the data source into an in-memory object, typically a DataSet or DataTable. The connection to the data source is then closed. You can then manipulate this in-memory data independently of the original data source. When ready, you can reconnect and submit the changes back to the data source.

When to Use:

• When you need to display data in a UI (e.g., WinForms, WPF, ASP.NET).
• When you need to perform complex data manipulation, filtering, or sorting in memory.
• When multiple operations need to be performed on the same set of data without keeping the connection open.
• For scenarios involving batch updates or synchronizing data.

Key Classes:

• DataSet
• DataTable
• DataRow
• DataAdapter (e.g., SqlDataAdapter, OleDbDataAdapter)

Example (using SqlDataAdapter and DataSet):

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

// ...

string connectionString = "your_connection_string";
string query = "SELECT OrderID, OrderDate FROM Orders";
DataSet dataSet = new DataSet();

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
    {
        adapter.Fill(dataSet); // Opens connection, fills DataSet, closes connection
    }
}

// Manipulate data in the DataSet (connection is closed here)
DataTable ordersTable = dataSet.Tables[0];
foreach (DataRow row in ordersTable.Rows)
{
    Console.WriteLine($"Order ID: {row["OrderID"]}, Date: {row["OrderDate"]}");
}

// To update the database (requires more setup with UpdateCommand)
// using (SqlConnection connection = new SqlConnection(connectionString))
// {
//     using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
//     {
//         // Define UpdateCommand, InsertCommand, DeleteCommand for SqlDataAdapter
//         // ...
//         adapter.Update(dataSet);
//     }
// }
            

3. Repository Pattern

While not strictly an ADO.NET pattern, the Repository pattern is a popular architectural pattern that complements ADO.NET. It abstracts the data access logic into a separate layer, making the rest of your application independent of the specific data access technology (like ADO.NET, Entity Framework, etc.).

How it Works:

A repository class acts as a mediator between the domain objects and data mapping layers. It provides methods for querying and persisting domain objects, hiding the underlying data access mechanisms.

Benefits:

• Decouples business logic from data access logic.
• Improves testability by allowing mocking of repositories.
• Facilitates switching data access technologies without affecting the business logic.

Example Structure:

// Interface
public interface ICustomerRepository
{
    Customer GetCustomerById(int id);
    IEnumerable<Customer> GetAllCustomers();
    void AddCustomer(Customer customer);
    void UpdateCustomer(Customer customer);
    void DeleteCustomer(int id);
}

// ADO.NET Implementation
public class AdosCustomerRepository : ICustomerRepository
{
    private readonly string _connectionString;

    public AdosCustomerRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public Customer GetCustomerById(int id)
    {
        // ADO.NET code to fetch customer from DB
        // ... return new Customer { ... };
        return null; // Placeholder
    }

    public IEnumerable<Customer> GetAllCustomers()
    {
        // ADO.NET code to fetch all customers
        // ... return list of Customers;
        return new List<Customer>(); // Placeholder
    }

    public void AddCustomer(Customer customer)
    {
        // ADO.NET code to insert customer
    }

    public void UpdateCustomer(Customer customer)
    {
        // ADO.NET code to update customer
    }

    public void DeleteCustomer(int id)
    {
        // ADO.NET code to delete customer
    }
}

// Usage in Business Logic
// public class CustomerService
// {
//     private readonly ICustomerRepository _repository;
//
//     public CustomerService(ICustomerRepository repository)
//     {
//         _repository = repository;
//     }
//
//     public Customer GetCustomer(int id)
//     {
//         return _repository.GetCustomerById(id);
//     }
//     // ...
// }
            

4. Micro-ORM Pattern

Micro-ORMs (Object-Relational Mappers) like Dapper or PetaPoco offer a middle ground between raw ADO.NET and full-fledged ORMs like Entity Framework. They simplify the mapping of query results to objects while still allowing you to write SQL queries.

Benefits:

• Improved performance compared to full ORMs for specific scenarios.
• More control over SQL queries.
• Reduced boilerplate code compared to raw ADO.NET.

Example (using Dapper):

using System.Data.SqlClient;
using System.Collections.Generic;
using Dapper; // Requires Dapper NuGet package

// Assuming Customer class exists

// ...

string connectionString = "your_connection_string";
int customerId = 5;

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    var customer = connection.QueryFirstOrDefault<Customer>(
        "SELECT CustomerID, CompanyName FROM Customers WHERE CustomerID = @Id",
        new { Id = customerId });

    if (customer != null)
    {
        Console.WriteLine($"Found Customer: {customer.CompanyName}");
    }
}
            

Conclusion

The choice of data access pattern depends heavily on the specific requirements of your application. For simple data retrieval, the connected pattern might suffice. For rich user interfaces and complex data manipulation, the disconnected pattern using DataSet/DataTable is robust. For better architecture and testability, consider implementing the Repository pattern, potentially using a micro-ORM for the actual data retrieval within your repository implementations.