Building an Agile SQL Data Access Layer

• Download source - 559.14 KB

Introduction

This article follows my previous one on "in-memory" data access in which I detailed an architecture for separating the data access layer from the application domain layer. The decoupling was obtained by defining a set of interfaces setting the contracts for retrieving and persisting objects. The implementation shown there was an in-memory persistence of objects that can be used to simulate the real data persistence for testing purposes and to defer the implementation of database structures, scripts and queries.

In this article I'll show the classes necessary to implement those same interfaces for the real db persistence. We'll develop the code necessary to substitute the in-memory classes with code that uses the ADO.NET SqlClient library to access a SQL Server instance (as in the diagram at the top of this article, see here for a complete explanation of the architecture).

Summary of the Interfaces to Implement

I'll recap the interfaces to derive from to implement a data access layer in the architecture.

The session interface provides connection and transaction management and acts as a factory for the classes that manage data access for the single entities.

public interface IOrdersSession: IDisposable
{
    IDbTransaction BeginTransaction();

    ICustomerDAL DbCustomers { get; }
    void Save(Customer customer);
    void Delete(Customer customer);

    IOrderDAL DbOrders { get; }
    void Save(Order order);
    void Delete(Order order);

    IOrderItemDAL DbOrderItems { get; }
    void Save(OrderItem orderItem);
    void Delete(OrderItem orderItem);

    IProductDAL DbProducts { get; }
    void Save(Product product);
    void Delete(Product product);
}

As a sample of the single data access interfaces we'll take the Order entity data access.

/// method to call to instantiate objects
public delegate Order OrderDataAdapterHandler(Guid id, Guid idCustomer,
    DateTime orderDate);

/// <summary>
/// Data access interface
/// </summary>

public interface IOrderDAL
{
    List<Order> Search(Guid id, Guid idCustomer, DateTime? orderDate,
    OrderDataAdapterHandler orderDataAdapter);
}

IOrdersSession SQL Implementation

The SqlOrdersSession class implements IOrdersSession. It should support transaction and connection management for data access across different entities (and, thus, database tables). To achieve this goal it contains two instance variables to store a SQL connection and a SQL transaction. Upon instantiation of the class the SQL connection is instantiated and it is opened. Since IOrdersSession implements IDisposable this class provides a Dispose method that rolls back the transaction if not committed and closes the SQL connection.

public class SqlOrdersSession: IOrdersSession
{
    private SqlConnection _sqlConnection;
    private SqlTransaction _sqlTransaction;

    public SqlOrdersSession(string connectionString)
    {
        OpenConnection(connectionString);
    }

    private void OpenConnection(string connectionString)
    {
        _sqlConnection = new SqlConnection(connectionString);
        try
        {
            _sqlConnection.Open();
        }
        catch (Exception ex)
        {
            ApplicationException exp = new ApplicationException("Db connection error.",
                ex);
            throw exp;
        }
    }
    
    #region IDisposable Members

    /// <summary>
    /// Clean up transaction if not committed and Sql connection
    /// </summary>
    public void Dispose()
    {
        if (_sqlTransaction != null && _sqlTransaction.Connection != null)
        {
            _sqlTransaction.Rollback();
        }
        if (_sqlConnection != null && _sqlConnection.State == ConnectionState.Open)
        {
            _sqlConnection.Close();
        }
    }
        
    //...

The class implements the IOrderSession BeginTransaction() method instantiating a SQL transaction and storing it in the _sqlTransaction instance variable.

public class SqlOrdersSession: IOrdersSession
{
    //...

    #region IOrdersSession Members

    public IDbTransaction BeginTransaction()
    {
        IDbTransaction st = _sqlConnection.BeginTransaction();
        _sqlTransaction = st as SqlTransaction;
        return st;
    }

It also implements a "Db" property for each entity to enable access to an instance of a class implementing the corresponding data access interface. In our example, considering the Order entity, we have a DbOrders property that gives access to an instance of the SqlOrderDAL class.

public class SqlOrdersSession: IOrdersSession
{
    private IOrderDAL _dbOrders; 

    //...
    
    public IOrderDAL DbOrders
    {
        get
        {
            if (_dbOrders == null)
                _dbOrders = new SqlOrderDAL(_sqlConnection, _sqlTransaction);
            return _dbOrders as IOrderDAL;
        }
        private set { _dbOrders = value; }
    }

    public void Save(Order order)
    {
        (DbOrders as SqlOrderDAL).Save(order);

    }

    public void Delete(Order order)
    {
        (DbOrders as SqlOrderDAL).Delete(order);
    }

IOrderDAL SQL Implementation

The SqlOrderDAL class implements IOrderDAL. It has an internal constructor since we don't want to let the client instantiate objects of this class directly. The constructor takes as parameters the SQL connection and the SQL trasaction owned by the SqlOrdersSession object.

public class SqlOrderDAL: IOrderDAL
{
	private SqlConnection _sqlConnection;
    private SqlTransaction _sqlTransaction;
	
    internal SqlOrderDAL(SqlConnection sqlConnection, SqlTransaction sqlTransaction)
    {
        _sqlConnection = sqlConnection;
        _sqlTransaction = sqlTransaction;
    }
    
    //...

This way all the single entities data access objects will share the same SQL connection and SQL transaction during the SqlOrdersSession lifetime. Thus the queries are transactional.

Object Creation in the DAL

SqlOrderDAL and the other entity DAL classes don't instantiate objects directly, they use the business logic factory method passed as delegate in the methods that retrieve objects off the database. In the following code snippet the method implementing search uses a OrderDataAdapterHandler delegate to create objects. In this manner no logic about object construction, validation or composition is demanded to the Data Access Layer.

public List<Order> Search(Guid id, Guid idCustomer, DateTime? orderDate,
    OrderDataAdapterHandler orderDataAdapter)
{
    List<Order> lro = new List<Order>();
    
    // ...

    SqlCommand sqlCmd = new SqlCommand(sb.ToString(), _sqlConnection);

    using (SqlDataReader dr = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
        while (dr.Read())
        {
            // Here the objects are instantiated with the factory method 
            // coming from the business logic through the delegate
            Order ro = orderDataAdapter(
                (Guid)dr["Id"],
                (Guid)dr["IdCustomer"],
                (DateTime)dr["OrderDate"]
                );
            lro.Add(ro);
        }
    }
    // ...
 
    return lro;
}

Using the DAL in the Business Logic Methods

In the business logic code the SqlOrdersSession is used as an instance of the IOrdersSession interface, so the code is exactly the same of that in the simulated, In-memory implementation (see previous article).

Here next is a method to retrieve Orders pertaining to a customer. The using statement is employed to assure proper session closing.

public List<Order> GetByCustomerId(Guid customerId)
{
    List<Order> lis;
    using (IOrdersSession sess = GBDipendencyInjection.GetSession())
    {
        lis = sess.DbOrders.Search(Guid.Empty, customerId, null, CreateOrder);
    }
    return lis;
}

The code calls the Search method of the IOrderDAL interface through the instance provided by IOrdersSession and the last parameter is CreateOrder, which is a method with the same signature of the delegate declared in the IOrderDAL interface. Following is the method code, it is a simple factory method. What it does is calling CustomerLogic to retreive the customer, verifying it is not null, instancing a new order and setting its values. Here additional logic can be added if, for example, it is necessary to instantiate different concrete implementations of classes inheriting from abstract classes or other construction and composition logic.

public Order CreateOrder(Guid id, Guid idCustomer, DateTime orderDate)
{
    Order order = new Order();
    CustomerLogic custumerLogic = new CustomerLogic();
    Customer customer = custumerLogic.GetById(idCustomer);
    if (customer == null)
    {
        throw new ArgumentException("Customer not found.");
    }
    order.Id = id;
    order.Customer = customer;
    order.OrderDate = orderDate;
    order.OrderItems = new List<OrderItem>();
    return order;
}

The last code snippet is an example of transaction usage. Here an order is saved, together with its order lines, in transactional manner.

public void SaveAllOrder(Order order)
{
    using (IOrdersSession sess = GBDipendencyInjection.GetSession())
    {
        System.Data.IDbTransaction trans = sess.BeginTransaction();
        sess.Save(order);
        foreach (OrderItem orderItem in order.OrderItems)
        {
            sess.Save(orderItem);
        }
        trans.Commit();
    }
}

Conclusions

The article's intent was to show how to implement data access to a SQL Server database separating this concern from the application domain. This has been done by making the data access code and the business logic code not to depend one on each other but by making them depend upon a common set of interfaces. This separation allows the domain code to ignore the details and inner workings of data access and persistence and the data access code to ignore inner construction logic of the objects it retrieves off the database. So we have data access that does and only knows about database structures and queries and business logic code that doesn't know anything about how objects are persisted and retrieved.

As a side effect we are able to use alternative data access code without changing domain logic code, this spawns the ability to temporarily simulate the real database access with an in-memory (fake) implementation as shown in my previous article or with mock objects, allowing to test the application in isolation from database access code and to defer the development of the real data access.