How to execute 3 Commands (SQL statements) to oracle tables at one shot?

Question

4.00/5 (1 vote)

See more:

Greetings,

I'm implementing an application using vb.net 2005 with oracle 10g and I came across a functionality that should Select/Insert/Update/Delete data from three different tables in oracle.

So I`m wondering if there is a way to prepare all the 3 commands (SQL Statements) separately and then execute them in one shot to the oracle, so in this way I can guarantee that all of them are successfully executed or all are failed to execute, and also I could gain more performance which is critical in my case.

Thus I`m looking for a syntax in vb.net that helps me execute more than one OracleCommand in one shot to the oracle.

So please advise...

Posted 23-Apr-12 23:27pm

dgthecodeproject

Add a Solution

2 solutions

Solution 1

It's not possible to combine queries within one OleDbCommand.
If possible, make a stored procedure.

Posted 23-Apr-12 23:37pm

uspatel

Comments

Pete O'Hanlon 24-Apr-12 5:43am

Of course it is possible - and he's using OracleCommand. To use multiple commands in one command, he would need to separate them by semi colons, and wrap them in a begin...end;

dgthecodeproject 24-Apr-12 8:54am

How is that Pete...Can you show me a sample here if possible...

Pete O'Hanlon 24-Apr-12 9:26am

From memory (and typed in this textbox), it would look like this:
Dim sql as New StringBuilder
sql.Append("begin ")
sql.Append("INSERT .....; ")
sql.Append("INSERT .....; ")
sql.APPEND("INSERT .....; ")
sql.Append("end;")

dgthecodeproject 24-Apr-12 9:45am

Thx man..Its a good idea but not in my case...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Accepted Answer · 2012-04-23T23:40:00

Solution 2

What you are actually looking to do is wrap them in a transaction. Fortunately for you, the OracleConnection class provides a BeginTransaction method (this returns an OracleTransaction). Simply call all your commands inside this connection, then call Commit() to save the changes if they execute successfully, or Rollback if one of the statement fails. A sample would look like this:

VB

Public Sub RunCommands(connection As OracleConnectioncommands As List(Of OracleCommand))
    Dim transaction As OracleTransaction
    connection.Open()
    ' Start a local transaction
    transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
    Try
      For Each command As OracleCommand In commands
        command.Connection = connection
        command.ExecuteNonQuery
      Next
      transaction.Commit()
    Catch e As Exception
      transaction.Rollback()
      Throw
    End Try
End Sub

Posted 23-Apr-12 23:40pm

Pete O'Hanlon

Updated 24-Apr-12 2:08am

v2

Comments

dgthecodeproject 24-Apr-12 7:35am

Thank you Pete..

I wonder where the execution method of the command is exist in your sample code...I tried your way but nothing executed to the database....please advise.

Pete O'Hanlon 24-Apr-12 7:47am

Oops, sorry. It goes after you assign the connection to command.Connection. Sorry - I just typed this in the editor, and I was busy concentrating on converting C# code to VB.NET (I'm a C# coder not a VB.NET one).

uspatel 24-Apr-12 8:00am

Yes,This is a good way.......
5+

dgthecodeproject 24-Apr-12 8:56am

Thanks very much Pete for your interest..Now it is running successfully...But do you think this way has better performance than executing each command alone and without using the transaction object??...

Pete O'Hanlon 24-Apr-12 9:21am

You can't really compare the two. As you have stated that you have to guarantee that the database is updated only if all the statements complete, then the transaction is your only option. It will performly slightly worse than issuing the commands outside a transaction because you have the overhead of creating the transaction; but executing the commands outside a transaction will leave your database in an inconsistent state if one of the statements fails.

dgthecodeproject 24-Apr-12 9:44am

Thanks Pete for the great solution...
This workaround provided by you is to manage data in a consistent state leaving the database in good shape.. but the draw back of this solution that its not providing better performance..So it is always like this choose the better way...