Multiple database commands in one OracleConnection

Question

Say I have the following code:

Dim sqlText as String = "MERGE INTO..."

 Using scope As New TransactionScope()
    Using conn As New OracleConnection(connString)
        conn.Open()
        Using oCommand As New OracleCommand(sqlText)
            oCommand.BindByName = True
            oCommand.Parameters.Add("param1",OracleDbType.Raw,value1,ParameterDirection.Input)
            oCommand.Parameters.Add("param2",OracleDbType.IntervalDS,value2,ParameterDirection.Input)
            // etc.
            oCommand.Connection = conn
            oCommand.Prepare()
            oCommand.CommandType = CommandType.Text
            oCommand.ExecuteNonQuery()
        End Using
    End Using
End Using

If I wanted to do multiple MERGE INTO...'s with different parameters (e.g. I'm putting a list of objects into the database) but the same code, where would be the best place to do that? Do I put the loop inside the "Using conn...", or outside?

e.g.:

Dim items as List(Of ItemsToPutIntoDatabase) = ...
Using scope as New TransactionScope()
    For Each item
        Using conn...

or

Dim items as List(Of ItemsToPutIntoDatabase) = ...
Using scope as New TransactionScope()
    Using conn...
        For Each item

When I put it outside (the second one), I get an error:

The Promote method returned an invalid value for the distributed transaction.

---Edit---

OK, so I found using TransactionScope : System.Transactions.TransactionAbortedException: The transaction has aborted, which answers the part about the error (multiple connections inside one TransactionScope cause the problem) - so I have to do each command as a separate transaction? Or, only connect once and do everything inside that.

Can someone help me to understand how to do multiple writes using the same OracleConnection object and SQL, but different parameters?

The MERGE statement execution(s) should be inside the Using conn... as otherwise you wouldn't have a database connection to work with. Best of luck. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Nov 12, 2015 at 12:33
Is the error you are receiving an ORA- error? If so please provide the error number and the full error message. — Christian Shay
– Christian Shay, Commented Nov 12, 2015 at 14:14
stackoverflow.com/questions/7950054/… answers the error part (I'll update the question) — simonalexander2005
– simonalexander2005, Commented Nov 12, 2015 at 14:36

Steve · Accepted Answer · 2015-11-12 16:56:18Z

1

When I do this type of thing, where I want all the commands in the same transaction, my loop would actually be inside the Using oCommand block.

Setup your command once, (ie, Timeout, CommandType, Connection, etc) and then your loop will set the parameters and execute repeatedly.

answered Nov 12, 2015 at 16:56

Steve

5,5553 gold badges20 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

Multiple database commands in one OracleConnection

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related