Maintiaining SQL Transactions over web service calls in .Net

Question

How do I perform a database transaction where I create a new record then make a call to a web service using the new record ID returned from the database which will also manipulate the same database? I could obviously update all tables directly from the same SQLConnection object but the logic within the web service call is subject to change and I really need to ensure that the changes my code makes are committed alongside those changes introduced by the called web service.

Answer 1

If the web service has the responsibility to manipulate the database, I would extend the web service to perform the actions that your code is doing. The point of using the web service is (ought to be) to separate your code from having to deal with the database directly. Introducing direct DB manipulation into your code is just going to make things much more convoluted and harder to maintain.

Answer 2

This isn't something that you want in an actual transaction. Transactions should be as short as possible, which means no user interaction in the middle of it or in this case waiting for a web service to respond.

As to your problem,if you have control of the web service then change the web service to make a stored procedure call that performs the whole transaction. If there are actions that MUST be completed every time the service is called then they should be part of the service.

My guess is that you don't have control over the web service though, or this wouldn't be an issue. How is the web service accessing the database? If it's using a stored procedure already then you should be able to add the necessary logic in there. Again, I'm guessing that's not the case or it wouldn't have been a problem. My suggestion is that you try to change the web service to fit that architecture if at all possible.

Assuming that the web service accesses tables directly (yuck), you will need to put code into your calling code that does the insert, calls the web service, then checks to see if the web service did what it was supposed to do, and if it didn't then your code takes the necessary actions to undo everything. It's basically like a home-grown transaction engine. It's also very messy and tends to be bug-prone and difficult to maintain. Not to mention, it's not 100% full proof.