The using statement is another awesome tool that helps reduce coding effort and possible issues. An attentive reader added a good comment to my last post on using CommandBehavior.CloseConnection to close your Connection object automatically when your DataReader closes. (Thanks for the lead in Wolfgang!) The using statement will close all of your objects and dispose of them for you. This is one of my personal favorites as it can make development a lot easier. (Any code that helps you clean up after yourself gets my vote.)
Here is a code block without using the using statement that explicitly disposes of its ADO.NET objects. <fieldset><legend>Sans using</legend>
private void Button2_Click(object sender, System.EventArgs e)
{
TextBox1.Text = "";
Label1.Text = "";
string sConnString = "Server=localhost\\sql;Database=Northwind;Integrated Security=True";
string sSql = "SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName";
SqlConnection cn = null;
SqlCommand cmd = null;
SqlDataReader rdr = null;
try
{
cn = new SqlConnection(sConnString);
cmd = new SqlCommand(sSql, cn);
cn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
TextBox1.Text += rdr["CategoryName"].ToString() + Environment.NewLine;
}
}
catch(Exception ex)
{
Label1.Text = ex.Message;
}
finally
{
if ((rdr != null) && (!rdr.IsClosed))
rdr.Close();
if (cmd != null)
cmd.Dispose();
if (cn != null)
cn.Dispose();
}
}
</fieldset>
Notice that this preceding code uses the finally block to clean up after itself. This code can easily be forgotten, mistyped, or in my case badly cut and pasted when you are in a hurry. There is nothing wrong with using the finally block for cleaning up, that is one of its intentions. But there is another way to tackle this clean up effort using the using statement.
The following code sample also cleans up after itself, but it does so using the using statement instead of an explicit call to the close and dispose methods. Again, this is one of my favorite tools in .NET. The using statement handles the disposal and subsequent closing of my objects for me. Even if an exception is thrown, as soon as execution leaves the using block the DIspose method of the object is invoked under the covers. For objects like the SqlConnection, this also closes the connection too. Quite a nice feature!
<fieldset><legend>Using using</legend>
private void Button3_Click(object sender, System.EventArgs e)
{
TextBox1.Text = "";
Label1.Text = "";
string sConnString = "Server=localhost\\sql;Database=Northwind;Integrated Security=True";
string sSql = "SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName";
try
{
using (SqlConnection cn = new SqlConnection(sConnString))
{
using (SqlCommand cmd = new SqlCommand(sSql, cn))
{
cn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
TextBox1.Text += rdr["CategoryName"].ToString() + Environment.NewLine;
}
}
}
}
}
catch(Exception ex)
{
Label1.Text = ex.Message;
}
}
</fieldset>
While either of these 2 code examples will work and clean up after themselves, I prefer to code with the using statement. To me, it is easier to read and it saves me from having to write the cleanup code.
One last note with the using statement that you may run into: Just like a try block, anything declared within the using statement can only be accessed within its block. If you want to refer to it outside of the using statement (or try block), then declare it prior to and outside of them.</li>
For you VB.NET’ers o
ut there, the using statement will be available in VB.NET in the reelase of Visual Studio .NET 2005. The syntax looks to be as follows:
<fieldset><legend>Using in VB.NET</legend>
Using cn As SqlConnection = New SqlConnection(sConnString)
'--- Do something
End Using
</fieldset>
Latest Projects
