How to use sql transactions across asp.net c# web services

0
votes

I use three different web service methods to insert data into three different tables. At first I am calling method1 to insert data into table1. Method1 returns primary key of table1. Then I pass primary key of table to method2 and method3. Both methods insert data into table2 and table3 correspondingly. Suppose if an exception occurs while inserting data into table3 I want to rollback data inserted in table1 and table2. I call the web service methods one after the another. I am using asmx web-services. I searched a lot to find solution but all I managed to find is rollback example in single method call. Is it possible to do sql transactions across c# web services. Please help me to fix this issue. Thanks in advance

2
c#sql-serverweb-servicestransactions
It is possible - its called distributed transactions, but may be easier just to call a single web service method which atomically modifies all 3 tables in a single SQL transaction. - Justin

2 Answers

1
votes

I really can not say for certain without seeing the application you are working with but I feel your problem may have exposed a design flaw in your system. SQL Server Distributed Transactions or WCF Transactions may assist you in getting something working I really feel Justin's comment about having a single web service call that modifies all tables involved in the best solution. This enables your business logic to handle the transaction atomically.

0
votes

Just try to add these two lines 

 try
        {

            transaction.Commit();
        }
        catch (SqlException ex)
        {
            transaction.Rollback();
        }