C # / SQL: perform multiple insert / update in ONE transaction

Question

I have a client object with 10 properties.

• 7 of these properties are stored in the customer table.
• 3 of these properties are stored in the test table.

3 properties in the test table: CustomerId, Label, Text.

When I request these 3 properties, I get 3 datasets as follows:

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 in the test table

In my SP, I verify that the dataset is with the specific identifier customerId. And already exists, then I do UPDATE else INSERT.

How could you call the stored procedure 3 times with all the commands CommandText, CommandType, ExecuteNonQuery, etc.

Answer 1

: TransactionScope.

, :

using(TransactionScope ts = new TransactionScope()){

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

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

[Edit] SqlConnection, . using , - .

Answer 2

, SqlTransaction, ExecuteNonQuery, , :

• XML XML; XML (SQL- ) sproc 3
• ", ", - ,
• (3000, 3), SqlBulkCopy , sproc, .

, " " - .

Answer 3

, DbTransaction. SqlTransaction .

Answer 4

. - TransactionScope ADO.NET, BEGIN COMMIT/ROLLBACK . , , , ( , ).

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