Solved

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

Posted on 2010-11-16
5
998 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
To properly understand GitHub, let’s divide it into two words ‘Git’ and ‘Hub’. Git is basically a ‘Distribution Version Control’ (DVC) and ‘Source Code Management’ (SCM) system widely used by software programmers while Hub means the efficient centre…
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 video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

19 Experts available now in Live!

Get 1:1 Help Now