Tired of looking for errors in log files? Use OneTrueError - Automated exception management in .NET.

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 of 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:WindowsMicrosoft.NETFrameworkv4.0.30319Config”. 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 it’s 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.

Quering 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 are 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 later 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 an 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(reader, 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 T();
                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>();
    }
}
This entry was posted in CodeProject and tagged , . Bookmark the permalink.
  • Pingback: ADO.NET Fakes version 2.0 | jgauffin's coding den

  • grip

    Thank you for your data-access posts.

    I have a basic question about the repository pattern.

    Let’s assume we have a vehicle class. A vehicle has n seats, n tires, 0..1 air-conditioning, 0..1 media system and whatever else.

    The seats can have seat-heaters, the media system can have CD-Player and/or USB-Port……
    All in all we have a complete model, with a few classes, representing a vehicle and all it’s stuff inside.

    How do I implement a repository “CRUDing” (creating,reading,deleting) whole vehicles.
    Is it a good Idea to so write something like the following?

    //Update a whole vehicle with all it’s items
    vehicleRep.Update(myVehicle);

    The VehicleRep would hide the use of the SeatRepo, the TireRepo, the AirCondition-Repo in a transparent way to the caller. Is that a good practice or are there any problems writing such code? As caller, would it be a better to call every single repo by myself?

    Thanks in advance.

    • JonasGauffin

      I think that you have to seperate the logic a bit further. When building a car you aren’t constructing the parts when you build the car. The fact is that each part has been constructed at any point of time before the car. Hence you’ve already stored those parts in the database using the correct repositories.

      The car itself just points on the other parts:
      Car: Name, CreatedAt, OwnerId, Wheel1, Wheel2 etc

      That makes it easy to store the car.

      The problem comes when you have to build the car again. In this case it’s perfectly fine to let the CarRepository use the other repositories to construct back the car. But do use batch methods (i.e. have a WheelRepository.Get(int[] ids))

      What I’m basically saying is that you have a class which is responsible of building the car (for instance CarBuildingService). That class is responsible of reserving/assigning each part to the car (so that no other car can use the specified parts). The car repository should only be responsible of storing references to the other parts to be able to reconstruct it at a later point. It should not have any logic such as reserving parts etc.

  • erikbullens

    where is the interface iconnectionfactory defined?

    • JonasGauffin

      You have to create it yourself. It’s just:

      public interface IConnectionFactory
      {
      IDbConnection Create();
      }

      • Bernhard Berger

        How do I solve the mapping of objects within objects?

        For example the following code throws me an “Commands out of sync” error in the UnitRepository..

        protected override void Map(System.Data.IDataRecord record, ArticlePrice entity)

        {

        base.Map(record, entity);

        var unitRepository = new UnitRepository(Context);

        var priceListRepository = new PriceListRepository(Context);

        var articleRepository = new ArticleRepository(Context);

        entity.Price = Convert.ToDouble(record["preis"]);

        entity.Unit = unitRepository.FindById((int) record["einheit"]);

        entity.PriceList = priceListRepository.FindById((int) record["preisliste"]);

        entity.Article = articleRepository.FindByArticleNumber(record["artikel"].ToString());

        }

        • http://blog.gauffin.org/ jgauffin

          You can do it in several ways.

          a) Denormalize the DB so that the ArticlePrice table contains the article name etc.

          b) Treat articleprice as a part of Article and store it as JSON in a column in the article table (and use Json serializer in the Article mapping)

          c) Just have the ids in your articleprice class and use the correct repositories when you required additional information.

          I typically just use b) or c) depending on the use case. In this case I see the ArticlePrice as a part of the Article entity and would store it as JSON in a column.

  • erikbullens

    why do you use :LinkedList _uows = new LinkedList(); ? in the createunitof work you add an item at the end of the list when createcommand you get the first.
    why nout create one unitofwork in stead of a list?
    how does he get the right item in the list?

    • JonasGauffin

      Some drivers allow you to create multiple transactiosn for the same connection (but most won’t allow that). I do the list under the assumtion that the latest connection will get disposed first. It’s not fail safe but will in most cases be enough

  • Ramires Souza

    Hi! Very, very good! I was looking for this, exactly like this.
    One question: in the Map method instead of passing User, one could use reflection and make it more generic, passing for exemple?

    Thank you and keep up good work!

    • JonasGauffin

      Yes, if you have a default constructor you could use the “new” generic restriction.

  • http://www.facebook.com/wilverpaul Paul Villalobos

    Thank you for your post, one question, in the Returning POCOs Section, in the following code:

    using (var reader = command.ExecuteReader())
    {
    List users = new List();
    while (reader.Read())
    {
    var user = new User();
    Map(record, user);
    users.Add(user);
    }
    return users;
    }

    When you call Map(record, user);
    Where does “record” come from?

    Thank you for your help.

    • JonasGauffin

      Sorry, that’s a type. It should say “reader” as the DataReader implement IDataRecord. I’ve updated the article.

  • Bernhard Berger

    How do I solve the mapping of objects within objects?

    For example the following code throws me an “Commands out of sync” error in the UnitRepository..

    protected override void Map(System.Data.IDataRecord record, ArticlePrice entity)

    {

    base.Map(record, entity);

    var unitRepository = new UnitRepository(Context);

    var priceListRepository = new PriceListRepository(Context);

    var articleRepository = new ArticleRepository(Context);

    entity.Price = Convert.ToDouble(record["preis"]);

    entity.Unit = unitRepository.FindById((int) record["einheit"]);

    entity.PriceList = priceListRepository.FindById((int) record["preisliste"]);

    entity.Article = articleRepository.FindByArticleNumber(record["artikel"].ToString());

    }

    • http://blog.gauffin.org/ jgauffin

      I have never heard of “Commands out of sync” error.

  • Arun

    Liked your implementation. The only questionable part I feel is including the mapping method in the repository itself. You could move the map outside the repository by using a repository declared Repository
    This will make the repository adhere to SRP

  • pranay

    where is _connection declare in Repository sample implementation

  • romb

    good job, thanks for sharing

  • alirıza adıyahşi

    Thanks. Could you share an example project?

  • Pingback: Introducing the data mapper in Griffin.Framework | jgauffin's coding den

  • dan

    In here the connection stay open until the context get disposed (which could be along app session); isn’t that too expensive?
    Thanks.

    • http://blog.gauffin.org/ jgauffin

      It depends on the type of application. If it’s a HTTP request the scope is limited. I would however not keep the connection open as long as a desktop client is open. Instead I would create a new unit of work for each action (i.e. when the user clicks the Save button)

  • kalmoor

    Thanks a lot for the post. Can you please show how the repository is “called” from an MVC controller or elsewhere? Also, do you have an example for how to unit test without using a database connection?