Chandradev's Blog

Maximum new programmer used to write code without calling Dispose() on sqlconnection and sqlcommand  object. Even thought I was also writing code like this.

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(commandString, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

But SqlConnection and SqlCommand implement IDisposable, which means they could have unmanaged resources to cleanup and it is our job, to make sure Dispose() gets called on these classes before finishing the task. Otherwise an exception could be raised if the database is unavailable,we need to make sure Dispose() gets called even in the case of an exception.

There are 2 methods to cleanup the resources

1. Using C# “Using” Keyword.
2. Using Try/ Finally

Syntax for “Using” keyword

using (SqlConnection con = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(“ SQL Query ”, con))
{
con.Open();
cmd.ExecuteNonQuery();
}
}

Syntax  for using  “Try/Finally”

SqlConnection con =  null;
SqlCommand cmd = null;

try
{
con = new SqlConnection(connectionString);
cmd = new SqlCommand(“SQL QUERY”, con);
con.Open();
cmd.ExecuteNonQuery();
}
finally
{
if (null != cmd);
cmd.Dispose();
if (null != con)
con.Dispose();
}