Closing SqlConnection and SqlCommand c#

Question

In my DAL I write queries like this:

using(SQLConnection conn = "connection string here")
{
    SQLCommand cmd = new ("sql query", conn);
    // execute it blah blah
}

Now it just occurred to me that I am not explicitly closing the SQLCommand object. Now I know the 'using' block will take care of the SQLConnection object, but will this also take care of the SQLCommand object? If not than I have a serious problem. I would have to put in the 'using' on the SQLCommand on thousands and thousands of lines of code or do a cmd.Close() on hundreds of methods. Please tell me that if putting in the using or closing the command will provide a better memory management of the web app?

I think this also should be interesting to you: http://valueinjecter.codeplex.com/wikipage?title=Data%20access%20layer%20%28ORM%29%20with%20the%20Value%20Injecter&referringTitle=Home — Omu, Jul 30 '10 at 11:37

score 13 · Answer 1 · answered Jul 30 '10 at 10:01

13

The SqlConnection has no knowledge about the SqlCommand, so you should close it by itself:

using (SqlConnection conn = new SqlConnection("connection string here"))
using (SqlCommand cmd = new SqlCommand("sql query", conn))
{
    // execute it blah blah
}

answered Jul 30 '10 at 10:01

Fredrik Mörk

155,851
29
291
343

Oded · Accepted Answer · 2010-07-30T11:31:12.280

10

No, the using statement will not take care of the command.

You should wrap the commands with using statements as well, since this will properly call Dispose on them:

using(SQLConnection conn = 'connection string here')
{
    using(SQLCommand cmd = new ('sql query', conn))
    {
        //execute it blah blah
    }
}

edited Jul 30 '10 at 11:31

answered Jul 30 '10 at 10:02

Oded

489,969
99
883
1,009

score 5 · Answer 3 · answered Jul 30 '10 at 10:01

It won't handle the SqlCommand, but the SqlCommand will eventually be handled by the garbage collector. I tend to do the following:

using (SqlConn conn ... )
using (SqlComm comm ... )
{
    conn.Open();
}

Stacking the using statements here will handle both.

Closing SqlConnection and SqlCommand c#

3 Answers3

Linked