How to execute two command.ExecuteNonQuery() methods using single connection?

Question

Hi,

I am trying to invoke ExecuteNonQuery() method of two different objects of OracleCommand class. Both the objects use same connection object but have different commandText and parameters. I am using ODP.net, C# (.net 2.0 framework) and Oracle 10g.

Code snippet is as follows :

// OpenDatabaseConnection() methods checks and opens database connection
bool connectionOpened = OpenDatabaseConnection();
if (connectionOpened)
{
    command.ExecuteNonQuery();
    commitCommand.ExecuteNonQuery();
}

Before executing above two statements, I am checking whether connection is open or not.If its not open, then I am opening connection. My problem is, after command.ExecuteNonQuery(); gets executed, connection gets closed and I get exception of 'Connection must be open to perform this operation' when control tries to execute second statement. Why does connection gets close automatically after performing ExecuteNonQuery() method?

Can anyone please tell me how to tackle this situation? In second command object, I am just trying to commit the changes, nothing else. How to commit changes without using transactions?

Thanks in Advance

**

EDIT

** Just wanted to know, what is the best practice for opening and closing the connection? Shall we open connection at each ExecuteNonQuery(), ExecuteScalar(),etc. methods and close connectio as long as are done or open connection at application startup and keep the connection open until application ends? Please enlighten !!

Answer 1

Create both commands from one Connection.

command = connection.CreateCommand();
commitCommand = connection.CreateCommand();

command.Connection.Open();
command.Execute...
command.Connection.Close();

commitCommand.Connection.Open();
commitCommand.Execute...
commitCommand.Connection.Close();

I hope it was useful!

Answer 2

How to commit changes without using transactions?

This doesn't make any sense. If you're not explicitly using a transaction, changes are committed automatically.

Answer 3

What is your commit command? Is that just to commit the work? If so you would not need to do so as a transaction would be implicitly created and committed on running the first query whether you like it or not.

If both queries need to be run and committed as a whole then it sounds like you want to might want to use transactions

using(var connection = new OracleConnection(connectionString))
{
    var firstCommand = new OracleCommand(firstCommandString);
    var secondCommand = new OracleCommand(secondCommandString);

    var transaction = connection.BeginTransaction("SampleTransaction");

    firstCommand.Connection = connection;
    firstCommand.Transaction = transaction;

    secondCommand.Connection = connection;
    secondCommand.Transaction = transaction;

    firstCommand.ExecuteNonQuery();
    secondCommand.ExecuteNonQuery();

    transaction.Commit();
}