How do I close an OracleConnection in .NET

Question

Say I have these two objects:

OracleConnection connection = new OracleConnection(connectionString);  
OracleCommand command = new OracleCommand(sql, connection);

To close the connection or Oracle, do I have to call command.Dispose(), connection.Dispose(), or both?

Is this good enough:

using(connection)  
{
    OracleDataReader reader = cmd.ExecuteReader();
    // whatever...
}

Answer 1

This is good enough. using statement will wrap the dispose statement, so even if the exception is thrown, you are safe, it's my preferred way to dispose the resource.

using(OracleConnection connection = new OracleConnection(connectionString);    )  
{
   //Create a command object 
    using(OracleCommand command = new OracleCommand(sql, connection))
    {
      using(OracleDataReader reader = cmd.ExecuteReader())
      {
      }

    }
    // whatever...
}

I think by use "using", you are ask the compiler to inject a try ... finally block , and in finally block, it will close the disposable object for you.

Answer 2

using (OracleConnection connection = new OracleConnection(connectionString))
{
    using (OracleCommand command = new OracleCommand(sql, connection))
    {
        using (OracleDataReader reader = cmd.ExecuteReader())
        {
        }
    }
}

If it implements IDisposable, and if you create it, then put it in a using block.

Answer 3

using will ensure your connection is closed. You could also pass in CommandBehavior.CloseConnection to your command's ExecuteReader method to close it before Dispose is called.

Answer 4

Both answers are pretty much on target. You always want to call .Dispose() on any IDisposeable object. By wrapping in a "using" you tall the compiler to always impliment a try/finialy block for you.

1 point of note, if you want to avoid the nesting, you can write the same code like this:

 using (OracleConnection connection = new OracleConnection(connectionString))
 using (OracleCommand command = new OracleCommand(sql, connection))
 using (OracleDataReader reader = cmd.ExecuteReader())
    {
        // do something here
    }

Answer 5

Aaron,

Wouldn't CommandBehavior.CloseConnection on ExecuteReader result in a premature termination of the connection? In the example above, perhaps not, but it is fairly common to have multiple statements processed by a single connection. I guess my point is, why not just let the using ( OracleConnection ...) take care of closing it? Is there another reason that I'm missing to apply that CommandBehavior to ExecuteReader?