Multiple Stored Procedure Calls using the BizTalk SQL Adapter without a loop shape

Alright, now that the New Zealand Tech Ed is done and dusted people around here are back at blogging, including me.

I thought I’d start with one that’s been on my list for a while: how to make multiple calls to the same stored procedure using the SQL Adapter without having to use a loop shape.

No matter how much you use BizTalk – you’re always learning a new trick. This one comes courtesy of some fellow BizTalk developers here at work – Mark Brimble, Capree Lim and Colin Dijkgraaf.

Say I have this table inside my SQL Server MyTestDB database: 

CREATE TABLE [dbo].[tbMyTestTable]  

([id] [int] IDENTITY(1,1) NOT NULL,

[Code] [nchar] (5) NOT NULL,
[Name] [nchar] (30) NOT NULL,
 [Type] [nchar] (10) NOT NULL,
[PartnerID] [int] NOT NULL,  

CONSTRAINT [PK_tbMyTestTable] PRIMARY KEY CLUSTERED 
([id] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]

 And I have this stored procedure on the same database:

CREATE
PROCEDURE [dbo].[myTestSProc]

    @Code nchar(5) ,@Name nchar(30), @Type nchar(10), @PartnerID int

AS BEGIN

    SET NOCOUNT ON;

    IF EXISTS(Select Code from tbMyTestTable tb where Code= @Code)

        UPDATE [MyTestDB].[dbo].[tbMyTestTable]

         SET [Code] = @Code, [Name] = @Name, [Type] = @Type, [PartnerID] = @PartnerID

         WHERE [Code] = @Code

    ELSE

        INSERT INTO [MyTestDB].[dbo].[tbMyTestTable]

            ([Code], [Name], [Type], [PartnerID])

        VALUES (@Code, @Name, @Type, @PartnerID)

END

 As you can see, the stored procedure will insert a new row in case the code doesn’t already exist, otherwise it will update the row with that code. From this I used the trusty “Add Generated Items” -> “Add Adapter Metadata” to create the schema for that stored procedure. It looks like this:

Now, here is the trick: on the properties of the myTestSProc node (the node with the name of your stored procedure) I changed the Max Occurs setting to unbounded. This will make the schema allow us to repeat that row of XML as many times as we want.

I then created a flat file schema with those same elements:

And, of course, a straight map between the two:

 

And for simplicity I created an orchestration to manage it all (although it could and should all have been done via messaging with no need for an orchestration):

 

Notice how there is no looping shape in the orchestration – it is doing a straight map. So if my (weird) test flat file looks like:

AAA,First Code,Blue,1

AAB,Second Code,Blue,2

AAC,Third Code,Blue,3

AAD,Fourth Code,Green,4

AAE,Fifth Code,Red,5

AAF,Sixth Code,Green,6

AAG,Seventh Code,Green,7

AAH,Eight Code,Red,8

AAI,Ninth Code,Yellow,9

AAA,Tenth Code,Green,10

 

It gets mapped to the following XML which is then sent to the SQL Adapter:

<ns0:SQLInsertCodeReq
xmlns:ns0=“http://MyBizTalkProject/SQLInsert“>

    <ns0:myTestSProc Code=“AAA” Name=“First Code” Type=“Blue” PartnerID=“1“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAB” Name=“Second Code” Type=“Blue” PartnerID=“2“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAC” Name=“Third Code” Type=“Blue” PartnerID=“3“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAD” Name=“Fourth Code” Type=“Green” PartnerID=“4“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAE” Name=“Fifth Code” Type=“Red” PartnerID=“5“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAF” Name=“Sixth Code” Type=“Green” PartnerID=“6“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAG” Name=“Seventh Code” Type=“Green” PartnerID=“7“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAH” Name=“Eight Code” Type=“Red” PartnerID=“8“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAI” Name=“Ninth Code” Type=“Yellow” PartnerID=“9“></ns0:myTestSProc>

    <ns0:myTestSProc Code=“AAA” Name=“Tenth Code” Type=“Green” PartnerID=“10“></ns0:myTestSProc>

</ns0:SQLInsertCodeReq>

So after configuring all the ports and binding everything as per normal SQL Adapter, we end up with nine rows in the table. Nine rows? Hah! Notice that the code on the last line is ‘AAA’, so the stored procedure just updates the row that was inserted first.

This is pretty powerful: all the calls to the stored procedure get wrapped around the same DTC transaction. If one of them fails (which I tested by changing a PartnerID to a string) the whole thing gets rolled back. We have something similar in production uploading a file with 30 thousand records in less than a minute as well.

DOWNLOAD THE SAMPLE CODE

Hope it’s useful to other BizTalkers.
–Thiago Almeida

---

Possibly related posts: (automatically generated)

• Penguin Storm 4 Item ID’s