ADO.NET, The Right Way

ADO.NET is actually quite powerful if you use it correctly. This post will teach you everything from making your ADO.NET code driver independent to how to implement the repository pattern and unit of work. This is the follow up post of my “Datalayer, the right way” post. The purpose is to demonstrate that ADO.NET can be used as an alternative to OR/Ms.

Background

ADO.NET is divided into several parts which is responsible for different things.

The Interfaces

The first part is the interfaces which specify structure of the library. They enable you to code against abstractions instead of against concretes. That is also the approach that I recommend you to use.

// command will be IDbCommand
using (var command = _connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM Users";
    
    // reader will be IDataReader
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // using the IDataRecord interface
            Console.WriteLine(reader["FirstName"]);
        }
    }
}

Depending on abstractiosn instead of concretes allows us to apply some magic which make the handling much easier. Keep reading and you’ll see how.

The Base Classes

ADO.NET also contains a set of base classes which makes it easier for the database vendors to implement ADO.NET Drivers. Among these are DbCommand, DbTransaction, etc.

The Drivers

Next are the actual drivers which are vendor specific. These drivers all contains their own small variations that you can take advantage of. I, however, discourage you from doing so.

All drivers should be registered in the machine.config located under “C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config”. This is typically done by the driver setup. The reason to this is so that we can use the DbProviderFactory as shown below.

Connecting to the Database

ADO.NET is controlled through the configuration file (app/webb.config). There is a section which is called <connectionStrings>. It’s used to map a connection name to its driver and the string to use while connecting to the database.

Here is the syntax:

<?xml version='1.0' encoding='utf-8'?>
<configuration>
    <connectionStrings>
        <add name="Name"  
        providerName="System.Data.ProviderName" 
        connectionString="Valid Connection String;" />
    </connectionStrings>
</configuration>

And a real world connection string:

<?xml version='1.0' encoding='utf-8'?>
<configuration>
    <connectionStrings>
        <add name="MyConName" 
            connectionString="Data Source=.\SQLEXPRESS;
            AttachDbFilename=|DataDirectory|\vsItems.mdf;
            Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" 
        />
    </connectionStrings>
</configuration>

All different kind of connection strings can be found at connectionstrings.com.

We can load the connection string by using the CongurationManager.ConnectionStrings property and then simply create a connection:

var connectionString = ConfigurationManager.ConnectionStrings
["MyConName"]"ConnectionString;
var connection = new SqlConnection(connectionString);

But then we have been forced to use a specific implementation (explicitly). Instead, we can use the DbProviderFactory class:

var connectionString = ConfigurationManager.ConnectionStrings["MyConName"];
var providerName = connectionString.ProviderName;
var factory = DbProviderFactories.GetFactory(providerName);
var connection = factory.CreateConnection();
connection.Open();

Which allows us to only change the connection string in the configuration to switch DB driver (and/or database). The cool thing with that is that we can create a set of reusable classes which we can use in all of our projects. More about that later.

Querying the Database

So we got a connection to the database. Now we need to query our DB too. Let’s use the sample from the beginning again:

using (var command = _connection.CreateCommand())
{
    command.CommandText = @"SELECT * 
                                FROM Users 
                                WHERE CompanyId = " + LoggedInUser.companyId + @"
                                AND FirstName LIKE '" + firstName + "%'";
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader["FirstName"]);
        }
    }
}

That’s pretty easy. There is however a serious security issue in that code. And that’s that the command is concatenated. Pretend that the “firstName” variable is filled from the query string in a web application. Someone changing it it ' OR 1 = 1 OR Name = ' would make the query list all users in the system. That’s because the entire query would look like:

SELECT * 
FROM Users 
WHERE CompanyId = 1
    AND FirstName LIKE ''
    OR 1 = 1
    OR Name = ''

The 1=1 will always be true, hence all rows are returned.

Querying Using Parameterized Queries

To avoid SQL injection, one should always use parameterized queries. In more recent versions of SQL Server, they are actually as fast as stored procedures.

using (var command = _connection.CreateCommand())
{
    command.CommandText = @"SELECT * 
                                FROM Users 
                                WHERE CompanyId = @companyId
                                AND FirstName LIKE @firstName";
    command.AddParameter("companyId", LoggedInUser.companyId);
    command.AddParameter("firstName", firstName + "%");
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader["FirstName"]);
        }
    }
}

There are two things to take into consideration here:

1. Wildcard searches should be included in parameter and not the query string.
2. The AddParameter() method is not part of the IDbCommand interface.

The latter is easily fixed with an extension method:

public static class CommandExtensions
{
    public static void AddParameter(this IDbCommand command, string name, object value)
    {
        if (command == null) throw new ArgumentNullException("command");
        if (name == null) throw new ArgumentNullException("name");

        var p = command.CreateParameter();
        p.ParameterName = name;
        p.Value = value ?? DBNull.Value;
        command.Parameters.Add(p);
    }
}

Returning POCOs

Now we know how to make queries which are safe. Since we do not want to return DataTable or DataSet, we have to figure out a reusable way of populating items. If we examine the IDataReader interface (which is returned from command.ExecuteReader()), we’ll see that it implements another interface called IDataRecord. That interface corresponds to a single record in the recordset. Hence, it’s the obvious choice to use when wanting to populate items.

To map a record to an item, we can therefore create a method like this:

public void Map(IDataRecord record, User user)
{
    user.FirstName = (string)record["FirstName"];
    user.Age = (int)record["Age"];
}

Which allows us to make a query like:

public IEnumerable<User> FindUsers()
{
    using (var command = _connection.CreateCommand())
    {
        command.CommandText = @"SELECT * FROM Users WHERE CompanyId = @companyId 
                             AND FirstName LIKE @firstName";
        command.AddParameter("companyId", LoggedInUser.companyId);
        command.AddParameter("firstName", firstName + "%");
        using (var reader = command.ExecuteReader())
        {
            List<User> users = new List<User>();
            while (reader.Read())
            {
                var user = new User();
                Map(record, user);
                users.Add(user);
            }
            return users;
        }
    }
}

We still have a some code which will be repeated in every class that queries the data source. And that’s the whole population of objects. Let’s define a base class with two methods:

public abstract class OurDbBaseClass<TEntity>
{
    protected IEnumerable<TEntity> ToList(IDbCommand command)
    {
        using (var reader = command.ExecuteReader())
        {
            List<TEntity> items = new List<TEntity>();
            while (reader.Read())
            {
                var item = CreateEntity();
                Map(record, item);
                items.Add(item);
            }
            return items;
        }
    }

    protected abstract void Map(IDataRecord record, TEntity entity);
    protected abstract TEntity CreateEntity();
}

That moves all duplication from each query class to the base class. Our method will now just look like:

public IEnumerable<User> FindUsers()
{
    using (var command = _connection.CreateCommand())
    {
        command.CommandText = @"SELECT * FROM Users 
                              WHERE CompanyId = @companyId AND FirstName LIKE @firstName";
        command.AddParameter("companyId", LoggedInUser.companyId);
        command.AddParameter("firstName", firstName + "%");
        return ToList(command);
    }
}

Paging

Paging is also something which can be moved to a generic class.

You can for instance create an extension method for the IDbCommand interface:

public static class DbCommandExtensions
{
    public static IDbCommand Page(this IDbCommand command, int pageNumber, int pageSize)
    {
        // modify command.CommandText here.
        
        return command;
    }
}

which allows you to:

public IEnumerable<User> FindUsers(int pageNumber, int pageSize)
{
    using (var command = _connection.CreateCommand())
    {
        command.CommandText = @"SELECT * FROM Users WHERE CompanyId = @companyId 
                              AND FirstName LIKE @firstName";
        command.AddParameter("companyId", LoggedInUser.companyId);
        command.AddParameter("firstName", firstName + "%");
        
        // here
        command.Page(pageNumber, pageSize);
        
        return ToList(command);
    }
}

Sample SqlServer pager

CRUD

CRUD (CReate, Update, Delete) requires a bit more work since it’s hard to extract things from the process. Each statement is unique. As for the queries, we should use parameterized SQL statements.

Insert Example

using (var command = _connection.CreateCommand())
{
    command.CommandText = @"INSERT INTO Users (CompanyId, FirstName) VALUES(@companyId, @firstName)";
    command.AddParameter("companyId", companyId);
    command.AddParameter("firstName", firstName);
    command.ExecuteNonQuery();
}

Update Example

using (var command = _connection.CreateCommand())
{
    command.CommandText = @"UPDATE Users SET CompanyId = @companyId WHERE Id = @userId";
    command.AddParameter("companyId", companyId);
    command.AddParameter("userId", LoggedInUser.Id);
    command.ExecuteNonQuery();
}

Delete Example

using (var command = _connection.CreateCommand())
{
    command.CommandText = @"DELETE FROM Users WHERE Id = @userId";
    command.AddParameter("userId", LoggedInUser.Id);
    command.ExecuteNonQuery();
}

Repository Pattern

To take it a step further, let’s create a repository pattern implementation together with a unit of work.

Basic Classes

The first step is to create a class which will create a connection. Let’s call it AppConfigConnectionFactory:

public class AppConfigConnectionFactory
{
    private readonly DbProviderFactory _provider;
    private readonly string _connectionString;
    private readonly string _name;

    public AppConfigConnectionFactory(string connectionName)
    {
        if (connectionName == null) throw new ArgumentNullException("connectionName");

        var conStr = ConfigurationManager.ConnectionStrings[connectionName];
        if (conStr == null)
            throw new ConfigurationErrorsException(string.Format
            ("Failed to find connection string named '{0}' 
            in app/web.config.", connectionName));

        _name = conStr.ProviderName;
        _provider = DbProviderFactories.GetFactory(conStr.ProviderName);
        _connectionString = conStr.ConnectionString;
    }

    public IDbConnection Create()
    {
        var connection = _provider.CreateConnection();
        if (connection == null)
            throw new ConfigurationErrorsException(string.Format
              ("Failed to create a connection using the connection string named '{0}' 
                in app/web.config.", _name));

        connection.ConnectionString = _connectionString;
        connection.Open();
        return connection;
    }
}

The connection itself is wrapped in another class which also is used to create unit of work objects and commands (which have been enlisted in the last transaction):

public class AdoNetContext
{
    private readonly IDbConnection _connection;
    private readonly IConnectionFactory _connectionFactory;
    private readonly ReaderWriterLockSlim _rwLock = new ReaderWriterLockSlim();
    private readonly LinkedList<AdoNetUnitOfWork> _uows = 
    		new LinkedList<AdoNetUnitOfWork>();

    public AdoNetContext(IConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
        _connection = _connectionFactory.Create();
    }

    public IUnitOfWork CreateUnitOfWork()
    {
        var transaction = _connection.BeginTransaction();
        var uow = new AdoNetUnitOfWork(transaction, RemoveTransaction, RemoveTransaction);

        _rwLock.EnterWriteLock();
        _uows.AddLast(uow);
        _rwLock.ExitWriteLock();

        return uow;
    }

    public IDbCommand CreateCommand()
    {
        var cmd = _connection.CreateCommand();

        _rwLock.EnterReadLock();
        if (_uows.Count > 0)
            cmd.Transaction = _uows.First.Value.Transaction;
        _rwLock.ExitReadLock();

        return cmd;
    }

    private void RemoveTransaction(AdoNetUnitOfWork obj)
    {
        _rwLock.EnterWriteLock();
        _uows.Remove(obj);
        _rwLock.ExitWriteLock();
    }

    public void Dispose()
    {
        _connection.Dispose();
    }
}

The last class to create before the actual repository class is the Unit Of Work:

public class AdoNetUnitOfWork : IUnitOfWork
{
    private IDbTransaction _transaction;
    private readonly Action<AdoNetUnitOfWork> _rolledBack;
    private readonly Action<AdoNetUnitOfWork> _committed;

    public AdoNetUnitOfWork(IDbTransaction transaction, 
           Action<AdoNetUnitOfWork> rolledBack, Action<AdoNetUnitOfWork> committed)
    {
        Transaction = transaction;
        _transaction = transaction;
        _rolledBack = rolledBack;
        _committed = committed;
    }

    public IDbTransaction Transaction { get; private set; }

    public void Dispose()
    {
        if (_transaction == null) 
            return;

        _transaction.Rollback();
        _transaction.Dispose();
        _rolledBack(this);
        _transaction = null;
    }

    public void SaveChanges()
    {
        if (_transaction == null)
            throw new InvalidOperationException("May not call save changes twice.");

        _transaction.Commit();
        _committed(this);
        _transaction = null;
    }
}

Repository

Now we got the classes that we need. Let’s move those methods that we defined for queries before into the repository base. One exception: Let’s define that all entities must have a public default constructor.

public class Repository<TEntity> where TEntity : new()
{
    AdoNetContext _context;
    
    public Repository(AdoNetContext context)
    {
        _context = context;
    }
    
    protected AdoNetContext Context { get; }
    
    protected IEnumerable<TEntity> ToList(IDbCommand command)
    {
        using (var reader = command.ExecuteReader())
        {
            List<TEntity> items = new List<TEntity>();
            while (reader.Read())
            {
                var item = new TEntity();
                Map(record, item);
                items.Add(item);
            }
            return items;
        }
    }

    protected abstract void Map(IDataRecord record, TEntity entity);
}

Implementation

So a sample implementation would now look like:

public class UserRepository : Repository<User>
{
    public UserRepository(AdoNetContext context) : base(context)
    {
    }

    public void Create(User user)
    {
        using (var command = _connection.CreateCommand())
        {
            command.CommandText = @"INSERT INTO Users (CompanyId, FirstName) 
                                  VALUES(@companyId, @firstName)";
            command.AddParameter("companyId", user.CompanyId);
            command.AddParameter("firstName", user.FirstName);
            command.ExecuteNonQuery();
        }
        
        //todo: Get identity. Depends on the db engine.
    }

    public void Update(User user)
    {
        using (var command = _connection.CreateCommand())
        {
            command.CommandText = @"UPDATE Users SET CompanyId = @companyId WHERE Id = @userId";
            command.AddParameter("companyId", user.CompanyId);
            command.AddParameter("userId", user.Id);
            command.ExecuteNonQuery();
        }
    }

    public void Delete(int id)
    {
        using (var command = _connection.CreateCommand())
        {
            command.CommandText = @"DELETE FROM Users WHERE Id = @userId";
            command.AddParameter("userId", id);
            command.ExecuteNonQuery();
        }
    }
    
    public IEnumerable<User> FindUsers(string firstName)
    {
        using (var command = _connection.CreateCommand())
        {
            command.CommandText = @"SELECT * FROM Users WHERE CompanyId = @companyId 
                                  AND FirstName LIKE @firstName";
            command.AddParameter("companyId", LoggedInUser.companyId);
            command.AddParameter("firstName", firstName + "%");
            return ToList(command);
        }
    }    
    
    public IEnumerable<User> FindBlocked()
    {
        using (var command = _connection.CreateCommand())
        {
            command.CommandText = @"SELECT * FROM Users WHERE Status = -1";
            return ToList(command);
        }
    }    
    
    protected void Fill(IDataRecord record, User user)
    {
        user.FirstName = (string)record["FirstName"];
        user.Age = (int)record["Age"];
    }
}

So with that code, we can use transactions like:

// during app start
var factory = new AppConfigConnectionFactory("MyConString");

// during start of the current session
var context = new AdoNetContext();

// for the transaction
using (var uow = context.CreateUnitOfWork())
{
    var repos1 = new UserRepository(context);
    var repos2 = new UserRepository(context);

    // do changes
    // [...]

    uow.SaveChanges();
}

Using an inversion of control container makes it even more straight forward.

Conclusion

ADO.NET is quite powerful and the architecture of the library is really good with a few exceptions. Hence, it’s easy to build upon it.

Most data mappers use principles like the ones described in this post. I’ve started to create one myself which will be limited to mapping only.

Sample code with my mapper:

public IEnumerable<User> FindAll()
{
    using (var cmd = _connection.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM Users";

        // this is the magic
        return cmd.ExecuteQuery<User>();
    }
}

Updates

• 2013-08-28 Fixed a typo ("new TEntity()" instead of the incorrect "new T()")

The post ADO.NET, the right way appeared first on jgauffin's coding den.