Closing connection when no explicit connection object

  • Thread starter Thread starter tshad
  • Start date Start date
T

tshad

If I have no Connection object defined (it is part of the SqlCommand
object), I assume I would need to close my connection using the
ExecuteNonQuery command. Would I do it like so?

SqlCommand dbCommand = null;

try
{
SqlCommand dbCommand = new SqlCommand("ReverseFileDetail",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;

....

dbCommand.Connection.Open();
dbCommand.ExecuteNonQuery();
catch (Exception exc)
{
throw;
}
finally
{
if (dbCommand.Connection != null)
dbCommand.Connection.Close();
}

Thanks,

Tom
 
tshad said:
If I have no Connection object defined (it is part of the SqlCommand
object), I assume I would need to close my connection using the
ExecuteNonQuery command. Would I do it like so?

SqlCommand dbCommand = null;

try
{
SqlCommand dbCommand = new SqlCommand("ReverseFileDetail",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString));
dbCommand.CommandType = CommandType.StoredProcedure;

...

dbCommand.Connection.Open();
dbCommand.ExecuteNonQuery();
catch (Exception exc)
{
throw;
}
finally
{
if (dbCommand.Connection != null)
dbCommand.Connection.Close();
}

Thanks,
Click to expand...

First, your code does have a connection object defined, just not associated
with an explicit variable pointer. But, as you have written, you can access
the object via the command object that you associated it with. So, in this
case, you could close it just as you have done in your finally block.
However, you really should close it from within the try block as soon as
your code no longer needs the open connection and then dispose of it in the
finally block as in:

SqlCommand dbCommand = null;

try
{
SqlCommand dbCommand = new SqlCommand("ReverseFileDetail",
new
SqlConnection(ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString)); dbCommand.CommandType = CommandType.StoredProcedure; ... dbCommand.Connection.Open(); dbCommand.ExecuteNonQuery(); dbCommand.Connection.Close(); catch (Exception exc) { throw; } finally { dbCommand.Connection.Dispose(); }ExecuteNonQuery has no effect on the open/closed status of your connection.It simply executes a command that is not performing a query operation (likean insert, update, or delete operation) and returns the number of rowaffected by the operation.Of course, you could just assign the connection instance to a variable andwork with it explicitly.-Scott
 
Back
Top