Search code examples
.netconnection-poolingsqlconnectiondapper

Why doesn't Dapper dot net open and close the connection itself?

Dapper implicitly expects a connection to be open when it uses it. Why doesn't it open and close it itself? Wouldn't this simply connection management?

I ask because a co-worker and I have been going back and forth on the nature of what goes on behind the scenes with connection pooling, and if there is any benefit to keeping a connection open amongst multiple commands, or to open and close it for each command.

Solution

  • Dapper now (and for quite some time) deals with this internally. It just works™

    Original (outdated) answer:

    You aren't wrong. The reason I hadn't noticed this inconvenience is that for legacy reasons (specifically: we used to use LINQ-to-SQL exclusively) our primary connection-like-thing is a DataContext - so we re-expose the dapper methods as extension methods on DataContext.

    The silly thing is: what these methods do is:

    using(db.Connection.EnsureOpen()) {
    db.Connection.{the dapper method}
}

    Here EnsureOpen is a cheeky method that:

    • if the connection is open, returns null
    • otherwise, it opens the connection, and returns an IDisposable token that closes the connection when done

    So: we obviously felt exactly your pain, but we implemented it a layer further up.

    Please log this as a feature request. We have all the code (although I'll need to tweak it slightly to fit the "reader" for non-buffered data) - there's absolutely no reason that dapper can't take ownership of this.