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

Question

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

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
– Justin, Commented Aug 12, 2015 at 12:23

Frito · Accepted Answer · 2015-08-12 12:36:48Z

1

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.

answered Aug 12, 2015 at 12:36

Frito

1,4931 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kamran Khan · Accepted Answer · 2015-08-12 13:30:05Z

0

Just try to add these two lines

 try
        {

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

answered Aug 12, 2015 at 13:30

Kamran Khan

1,11111 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related