35

I saw that in most samples SqlCommand was used like this

using (SqlConnection con = new SqlConnection(CNN_STRING))
{
    using (SqlCommand cmd = new SqlCommand("Select ID,Name From Person", con))
    {
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);           
        return ds;
    }
}

I know why we are using the using statement. But SqlCommand doesn't include a Close() method so should we really use it within a using statement?

Improve this question
2
  • 2
    In this example you should not really care about closing connection since Fill() manage connection itself even you r not using using statement. Commented Apr 21, 2014 at 5:19
  • 2
    Yes, you are right. From the MSDN: "The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open." - msdn.microsoft.com/en-us/library/377a8x4t.aspx Commented Apr 21, 2014 at 6:19

4 Answers 4

Reset to default
42

Because it also implements IDisposable.

The purpose of Using statement is that when control will reach end of using it will dispose that object of using block and free up memory. its purpose is not only for auto connection close, basically it will dispose connection object and obviously connection also closed due to it.

Its purpose is to free up the resources that we used inside the Using statement.

According to MSDN:

As a rule, when you use an IDisposable object, you should declare and instantiate it in a using statement. The using statement calls the Dispose method on the object in the correct way, and (when you use it as shown earlier) it also causes the object itself to go out of scope as soon as Dispose is called. Within the using block, the object is read-only and cannot be modified or reassigned.

The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler. The code example earlier expands to the following code at compile time (note the extra curly braces to create the limited scope for the object):

NOTE:

You can instantiate the resource object and then pass the variable to the using statement, but this is not a best practice. In this case, the object remains in scope after control leaves the using block even though it will probably no longer have access to its unmanaged resources. In other words, it will no longer be fully initialized. If you try to use the object outside the using block, you risk causing an exception to be thrown. For this reason, it is generally better to instantiate the object in the using statement and limit its scope to the using block.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

How can I understand if a class implements IDisposable?
a class that not implements IDisposable cannot be using with using, it must implement IDisposable for that
My question is how do we know SqlCommand implements Idisposible? I am new so I want lo learn
you can check if a Dispose() method you can have with the object it implements IDisposable otherwise it not implements
means if you see a method Dispose that is accessible with class object its implementing IDisposable
9

SqlCommand does implement IDisposable which a using statement will call .Dispose() on before the using block is finished. I'm not sure what SqlCommand.Dispose() does, but it's a good idea to call .Dispose() on an instance you are finished with i.e. it will clear up the database connection perhaps.

Improve this answer

5 Comments

The purpose of SqlCommand.Dispose() is used to release all ressources used by SqlCommand. In his case, the object cmd.
How can I understand if a class implements IDisposable?
Various ways - one being in Visual Studio go to the type of the instance and right click on it, select "Go To Definition". That window will tell you if the type implements IDisposable.
Now that the source is available, you can see what it does. It's not much, but it does do something: referencesource.microsoft.com/#System.Data/System/Data/…
If using Jason's trick, right click SqlCommand, Go To Definition, and see public sealed class SQLiteCommand : DbCommand, ICloneable, thus it inherits from DbCommand. Right click DbCommand, Go To Definition, and see it inherits from IDisposable.
0

SqlCommand.Dispose() is used to release all resources used by SqlCommand.

Improve this answer

1 Comment

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
0

I did some research and found an answer which I summarized below. For a more detailed explanation, see here

The using statement for SqlCommand does not dispose of a whole lot. SqlCommand indirectly inherits from Component which implements a finalizer. Unless the implementation has changed, the GC ignores objects with a finalizer the first time it encounters an out-of-scope one. Instead of removing it from memory, the GC calls the finalizer and then moves on. The second time it encounters it, the GC will remove the object from memory. This is obviously rather expensive so you do not want the finalizer to run very often.

To prevent the finalizer from running, Component (which SqlCommand indirectly inherits from) implements a dispose method that tells the GC not to call the finalizer since the dispose method already handled any cleanup.

In a nutshell, the using statement around SqlCommand doesn't do anything special, but it prevents the more expensive finalizer from being called.

If you have more details into how this works, please let me know in the comments.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.