Multiple OracleCommands per OracleConnection

Question

I am opening a connection to a database in which I need to issue multiple deletes before closing the connection. I have the following code but it looks strange with all the using statements. Is there a better way to do this? Is this the correct way to use the using statement with connections/commands?

using(OracleConnection oracleConnection = new OracleConnection(connectionString))
{
    string table1Command= "DELETE FROM TABLE1...";
    using(OracleCommand oracleCommand = new OracleCommand(table1Command, oracleConnection))
    {
        oracleCommand.ExecuteNonQuery();
    }

    string table2Command= "DELETE FROM TABLE2...";
    using(OracleCommand oracleCommand = new OracleCommand(table2Command, oracleConnection))
    {
        oracleCommand.ExecuteNonQuery();
    }
}

Answer 1

You do the same steps twice... so why don't just make a function?

Answer 2

Write a function:

void InvokeCommand (OracleConnection oracleConnection, string tableCommand) 
{
     using(OracleCommand oracleCommand = new OracleCommand(tableCommand, oracleConnection)) 
     { 
         oracleCommand.ExecuteNonQuery(); 
     } 
}

Answer 3

You are using the using statement correctly. Both OracleConnection and OracleCommand should be Disposed of when you are finished with them, and the using statement is the clearest way of doing this within the bounds of a method.

There are some subtleties regarding how ConnectionPooling is configured, but I think that's beyond the scope of your question.

---

* Note that the OracleConnection class is not Thread-Safe, meaning that if you try to re-use OracleConnection instances then you have to make sure multiple threads don't use the same instance at the same time.