通过

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.

  Untitled

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

 image 

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.

Untitled

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

 Untitled2

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

 Untitled3

 TypedProcedure.dbo.xsd

  Untitled4

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.

Comments

  • Anonymous
    August 24, 2010
    Another article from the "Microsoft Expert" that does not work. So typical
  • Anonymous
    October 15, 2010
    The SqlDbType "" is not supported. Modify your table, view, stored procedure, or function definition to avoid having parameters or columns of this type.
  • Anonymous
    January 18, 2011
    This approach works very well when you are on SQL 2008 or newer.
  • Anonymous
    January 19, 2011
    I also faced the same SqlDbType issue but got it resolved. It turned out to be a security permissions issue. Ensure that the account that is used to access the stored proc also has permissions on the Table type. Further I used the WCF-Custom adapter with Sql binding.
  • Anonymous
    February 16, 2011
    Can it be taken for granted that the procedure will work when security (permissions) are set as required?
  • Anonymous
    March 22, 2011
    What if there are no order details? then this fails with DBNULL value.How can we check if order details exists and then do the Insert and if it's not existing skip the insert?