none
How to manually commit a transaction after WCF-SQL or WCF-OracleDb send port operation? RRS feed

  • Question

  • Hi,

    I have been repeatedly facing following scenario,

    First operation : Insert records into Oracle database using WCF-OracleDb  or insert records into SQL Server database using WCF-SQL adapter

    Second operation: Invoke a web service (asmx or WCF) and retrieve a process status flag as true or false in the response

    If first operation fails, I can catch exception and will not invoke second operation.

    Where as if first operation is successfully processed, then I need to commit it only when second operation returns success and roll back if second operation fails or returns false in the process flag of the response message(so this needs to be determined by manual inspection of response message).

    Is there any way to manually commit or rollback the transaction after an operation is performed using WCF-SQL or WCF-OracleDb adapter?

    (In the past, I have created a separate WCF service to deal with similar kind of scenario. I constucted a data contract in this service which contains both WCF-OracleDb input message(first op) and web service input message(second op). I constructed this aggregated message in BizTalk by applying map on source message and passed this on to WCF service. Then in the WCF service, I manually initiated a transaction using TransScope and committed/rolled back depending on web service response. I am wondering, if I can manually commit or roll back transaction inside BizTalk orchestration after invoking operation on Send Port.)

    I appreciate any inputs on this.

    TIA.


    Please mark it as answer by clicking on "Propose As Answer", if it helps. My Blog : http://dotnetizen.blogspot.com
    Tuesday, June 14, 2011 2:56 AM

Answers

  • as soon as the send port response comes back, the message transaction spanning that send port is comitted.

     

    I'm not sure if it would work, but maybe (just a thought) you can create a custom receive pipeline that would call the web service to figure out if the transaction was successful or not. if successful, you could forward the message, and if not, you could generate an error that you could catch in your orchestration..


    -- Please mark as answered if this answers your question.
    • Marked as answer by Rajesh Kolla Wednesday, June 15, 2011 12:03 PM
    Wednesday, June 15, 2011 4:14 AM

All replies

  • Hi Rajesh,

    In BizTalk, a transaction cannot span two send ports. I think what you implemented is a very neat way to do this. With transactions spanning multiple ports, there would be a chance of duplicate messages or lost messages. In Oracle adapter we have the concept of Composite Operation, that allows executing multiple Oracle operations in the same transaction.

    Thanks,

    Manas

     


    -- Please mark as answered if this answers your question.
    Tuesday, June 14, 2011 5:38 AM
  • Hi Manas,

    Thanks for your response. I did use Composite operation, when I had to do multiple operations on same database(server). I do agree with you regarding the issues mentioned about transactions spanning across multiple send ports. That is why, I am not planning to enlist second operation in transaction and just wondering if there is any way to manually catch hold of the transaction initiated in the first operation by WCF-OracleDd send port and then commit/roll back later inside the orchestration.

    TIA.


    Please mark it as answer by clicking on "Propose As Answer", if it helps. My Blog : http://dotnetizen.blogspot.com
    Tuesday, June 14, 2011 12:41 PM
  • as soon as the send port response comes back, the message transaction spanning that send port is comitted.

     

    I'm not sure if it would work, but maybe (just a thought) you can create a custom receive pipeline that would call the web service to figure out if the transaction was successful or not. if successful, you could forward the message, and if not, you could generate an error that you could catch in your orchestration..


    -- Please mark as answered if this answers your question.
    • Marked as answer by Rajesh Kolla Wednesday, June 15, 2011 12:03 PM
    Wednesday, June 15, 2011 4:14 AM
  • I think, doing the web service call inside the receive pipeline would be similar to doing web service call as first operation and database operation as second operation. The problem with this is, if database operation fails then there will be not be any way to roll back the web service actions unless target system implements undo functionality in their service.

    I will probably choose the separate WCF service path as I have already done it once and it has been working fine so far.

    I am hoping to see transaction support across multiple send ports in future BizTalk Server version.

     

    Thanks for your continued support, Manas.

     


    Please mark it as answer by clicking on "Propose As Answer", if it helps. My Blog : http://dotnetizen.blogspot.com
    Wednesday, June 15, 2011 12:03 PM