Often when I see database code on forums and newsgroups I notice resource management in a situation of failure is non-existent. Exceptions can be handled gracefully and allow the application to continue running, so why should we ignore the mess of open connections and resources in the background that are not cleaned up in most cases due to cleanup code being bypassed when an exception is thrown.
Allow me to give a typical example…
public DataSet Load(string username)
{
DataSet ds = null;
try
{
SqlConnection conn = new SqlConnection(CONN_STRING);
SqlDataAdapter da = new SqlCommand();
SqlCommand cmd = new SqlDataAdapter(cmd);
cmd.CommandText = "GetUser";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = username;
conn.Open();
da.Fill(ds, "user");
conn.Close();
}
catch (System.Exception ex)
{
throw ex;
}
return ds;
}
If you look above you may think, “we are closing the connection so what is the problem?” By closing the connection within the try block, we risk that the call to close on the connection object may never be called if an exception is thrown from our data adapter after the connection is opened. We are also are relying on the garbage collector to dispose all of our current database resources which isn’t very efficient.
So lets clean this up a bit with the addition of a ‘finally’ to our try catch.
public DataSet Load(string username)
{
DataSet ds = null;
SqlConnection conn = null;
SqlDataAdapter da = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(CONN_STRING);
cmd = new SqlCommand();
da = new SqlDataAdapter(cmd);
cmd.CommandText = "GetUser";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = username;
conn.Open();
da.Fill(ds, "user");
}
catch (System.Exception ex)
{
throw ex;
}
finally
{
if (conn != null)
{
if(conn.State == System.Data.ConnectionState.Open)
conn.Close();
conn.Dispose();
}
if(cmd != null)
cmd.Dispose();
if(da != null)
da.Dispose();
}
return ds;
}
The advantage of using a finally block here is that whether or not an exception is thrown or is not thrown, the finally block will always be executed. This gives us the perfect place to put any final cleanup and connection code that we must guarantee will be executed, even in the event of an exception.
Note the ‘null’ checks on all objects before calling close and dispose methods on them. We do this because cannot guarantee that the objects have been initialized when they reach the finally block because the code could have thrown an exception very early within the try block before full initialization took place. We also need to place the object declarations outside of the try catch in order for them to be in proper scope for the finally block.
It is great to handle exceptions gracefully and continue application execution but you also need to be aware of what is going on behind the scenes with resources. Overtime with applications throwing many exceptions and many connections being left open, you are likely to see a slight performance hit in your applications, especially with a large user base.
Now this is all good, we now have successful closing of our connections, we have disposing even when exceptions are thrown, but personally I don’t like how long this code has become. Add a SqlDataReader to this code and it grows even more. So what can we do while still ensuring that clean up will happen?
C# offers a great solution called “using”. “Using” provides scope for your resources, and ensures that resources and even connections are both disposed and closed when they reach the end of their life. Underneath the hood a try finally is being wrapped around the objects, and providing the closing and disposing calls for you. Lets see what our example looks like with this new syntax.
public DataSet Load(string username)
{
try
{
using(SqlConnection conn = new SqlConnection(CONN_STRING))
{
conn.Open();
using(SqlCommand cmd = new SqlCommand("spGetUser", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = username;
using(SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds, "users");
}
}
}
}
catch (System.Exception ex)
{
throw ex;
}
return ds;
}
So as you can see our finally clause is now gone. We have also removed the need to declare our Sql objects at the beginning of our function and have moved the stored procedure and connection into the command object’s constructor. To me this provides much nicer code and organizes everything into nice workable chunks.
So in the end its really up to you which method you choose, but if you prefer organized code the latter will be the most likely choice. Remember, the more code you have to write the larger the chance for a bug to occur or a missed closing call.
