C# Data Access Layer

By Fons Sonnemans (updated may 2006)

Download DAL.zip (VS.NET 2003 & VS 2005 projects)

A well-developed application, no matter how large or small, should be written with maintenance in mind. One of the best approaches to maintainability is to use a layered paradigm when designing the application. This approach dictates that the application be designed in at least three layers: a Presentation Layer (PL), a Business Logic Layer (BLL), and a Data Access Layer (DAL). These layers can be coded within the application, or they can be separate components outside of the application. This separation allows for a more easily maintained code base and also ensures that when the time comes to turn the code into a component, the effort required will be minimal. Separating this functionality into a component also makes the code far easier to debug, extend, and reuse.

In this article I demonstrate a DAL which uses the Northwind database of a SQL Server 2000. All access to the database is done using Stored Procedures. This to maximize the performance and improve the security. The SqlClient provider is used to access the database.



Note: The Business Logic Layer is not implemented in the provided sample application. The NorthwindDemoForm (Presentation Layer) access the DAL directly, you should problably not do this in your applications.


NorthwindStoredProcedure class

The NorthwindStoredProcedure class encapsulates the Northwind stored procedures. There is a method for each stored procedure which executes the procedure. There are only 2 methods implemented for the sample application.

public static class NorthwindStoredProcedures
{

    public static DataTable GetSalesByCategory(string category, int orderYear)
    {
        using (SqlStoredProcedure sp = new SqlStoredProcedure("dbo.SalesByCategory",
                 ConfigManager.GetNewSqlConnection)) {
        
            sp.AddParameterWithValue("@CategoryName", SqlDbType.NVarChar, 15,
                ParameterDirection .Input, category);
                        
            sp.AddParameterWithValue("@OrdYear", SqlDbType.NVarChar, 4,
                ParameterDirection .Input, orderYear.ToString());


            return sp.ExecuteDataTable();
        }
    }
    
    public static DataTable GetTenMostExpensiveProducts()
    {
        using (SqlStoredProcedure sp = new SqlStoredProcedure("dbo.[Ten Most Expensive Products]",
                 ConfigManager.GetNewSqlConnection)) {
            return sp.ExecuteDataTable();
        }
    }
}

SqlStoredProcedure Class

The SqlStoredProcedure class is a SqlCommand wrapper for SQL Server which offers next to all normal 'execute' mehods some extra features:                

  • The type is set to 'StoredProcedure'.
  • The Timeout is set to 0.
  • A 'non-open' Connection is Opened and Closed automatically.
  • ReturnValue support.
  • Tracing support
  • ExecuteDataSet and ExecuteDataTable methods
  • Advanced Error handling is provided (see below).
  • Empty string parameter values can automatically be converted to a DbNull value, this simplifies the use of NOT NULL constraints.
  • A null parameter value is automatically converted to a DbNull value.
Make sure to close your database connections
A SqlConnection object which falls out of scope without closing it remains open and stuck in the connection pool until sometime in the spring. This means its essential (critical) to use the Close method on your opened Connections before they fall out of scope.                

The SqlStoredProcedure class will Open and Close the 'non-open' Connection automatically for you in the Execute methods except the ExecuteReader() method. You can ask the ExecuteDataReader to automatically close the connection for you, but this does not happen until you close the DataReader itself.                

DataReader dr = sp.ExecuteReader(CommandBehavior.CloseConnection)                    

You can create a SqlStoredProcedure object and give it an 'open' connection. This connection is not closed by any Execute method. This to support RDBMS transactions (see below). Make sure you close it yourself.

Advanced Error Handling    

The SqlClient throw a SqlException when an Execute method fails. You can then inspect the SqlException.Number property to figure out what exactly went wrong. I have tried to make this easier. All SqlExceptions are converted to a DalException. The Number of some common errors is used to create more specific exceptions like the DalUniqueConstraintException, DalDeadLockException, DalLoginException and the DalForeignKeyException. The calling layers can catch these exceptions and don't have to inspect the SqlClient.Number. This makes the software easier to use and maintain.    

 

The TranslateException method of the SqlStoredProcedure class translates SqlExceptions to DalExceptions. 

/// <summary>
/// Executes the StoredProcedure on the Connection and builds a DataTable object.
/// </summary>
/// <returns>A DataTable object.</returns>
virtual public DataTable ExecuteDataTable() {
    DataTable dt = null;
    try {
        Prepare ("ExecuteDataTable");

        SqlDataAdapter a = new SqlDataAdapter(this.Command);
        dt = new DataTable();
        a .Fill(dt);

        TraceResult ("# Rows in DataTable = " + dt.Rows.Count);
    } catch (SqlException e) {
        throw TranslateException(e);
    } finally {
        CloseOpenedConnection ();
    }

    return dt;
}

/// <summary>
/// Translate a SqlException to the correct DALException
/// </summary>
/// <param name="ex">SqlException to be translated</param>
/// <returns>An DALException</returns>
protected Exception TranslateException(SqlException ex) {
    Exception dalException = null;

    SqlStoredProcedure .TraceSource.TraceEvent(TraceEventType.Error, _eventId, "{0} throwed exception: {1}", this.Name, ex.ToString());

    // Return the first Custom exception thrown by a RAISERROR
    foreach (SqlError error in ex.Errors) {
        if (error.Number >= 50000) {
            dalException = new DalException(error.Message, ex);
        }
    }

    if (dalException == null) {
        // uses SQLServer 2000 ErrorCodes
        switch (ex.Number) {
            case 17:
            //     SQL Server does not exist or access denied.
            case 4060:
            // Invalid Database
            case 18456:
                // Login Failed
                dalException = new DalLoginException(ex.Message, ex);
                break;
            case 547:
                // ForeignKey Violation
                dalException = new DalForeignKeyException(ex.Message, ex);
                break;
            case 1205:
                // DeadLock Victim
                dalException = new DalDeadLockException(ex.Message, ex);
                break;
            case 2627:
            case 2601:
                // Unique Index/Constriant Violation
                dalException = new DalUniqueConstraintException(ex.Message, ex);
                break;
            default:
                // throw a general DAL Exception
                dalException = new DalException(ex.Message, ex);
                break;
        }
    }

    // return the error
    return dalException;
}
SqlStoredProcedure.TranslateException method 

Transactions    

A transaction symbolizes code or a set of components or procedures which must be executed as a unit. All the methods must execute successfully or the complete unit fails. A transaction can be described to cover the ACID (Atomicity, Consistency, Isolation, Durability) properties for mission critical applications.    

A transaction should be kept as short as possible to avoid concurrency issues and to enable maximum number of positive commits.

I have implemented an extra sample method to demonstrate transactions:

CREATE PROCEDURE AddRegion ( @id int, @description nvarchar(50) )                
AS                
BEGIN                
     INSERT INTO region VALUES (@id, @description)                
END            
AddRegion Stored Procedure
 
public static void Add2Regions(int id, string description)
{
    // Open the Connection
    using (SqlConnection con = ConfigManager.GetNewSqlConnection) {
        con.Open();

        int retry = 5;
        while (retry > 0)
        {
            try
            {
                // Begin the Transaction
                using (SqlTransaction tran = con.BeginTransaction())
                {

                    // Add record 1
                    using (SqlStoredProcedure sp = new SqlStoredProcedure("dbo.AddRegion", con))
                    {
                        sp.AddParameterWithValue("@Id", SqlDbType.Int, 4,
                            ParameterDirection .Input, id);
                        sp.AddParameterWithValue("@description", SqlDbType.NVarChar, 50,
                            ParameterDirection .Input, description);
    
                        // Set the Transaction
                        sp.Transaction = tran;

                        // Execute
                        sp.ExecuteNonQuery();
                    }

                    // Add record 2
                    using (SqlStoredProcedure sp = new SqlStoredProcedure("dbo.AddRegion", con))
                    {
                        sp.AddParameterWithValue("@Id", SqlDbType.Int, 4,
                            ParameterDirection .Input, id + 1);
                        sp.AddParameterWithValue("@description", SqlDbType.NVarChar, 50,
                            ParameterDirection .Input, description);

                        // Set the Transaction
                        sp.Transaction = tran;

                        // Execute
                        sp.ExecuteNonQuery();
                    }

                    // Commit the Transaction
                    tran.Commit();

                    // Successful, no retries necessary
                    retry = -1;

                } // Automatic rollback when not committed
            }
            catch (DalDeadLockException)
            {
                retry--;
                if (retry == 0)
                {
                    // stop retry
                    throw;
                }
            }
        }
    }
}
Add2Regions method

Within the Try block the transaction is started and Commited if everything proceeds smoothly. Committing a transaction will write the changes to the database. If there is an exception thrown the transaction is rollbacked. This is automatically done by the Dispose() method of the SqlTransaction class. This Dispose() method is invoked by the using() statement.  

When in this sample a deadlock exception is catched the statement is retried five times. After that the exception is retrown. This implementation is very easy due to the DalDeadLockException thrown by the 'execute' methods of the SqlStoredProcedure class.

Tracing

In the .NET Framework 2.0 there is a new class System.Diagnostics.TraceSource which is used by the SqlStoredProcedure class to log all executes. With the switchValue you can specify whether you only want errors (Error) logged, or the name of all executed procedures (Info) or also all parameter values (Verbose).

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="ConnectionString"
                connectionString="Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=." />
    </connectionStrings>
    <system.diagnostics>
        <sources>
            <source name="SqlStoredProcedure" switchValue="Verbose">
                <listeners>
                    <add initializeData="c:\SqlStoredProcedureTrace.txt" type="System.Diagnostics.TextWriterTraceListener"
                        name="myLocalListener" traceOutputOptions="DateTime" />
                    <add name="consoleListener"/>
                    <remove name="Default" />
                </listeners>
            </source>
        </sources>
        <sharedListeners>
            <add type="System.Diagnostics.ConsoleTraceListener" name="consoleListener" />
        </sharedListeners>
        <trace autoflush="true" indentsize="4">
            <listeners>
                <remove name="Default" />
            </listeners>
        </trace>
    </system.diagnostics>
</configuration> <
App.config or Web.config (.NET 2.0)

In the .NET Framework 1.1 we use a System.Diagnostics.TraceSwich in combination with the System.Diagnostics.Trace class instead of the TraceSource.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="ConnectionString" value="Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=." />
    </appSettings>
    <system.diagnostics>
        <switches>
            <add name="SqlStoredProcedure" value="4" />
        </switches>
        <trace autoflush="true" indentsize="4">
            <listeners>
                <add name="myListener" type="System.Diagnostics.TextWriterTraceListener" initializeData="c:\myListener.log" />
            </listeners>
        </trace>
    </system.diagnostics>
</configuration>
App.config or Web.config (.NET 1.1)

Conclusion

The SqlStoredProcedure class can be used in all projects which use SQL Server 2000 and Stored Procedures. It takes care of a whole lot of plumbing and gives you a higher productivity and better reliability.