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

Question

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 !!

Are you creating the command in a using statement? I suspect that could be the reason. — Aliostad
– Aliostad, Commented Sep 8, 2010 at 11:54

Winston Smith · Accepted Answer · 2010-09-08 11:53:07Z

4

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.

answered Sep 8, 2010 at 11:53

Winston Smith

22k10 gold badges63 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ian Johnson · Accepted Answer · 2010-09-08 11:56:20Z

1

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();
}

answered Sep 8, 2010 at 11:56

Ian Johnson

2,2441 gold badge13 silver badges9 bronze badges

4 Comments

Shekhar Over a year ago

Thanks. Actually I tried to use transaction but I was getting problem in commit method. So I thought of commiting updates using another command object.

Shekhar Over a year ago

if connection object is null because of some exception or something, then shouldnt we check the connection object first and then use it?

Ian Johnson Over a year ago

I do not believe that the connection object would ever be null "new OracleConnection" will guarantee to return an object or throw an exception. If an exception is raised then the rest of the code will not execute. You may want to wrap the transaction in a try-catch block and rollback the transaction if an error has occurred

Alex Gordon Over a year ago

my first command is to create a temp table. my next command needs to use that temp table. in your example, the temp table disappears. when i did a trace, the trace showed me "exec CreateTableSproc ". how would i get the temp table to be seen by the next command?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related