Leveraging the "using" keyword in C#

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'stake a look at a typical code block for reading data from a database table and displaying it in a ListBox control using the DataReader.

Listing 1.

using System.Data.SqlClient;
// Assuming connectionString and commandString are defined elsewhere
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();

Now, let'sthink. What is wrong with the code in Listing 1? What if there is an exception on line list Box1.Items.Add? For example, if the reader[0] brings null data, the ToString() method would fail and there will be an exception, and the 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 a 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.

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)
{
    // Handle the exception, e.g., display an error message
    // You can also log the exception for debugging purposes
}
finally
{
    if (reader != null)
        reader.Close(); // Close the reader if it's not null
    if (connection.State != ConnectionState.Closed)
        connection.Close(); // Close the connection if it's open
}

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, the code is much tidy, and under the hood, Listing 3 does what Listing 2 would do for you.

Listing 3.

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());
        }
    }
}