Share via

Invoke a SQL Stored Procedure on the BizTalk WCF Send Port without Needing an Orchestration

  • Article

Introduction

There was a scenario in BizTalk where we needed to insert data into a database by executing an existing stored procedure that accepts XML as input parameter.

The input XML message in my case was already being published to BizTalk message box but in general it can be coming from any other source – may be a file share or from another database or something else.

WCF adapter is best suited for the same. But we needed to create an orchestration that would define the ‘operation’ to be performed on database (which is executing a stored procedure in this case). This orchestration will then will be bound to a WCF Custom (SQL) adapter on send port.

An alternative method of not using the orchestration is to use ‘Template’ feature of WCF adapter. Template is mainly used to add wrapper elements around the body of the outgoing message from send port.

The below example illustrates how can we use this feature to execute the stored procedure using WCF send port without need of orchestration. This takes an XML has input and pass this to stored procedure using WCF send port

Steps

Create database table and stored procedure

  1. Create a database table

    CREATE TABLE  [dbo].[WCFTable](
       [Id] [bigint] IDENTITY(1,1) NOT  NULL,
       [Name] [nvarchar](20) NULL,
       [Date] [datetime] NULL,
       [Period] [int] NULL,
       [Value] [int] NULL
) ON  [PRIMARY]

  2. Create a stored procedure that accepts XML as input parameter and  populates the table

    CREATE  PROCEDURE  [dbo].[InsertSeries]
@SeriesData XML
AS
    Declare @SeriesName nvarchar(50)
    Declare  @Date Datetime
    Declare  @PeriodNumber int
    Declare  @PeriodValue int
    DECLARE @LoopCount int
    DECLARE @ConditionCount int
    Declare @ObservationNullValue nvarchar(max)
    Declare @XmlObservationDate xml
    DECLARE @RowCount int
    DECLARE  @TempTable TABLE(RowID    int   not null  identity(1,1),
                             PeriodNumber int  not null,PeriodValue int  not null)
 
BEGIN
--Extract the values from XML
    SELECT @SeriesName = nref.value('(Name)[1]', 'nvarchar(20)'),
      @Date = nref.value('(Date)[1]', 'datetime')     
    FROM   @SeriesData.nodes('declare namespace BTS="http://testwcf.sourceschema/"; //BTS:Source') AS  R(nref)
 
 --Inserts repeating node values from Xml  into Table Variable
    INSERT INTO  @TempTable (PeriodNumber, PeriodValue) 
               (SELECT nref.value('(Number)[1]', 'int') Period,  nref.value('(Value)[1]', 'nvarchar(20)')    Value           FROM   @SeriesData.nodes('//Period') AS  R(nref))
            SET @ConditionCount = 1
    SELECT @LoopCount=COUNT(RowID) FROM  @TempTable
 WHILE(@LoopCount>0)
 
BEGIN
 
SELECT @PeriodNumber =PeriodNumber , 
       @PeriodValue = PeriodValue     
       FROM @TempTable WHERE (RowID = @ConditionCount)
 
     --You may write any custom logic based on the requirement
     BEGIN
           --Insert new row
           INSERT INTO  dbo.[WCFTable](Name  ,Date  ,Period,Value)                 
           VALUES(@SeriesName,@Date,@PeriodNumber,@PeriodValue)
    END  
 
           SET @LoopCount = @LoopCount - 1
           SET @ConditionCount = @ConditionCount + 1
 
END
END

Create BizTalk Application

  1. Create a new BizTalk Project in Visual Studio
  2. Add a new XML Schema which has following field elements:
    1. Name ( String)
    2. Date ( Date time)
    3. Period- Repeating node
    4.  Number ( Int)
    5. Value ( int)
  3. Now compile and deploy the BizTalk application. Let's name the application as  WCF.SQLOperations
  4. Create a new receive port and receive location under the newly deployed application and set the following properties:
    1. Receive Port Name: WCF.ReceiveInputMesssagePort
    2. Receive location:
      1. Name: WCF.Receive
      2. Transport Type: File
      3. Receive Pipeline: XML receive
  5. Create Send port:
    1. Static one way send port
    2. Name: WCF.InvokeSP
    3. Filter: BTS.ReceivePortName= “WCF.ReceiveInputMesssagePort”
    4. Transport Type_ WCF Custom, Set Properties as follows:
      1. General:
        1. Address URI: mssql:<DatabaseInstance>/<DatabaseName>
        2. Action: Procedure/<SchemaName><StoredProcedureName>
  6. Bindings:
    1. Binding Type: SQLBindings
    2. Set default settings (You may change time out settings based on requirements)
    3. Message:

      1. Use Template option

      2. Specify template in following format
         

        <ns0:StoredProcedureName xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo"><ns0:InputParameterName><bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/></ns0: InputParameterName ></ns0: StoredProcedureName >

              
        For example, in this case format will be:

        <ns0:InsertSeries xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo"><ns0:SeriesData><bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/></ns0:SeriesData></ns0:InsertSeries>

           

      3. Click OK.

      4. Now drop following input file:

        <ns0:Source xmlns:ns0="http://testwcf.sourceschema/">
        <Name>WCFTest</Name>
        <Date>12-Aug-2013</Date>      <Period>        <Number>1</Number><Value>12</Value></Period><Period><Number>2</Number><Value>13    </Value></Period><Period>   <Number>3</Number><Value>14</Value></Period</ns0:Source>

      5. Within a few moments, the data will be inserted into the target table.

It's worth mentioning that if you have lot of complex logic on database side, using SQL stored procedure will be a better option and will be better in terms of maintainability and performance as well. Though you will need to make sure you are using right SQL isolation level on the send port.

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.