9

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();
}
xaisoft
  • 3,343
  • 8
  • 44
  • 72

4 Answers4

15

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.

Eric Lippert
  • 647,829
  • 179
  • 1,238
  • 2,067
  • 1
    In addition, I would mention the article of Stephen Toub about the disposing of .Net Tasks: [Do I need to dispose of Tasks?](http://blogs.msdn.com/b/pfxteam/archive/2012/03/25/10287435.aspx) – Luca Cremonesi Mar 16 '15 at 14:43
10

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
2

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.

meda
  • 45,103
  • 14
  • 92
  • 122
  • Not disposing resources you're sure to not use again IS a bad practice. – Mephy Mar 11 '15 at 01:27
  • 1
    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? – xaisoft Mar 11 '15 at 01:29
  • 1
    "Why rely strictly on the garbage collector" --- because you cannot rely on it. – zerkms Mar 11 '15 at 01:30
  • @xaisoft My answer was not specific but its better peactice so do use Idisposable whenever you can – meda Mar 11 '15 at 01:36
  • @zerkms - It was a rhetorical question. – xaisoft Mar 11 '15 at 01:40
0

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.

Michel de Becdelièvre
  • 234
  • 2
  • 5