Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2010-11-16
5
Medium Priority
?
1,010 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 1000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

608 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