Inserting parent child records with Identity column using WCF-SQL Adapter in one transaction

There would be scenarios where you are inserting into a Parent table which have got an auto incrementing or an Identity field and you need to get this identity value back and use it to insert into child table records. And that too all, in a single transaction.

In Native SQL Adapter, this kind of composite operation can be achieved using updategram by capturing the value generated for the Identity field using the updg:at-identity attribute. This is documented at whitepaper: Working With BizTalk Adapter for SQL Server. But when it comes to WCF-SQL Adapter (Adapter Pack 2.0), since it would not work with updategram, only way I could figure out is to write a Stored Procedure which will do all the stuff in a single transaction and you can then call it from the Send Port. Of course, you can do the same in multiple operations without calling Stored Procedure where you first write to Parent table and get the identity value back as response and then write to child table; but this would not be a single transaction and any failures will writing to child table will not rollback the insertion into Parent table by itself.

Now, when it comes to writing Stored Procedure, if you just need to insert into one parent record and one child record, it would be simpler. But what about if there are multiple child records for a single parent record? For this, we would need to create an Xml message and pass this to the Stored Procedure as an Xml parameter. And then inside the stored procedure, we need to retrieve the values from the Xml message and insert it to our Parent and Child tables. Parsing the Xml message inside Procedure will be not an easy task if the message schema is complicated (especially for EDI Schemas :) ) and will involve good knowledge of writing Xpath Queries. But this becomes really simpler with use of Table Types and Table-Valued Parameters in SQL 2008. (Thanks to Mustansir Doctor for pointing me to this new feature of SQL 2008)

Here is an example of how we can achieve the same using Table Types and Table-Valued Parameters.

Lets say we have two tables Orders & Order Details in SQL 2008 with Primary Foreign relationship  and OrderId is an auto incrementing field.

Now, first step will be to create Table Types for the above two tables:

We do not need to have OrderId field in the Table Type created as this is an incrementing field and we do not need to pass this in parameters passed to Stored Procedure.

Now, create a stored procedure to which we will pass this Table Type as parameter and inside it we will first insert into Orders table and read out the Identity value and use it to insert into OrderDetails table. Also, notice that reading out values from the Table Type objects is as simple as reading out form a Table.

Now, what all we have to do is to use Consume Adapter Service through Visual Studio to generate Schema.

It will generate two schemas TableType.dbo.xsd and TypedProcedure.dbo.xsd along with a binding file for the WCF-SQL Adapter.

TableType.dbo.xsd

 TypedProcedure.dbo.xsd

TypedProcedure.dbo.xsd contains the request and response schema for WCF-SQL Adapter.

Now, only thing remaining is to construct a message of the request schema type inside the Orchestration and send it to WCF-SQL Adapter which will execute the Stored Procedure. I am not going into it more detail on how we do it inside Orchestration as it will be similar to any other operation we do using WCF-SQL Adapter.

You can see that using the Table Types simplify the things to a large extent and we do not need to write a single line of code to parse the XML.