Data Access Objects in ADO.NET

This document provides an in-depth overview of Data Access Objects (DAOs) within the ADO.NET framework, a foundational technology for data access in .NET applications. DAOs represent a design pattern that abstracts the underlying data access logic, making your application more maintainable, testable, and flexible.

Introduction to Data Access Objects

The Data Access Object (DAO) pattern is a structural design pattern that isolates the data access logic from the rest of the application. Instead of scattering database queries and connection management throughout your codebase, you encapsulate them within dedicated DAO classes. This approach offers several benefits:

Abstraction: Hides the complexity of data retrieval and manipulation from business logic.
Maintainability: Changes to the database or data access technology (e.g., switching from SQL Server to Oracle) can be localized within the DAOs.
Testability: DAOs can be easily mocked or stubbed for unit testing business logic without requiring a live database connection.
Reusability: Common data access operations can be implemented once and reused across multiple parts of the application.

Core ADO.NET Objects for Data Access

ADO.NET provides a rich set of objects that form the building blocks for implementing the DAO pattern. The most crucial ones include:

Connection: Represents an open connection to a data source.
Command: Represents a Transact-SQL statement or stored procedure to execute against a data source.
DataReader: Provides a forward-only, read-only stream of data rows from the data source.
DataAdapter: Acts as a bridge between a DataSet and a data source to retrieve and save data.
DataSet: An in-memory representation of data, independent of the data source.
DataTable: Represents a single table of data in memory.

Implementing a Simple DAO

Let's consider a simple example of a DAO for managing Product entities. We'll use the SqlClient provider for SQL Server.

1. Define the Entity Class

First, define the class that represents our data:

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

2. Create the Product DAO Class

Next, create a class responsible for interacting with the database for Product objects.

using System;
using System.Collections.Generic;
using System.Data.SqlClient; // Or other provider namespace

public class ProductDao
{
    private readonly string _connectionString;

    public ProductDao(string connectionString)
    {
        _connectionString = connectionString ?? throw new ArgumentNullException(nameof(connectionString));
    }

    public Product GetProductById(int productId)
    {
        Product product = null;
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            string sql = "SELECT ProductId, Name, Price FROM Products WHERE ProductId = @ProductId";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@ProductId", productId);
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        product = new Product
                        {
                            ProductId = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Price = reader.GetDecimal(2)
                        };
                    }
                }
            }
        }
        return product;
    }

    public IEnumerable<Product> GetAllProducts()
    {
        var products = new List<Product>();
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            string sql = "SELECT ProductId, Name, Price FROM Products";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        products.Add(new Product
                        {
                            ProductId = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Price = reader.GetDecimal(2)
                        });
                    }
                }
            }
        }
        return products;
    }

    public void AddProduct(Product product)
    {
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@Name", product.Name);
                command.Parameters.AddWithValue("@Price", product.Price);
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}

Key Considerations for DAO Implementation

Connection Management: Always use using statements for Connection and Command objects to ensure resources are properly disposed of, even if errors occur.
Parameterization: Use parameterized queries (SqlParameter or AddWithValue) to prevent SQL injection vulnerabilities and improve performance.
Error Handling: Implement robust error handling (try-catch blocks) to gracefully manage database exceptions.
Transaction Management: For operations involving multiple database modifications, use SqlTransaction to ensure atomicity.
Provider Independence: For greater flexibility, consider abstracting the specific data provider (e.g., using interfaces and factories) to allow easy switching between different database systems.

ADO.NET Data Providers

ADO.NET supports various data providers, each designed to connect to a specific data source. Common providers include:

SqlClient: For Microsoft SQL Server.
OracleClient: For Oracle databases (note: this provider is deprecated in newer .NET versions).
MySqlClient: For MySQL databases (requires installing a third-party library).
Npgsql: For PostgreSQL databases (requires installing a third-party library).

The core ADO.NET objects (Connection, Command, etc.) have provider-specific implementations (e.g., SqlConnection, SqlCommand). Your DAO implementation will typically depend on a specific provider.

MSDN Documentation