Do I have to Close() a SQLConnection before it gets disposed?
C#asp.netUsingSqlconnectionSqlcommandC# Problem Overview
Per my other question here about Disposable objects, should we call Close() before the end of a using block?
using (SqlConnection connection = new SqlConnection())
using (SqlCommand command = new SqlCommand())
{
command.CommandText = "INSERT INTO YourMom (Amount) VALUES (1)";
command.CommandType = System.Data.CommandType.Text;
connection.Open();
command.ExecuteNonQuery();
// Is this call necessary?
connection.Close();
}
C# Solutions
Solution 1 - C#
Since you have a using block, the Dispose method of the SQLCommand will be called and it will close the connection:
// System.Data.SqlClient.SqlConnection.Dispose disassemble
protected override void Dispose(bool disposing)
{
if (disposing)
{
this._userConnectionOptions = null;
this._poolGroup = null;
this.Close();
}
this.DisposeMe(disposing);
base.Dispose(disposing);
}
Solution 2 - C#
Disassembly of SqlConnection from using .NET Reflector:
protected override void Dispose(bool disposing)
{
if (disposing)
{
this._userConnectionOptions = null;
this._poolGroup = null;
this.Close();
}
this.DisposeMe(disposing);
base.Dispose(disposing);
}
It calls Close() inside of Dispose()
Solution 3 - C#
The using keyword will close the connection correctly so the extra call to Close is not required.
From the MSDN article on SQL Server Connection Pooling:
> "We strongly recommend that you always > close the connection when you are > finished using it so that the > connection will be returned to the > pool. You can do this using either the > Close or Dispose methods of the > Connection object, or by opening all > connections inside a using statement > in C#"
The actual implementation of SqlConnection.Dispose using .NET Reflector is as follows:
// System.Data.SqlClient.SqlConnection.Dispose disassemble
protected override void Dispose(bool disposing)
{
if (disposing)
{
this._userConnectionOptions = null;
this._poolGroup = null;
this.Close();
}
this.DisposeMe(disposing);
base.Dispose(disposing);
}
Solution 4 - C#
Using Reflector, you can see that the Dispose
method of SqlConnection
actually does call Close()
;
protected override void Dispose(bool disposing)
{
if (disposing)
{
this._userConnectionOptions = null;
this._poolGroup = null;
this.Close();
}
this.DisposeMe(disposing);
base.Dispose(disposing);
}
Solution 5 - C#
No, calling Dispose() on SqlConnection also calls Close().
Solution 6 - C#
No, having the Using block calls Dispose()
for you anyway, so there is no need to call Close()
.
Solution 7 - C#
No, it is not necessary to Close a connection before calling Dispose.
Some objects, (like SQLConnections) can be re-used afer calling Close, but not after calling Dispose. For other objects calling Close is the same as calling Dispose. (ManualResetEvent and Streams I think behave like this)
Solution 8 - C#
No, the SqlConnection class inherits from IDisposable, and when the end of using (for the connection object) is encountered, it automatically calls the Dispose on the SqlConnection class.