in .NET Core and Dapper, can you specify a lifetime/scope to a SqlConnection?

asp.net-core c# dapper dependency-injection

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.

1
13
3/21/2017 8:54:12 PM

Accepted Answer

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.

7
3/21/2017 9:00:46 PM

Popular Answer

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.


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow