Click here to Skip to main content
15,868,080 members
Articles / Programming Languages / C#

ADO.NET, The Right Way

Rate me:
Please Sign up or sign in to vote.
4.67/5 (18 votes)
28 Aug 2013LGPL35 min read 100.6K   75   29
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.

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.

C#
// 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
<?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
<?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:

C#
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:

C#
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:

C#
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:

SQL
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.

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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

C#
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

C#
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

C#
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:

C#
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):

C#
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:

C#
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.

C#
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:

C#
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:

C#
// 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:

C#
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.

This article was originally posted at http://blog.gauffin.org/2013/01/ado-net-the-right-way

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)


Written By
Founder 1TCompany AB
Sweden Sweden

Comments and Discussions

 
QuestionGetting Identity Pin
Cool Smith24-Mar-19 22:39
Cool Smith24-Mar-19 22:39 
Question2 errors on compile Pin
jrgunawan2-Feb-15 9:25
jrgunawan2-Feb-15 9:25 
AnswerRe: 2 errors on compile Pin
jgauffin2-Feb-15 9:44
jgauffin2-Feb-15 9:44 
GeneralVery good, but not perfect yet. Pin
Paulo Zemek16-Feb-14 11:27
mvaPaulo Zemek16-Feb-14 11:27 
GeneralRe: Very good, but not perfect yet. Pin
jgauffin16-Feb-14 19:38
jgauffin16-Feb-14 19:38 
GeneralRe: Very good, but not perfect yet. Pin
Paulo Zemek17-Feb-14 1:01
mvaPaulo Zemek17-Feb-14 1:01 
GeneralRe: Very good, but not perfect yet. Pin
jgauffin21-Feb-14 18:19
jgauffin21-Feb-14 18:19 
QuestionSource Code Pin
John Savold16-Feb-14 10:26
John Savold16-Feb-14 10:26 
QuestionCannot find IUnitOfWork class implemetation Pin
sanjuksuthar27-Nov-13 19:59
sanjuksuthar27-Nov-13 19:59 
AnswerRe: Cannot find IUnitOfWork class implemetation Pin
jgauffin9-Jan-14 8:59
jgauffin9-Jan-14 8:59 
QuestionWhere open/close the connection Pin
Mikael Ekroth25-Nov-13 4:13
Mikael Ekroth25-Nov-13 4:13 
AnswerRe: Where open/close the connection Pin
jgauffin9-Jan-14 9:00
jgauffin9-Jan-14 9:00 
Questionwhere to put the AppConfigConnectionFactory in mvc 5 Pin
yamishinigami14-Oct-13 8:09
yamishinigami14-Oct-13 8:09 
AnswerRe: where to put the AppConfigConnectionFactory in mvc 5 Pin
jgauffin9-Jan-14 9:01
jgauffin9-Jan-14 9:01 
QuestionSourcs Code Pin
Smuget9-Oct-13 10:18
professionalSmuget9-Oct-13 10:18 
AnswerRe: Sourcs Code Pin
jgauffin9-Jan-14 9:01
jgauffin9-Jan-14 9:01 
GeneralMy vote of 5 Pin
John B Oliver29-Sep-13 11:58
John B Oliver29-Sep-13 11:58 
GeneralRe: My vote of 5 Pin
jgauffin9-Jan-14 9:03
jgauffin9-Jan-14 9:03 
GeneralMy vote of 5 Pin
cocis4829-Aug-13 10:12
cocis4829-Aug-13 10:12 
GeneralRe: My vote of 5 Pin
jgauffin9-Jan-14 9:03
jgauffin9-Jan-14 9:03 
Questionsource code Pin
devnet2471-Jun-13 21:25
devnet2471-Jun-13 21:25 
AnswerRe: source code Pin
jgauffin28-Aug-13 2:20
jgauffin28-Aug-13 2:20 
QuestionOne line is tripping me up. Pin
RoketSauce8-Apr-13 10:00
RoketSauce8-Apr-13 10:00 
AnswerRe: One line is tripping me up. Pin
jgauffin28-Aug-13 2:22
jgauffin28-Aug-13 2:22 
QuestionSmall Correction Pin
Ivan Mladenović25-Jan-13 0:43
Ivan Mladenović25-Jan-13 0:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.