Click here to Skip to main content
13,403,071 members (52,444 online)
Click here to Skip to main content
Add your own
alternative version

Stats

70.9K views
71 bookmarked
Posted 6 Jan 2013

ADO.NET, The Right Way

, 28 Aug 2013
Rate this:
Please Sign up or sign in to vote.
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.

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

License

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

Share

About the Author

jgauffin
Founder 1TCompany AB
Sweden Sweden
Skip logfiles, try automated error handling!

I'm one of the founders of codeRR, a .NET service which takes care of everything related to exceptions, so that you can focus on writing code.

blog | twitter

You may also be interested in...

Comments and Discussions

 
Question2 errors on compile Pin
jrgunawan2-Feb-15 10:25
memberjrgunawan2-Feb-15 10:25 
AnswerRe: 2 errors on compile Pin
jgauffin2-Feb-15 10:44
memberjgauffin2-Feb-15 10:44 
GeneralVery good, but not perfect yet. Pin
Paulo Zemek16-Feb-14 12:27
professionalPaulo Zemek16-Feb-14 12:27 
GeneralRe: Very good, but not perfect yet. Pin
jgauffin16-Feb-14 20:38
memberjgauffin16-Feb-14 20:38 
Thank you for your comment. Much appreciated.

Paulo Zemek wrote:
You present the AddParameter extension method. In the case of null values, the DBNull.Value is used, but some databases also require that the DbType is set (as actually a null string is not the same as a null int);
I didn't know that. Can you give example of an database?


Paulo Zemek wrote:
fact, some databases are even more severe and always require the DbType to be set and/or require a parameter size for strings
Same here. Please give an example.

Paulo Zemek wrote:
My main concern is the pagination. As I see, instead of writing a query that requests the right page from the database, you do a query that reads all the pages, but you actually "skip" the pages you don't need, then read the page you need (and probably close the command before continuing). This may work fine for the first pages, but it is pretty bad for the latest pages as the entire query must be read ignoring most of the records;
No. That was not my intention. Some DBs have simple paging clauses (PostgreSQL/MySQL) while others have more complex solutions (Oracle/SqlServer). My point with this example was just to show how you can use vanilla ADO.NET to build a repository.

It's still some work that needs to be done by the reader.

I could however have elaborated on that.


Paulo Zemek wrote:
From the article I don't see how the "Returning POCOs" is actually avoiding repetitive code. It may be avoiding the loop, but it appears that you should write the Map method for every type. And you put all the contents into a List. Using the yield return may be better
I did not want to avoid repetitive code. This article do not represent a complete mapping layer either. It's purpose is just to show how you can use vanilla ADO.NET in a better way.

As for yield it can get problematic when using the using statement as the connection might be disposed before the iteration of the enumerator begins. To get around that you need to create a custom enumerator which manages the command and connection.

In my experience the mapping between a column and a property may require some adaptation. It might be that the column names does not match the property name or that the types differ.

If you compare a repository implementation like the one above with a fully featured OR/M the initial time is a lot longer, but you probably get that back in the end. Because when users start to struggle with more complex queries in an OR/M they have full control of the queries with the implementation shown in this article. So yes, you have to write repetitive code, but you'll always understand why the data layer is failing when you maintain your application.

I am writing a complete mapping layer which also can take care of the table/class mapping (if you want to). Hence still not an OR/M, but you don't have to write those CRUD statements. You still have to write the SELECT queries though (I force you to have control Wink | ;) ) My layer do no try to hide ADO.NET but is built as extensions to it. i.e. you still use IDbCommand etc.
GeneralRe: Very good, but not perfect yet. Pin
Paulo Zemek17-Feb-14 2:01
professionalPaulo Zemek17-Feb-14 2:01 
GeneralRe: Very good, but not perfect yet. Pin
jgauffin21-Feb-14 19:19
memberjgauffin21-Feb-14 19:19 
QuestionSource Code Pin
John Savold16-Feb-14 11:26
memberJohn Savold16-Feb-14 11:26 
QuestionCannot find IUnitOfWork class implemetation Pin
sanjuksuthar27-Nov-13 20:59
membersanjuksuthar27-Nov-13 20:59 
AnswerRe: Cannot find IUnitOfWork class implemetation Pin
jgauffin9-Jan-14 9:59
memberjgauffin9-Jan-14 9:59 
QuestionWhere open/close the connection Pin
Mikael Ekroth25-Nov-13 5:13
memberMikael Ekroth25-Nov-13 5:13 
AnswerRe: Where open/close the connection Pin
jgauffin9-Jan-14 10:00
memberjgauffin9-Jan-14 10:00 
Questionwhere to put the AppConfigConnectionFactory in mvc 5 Pin
yamishinigami14-Oct-13 9:09
memberyamishinigami14-Oct-13 9:09 
AnswerRe: where to put the AppConfigConnectionFactory in mvc 5 Pin
jgauffin9-Jan-14 10:01
memberjgauffin9-Jan-14 10:01 
QuestionSourcs Code Pin
smuget9-Oct-13 11:18
membersmuget9-Oct-13 11:18 
AnswerRe: Sourcs Code Pin
jgauffin9-Jan-14 10:01
memberjgauffin9-Jan-14 10:01 
GeneralMy vote of 5 Pin
John B Oliver29-Sep-13 12:58
memberJohn B Oliver29-Sep-13 12:58 
GeneralRe: My vote of 5 Pin
jgauffin9-Jan-14 10:03
memberjgauffin9-Jan-14 10:03 
GeneralMy vote of 5 Pin
cocis4829-Aug-13 11:12
membercocis4829-Aug-13 11:12 
GeneralRe: My vote of 5 Pin
jgauffin9-Jan-14 10:03
memberjgauffin9-Jan-14 10:03 
Questionsource code Pin
devnet2471-Jun-13 22:25
memberdevnet2471-Jun-13 22:25 
AnswerRe: source code Pin
jgauffin28-Aug-13 3:20
memberjgauffin28-Aug-13 3:20 
QuestionOne line is tripping me up. Pin
RoketSauce8-Apr-13 11:00
memberRoketSauce8-Apr-13 11:00 
AnswerRe: One line is tripping me up. Pin
jgauffin28-Aug-13 3:22
memberjgauffin28-Aug-13 3:22 
QuestionSmall Correction Pin
Ivan Mladenović25-Jan-13 1:43
memberIvan Mladenović25-Jan-13 1:43 
AnswerRe: Small Correction Pin
jgauffin29-Jan-13 8:01
memberjgauffin29-Jan-13 8:01 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1802120.2 | Last Updated 28 Aug 2013
Article Copyright 2013 by jgauffin
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid