C#/SQL: Execute Multiple Insert/Update in ONE Transaction

Question

I have a customer entity with 10 Properties.

• 7 of those properties are saved in the customer table.
• 3 of those properties are saved in the test table.

The 3 properties in test table are CustomerId, Label, Text.

When I query these 3 properties I get 3 dataset like this:

CustomerId | Label  | Text
1005       | blubb  | What a day
1006       | hello  | Sun is shining
0007       |        |

When I save them I have to call my stored procedure 3 times on the test table

In my SP I check wether the dataset with the specific customerId AND Label already exists then I do an UPDATE else an INSERT.

How would you call the stored procedure 3 times with all CommandText, CommandType, ExecuteNonQuery etc stuff ?

Answer 1

The easiest way : use the TransactionScope class.

Simply put the call into a block like :

using(TransactionScope ts = new TransactionScope()){

    using(SqlConnection conn = new SqlConnection(myconnstring)
    {
        conn.Open();
... do the call to sproc

        ts.Complete();
        conn.Close();
    }
}

[Edit] I also added the SqlConnection, because I'm very fan of this pattern. The using keyword ensure the connection is closed and the transcation rollback if something wrong happened