Difference between a 'using statement' for every SqlConnection and single SqlConnection as private property in C# data layer
c# dapper n-tier-architecture repository-pattern sqlconnection
Question
When implementing the repository pattern using Dapper ORM I am currently doing the following:
private readonly ConnectionStrings _connectionStrings;
private IDbConnection _db;
public CustomerRepository(IOptions<ConnectionStrings> connectionStrings)
{
_connectionStrings = connectionStrings.Value;
_db = new SqlConnection(_connectionStrings.DefaultConnection);
}
public Customer Find(int id)
{
return this._db.Query<Customer>("SELECT * FROM Contacts WHERE Id = @Id", new { id }).SingleOrDefault();
}
Can someone please tell me if I should be doing it this way or if I should be using a using statement with a new SqlConnection in every single repository function.
I am assuming my above code will need something like UnitOfWork to be effective right? And also some way of disposing the connection when done running all of the repository functions needed.
Popular Answer
In general when a type implements IDisposable (and hence works with using) it can sometimes be useful to wrap it in another type, having that other type also implement IDisposable and have its Dispose() call the wrapped object, and then use using (or another mechanism to call Dispose()) on it.
The question is whether this is one of those sometimes.
It's not. In particular note that SqlConnection implements pooling behind the scenes, so (unless you explicitly opt-out in your connection string) instead of Dispose() shutting down the entire connection to the server what actually happens is that an object internal to the assembly of SqlConnection that handles the details of the connection is put into a pool to use again the next time an SqlConnection with the same connection string is opened.
This means that your application will get as efficient use of as few connections as possible over many uses of the SqlConnection class. But you are stymying that by keeping the connections out of the pool by not returning to the pool as promptly as possible.