How to close SqlConnection automatically after SqlMapper GridReader in 3rd Library

dapper

Question

Here is code snippet from my own helper ,But it has some faults.

  /// <summary>
  /// Execute a command that returns multiple result sets, and access each in turn
  /// </summary>
  public static  SqlMapper.GridReader QueryMultiple(string sql, dynamic param = null, string connectionName = null)
  {

      using (SqlConnection connection = GetOpenConnection(connectionName))

      { return connection.QueryMultiple(sql, param);}

  }

QueryMultiple is called and closed manually by external program. Shortly after that, SqlConnection should be closed automatically , how to wrap up my helper? Thanks in advance.

Accepted Answer

Oddly enough, one option here might be: don't give it an open connection. This isn't deployed to nuget yet, but if you pull the version from github it actually now takes a lot more control over the connection if it needs to. All of the main methods (Query[<T>], QueryMultiple, Execute) now work fine with closed connections, closing it again when it can. So if instead you had:

return GetClosedConnection(connectionName).QueryMultiple(sql, param);

then it will close automatically when the GridReader reaches the end of the data. Note that I've re-structured it to ensure we don't Dispose() the connection when leaving the method.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow