Data Layer Abstraction

One of the most common functions of an application is to retrieve data from a database. I’m going to provide a simple layer of abstraction to provide the basis from which to implement a service layer that can be used to retrieve data from a database to provide for both abstraction and scalability. The implementation provided is an attempt to limit the dependency on SQL Server and allow for the possibility to switch to Oracle or another database if needed with relative ease. There is some degree of support for Oracle within the .Net framework that would make the transition to this platform easier than others. There is the possibility that switching to DB2, MySql or another provider but there would be an amount of work to implement some classes to comply with the interfaces used in this implementation. Another important aspect of the abstraction is to provide the ability to unit test services which rely on these classes.

It should be mentioned that generally I’m not providing the interface definitions. Unless otherwise noted, assume all public members are exposed via the interface. I’ve also removed comments from all of the members for sake of brevity but comments should always be included!

The implementation below of the CommandExecutor is to execute the provided database command and return either an IDataReader or the number of records affected by the query depending on the method executed.

public class CommandExecutor : ICommandExecutor
{
    private readonly ILogger logger;

    public CommandExecutor(ILogger logger)
    {
        this.logger = logger;
    }

    public IDataReader ExecuteReader(IDbCommand command)
    {
        IDataReader reader;
        try
        {
            reader = command.ExecuteReader();
        }
        catch(SqlException e)
        {
            logger.Log(e);
            throw;
        }
        catch(InvalidOperationException e)
        {
            logger.Log(e);
            throw;
        }

        return reader;
    }

    public int ExecuteNonQueury(IDbCommand command)
    {
        try
        {
            return command.ExecuteNonQuery();
        }
        catch(SqlException e)
        {
            logger.Log(e);
            throw;
        }
        catch(InvalidOperationException e)
        {
            logger.Log(e);
            throw;
        }
    }
}

The purpose of the ObjectMapper class is to utilize the utility automapper which I’ve discussed in 2 parts here and here. This class will map the results contained in an IDataReader to a List of strongly typed objects that make sense in the domain in which they are used. We’ll see in a bit the configuration required in order to make this work (it’s pretty simple, only a single line of code if everything lines up right).

public class ObjectMapper : IMapper
{
    public TDestination Map<TSource, TDestination>(TSource source)
    {
        return Mapper.Map<TSource, TDestination>(source);
    }

    public TDestination Map<TSource, TDestination>(TSource source, TDestination destination)
    {
        return Mapper.Map(source, destination);
    }
}

Below is an implementation of a SQL Server specific connection. The connection string is pulled from the application configuration file of the project in which this class is contained. This class does not protect itself against some possible null reference exceptions and attempts to log critical errors regarding failure to connect to the database. Remember all public members are exposed through the interface and note the judicious use of non-SQL Server specified interfaces as the return types of the methods and properties in order to support the possibility of utilizing a different database platform. As a side effect, the use of these interfaces also eases the unit testability of this abstraction.

public class SqlConnection : IDatabaseConnection
{
    private readonly ILogger logger;

    public SqlConnection(ILogger logger)
    {
        this.logger = logger;

        ChangeConnection(Settings.Default.MADatabase);
    }

    public IDbConnection DbConnection { get; set; }

    public void Open()
    {
        DbConnection.Open();
    }

    public IDbTransaction GetSqlTransaction()
    {
        return DbConnection.BeginTransaction();
    }

    public void EstablishNewConnection()
    {
        ChangeConnection(Settings.Default.MADatabase);
    }

    public void ChangeConnection(string connectionString)
    {
        // Nice to register connection string in the container, then resolve named parameters (Unity addon would be needed).
        if (string.IsNullOrEmpty(connectionString))
        {
            logger.Log("Could not retrieve db connection string from Settings file.", LogSeverity.Error);
            return;
        }

        try
        {
            DbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
            DbConnection.Open();
        }
        catch(InvalidOperationException e)
        {
            logger.Log("Could not open database connection!", e, LogSeverity.Critical);
        }
        catch(SqlException e)
        {
            logger.Log("Could not open database connection!", e, LogSeverity.Critical);   
        }
    }

    public IDbCommand CreateCommand()
    {
        return DbConnection.CreateCommand();
    }
}

The below class, DatabaseExecutor, is used as a utility by the abstract class that our domain specific services will implement. This class allows for the creation of transactions as well as executing commands and returning strongly typed results. The dependencies have all been defined above and are represented as interfaces of which the implementations will be injected. There are a number of overloads of the Execute method to return the results of the command, return whether the command executed successfully and passing the results as an out parameter and finally executing a command that doesn’t return results.

public class DatabaseExecutor : IDatabase
{
    private readonly ILogger logger;
    private readonly IDatabaseConnection dbConnection;
    private readonly IMapper mapper;
    private readonly ICommandExecutor commandExecutor;
    private IDbTransaction currentTransaction;

    public DatabaseExecutor(ILogger logger, IDatabaseConnection dbConnection,
        IMapper mapper, ICommandExecutor commandExecutor)
    {
        this.logger = logger;
        this.dbConnection = dbConnection;
        this.mapper = mapper;
        this.commandExecutor = commandExecutor;

        currentTransaction = null;
    }

    public void BeginTransaction()
    {
        currentTransaction = dbConnection.GetSqlTransaction();
    }

    public void CommitTransaction()
    {
        currentTransaction.Commit();

        currentTransaction = null;
    }

    public void RollbackTransaction()
    {
        currentTransaction.Rollback();

        currentTransaction = null;
    }

    public T Execute<T>(IDbCommand command) where T : class, new()
    {
        if (command == null)
        {
            return null;
        }

        command.Connection = dbConnection.DbConnection;
        command.Transaction = currentTransaction;

        IDataReader reader;
        try
        {
            reader = commandExecutor.ExecuteReader(command);
        }
        catch(SqlException)
        {
            StringBuilder builder = new StringBuilder("Database command failure details");
            builder.AppendLine();
            builder.AppendLine(command.CommandText);
            foreach (IDataParameter parameter in command.Parameters)
            {
                builder.AppendLine(string.Format("{0}:{1}",
                    parameter.ParameterName, parameter.Value));
            }

            logger.Log(builder.ToString(), LogSeverity.Error);

            throw;
        }

        if (reader == null)
        {
            return null;
        }

        try
        {
            return typeof(IEnumerable).IsAssignableFrom(typeof(T))
                       ? mapper.Map<IDataReader, T>(reader)
                       : mapper.Map<IDataReader, List<T>>(reader).FirstOrDefault();
        }
        catch(Exception e)
        {
            logger.Log(e);
            throw;
        }
        finally
        {
            reader.Close();
        }
    }

    public bool Execute<T>(IDbCommand command, out T result)
        where T : class, new()
    {
        result = Execute<T>(command);

        return result != null
            ? true
            : false;
    }

    public void Execute(IDbCommand command)
    {
        if (command == null)
        {
            return;
        }

        command.Connection = dbConnection.DbConnection;
        command.Transaction = currentTransaction;

        try
        {
            commandExecutor.ExecuteNonQueury(command);
        }
        catch(SqlException)
        {
            StringBuilder builder = new StringBuilder("Database command failure details");
            builder.AppendLine(command.CommandText);
            foreach (IDataParameter parameter in command.Parameters)
            {
                builder.AppendLine(string.Format("{0}:{1}",
                    parameter.ParameterName, parameter.Value));
            }

            logger.Log(builder.ToString(), LogSeverity.Error);
            throw;
        }
    }
}

The abstract class that should be used as the base for any data access services is provided below. From this class transaction may be started. Also is a helper function, GetValueCatchNull, which is used when using Automapper to map between fields that don’t match by convention (example shown further below).

My original implementation of this abstract class included properties and methods that would dynamically inspect the properties of an object and create parameter and value lists that could be passed to sql insert/select/update/delete commands but was only half-baked and thus not included here so if this looks a bit sparse and you’re wondering why you couldn’t just request the IDatabase class as a direct dependency within your data access service, you could. Hopefully in the future I’ll find a better implementation of a way to do this but as of yet hasn’t worked very well.

public abstract class DatabaseServiceBase
{
    protected readonly IDatabase database;

    protected DatabaseServiceBase(IDatabase database)
    {
        this.database = database;
    }

    public void BeginTransaction()
    {
        database.BeginTransaction();
    }

    public void CommitTransaction()
    {
        database.CommitTransaction();
    }

    public void RollbackTransaction()
    {
        database.RollbackTransaction();
    }

    protected static T GetValueCatchNull<T>(Func<T> func)
    {
        try
        {
            return func();
        }
        catch(SqlNullValueException)
        {
            return default(T);
        }
    }
}

Finally, an implementation of the data service layer utilizing the above defined classes. Notice here how we’re implementing the abstract class DatabaseServiceBase and calling the base constructor and passing to it the requested dependency IDatabase. Also of importance is the static constructor which creates the mapping between the results from the database (IDataReader) and mapping that to the User object. See the previous automapper post for info on why we’re mapping a single object to a data type that may return multiple results. Notice the use of the GetValueCatchNull in order to manage if a null value is returned from the IDataReader and cannot be applied to value types. Last but not least notice that in the GetUsers method we’re actually returning a list of results and this is possible due to the previous implementation of the IDatabase interface defined as DatabaseExecutor which allows us to pass in a single object or List of objects and it will map both.

public class UserService : DatabaseServiceBase, IUserService
{
    private readonly ILogger logger;

    public UserService(ILogger logger, IDatabase database)
        : base(database)
    {
        this.logger = logger;
    }

    static UserService()
    {
        Automapper.Mapper.CreateMap<IDataReader, User>()
            .ForMember(m => m.FirstName, opt => opt.MapFrom(p => GetValueCatchNull(() => p.GetString(p.GetOrdinal("First")))));
    }

    public User GetUserById(int userId)
    {
        SqlCommand command = new SqlCommand("select * from Users where UserId = @UserId");
        command.Parameters.AddWithValue("@UserId", userId);

        return database.Execute<User>(command);
    }

    public List<Users> GetUsers()
    {
        SqlCommand command = new SqlCommand("select * from Users");
        
        return database.Execute<List<Users>>(command);
    }
}

There you have it, an abstraction of a data layer that will ease transition from a SQL Server database to Oracle or even an unsupported system like DB2 or MySql.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s