ado.net Closing Connection when using "using" statement

Question

I am doing my database access methods to SQL Server like this

  using (SqlConnection con = new SqlConnection(//connection string)
  {
    using (SqlCommand cmd = new SqlCommand(storedProcname, con))
     {
       try{
           con.open();
           //data reader code
       }
       catch
       {

       }
     }
  }

Do I need to be closing or disposing of SqlCommand, or will the using statement take care of that for me? I just don't want connection hanging open Thanks

score 62 · Accepted Answer · answered Dec 08 '10 at 16:01

62

The using will take care of it for you. Under the hood, SqlConnection.Dispose() calls the SqlConnection.Close() method, and SqlCommand.Dispose() calls SqlCommand.Close().

As additional background, a using statement is syntactic sugar for a try ... finally that disposes the IDisposable object in the finally.

answered Dec 08 '10 at 16:01

Phil Hunt

8,404
1
30
25

3

It will take care of it - but I would still close it explicitly and use the using() block only as a "backup" for when I forget. – marc_s Dec 08 '10 at 16:06
1

Great Thank you! Thanks to everyone who responded as well! – twal Dec 08 '10 at 16:07
Relying on such 'features' can be dangerous, and confusing. I would still close it explicitly, just as marc_s. TBH, I would dispose it explicitly, as well, and only at the place I would want it disposed. – ThunderGr Dec 13 '12 at 11:38

score 12 · Answer 2 · edited Feb 22 '16 at 05:26

12

As an aside, you can make the code more concise and readable as follows:

 using (SqlConnection con = new SqlConnection(/*connection string*/))
 using (SqlCommand cmd = new SqlCommand(storedProcname, con))
 {
    //...
 }

edited Feb 22 '16 at 05:26

peak

105,803
17
152
177

answered Dec 08 '10 at 16:05

Dan Bryant

27,329
4
56
102

score 4 · Answer 3 · answered Dec 08 '10 at 16:04

As Phil said, the using clause will take care of it for you. When compiled down it wraps the connection create in a try .. finally and places the connection disposal call inside the finally.

For more information you can see the using statement article at msdn.

score 3 · Answer 4 · answered Dec 08 '10 at 16:07

Yes your code will close the connection, however that typcally means release back to the connection pool to be truely closed later.

If you execute this snippet of code, and then do an sp_who and observe that your connection is still there, that would be why.

If you absolutely need the connection truely closed (an edge case to be sure) then use the ClearAllPools static method of ths SqlConnection

score 2 · Answer 5 · edited Nov 14 '12 at 13:36

2

Using keyword will automatically close the connection for you so you don't need to worry about calling connection.close() at the end every time.

edited Nov 14 '12 at 13:36

Adi

5,089
6
33
47

answered Nov 14 '12 at 13:16

PUBG

199
2
12

1

I don't quite see, how this differs from the accepted answer. Can you elaborate? – Nicktar Nov 14 '12 at 13:38

score 1 · Answer 6 · answered Dec 08 '10 at 16:03

1

when the scope

using (SqlConnection con = new SqlConnection(//connection string) 
{
}

will over , connection will automatically be disposed by runtime. so don't worry

answered Dec 08 '10 at 16:03

TalentTuner

17,262
5
38
63

score 0 · Answer 7 · answered Dec 08 '10 at 16:04

0

I think "using" was not required for SqlCommand. "Using" for SqlConnection would have done the job for you alone. In fact you connection is submitted to Connection pool.

answered Dec 08 '10 at 16:04

Kunal

1,913
6
29
45

4

No - you need using() for every disposable object - `using(SqlConnection.....)` alone won't take care of your `SqlCommand` – marc_s Dec 08 '10 at 16:07
6

Anything that implements IDisposable should be placed in a using statement. It's good practice to be able to quickly see code that just isn't right. – NotMe Dec 08 '10 at 16:07

ado.net Closing Connection when using "using" statement

7 Answers7

Linked