The "using" keyword in C# is one of the best friends of programmers but many of us may not realize this. The "using" keyword is used in two cases - First when importing a namespace in your code and second in a code block.
Here I am talking about using the "using" in the code block.
Let's take a look at a typical code block for reading data from a database table and displaying it in a ListBox control using the DataReader. See Listing 1.
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader reader = null;
SqlCommand cmd = new SqlCommand(commandString, connection);
connection.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
listBox1.Items.Add(reader[0].ToString() + ", " + reader[1].ToString());
}
reader.Close();
connection.Close();
Listing 1.
Now, let's think. What is wrong with the code in Listing 1? What if there is an exception on line listBox1.Items.Add? For example, if the reader[0] brings null data, the ToString() method would fail and there will be an exception and code will exit. As we know, if you open a SqlDataReader or SqlConnection, it is advised to close them to free the connections immediately. But in possible scenario of exception, it will not happen. The code lines reader.Close() and connection.Close() will not be executed if an exception occurs.
To make sure to close DataReader and Connection objects, one possible way of doing is use a try..catch..finally block and close DataReader and Connection in finally block. This will ensure that both DataReader and Connection are closed. See Listing 2.
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader reader = null;
try
{
SqlCommand cmd = new SqlCommand(commandString, connection);
connection.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
listBox1.Items.Add(reader[0].ToString() + ", " + reader[1].ToString());
}
}
catch(Exception exp)
{
// Do something with exception like display a message
}
finally
{
reader.Close();
connection.Close();
}
Listing 2.
Alternatively, you may use the "using" keyword that will also ensure that the DataReader and Connection objects are closed before exiting the loop. See Listing 3. As you can see from Listing 3, code is much tidy and under the hood, Listing 3 does what Listing 2 would do for you.
using ( SqlConnection connection = new SqlConnection(connectionString) )
{
SqlCommand cmd = new SqlCommand(commandString, connection);
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
listBox1.Items.Add(reader[0].ToString() + ", " + reader[1].ToString());
}
}
}
Listing 3.