Logo
programming4us
programming4us
programming4us
programming4us
Windows XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
 
Windows Server

BizTalk 2010 : ASDK SQL adapter examples (part 4) - Composite Operations

12/2/2012 11:43:56 AM

Example 4 - Composite Operations

The purpose of a Composite Operation is to be able to execute multiple database actions in the context of a single unit of work, or transaction. Even though we are calling multiple actions we expect all actions to either succeed or fail as a group. The idea is that if we need to insert/update multiple tables, we want all inserts/updates to succeed otherwise we want to rollback the action so that our tables remain in sync.

Once again we find ourselves in the Consume Adapter Service wizard. There are two tables of particular interest to us: SpecialOffer and SpecialOfferProduct. The SpecialOfferProduct table links Special Offers and Products together. We want to perform Insert operations against both of these tables.

Once the wizard has been completed, we will discover two schemas of interest to us that model our Database tables: SpecialOffer.xsd and SpecialOfferProduct.xsd:

In order to build a Composite Operation, we need to have a single schema that contains multiple root nodes. Under the first root node, we add all the operation execution request schemas, and under the other we add all the operation response schemas. The only naming convention rule for the root nodes is that the operations response root node has to be the same as the operations root node and suffixed with 'Response'. For this example we have created a schema called CompositeSchema.xsd, and created two root nodes: Request and RequestResponse.

Once we have the root nodes we use the Import property of BizTalk Server schemas to import the two update schemas into the composite one:

With our schemas imported, we need to ensure that all parent nodes have the correct Data Structure Type specified. As you can see in the following image, we have two Insert nodes underneath the Request root node. Within each of these Insert nodes we provide the Data Structure type for our SpecialOffer request and SpecialOfferProduct request. Similarly, we do the same for RequestResponse nodes:

Here is our map that will demonstrate taking data from a single request and splitting the data across our multiple request nodes in our composite schema. When this is run through BizTalk, we will insert data into both our SpecialOffer and SpecialOfferProduct tables as a single transaction:

On the solicit response send port that we created for the composite operation we need to use a special Action name called CompositeOperation:

Another adapter property to be aware of is on the Binding tab and is called useAmbientTransaction, which should be set to True. In conjunction with providing CompositeOperation in our SOAP Action header, this property instructs the adapter that multiple operations will be executed and these operations have to be executed as a single transaction.

By using a composite operation against the two table updates we have the following benefits:

  • The same transaction is used for both operations. This means if there is an exception both are rolled back. This is important for our solution since we want both updates to be executed together, and if one of them fails both get rolled back.

  • The same database connection is used. The adapter will use the same connection from the connection pool for all the update, insert and delete statements in the composite operation, minimizing the risk of running out of connections in the pool.

Now with our composite schema in place and our map to transform our incoming request to the composite message, we can build out the rest of our solution:

With our application deployed we can initialize this process by copying the …\ Chapter02\Example4\SpecialOffer.xml file and pasting it into the Chapter02\Example4\FileDrop folder.

If we examine our SpecialOffer and SpecialOfferProduct tables we will discover that we have the SpecialOfferID of 182 (will likely be different on your system since the first scripting functoid in the Special_Offer_To_Composite map generates a random number for SpecialOfferID) in both of these tables with a similar timestamp. These records were written to this database as a single transaction. As expected, there were no issues when we ran this composite operation so there is no data being passed back in the SQL Server response.

Other -----------------
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Sending email using Outlook
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Exporting data to Microsoft Project
- Microsoft Content Management Server : The ASP.NET Stager Application (part 3) - Staging Attachments
- Microsoft Content Management Server : The ASP.NET Stager Application (part 2) - Staging Channels and Postings
- Microsoft Content Management Server : The ASP.NET Stager Application (part 1) - The DotNetSiteStager Project, Recording Messages to a Log File
- Microsoft Content Management Server : Staging Static Pages - Site Stager in Brief
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 2)
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 1)
- Windows Server 2008 Server Core : Renaming a File with the Ren and Rename Commands, Sorting File Content with the Sort Utility
- Windows Server 2008 Server Core : Moving Files and Renaming Files and Directories with the Move Command, Recovering Lost Files with the Recover Utility
 
 
Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
 
programming4us
Girls
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone