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?

Answer 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.