34

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?

Omer
  • 8,194
  • 13
  • 74
  • 92
  • 2
    In this example you should not really care about closing connection since `Fill()` manage connection itself even you r not using `using` statement. – Ankush Madankar Apr 21 '14 at 05: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." - http://msdn.microsoft.com/en-us/library/377a8x4t.aspx – Omer Apr 21 '14 at 06:19

4 Answers4

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.

Community
  • 1
  • 1
Ehsan Sajjad
  • 61,834
  • 16
  • 105
  • 160
8

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.

user692942
  • 16,398
  • 7
  • 76
  • 175
Jason Evans
  • 28,906
  • 14
  • 90
  • 154
  • The purpose of SqlCommand.Dispose() is used to release all ressources used by SqlCommand. In his case, the object cmd. –  Apr 20 '14 at 18:33
  • How can I understand if a class implements IDisposable? – Omer Apr 20 '14 at 18:38
  • 4
    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`. – Jason Evans Apr 20 '14 at 18:43
  • 2
    Now that the source is available, you can see what it does. It's not much, but it does do something: https://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlCommand.cs,53ad9885e5a8fc48 – Gabriel Luci Sep 26 '17 at 15:49
  • 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. – John Doe Dec 27 '19 at 15:10
0

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

  • 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](/help/how-to-answer). – Community Mar 26 '22 at 12:41
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.

Matthew Peterson
  • 1,055
  • 12
  • 21