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

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开发者_JS百科 3 times with all CommandText, CommandType, ExecuteNonQuery etc stuff ?

---

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

---

Well, a SqlTransaction spanning three ExecuteNonQuery is the simplest, but some alternatives:

• use the XML datatype to pass all three in as XML; parse the XML (SQL server has functions for this) in the sproc into 3 records
• use a "table valued parameter" to pass them in a single call - note this needs additional definition at the DB to represent the structured data
• if the data volume is huge (3000 rather than 3), SqlBulkCopy into a staging table, then run a sproc to move the data into the real table in one set-based operation

Finally, watch out for the "inner platform effect" - it sounds a bit like a DB inside a DB.

---

There are several classes that inherit from DbTransaction. The documentation for SqlTransaction has sample code.

---

You should encapsulate your INSERTs into a transaction. The bad way to do it is to use a TransactionScope in ADO.NET, the good way is to write a stored procedure and BEGIN and COMMIT/ROLLBACK your transaction inside you proc. You don't want to go back and forth form client to server while maintaing a transaction, because you will hurt concurreny and performance (exclusive locks are hold on the resources inserted until the transaction ends).

BEGIN TRAN BEGIN TRY INSERT INSERT COMMIT TRAN END TRY BEGIN CATCH PRINT ERROR_MESSAGE() -- you can use THROW in SQL Server 2012 to retrhrow the error ROLLBACK END CATCH