C# Should I place the Sql/OleDb transaction.Commit() outside of a for loop?

Question

I have the following basic oledb loop:

using (OleDbConnection conn = new OleDbConnection(Con))
{
    using (OleDbCommand cmd = new OleDbCommand())
    {
        for loop 
        {
        cmd.Connection = conn;
        conn.Open();
        transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
        cmd.CommandText = "row by row insert"
        cmd.Some parameters, then ExecuteNonQuery
        transaction.Commit();
        }
    }
}

However, I'm not certain if I should be placing the Commit within the loop after the command execution. Should I place it after all is said and done? Outlining the using would be a try catch containing a rollback in the catch. So I'm guessing if I want to be able to rollback all of my changes, I will want the loop to run completely, and then commit it?

I also just learned about TransactionScopes which outline the SQL context in a using, so I'd like to know how it applies to that as well.

Answer 1

You cannot move the Commit() call outside the loop when the BeginTransaction() call is inside the loop. Either both will stay in the loop, or both will go outside the loop.

So, your question should be: "should I perform one transaction per iteration of my loop, or should I start a transaction, do my loop, and then commit the transaction?"

Well, the only reason for doing one transaction per iteration would be the very unlikely scenario where you would want all non-failing insertions to be committed even if some of them fail. (In which case you would also need to do some exception handling.)

If you do not have such a requirement, and if you would not mind an order-of-magnitude performance improvement, then you better start a transaction, do your loop, then close your transaction.