C# IDisposable Using: Best Practice

Question

I have been wrapping my OracleConnection and OracleCommand objects in USING statements for a while now, however, after running code analyzer I discover that OracleParameter also implements IDisposable. Is the following code correct? Is there a better technique for readability or structure? At first glance it just seems to be cluttered with USING statements:

using (OracleConnection conn = new OracleConnection(connectionstring))
{
    conn.Open();
    using (OracleCommand cmd = new OracleCommand(sql, conn))
    {
        cmd.BindByName = true;

        using (OracleParameter param1 = new OracleParameter("p1", OracleDbType.Int32, System.Data.ParameterDirection.Input))
        {
            param1.Value = int.Parse(value1);
            cmd.Parameters.Add(param1);
        }

        using (OracleParameter param2 = new OracleParameter("p2", OracleDbType.Varchar2, System.Data.ParameterDirection.Input))
        {
            param2.Value = value2;
            cmd.Parameters.Add(param2);
        }

        using (OracleDataReader dr = cmd.ExecuteReader())
        {
            // loop data here...
        }
    }
}

Answer 1

You want to dispose of the parameters only at the very end of their use, including during the query (and possibly the reading of the results):

using (OracleConnection conn = new OracleConnection(connectionstring))
{
    conn.Open();
    using (OracleCommand cmd = new OracleCommand(sql, conn))
    {
        cmd.BindByName = true;

        using (OracleParameter param1 = new OracleParameter("p1", OracleDbType.Int32, System.Data.ParameterDirection.Input))
        using (OracleParameter param2 = new OracleParameter("p2", OracleDbType.Varchar2, System.Data.ParameterDirection.Input))
        {
            param1.Value = int.Parse(value1);
            cmd.Parameters.Add(param1);
            param2.Value = value2;
            cmd.Parameters.Add(param2);

            using (OracleDataReader dr = cmd.ExecuteReader())
            {
                // loop data here...
            }
        }
    }
}

Notice that you can put multiple using statements in a row. This is because, like the if statement,

1. A using statement is considered a simple statement (even with a block); and
2. A using statement can take either a block or a statement underneath.

Answer 2

using (OracleConnection conn = new OracleConnection(connectionstring)) 
using (OracleCommand cmd = new OracleCommand(sql, conn)) 
using (OracleParameter param1 = new OracleParameter("p1", OracleDbType.Int32,
       System.Data.ParameterDirection.Input)) 
using (OracleParameter param2 = new OracleParameter("p2", OracleDbType.Varchar2,
       System.Data.ParameterDirection.Input)) 
}
    conn.Open(); 
    cmd.BindByName = true; 

    param1.Value = int.Parse(value1); 
    cmd.Parameters.Add(param1); 

    param2.Value = value2; 
    cmd.Parameters.Add(param2); 

    using (OracleDataReader dr = cmd.ExecuteReader()) 
    { 
        // loop data here... 
    } 
} 

Answer 3

I'm not sure that will work properly. Consider that at the end of the using, both parameters should have been disposed. The fact that your cmd.Parameters object is still holding a reference to them does not preclude what may be happening in the OracleParameter Dispose method. For all intensive purposes, the developer of that particular object may be clearing fields that your OracleCommand is expecting to be filled.

There's some danger there. If you're absolutely certain you want to dispose your OracleParameters properly, I suggest you dispose of them after the OracleDataReader using.

Remember that generally you call Dispose when you're done using the object. You're telling it to release all resources it's holding back to the pool. If you're not done using an object, don't dispose of it prematurely.