none
SqlConnection, Dispose() vs Close() RRS feed

  • Question

  • what is the difference between SqlConnection, Dispose() vs Close() ?

    many people say close and dispose both same....dispose call close()

    the difference is when we close then we can call connection object function that will not throw error but in case dispose it will throw error.

    suppose after close can we call open function or can we call execute non query? so after close if we call connection execute non query then automatically connection will be open ?

    please let me know. thanks

    Monday, January 8, 2018 12:42 PM

Answers

  • SqlConnection class implements IDisposable which has a method called Dispose and SqlConnection provides the implementation of it which contains the logic to cleanup the unmanaged resources when it is called. You can using the using block which will take care of cleaning up the resources used by SqlConnection object and you will not have to worry about calling Dispose or Close like:

    using(SqlConnection connection = new SqlConnection())
    {
    
      // your database calling code goes here
    } // as soon as control reaches this block resources will be cleaned up

    If you refer to the following post, it clearly says that calling either would work, as Dispose() implementation would clean all the resources that needs to be cleaned of SqlConnection object which includes calling Close() method too:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/f7d1fc91-2829-4835-9021-63b7353dd481/sqlconnection-dispose-vs-close?forum=netfxbcl

    The implementation of dispose() for SqlConnection  class looks like following if we see the source code of it:

    // System.Data.SqlClient.SqlConnection.Dispose
    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            this._userConnectionOptions = null;
            this._poolGroup = null;
            this.Close();
        }
        this.DisposeMe(disposing);
        base.Dispose(disposing);
    }
    and took from this SO post
     

    From MSDN doucmentation:

    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):

    Hope it helps!


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites




    • Edited by Ehsan Sajjad Monday, January 8, 2018 7:04 PM
    • Marked as answer by Sudip_inn Tuesday, January 9, 2018 6:36 PM
    Monday, January 8, 2018 6:15 PM
  • To some extent yes, the Dispose() actually will cleanup all the unmanaged resources that are needed to be which includes closing the connection as well but Close() will only be responsible for closing the database connection, so in practice you can use Dispose() instead of Close(), but more better way is to use Using statement block which will take care of cleaning up the un-manged resources.

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Sudip_inn Tuesday, January 9, 2018 6:36 PM
    Tuesday, January 9, 2018 12:13 PM

All replies

  • SqlConnection class implements IDisposable which has a method called Dispose and SqlConnection provides the implementation of it which contains the logic to cleanup the unmanaged resources when it is called. You can using the using block which will take care of cleaning up the resources used by SqlConnection object and you will not have to worry about calling Dispose or Close like:

    using(SqlConnection connection = new SqlConnection())
    {
    
      // your database calling code goes here
    } // as soon as control reaches this block resources will be cleaned up

    If you refer to the following post, it clearly says that calling either would work, as Dispose() implementation would clean all the resources that needs to be cleaned of SqlConnection object which includes calling Close() method too:

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/f7d1fc91-2829-4835-9021-63b7353dd481/sqlconnection-dispose-vs-close?forum=netfxbcl

    The implementation of dispose() for SqlConnection  class looks like following if we see the source code of it:

    // System.Data.SqlClient.SqlConnection.Dispose
    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            this._userConnectionOptions = null;
            this._poolGroup = null;
            this.Close();
        }
        this.DisposeMe(disposing);
        base.Dispose(disposing);
    }
    and took from this SO post
     

    From MSDN doucmentation:

    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):

    Hope it helps!


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites




    • Edited by Ehsan Sajjad Monday, January 8, 2018 7:04 PM
    • Marked as answer by Sudip_inn Tuesday, January 9, 2018 6:36 PM
    Monday, January 8, 2018 6:15 PM
  • you mean to say there is no difference between close and dispose ?
    Tuesday, January 9, 2018 9:17 AM
  • To some extent yes, the Dispose() actually will cleanup all the unmanaged resources that are needed to be which includes closing the connection as well but Close() will only be responsible for closing the database connection, so in practice you can use Dispose() instead of Close(), but more better way is to use Using statement block which will take care of cleaning up the un-manged resources.

    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Sudip_inn Tuesday, January 9, 2018 6:36 PM
    Tuesday, January 9, 2018 12:13 PM
  • The sources of SqlConnection and its base classes are available: https://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlConnection.cs,48932d2b804ed473, therefore you can determine if Dispose calls Close and vice versa, for pooled and non-pooled connections.

    Tuesday, January 9, 2018 6:17 PM
  • i found after dispose we can not call any function of sql connection class but after close we can call function of sql connection class.....am i right?
    Tuesday, January 9, 2018 6:38 PM
  • I don't think that is the case, and you can always check by doing this to see how it goes instead of wondering that what would happen, create a test code which you can use to clear out these doubts, and as Voirel_ pointed out, the source code is available online for SqlConnection  class which you can check out. Here is the method implementation for Close():

    Close()

    and here is final Dispose() method implementation which gets called too from within the Close() when Close() is called clear the unmanaged memory and disposing  it to free the connection:

    Dispose()


    [If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click Answered "Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


    Blog | LinkedIn | Stack Overflow | Facebook
    profile for Ehsan Sajjad on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, January 9, 2018 6:46 PM