Simple but good example on how to use Dapper with Structuremap and dependency injection

Question

I am trying to understand how to use Dependency Injection with Dapper (IDbConnection) and still being able to use built in dispose.

I have found a couple of articles on the web but non that I think is easy to understand.

What I am trying to figure out is how to make this simple class be testable:

public class UserProfileRepository : IUserProfileRepository
{
    private readonly IConfigRepository _configRepository;

    public UserProfileRepository(IConfigRepository configRepository)
    {
        _configRepository = configRepository;
    }

    public UserProfile GetUserProfile(string userId)
    {
        const string query = @"Select UserId, UserName
                                From Users
                                Where UserId = @UserId";

        using (var conn = new SqlConnection(_configRepository.GetConnectionString("MyConnectionString")))
        {
            conn.Open();
            return conn.Query<UserProfile>(query, new { UserId = userId }).SingleOrDefault();
        }
    }
}

I have a config repository that looks like this so I can mock the request to web.config away:

public class ConfigRepository : IConfigRepository
{
    public string GetConnectionString(string key)
    {
        var conString = ConfigurationManager.ConnectionStrings[key];
        if (conString != null)
        {
            return conString.ConnectionString;
        }

        return string.Empty;
    }
}

I have read that you could use ConnectionFactory but has not figur out how to implement it and still know I am disposing it correct.

Can anyone point me in the right direction?

Annikaanniken

There are other options that you haven't considered. One, test this class via an integration test. Two, mock the entire repository (since you are using dependency injection after all) and forego the database calls. It is unclear what you are actually trying to accomplish since you still instantiate the sql connection directly, making it unmockable. – Bicker

Thanks. Yes I am just starting to try to make these repositories testable but either am I bad at googling or there are no best practices on how to do it. – Annikaanniken

@HenrikFransas best practices are based on your requirements, there's no standard rule that fits all cases. Nonetheless let me post a sample of DI using Ninject, similar mechanism shall exist for Structure Map too – Burrussburry

My answer underneath is a repetition from another question, but requirements are more or less same, only Mocking is additional – Burrussburry

Answer 1

Best connection creation mechanism as per my experience is the combination of DependencyInjection and ConnectionFactory. I am getting rid of IConfigRepository, since here all the work is done using factory

Advantages are Multi fold:

• Create a connection object at runtime in transaction or thread scope
• At runtime change the data provider and thus database of the system ( using Connection Factory)

What you shall do (in Code):

Declare the IDBConnection object in the Data access Layer:

[Inject] // Property Injection
public IDBConnection Connection {get; set;}

Declare the binding using a DI framework like Ninject:

Bind<IDBConnection>().ToMethod(ctx => 
ConnectionFactory.CreateDbConnection("DefaultConnection"));

Create the DBConnection Factory as follows:

Connection factory fetches the Connection provider and connection string from the config file as follows:

<connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=<Value>;Initial Catalog=<Value>;User Id=<Value>;Password=<Value>" providerName="System.Data.SqlClient" />
</connectionStrings>

Identifier is DefaultConnection, which is using the SqlClient provider, but at run time can be changed to the different client like Oracle, MySql

 using System;
 using System.Data.Common;

 public static class ConnectionFactory
    {
        /// <summary>
        /// Create DBConnection type based on provider name and connection string
        /// </summary>
        /// <param name="connectionIdentifier"></param>
        /// <returns></returns>
        public static DbConnection CreateDbConnection(string connectionIdentifier)
        {
            // Provider name setting
            var providerNameValue = ConfigurationManager.ConnectionStrings[connectionIdentifier].ProviderName;

            // Connection string setting
            var connectionStringValue = ConfigurationManager.ConnectionStrings[connectionIdentifier].ConnectionString;

            // Assume failure.
            DbConnection connection;

            // Null connection string cannot be accepted
            if (connectionStringValue == null) return null;

            // Create the DbProviderFactory and DbConnection.
            try
            {
                // Fetch provider factory
                var factory = DbProviderFactories.GetFactory(providerNameValue);

                // Create Connection
                connection = factory.CreateConnection();

                // Assign connection string
                if (connection != null)
                    connection.ConnectionString = connectionStringValue;
            }
            catch (Exception ex)
            {
                connection = null;
            }
            // Return the connection.
            return connection;
        }
}

How to use it:

For a single call and dispose

using(Connection)
{
 ...
}

For a Transaction context, use as-is, no using required

Regarding Mocking:

Which ever Mock framework you use for the Unit testing you have to mock the result of the UserProfileRepository :: GetUserProfile(string userId), this would be easier instead of filling a MockConnection using dependency Injection, which will make it complex. DI is good for real use case, for filling connection object at runtime

Burrussburry

Thanks, will try it out. Will this make it dispose correct as per request or what scope does it use? – Annikaanniken

In Pooling on mode, Close / Dispose send the connection object back to pool instead of destroying it. Objection creation and Dispose is always per request, but you can make scope Transaction based using Dependency Injection Invocation / Binding. My code is with Ninject, please ensure that you find StructureMap replacements. – Burrussburry

Thanks, will try it out and get back to you – Annikaanniken

Sorry, have not had time to try it out yet, will do it this weekend, – Annikaanniken

How the connection object is getting disposed and closed for a transaction context? – Hosmer

It is still developer's job to call Close / Dispose on the IDBConnection object, just that pooled connection will return back to the Pool. In this case I have just shown a pattern to create DBConnection using Factory and inject it at runtime – Burrussburry

Can anyone provide a working version of this? Maybe a sample project? – Enthrone

@Enthrone created a small sample to just explain the Dependency injection using Ninject, dotnetfiddle.net/GDs34d Let me know if you have any doubt. We use the Constructor Injection for injecting the IDbConnection object with SqlConnection – Burrussburry