Introduction
Here I
would like to introduce the method of closing the database connection using the
Commandbehaviour.CloseConnection.
Code Example
If we
need to fetch some data from the database then we usually use the sample code
written below.
SqlConnection
myConnection = new
SqlConnection(myConnectionString);
SqlCommand myCommand = new
SqlCommand(mySelectQuery, myConnection);
myConnection.Open();
SqlDataReader myReader;
myReader =
myCommand.ExecuteReader();
try
{
while
(myReader.Read())
{
Console.WriteLine(myReader.GetString(0));
}
}
finally
{
myReader.Close();
myConnection.Close();
}
But in
the above written code, we have to close the connection explicitly each and
every time we open the connection.
To
overcome this issue we have another better method to close the connection -
mentioned below.
SqlConnection
myConnection = new
SqlConnection(myConnectionString);
SqlCommand myCommand = new
SqlCommand(mySelectQuery, myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
while (myReader.Read())
{
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
//Implicitly
closes the connection because CommandBehavior.CloseConnection was specified.
Explanation of the above code
In the
above written code we are using the CommandBehavior.CloseConnection, which is a
parameter of the ExecuteReader method.
If we
are using the CommandBehavior.CloseConnection, then we do not need to close the
database connection explicitly because once we close the datareader object then
it automatically closes the database connection as well.
MSDN link
http://msdn.microsoft.com/en-us/library/aa326246(v=vs.71).aspx