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

  • Article

BizTalk natively supports using SQL Server updategrams to do inserts, updates and deletes. There are a few examples online of folks demonstrating ways to insert into multiple tables within a single updategram. What I want to show is how to update one table, but perform inserts, updates and deletes in a single transaction.

The first part of this post is the setup of the project, the second is the stunning conclusion. In Part I, I'll look at a building up the solution using the pattern of reusing an existing BizTalk project.

I start with an empty solution. Create a single "base" project. Right-click the project and add generated items. Here you can select the SQL adapter, and choose how to interact with the adapter. After setting the connection string, and choosing to "send" data to SQL, you have the choice of using a stored procedure or an updategram as the SQL statement to run. Go ahead and choose the Insert updategram operation and select the table you've chosen to insert data into. When you complete the wizard, you automatically get a new schema file and orchestration. The orchestration is empty, but contains the port type for the operation. Now in many cases you're going to do more than a single database operation, so go ahead and run the wizard twice more to build out the update and delete schemas and orchestrations. When you're done, you have 3 schemas and 3 orchestrations generated for you.

TIP: One thing I've noticed when doing this. If you try and build the project at this point, you get a fairly cryptic error that complains about a type name in the project. What happens is that when you run this wizard these 3 times, the Typename property of the first orchestration has the same identifier as the second one built. So, just change the Typename of the first orchestration to something like "Orchestration_0" and it should compile. Typenames have to be unique across the objects, so the Visual Studio compiler chokes on this unexpected naming.

So the idea here is that we have this base project with all the messages and orchestration ports that we need for any project that wants to update the chosen data table. We can reference this base project in another BizTalk project and reuse all the message parts and ports. This type of project inheritance is a great way within projects to define a base set of ports or messages and remove redundancy or error when folks are continually creating the same types repeatedly. what we need to do for this to work is:

  • Open up each Orchestration, expand the Types in the Orchestration View window and change the port type's Type Modifier to Public. This makes each port type accessible in orchestrations that reference this one.
  • Again get into the Types window and dig into the Multi-Part Message Types that were created by the SQL adapter wizard. These message parts are the ones accepted by the generated ports. Therefore, we need to switch them to Public as well.

What you have right now, is a project that will compile, and resources that can be reused in orchestrations that reference this project. Pretty cool way to architect a solution. Part II of this post (coming tomorrow) will demonstrate the quasi-tricky steps necessary to get our enhanced updategram in a project that inherits this base.