Dapper Exception - "The ConnectionString property has not been initialized" when 'buffered' is 'false'

Question

I am trying out Dapper and I have stumbled upon this exception:

System.InvalidOperationException: The ConnectionString property has not been initialized.
...

Here is the code:

public IEnumerable<T> GetAll<T>(string sql, DynamicParameters parameters, string connectionString, CommandType commandType = CommandType.StoredProcedure)
{
    using IDbConnection db = new SqlConnection(connectionString);
    var result = db.Query<T>(sql, parameters, commandType: commandType, commandTimeout: COMMAND_TIMEOUT, buffered: false);
    return result;
}

When I remove the buffered parameter, everything works OK.

var result = db.Query<T>(sql, parameters, commandType: commandType, commandTimeout: COMMAND_TIMEOUT);

Marc Gravell · Accepted Answer · 2021-09-13T10:42:16.153

I suspect the real problem here is that you are returning an open queryable, which means you're leaving the scope of the using block and disposing the connection that is still being used. The real problem here is an object disposed too soon, but it is manifesting in an unusual way.

Basically, you need to not dispose the connection until you're done with the data that needs the connection. A lazy way of doing this would be:

using IDbConnection db = new SqlConnection(connectionString);
foreach (var row in db.Query<T>(sql, parameters, commandType: commandType, commandTimeout: COMMAND_TIMEOUT, buffered: false)
{
    yield return row;
}

This only happens when not buffering the data, because buffering means "get all the data before returning to the caller", instead of returning an open query. By switching to an "iterator block" (via yield return) the scope of the using is extended until the last of the data has been consumed (or the loop somehow else exited).

Thank you! This exception has really confused me because it didn't mentioned disposing. — lijevosmetalo, Sep 13 '21 at 11:04
@lijevosmetalo ultimately, it is the ADO.NET provider that is raising that exception, which makes it very hard for us to handle at the library layer; I'd rather not start trying to detect "magic strings", as that has locale issues — Marc Gravell, Sep 13 '21 at 11:37

Dapper Exception - "The ConnectionString property has not been initialized" when 'buffered' is 'false'

1 Answers1