AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1260&pId=-1
Updating Tables Using a Single Updategram Message in BizTalk Server 2004 and 2006
page
by Naveen Karamchetti
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 35442/ 57

Overview

The SQL Transport Schema Generation Wizard in BizTalk Server 2004/2006 does not permit the selection of multiple tables for updating when generating a schema for an Updategram.

In this article we shall manually create an Updategram that will update multiple tables (master and detail).

What is an Updategram?

An Updategram is a data structure that is used to specify a change in the data in the database tables. INSERT, UPDATE, and DELETE commands are represented in an Updategram by the difference of the image of the data before and the image of the data after a change.

One way to run an XML Updategram is to save the Updategram as a file and then run the file in the URL as a template file.

Sample Updategram

Listing 1 – Sample Updategram

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
   <updg:sync>
      <updg:before>
         <City updg:id="1" Name="HYD"/>
         <City updg:id="2" Name="BGL"/>
      </updg:before>
      <updg:after>
         <City updg:id="1" Name="Hyderabad"/>
         <City updg:id="2" Name="Bangalore"/>
      </updg:after>
   </updg:sync>
</ROOT>

NOTE: All updategram/diffgrams nodes with siblings must have ids, either user specified ones or mapping schema based key field id.

Problem to be solved!

1.    Using the SQL Transport Schema Generation Wizard in BizTalk Server only one table can be selected within a datagram. The schema generated would have only one table in the datagram. We are trying to modify the schema in such a way so as to insert multiple rows in a master and detail fashion.

2.    The master table would generate a key value for every row inserted and the detail row/rows would use the same key generated by the master table to insert rows in the detail table.

NOTE: Any promoted property which would be the same across all the messages can also be used as the correlation type property.

Scenario: Book Store Demo

Book Store Database tables

A Book Store would have the following tables.

1.    Publisher

2.    Books

Listing 2 – SQL Script for creating database and tables

CREATE DATABASE BookStore
GO
USE BookStore
GO
 
CREATE TABLE Publisher
           (
            PublisherID int IDENTITY(1, 1) NOT NULL 
             constraint PK_PUBLISHER_ID primary key,
            PublisherName nvarchar(100) NULL
           )
 
CREATE TABLE Book
           (
            ISBN varchar(10) NOT NULL,
            PublisherID int NOT NULL references Publisher(PublisherID),
            BookTitle varchar(100),
            authorName varchar(100)
          )
Creating Updategram

Observe the following XML structure.

Listing 3 – Updategram XML structure

We would need to generate a schema in such a way so as to map the XML structure shown in Listing 3. Notice the attribute at-identity. This denotes that the value for the identity column which gets generated (when the Publisher row is inserted) shall be picked up by the Book; notice the attribute PublisherID in the Book element.

The value of the at-identity and the PublisherID shall be "NewID."

There are two different namespaces referred to in the above XML document, one is the "ns0" namespace and another one is the "ns1" namespace for the updategram.


Creating Schema for the "at-identity" attribute

Listing 4 – Root schema structure for the attribute 'at-identity'

 

Observe the schema in Listing 4, the target namespace for the attribute is the updategram.

Figure 1 – The completed schema structure for the updategram

This schema references the attribute schema created in Listing 4.

The schema in Figure 1 would generate the XML structure specified in Listing 3.

Mapping to the datagram

This map is used to map the incoming messages to the updategram schema. In this case the book and publisher messages are mapped to the updategram schema.

Figure 2 – Mapping messages to datagram schema

Orchestration

The following is the high level overview of the orchestration.

1.    There are two incoming messages, Book and Publisher. Since a Parallel shape is used, both messages would need to arrive to process and complete the Orchestration.

2.    An updategram is created by mapping the Book and Publisher messages.

3.    The Map is called in the Transform shape.

4.    Notice that the send shape is used to transmit the updategram XML to the SQL Send port.

5.    Observe the database tables and you would notice that two rows would have been inserted in the tables (one in each table).

Figure 3 – Book Store Orchestration

 

 

Quick Takeaways

·         The updategram schema can have several tables listed.

·         The namespace of the at-identity attribute is different from that of the schema and another schema is used for the at-identity attribute.

·         The Sent port in this case is a one-way port instead of a two-way port. A two-way solicit-response port can also be used for the datagram schema.

Summary

In this article we have seen how to manually create an Updategram that will update multiple master and detail tables.


Product Spotlight
Product Spotlight 

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 4:16:03 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search