Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-16
5
Medium Priority
?
1,007 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

When ransomware hits your clients, what do you do?

MSPs: Endpoint security isn’t enough to prevent ransomware.
As the impact and severity of crypto ransomware attacks has grown, Webroot has fought back, not just by building a next-gen endpoint solution capable of preventing ransomware attacks but also by being a thought leader.

Question has a verified solution.

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

The task of choosing a web design company to build a website for your business should never be taken in a light manner. Provided the fact that your website will act as a representative to your business and will be responsible for imposing an online …
Online collaboration is quickly becoming embedded in the workplace, and its benefits are tangible. See what the current landscape looks like and what the future holds for collaboration tools and the future of work.
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 lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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