Managing connection with unbuffered queries in Dapper

Question

I recently started using Dapper , everything seems nice and easy, but there is one thing that confuses me: Connection Management.

According to the documentation:

Dapper does not manage the connection life cycle, it assumes that the connection it receives is open and does not have existing data listing (if MARS is enabled)

In light of this, I started doing this in the implementation of my repository methods:

using (var db = new SqliteConnection(connectionString)) { // call Dapper methods here } 

Then I came across a table with a lot of records, so although I am returning IEnumerable<T> by passing buffered: false to the Query<> method, and when I started to list enumerables at the front end, an arrow exception says that the connection was closed and removed , which is expected, as I wind my calls with the previous block used.

Question: The best way to solve this problem

Side question: Do I use the way I control the connection, the preferred way to do this?

Answer 1

I would suggest this repository template:

 public class Repository { private readonly string _connectionString; public Repository(string connectionString) { _connectionString = connectionString; } protected T GetConnection<T>(Func<IDbConnection, T> getData) { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); return getData(connection); } } protected TResult GetConnection<TRead, TResult>(Func<IDbConnection, TRead> getData, Func<TRead, TResult> process) { using (var connection = new SqlConnection(_connectionString)) { connection.Open(); var data = getData(connection); return process(data); } } } 

For buffered requests, you want to use the first overload of the GetConnection method, for non- GetConnection , you use the second, specifying a callback to process the data:

 public class MyRepository : Repository { public MyRepository(string connectionString) : base(connectionString) { } public IEnumerable<MyMapObject> GetData() { return GetConnection(c => c.Query<MyMapObject>(query)); } public IEnumerable<ResultObject> GetLotsOfData(Func<IEnumerable<MyMapObject>, IEnumerable<ResultObject>> process) { return GetConnection(c => c.Query<MyMapObject>(query, buffered: false), process); } } 

Very simple use:

 static void Main(string[] args) { var repository = new MyRepository(connectionString); var data = repository.GetLotsOfData(ProcessData); } public static IEnumerable<ResultObject> ProcessData(IEnumerable<MyMapObject> data) { foreach (var record in data) { var result = new ResultObject(); //do some work... yield return result; } } 

But keep in mind - the connection may be open too long in this case ...

Answer 2

@ SERGIO, AMAZING! Thanks for such a great picture. I slightly modified it to asynchronously to use it with Dapper asynchronous methods. Makes my whole chain of request asynchronization, starting from controllers, right down to the database! Great!

 public abstract class BaseRepository { private readonly string _ConnectionString; protected BaseRepository(string connectionString) { _ConnectionString = connectionString; } // use for buffered queries protected async Task<T> WithConnection<T>(Func<IDbConnection, Task<T>> getData) { try { using (var connection = new SqlConnection(_ConnectionString)) { await connection.OpenAsync(); return await getData(connection); } } catch (TimeoutException ex) { throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex); } catch (SqlException ex) { throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex); } } // use for non-buffeed queries protected async Task<TResult> WithConnection<TRead, TResult>(Func<IDbConnection, Task<TRead>> getData, Func<TRead, Task<TResult>> process) { try { using (var connection = new SqlConnection(_ConnectionString)) { await connection.OpenAsync(); var data = await getData(connection); return await process(data); } } catch (TimeoutException ex) { throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex); } catch (SqlException ex) { throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex); } } } 

Use with Dapper as follows:

 public class PersonRepository : BaseRepository { public PersonRepository(string connectionString): base (connectionString) { } // Assumes you have a Person table in your DB that // aligns with a Person POCO model. // // Assumes you have an existing SQL sproc in your DB // with @Id UNIQUEIDENTIFIER as a parameter. The sproc // returns rows from the Person table. public async Task<Person> GetPersonById(Guid Id) { return await WithConnection(async c => { var p = new DynamicParameters(); p.Add("Id", Id, DbType.Guid); var people = await c.QueryAsync<Person>(sql: "sp_Person_GetById", param: p, commandType: CommandType.StoredProcedure); return people.FirstOrDefault(); }); } }