Share via

SQL Updategrams (Insert/Update/Delete) in BizTalk: Part II

  • Article

Yesterday (see Part I) we looked at the first part of a BizTalk and SQL Server updategram project. The process will enable a single updategram to process inserts, updates AND deletes in one transaction. We've already set up a base project that contained all of the ports and message parts needed to use an insert/update/delete updategram for a particular table. Today's piece looks at how to get the whole BizTalk project working.

The very first thing we do here is make a change to our updategram schema in order to support multiple record updates. I use the standard SQL updategram updg:id field as my unique identifier. This is what helps SQL Server resolve which before block coincides with which after block. Please see here for some very nice instructions on how to get a base updategram required field into your updategram schema. Notice the element updg:id identified in my BizTalk-generated CustomerUpdate schema here.

Now add a new BizTalk project to the solution. Add a reference to the "base" project created previously. In this new project we add a schema that represents the "customers" that we are interacting with. In the picture below, you can see that I have a repeating Customer node that contains a DatabaseAction element which identifies whether or not that customer is to be inserted, updated or deleted in the database.

The next big piece of the equation is the XSLT map that builds the actual updategram message. On the left side of the map is our recently created "customer" schema. On the right, we use the "update" edition of the updategram schema in the referenced project. Why the "update" one? If you're not familiar with updategrams, the key concept is that you have a before and after section in the updategram message that SQL Server uses to figure out what to do with the data. For instance, having a record in the before but not in the after is equal to a Delete operation in SQL Server. Also, an after record with no before equals an Insert and a record in both the before and after results in an Update. So the core goal of this map is to add appropriate sections to the before and after sections to achieve the desired outcome in the database. I created two pages in the map in order to separate the logic. The first page contains the looping functoid with a link from the source customer node and two links into the destination customer nodes in the before and after sections of the updategram.

The second page on my map contains links between the all the source fields, and the corresponding fields in the destination after block. The only field mapped to the destination before block is the customerID since that's all I really need from the original record (in the case of an update operation). Now, the key to the looping functoid creating the correct number of and instances of records lies in the usage of the Logical Equal functoids that I use. In a nutshell, the logic is:

  • If the DatabaseAction equals either Update or Delete, then create the before updategram node. Remember from our conditions above, the update/delete scenario requires a before block.
  • If the Database Action equals either Update or Insert, then create the after updategram node.

When a Looping functoid and Logical Equals functoid both connect to the same destination node, a looping condition is created. So, just because there are 10 customers in my source doesn't mean the Looping functiod will create 10 before or after blocks unless that's what the conditions dictates. Makes sense?

Now we simply build our orchestration that will do the data update. Create 3 orchestration messages: one for the customer schema, one that points to the referenced project's multi-part message UpdateCustomer request schema, and one that points to the referenced project's multi-part message UpdateCustomer response schema. These message parts showed up in the message picker because we previously marked them as public. Then build out the simple process flow, and use the port types from the referenced project to do the send/receive to SQL.

Build and deploy the base project, and then do the same with the primary project. Create the necessary send/receive ports and locations for the primary project and bind it. Note that you do not have to bind or enlist the base orchestrations that hold the shared port types and message parts. Then create an instance of the "customer" schema and populate it with data and the appropriate database action. There you go, an updategram that can do mass operations of all types in a single table. Neato.

If you're interested in any of the source code, lemme know.