Dapper with .NET Core - injected SqlConnection lifetime/scope
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());
}
}
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.
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 inBaseDal
.
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:
- Connection per action.
This of-course will be shortest life span of connection. You enclose connection inusing
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 useusing
block here. Solution is UnitOfWork as below. - 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.