Managing connection with non-buffered queries in Dapper

Question

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

As per the documentation:

Dapper does not manage your connection's lifecycle, it assumes the connection it gets is open AND has no existing datareaders enumerating (unless MARS is enabled)

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

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

Then I came across a table with a large number of records, so I though of returning an IEnumerable<T> by passing buffered: false to the Query<> method, and when I started enumerating the enumerable in the front end, boom an exception saying the connection was closed and disposed which is expected since I am wrapping my calls with the preceding using block.

Question: Best way to solve this ?
Side question: Is the way I am managing the connection the preferred way to go about it ?

Answer 1

I'd offer this repository pattern:

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 queries you want to use first overload of GetConnection method, for non-buffered you use second, specifing callback for processing 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 basic usage:

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 - connection may be opened for too long time in this case...