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.
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.
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.
UnitOfWorkis injected as transient.
DalSessionfor each data source.
UnitOfWorkis injected in
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:
usingblock 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.
usingblock here. Solution is UnitOfWork as below.