Dapper with .NET Core - injected SqlConnection lifetime/scope

Question

I'm using .NET Core Dependency Injection to instantiate a SqlConnection object during the application startup, which I'm then planning to inject in my repository. This SqlConnection will be used by Dapper to read/write data from the database within my repository implementation. I am going to use async calls with Dapper.

The question is: should I inject the SqlConnection as transient or as a singleton? Considering the fact that I want to use async my thought would be to use transient unless Dapper implements some isolation containers internally and my singleton's scope will still be wrapped within whatever the scope Dapper uses internally.

Are there any recommendations/best practices regarding the lifetime of the SqlConnection object when working with Dapper? Are there any caveats I might be missing?

Thanks in advance.

Answer 1

If you provide SQL connection as singleton you won't be able to serve multiple requests at the same time unless you enable MARS, which also has it's limitations. Best practice is to use transient SQL connection and ensure it is properly disposed.

In my applications I pass custom IDbConnectionFactory to repositories which is used to create connection inside using statement. In this case repository itself can be singleton to reduce allocations on heap.

Answer 2

I agree with @Andrii Litvinov, both answer and comment.

In this case I would go with approach of data-source specific connection factory.

With same approach, I am mentioning different way - UnitOfWork.

Refer DalSession and UnitOfWork from this answer. This handles connection.
Refer BaseDal from this answer. This is my implementation of Repository (actually BaseRepository).

• UnitOfWork is injected as transient.
• Multiple data sources could be handled by creating separate DalSession for each data source.
• UnitOfWork is injected in BaseDal.

Are there any recommendations/best practices regarding the lifetime of the SqlConnection object when working with Dapper?

One thing most of developers agree is that, connection should be as short lived as possible. I see two approaches here:

1. Connection per action.
   This of-course will be shortest life span of connection. You enclose connection in using block for each action. This is good approach as long as you do not want to group the actions. Even when you want to group the actions, you can use transaction in most of the cases.
   Problem is when you want to group actions across multiple classes/methods. You cannot use using block here. Solution is UnitOfWork as below.
2. Connection per Unit Of Work.
   Define your unit of work. This will be different per application. In web application, "connection per request" is widely used approach.
   This makes more sense because generally there are (most of the time) group of actions we want to perform as a whole. This is explained in two links I provided above.
   Another advantage of this approach is that, application (that uses DAL) gets more control on how connection should be used. And in my understanding, application knows better than DAL how connection should be used.

Answer 3

Great question, and already two great answers. I was puzzled by this at first, and came up with the following solution to solve the problem, which encapsulates the repositories in a manager. The manager itself is responsible for extracting the connection string and injecting it into the repositories.

I've found this approach to make testing the repositories individually, say in a mock console app, much simpler, and I've have much luck following this pattern on several larger-scale project. Though I am admittedly not an expert at testing, dependency injection, or well anything really!

The main question I'm left asking myself, is whether the DbService should be a singleton or not. My rationale was that, there wasn't much point constantly creating and destroying the various repositories encapsulated in DbService and since they are all stateless I didn't see much problem in allowing them to "live". Though this could be entirely invalid logic.

EDIT: Should you want a ready made solution check out my Dapper repository implementation on GitHub

The repository manager is structured as follows:

/*
 * Db Service
 */
public interface IDbService
{
    ISomeRepo SomeRepo { get; }
}

public class DbService : IDbService
{
    readonly string connStr;
    ISomeRepo someRepo;

    public DbService(string connStr)
    {
        this.connStr = connStr;
    }

    public ISomeRepo SomeRepo
    {
        get
        {
            if (someRepo == null)
            {
                someRepo = new SomeRepo(this.connStr);
            }

            return someRepo;
        }
    }
}

A sample repository would be structured as follows:

/*
 * Mock Repo
 */
public interface ISomeRepo
{
    IEnumerable<SomeModel> List();
}

public class SomeRepo : ISomeRepo
{
    readonly string connStr;

    public SomeRepo(string connStr)
    {
        this.connStr = connStr;
    }

    public IEnumerable<SomeModel> List()
    {
        //work to return list of SomeModel 
    }
}

Wiring it all up:

/*
 * Startup.cs
 */
public IConfigurationRoot Configuration { get; }

public void ConfigureServices(IServiceCollection services)
{
    //...rest of services

    services.AddSingleton<IDbService, DbService>();

    //...rest of services
}

And finally, using it:

public SomeController : Controller 
{
    IDbService dbService;

    public SomeController(IDbService dbService)
    {
        this.dbService = dbService;
    }

    public IActionResult Index()
    {
        return View(dbService.SomeRepo.List());
    }
}