I wanted a way to insert a parent row with multiple child rows in one single transaction into a database using BizTalk 2010.

The solution I came up with uses the table data type in SQL 2008 to insert parent-child relationship records and the BizTalk WCF SQL Adaptor. The data I will be inserting will consist of a Sales Order header record with several sales item lines.

Below are the steps I used to accomplish this.

1. Create the tables for the Sales header record and the Sales Item records.

CREATE TABLE [dbo].[SalesHeader]
(
    [SalesHeader] [int] IDENTITY(1,1) NOT NULL,
    [OrderNumb] [nvarchar](50) NOT NULL,
    [OrderDate] [datetime] NOT NULL
)

CREATE TABLE [dbo].[SalesItem]
(
    [SaleItem] [int] IDENTITY(1,1) NOT NULL,
    [SalesHeaderID] [int] NOT NULL,
    [ItemCode] [nvarchar](255) NOT NULL,
    [Qty] [int] NOT NULL,
    [Price] [money] NOT NULL
)

go
--add the FK Cconstraint
ALTER TABLE [dbo].[SalesItem]  WITH CHECK ADD  CONSTRAINT [FK_SalesItem_SalesHeader] FOREIGN KEY([SalesHeaderID])
REFERENCES [dbo].[SalesHeader] ([SalesHeader])
ALTER TABLE [dbo].[SalesItem] CHECK CONSTRAINT [FK_SalesItem_SalesHeader]
go

2. Create the table data types in SQL. One for the header record and another for the order lines. These new table types in SQL2008 allow you to pass multiple rows in as a single parameter. In the old  days you would have to pass the data in as a XML document or use updategrams.

CREATE TYPE [dbo].[SalesHeaderTableType] AS TABLE(
    [OrderNumb] [nvarchar](255) NULL,
    [OrderDate] [datetime] NULL
)
go
CREATE TYPE [dbo].[SalesItemTableType] AS TABLE(
    [ItemCode] [nvarchar](255) NULL,
    [Qty] [int] NULL,
    [Price] [money] NULL
)
go

3. Create the stored procedure to insert the data into the tables using the table data types as the input parameters. Also I am using the scope_identity function to get the parent header id. Note table parameters can only be used as read only.

CREATE procedure [dbo].[InsertSalesOrder]
(
    @SalesHeader as dbo.SalesHeaderTableType READONLY,
    @SalesItem as dbo.SalesItemTableType READONLY
)
as

declare @SalesHeaderID int

--add the sales header record
insert into dbo.SalesHeader(OrderNumb,OrderDate)
select OrderNumb, OrderDate from @SalesHeader
select @SalesHeaderID = scope_identity()

--add the line items
insert into dbo.SalesItem (SalesHeaderID,ItemCode,Qty,Price)
select @SalesHeaderID,ItemCode,Qty,Price from @SalesItem

return @SalesHeaderID
go

4. The next step is to generate the schema for the stored procedure created above using the “Consume Adapter Service” wizard.

This will create the following 3 files Sales.DataSetSchema.xsd, Sales.Procedure.dbo.xsd and Sales.TableType.dbo.xsd

5. Create the schema for the input data which is just a simple Header – Detail  data structure as shown below. Note the Detail node ‘Max Occurs’ is set to unbound as there can be multiple detail line items.

6. Create map from the input data schema to the generated schema for the stored procedure. The functiod in the map connecting the Detail node to the SaleItemTableType is just a Looping shape.

7. Create a simple orchestration to write the sales data to the database. This orchestration just simply picks up the xml sales data from a pickup folder and inserts the data using a static resquest/response port. The response is simply the identity id returned from the stored procedure.  The response is then written to a drop folder as xml.

8. Compile and deploy the application to BizTalk. The request/response port is configured as a WCF SQL.

The SOAP Action header of the WCF SQL Transport Properties are configured as below

That’s pretty much it. Just drop a test message and check the tables have been populated.

Below is the results after dropping the test message in the pickup folder a few times.

Enjoy.