Should dapper use a "using" statement?

Question

I have seen examples where someone is doing:

IDbConnection db = new MySqlConnection(conn);

var people = db.Query<People>("SELECT * FROM PEOPLE").ToList();

or is the above a bad practice and should all queries be put in using statements like so:

using (var db = new MySqlConnection(conn))
{
var people = db.Query<People>("SELECT * FROM PEOPLE").ToList();
}

Sackey

yes put a using around the connection. – Tennilletennis

Answer 1

As others have correctly noted, the best practice in general is to use using any time an object implements IDisposable and you know that the lifetime of the object is going to be short -- that is, not longer than the duration of the current method. Doing so ensures that scarce operating system resources are cleaned up in a timely manner. Even if an object's disposal is "backstopped" by its finalizer, you don't want to be in a situation where, say, you have a lock on a file or database or something that is not going to be released until the finalizer runs several billion nanoseconds from now.

However I would temper that advice by saying that there are a small number of types that implement IDisposable for reasons other than timely disposal of unmanaged resources. In some very specific cases you can safely skip the using. However, it is almost never wrong to use using, even when it is not strictly speaking necessary, so my advice to you is to err on the side of caution and over-use, rather than under-use using.

Moonshine

In addition, I would mention the article of Stephen Toub about the disposing of .Net Tasks: Do I need to dispose of Tasks? – Hom

Answer 2

Dapper doesn't have a view on this; what you are using here is the database connection. If you have finished with the connection: you have finished with the connection. Basically, yes, you should probably be using using.

Lozengy

Answer 3

The main purpose use of using statments is to release unmanaged resources.When an object is no longer used The garbage collector automatically releases the memory allocated to it but sometimes the garbage collector does not release resources such as files, streams or db connection like in your example.

Think of it of a way to explicitly dispose objects rather than leave it up to the compiler so you can say it's better practice.

Stablish

Not disposing resources you're sure to not use again IS a bad practice. – Lyte

I know what the garbage collector does and I have always seen people say that the using statement should always be used especially with connections so they are only kept open when needed and released back to the connection pool. Why rely strictly on the garbage collector when you can put the connection in a using statement. I am more concerned with if dapper does this behind the scenes? – Sackey

"Why rely strictly on the garbage collector" --- because you cannot rely on it. – Kevon

@Sackey My answer was not specific but its better peactice so do use Idisposable whenever you can – Stablish

@Kevon - It was a rhetorical question. – Sackey

Answer 4

In my experience with Sql Server and Oracle (using the ODP.Net drivers and the MS drivers), you need to use using around Connections, Commands and Transactions or you will soon exhaust your connection pool if you do anything but the simplest database interaction in a production environment (the connection pool is typically ~50 - 200 connections).

You may not notice the behaviour in a development environment because debug = a lot of restarts, which clears the pool.

As Eric Lippert above said, it is good practice in general to use using around IDisposable objects.

In practice, you can skip "using" on Parameters for SqlServer and Oracle.

Outoftheway