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


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
        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)
    --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
    SELECT @PeriodNumber =PeriodNumber ,
           @PeriodValue = PeriodValue    
           FROM @TempTable WHERE (RowID = @ConditionCount)
         --You may write any custom logic based on the requirement
               --Insert new row
               INSERT INTO dbo.[WCFTable](Name ,Date ,Period,Value)                
               SET @LoopCount = @LoopCount - 1
               SET @ConditionCount = @ConditionCount + 1

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=""><ns0:InputParameterName><bts-msg-body xmlns="" encoding="string"/></ns0: InputParameterName ></ns0: StoredProcedureName >
        For example, in this case format will be:

        <ns0:InsertSeries xmlns:ns0=""><ns0:SeriesData><bts-msg-body xmlns="" encoding="string"/></ns0:SeriesData></ns0:InsertSeries>
      3. Click OK.
      4. Now drop following input file:
        <ns0:Source xmlns:ns0="http://testwcf.sourceschema/">
                <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.