Solved

How to make my Webservice transaction-save (including DB calls)

Posted on 2010-11-16
5
1,000 Views
Last Modified: 2012-05-10
Hi,
I am facing following problem:
I have to implement a .Net Webservice in C#. This Webservice receives 2 Objects (O1, O2).
To save the objects to the DB, i have to call a stored procedure for O1 and O2.

But, if the procedure for O2 fails, the changed which was performed by procedure 1 (for O1) should be "rollbacked".
In other words, I have to scope these two procedures with a transaction.

I have read about the "transaction support for XML Web services" in .Net and the tag "TransactionOption = TransactionOption.RequiresNew". (see: http://www.codeproject.com/KB/XML/TransactionASPNET.aspx)
But it didn't work when I was testing it - O1 was committed to the DB although O2 failed.

Any suggestions what I am doing wrong?
Maybe this "transaction support" does not work when using stored procedures?

Im using .net 3.5 and MSSQL-Server 2008 R2.

thx!

0
Comment
Question by:wenatexx
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Dennis Aries
ID: 34145816
You don't want the transaction in the webservice. In your webservice-call, you send all objects at once.
You want to add a transactional state to your database.

Use
BEGIN TRANSACTION <transactionname>
...
COMMIT TRANSACTION <transactionname>

Open in new window


before/after your calls.

http://www.functionx.com/sqlserver/Lesson22.htm
0
 

Author Comment

by:wenatexx
ID: 34145999
Hi dijaries,
i know what you mean - but this is not a solution for me.

I have several stored procedures which are called from serveral WebService-Methods.

For Example:
SavePerson() -> calls the stored procedures (sp_setperson, sp_setphonenumbers (a person can have more than 1 number, so this SP is called X-times), sp_setaddress)

SaveHousehold() -> calls the stored procedures (sp_setperson, sp_setadress (X-times).

So it's not possible for me to handle the transaction only in the database.
0
 
LVL 8

Expert Comment

by:davesgonebananas
ID: 34150778
Yes, automatic transactions work with stored procedures.  However, if your stored procedure is not raising an error the transaction will autocommit.
0
 

Author Comment

by:wenatexx
ID: 34153465
hi,
i know that stored procedures are working with transactions. but my problem is, that all the SP should be scoped within one transaction.So if SP1 is valid and SP2 fails, SP1 should also do a rollback. For example:
I get an Household object from the webservice with 3 adresses.
I am calling the SP for the household which does not fail. then i am calling 2 times times the procedures for adresses which are valid too. the 3rd call of the adress procedure fails.
If this happens, the other 3 procedures which were called should "rolleback" too.
0
 
LVL 8

Accepted Solution

by:
davesgonebananas earned 250 total points
ID: 34155720
Just wanted to check something.  The article you are using refers to the old .Net 2.0 XML web services with COM+ transactions.  This should not be confused with WCF web services.

If you are using WCF on .Net 3.5 I think you should use WCF transactions (System.Transactions) via the [OperationBehavior(TransactionScopeRequired = true)] attribute.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
These instructions are based on installing Owncloud on your new raspberry pi connected with a usb HDD. What do you need Part A? A Raspberry Pi, model B. A boot SD card for the Raspberry Pi. A usb HDD An Ethernet cable to connect to the lo…
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now