Solved

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

Posted on 2010-11-16
5
1,005 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 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

Put Machine Learning to Work--Protect Your Clients

Machine learning means Smarter Cybersecurity™ Solutions.
As technology continues to advance, managing and analyzing massive data sets just can’t be accomplished by humans alone. It requires huge amounts of memory and storage, as well as the high-speed power of the cloud.

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…
New Relic recently released its Synthetics product that allows for the creation of performance monitors that periodically test a site's performance. If you wish to test an interactive workflow New Relic employs Selenium WebDriverJS to run those test…
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 this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

691 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