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

Question

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.

Epps

This change is now committed btw – Bourn

I saw this morning :) Thank you very much. I like the way you handle it... if it is already open, leave it open. If it is closed, close it when done. Simple. – Epps

@MarcGravell What is the syntax for the connection handling now? – Pepin

@StephenPatten not clear what you are asking; but in the context of this question - it should open/close automatically – Bourn

@MarcGravell A typical scenario would be to put the connection in a using statement, open the con, then execute your query. Does this mean we can now skip the con.open() step? – Pepin

@StephenPatten yes, if using a recent dapper – Bourn

Latest from Nuget Dapper dot net 1.13 is what I'm using. Thanks for making it even leaner! – Pepin

Answer 1

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.

Bourn

@Marc - Right now we use the unit of work pattern to handle the connection / TransactionScope . If we were to dispose the IDbConnection in the Query extension method (effectively removing the need to handle connection state in the UnitOfWork), wouldn't that negatively affect performance for multiple subsequent queries? – Circumbendibus

@Chris the key is to make it work both ways. If you want to have a connection that spans multiple operations, then sure: open it for your unit-of-work (actually, often an entire web-request is a unit-of-work). If you want it to handle it internally, then that should be supportable to. It won't stop working for what you intend. I am more than a little aware of performance optimisation demands :) – Bourn

@MarcGravell, I submitted a few requests as Issues on the Google Code project. Let me know if there's anything I can do to help. – Epps

@MarcGravell I know you answered this on 2012. Does this feature has ever been released? According to Dapper page on GitHub, it said "Dapper does not manage your connection's lifecycle, " I am interested in how Dapper do the connection pooling or when it closes the connection. Could you please confirm that? – Hypothesis

@Michael wow, that remark on github is totally out of date. Dapper indeed does (for some time) open/close as necessary if it detects the connection is not open. – Bourn

@MarcGravell Thanks for the confirmation! Would you recommend us to open/close the connection (with "using" block) manually from our code? Or, Would it be better if we let Dapper take care of connection lifetime so we can get the advantage from connection pooling? Thanks! – Hypothesis

@Michael well, technically open/closed is different to disposed. If you are only going to be opening/closing around the individual calls, you might as well let dapper do it. If you are opening/closing at a wider granularity (per request, for example), it would be better for your code to do it and pass an open connection to dapper. – Bourn

Thanks @MarcGravell .. Appreciated it. – Hypothesis

Answer 2

I have to add a contrary answer here, or at least suggest that Dapper may handle connections differently, if only in certain circumstances. I have just reflected over Dapper.SqlMapper and there are checks in the ExecuteCommand Method (called out to by Execute (on the public api)) to check if a connection is closed and then it opens it, if it isn't.

I come across this as a code review by my colleague highlighted that I wasn't explicitly calling a connection.open before calling out via dapper to the DB. This wasn't picked up as my integration tests were all green, everything was hunky-dory at runtime. So we dived into the Dapper code. It could be argued its better to call open for explicitness, but conversely some may argue that the less code the better.

Francinafrancine

Just saw Marc's comment above about connedtions being managed automatically. Apologies for stating what is already known to some, but it wasn't immediately apparent when I first ran through the answers. – Francinafrancine

Answer 3

I believe Dapper doesn't manage your connections as it's outside of it's responsibilities as ORM mapper. Dapper doesn't know if you will be reusing the same connection later on - that's why it accepts a connection as one of the parameters. The same applies to transactions - it's the application that should manage it, not ORM mapper.

It is trivial to write own extension methods that manage connection.

Verret

I don't think it is outside it's responsibilities. It is accessing the database, why can it not open the connection when it is ready to run a command and close it when it's done? It is perhaps an extra 5 lines of code. – Epps

@Epps Probably a few more lines than 5, due to complications of buffered vs non-buffered, but I can't disagree with you on the rest of it. – Bourn

@Epps open coneection might be ok, but you cannot close a connection in the middle of a transaction. Not sure why Query method opens the connection but some others methods don't do it. – Kissable