Using statement question

Question

I have two questions.

1) Should you always use a using statement on a connection? So, I would use it on the connection and then another one on a reader within the connection? So I would be using two using statements.

2) Lets say you use the using statement on the connection and also a reader being returned on the connection. So you have two using statements. Does it create two Try{}Finally{} blocks or just one?

Thanks!

Answer 1

Be careful here. You should always have a using statement on any local object that implements IDisposable. That includes not only connections and readers, but also the command. But it can be tricky sometimes exactly where that using statement goes. If you're not careful it can cause problems. For example, in the code that follows the using statement will close your reader before you ever get to use it:

DataReader MyQuery()
{
    string sql="some query";
    using (var cn = new SqlConnection("connection string"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            return rdr;
        }
    }
}

Instead, you have four options. One is to wait to create the using block until you call the function:

DataReader MyQuery()
{
    string sql="some query";
    using (var cn = new SqlConnection("connection string"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cn.Open();
        return cmd.ExecuteReader();
    }
}

using (var rdr = MyQuery())
{
    while (rdr.Read())
    {
        //...
    }
}

Of course, you still have to careful with your connection there and it means remember to write a using block everywhere you use the function.

Option two is just process the query results in the method itself, but that breaks separation of your data layer from the rest of the program. A third option is for your MyQuery() function to accept an argument of type Action that you can call inside the while (rdr.Read()) loop, but that's just awkward.

I generally prefer option four: turn the data reader into an IEnumerable, like this:

IEnumerable<IDataRecord> MyQuery()
{
    string sql="some query";
    using (var cn = new SqlConnection("connection string"))
    using (var cmd = new SqlCommand(sql, cn))
    {
        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
              yield return rdr;
        }
    }
}

Now everything will be closed correctly, and the code that handles it is all in one place. You also get a nice bonus: your query results will work well with any of the linq operators.

Finally, something new I'm playing with for the next time I get to build a completely new project that combines the IEnumerable with passing in a delegate argument:

//part of the data layer
private static IEnumerable<IDataRecord> Retrieve(string sql, Action<SqlParameterCollection> addParameters)
{
    //DL.ConnectionString is a private static property in the data layer
    // depending on the project needs, it can be implementing to read from a config file or elsewhere
    using (var cn = new SqlConnection(DL.ConnectionString))
    using (var cmd = new SqlCommand(sql, cn))
    {
        addParameters(cmd.Parameters);

        cn.Open();
        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
              yield return rdr;
        }
    }
}

And then I'll use it within the data layer like this:

public IEnumerable<IDataRecord> GetFooChildrenByParentID(int ParentID)
{
    //I could easily use a stored procedure name instead, and provide overloads for commandtypes.
    return Retrieve(
        "SELECT c.* 
         FROM [ParentTable] p 
         INNER JOIN [ChildTable] c ON c.ParentID = f.ID 
         WHERE f.ID= @ParentID", p => 
       {
          p.Add("@ParentID", SqlDbType.Int).Value = ParentID;
       }
     );
}

Answer 2

1) Should you always use a using statement on a connection? So, I would use it on the connection and then another one on a reader within the connection? So I would be using two using statements.

Yes, because they implement IDisposable. And don't forget a using statement on the command too :

using (DbConnection connection = GetConnection())
using (DbCommand command = connection.CreateCommand())
{
    command.CommandText = "SELECT FOO, BAR FROM BAZ";
    connection.Open();
    using (DbDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            ....
        }
    }
}

2) Lets say you use the using statement on the connection and also a reader being returned on the connection. So you have two using statements. Does it create two Try{}Finally{} blocks or just one?

Each using statement will create its own try/finally block