Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010- Composite Operation (Message Transformation Pattern)

byMaheshkumar Tiwari •
0
{tocify} $title={Table of Contents}



Introduction


In this post we will see how to Insert records in SQL tables without using Orchestration . The transformation of the message is done at Receive port.

 The idea to implement this struck me , when I was composing my other post(so most of the part will be common ) : https://tech-findings.com/2013/07/insert-records-in-sql-server-using-wcf.html
Scenario is I receive Purchase Order and need to insert in SQL tables viz. 1. Header table(Parent) 2. Details table(Child)

 First thing is to create Tables and Stored Procedures which we will call to insert values in the tables.

Header Table Create Script:


CREATE TABLE [dbo].[HDR4201](
[RequestDate] [date] NOT NULL,
[OrderID] [nchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[BillToID] [nchar](50) NOT NULL,
[ShipToID] [nchar](50) NOT NULL,
[ACKFlag] [char](1) NULL,
[SNFlag] [char](1) NULL,
[BillToName] [nchar](50) NOT NULL,
[BillToCity] [nchar](50) NOT NULL,
[BillToState] [nchar](50) NOT NULL,
[BillToPostal] [nchar](50) NOT NULL,
[BillToCountry] [nchar](50) NOT NULL,
[ShipToName] [nchar](50) NOT NULL,
[ShipToCity] [nchar](10) NOT NULL,
[ShipToState] [nchar](50) NOT NULL,
[ShipToPostal] [nchar](50) NOT NULL,
[ShipToCountry] [nchar](50) NOT NULL,
[Comments] [nchar](999) NULL,
[TotalAmount] [decimal](18, 2) NOT NULL,
[TotalTax] [decimal](18, 2) NOT NULL,
[Currency] [nchar](10) NOT NULL,
[DOCO] [bigint] IDENTITY(1000,1) NOT NULL,
[INFlag] [char](1) NULL,
 CONSTRAINT [PK_HDR4201] PRIMARY KEY CLUSTERED 
(
[OrderID] ASC,
[OrderDate] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Detail Table Create Script:


CREATE TABLE [dbo].[DTL4211](
[Doco] [bigint] NOT NULL,
[LineNumber] [float] NOT NULL,
[OrderID] [nchar](50) NOT NULL,
[OrderDate] [datetime] NOT NULL,
[RequestDate] [date] NOT NULL,
[Quantity] [int] NOT NULL,
[UnitPrice] [decimal](18, 2) NOT NULL,
[Tax] [decimal](18, 2) NOT NULL,
[TotalLineAmount] [decimal](18, 2) NOT NULL,
[UnitOfMeasure] [nchar](4) NOT NULL,
[ACKFlag] [nchar](1) NULL,
[SNFlag] [nchar](1) NULL,
[CurrentDate] [datetime] NOT NULL,
[ACKSentDate] [datetime] NOT NULL,
[ShipmentDate] [datetime] NOT NULL,
[ItemDescription] [nchar](50) NULL,
[LineComment] [nchar](50) NULL,
[ItemID] [nchar](20) NOT NULL,
 CONSTRAINT [PK_DTL4211] PRIMARY KEY CLUSTERED 
(
[Doco] ASC,
[LineNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Stored Procedure to insert in Header Table:


 CREATE PROCEDURE  [dbo].[uspInsertOrderHeader]
@RequestDate date,
@OrderID nchar(50),
@OrderDate nchar(50),
@BillToID nchar(50),
@ShipToID nchar(50),
@BillToName nchar(50),
@BillToCity nchar(50),
@BillToState nchar(50),
@BillToPostal nchar(50),
@BillToCountry nchar(50),
@ShipToName nchar(50),
@ShipToCity nchar(50),
@ShipToState nchar(50),
@ShipToPostal nchar(50),
@ShipToCountry nchar(50),
@Comments nchar(999),
@TotalAmount decimal(18,2),
@TotalTax decimal(18,2),
@Currency nchar(10)
AS
BEGIN

SET NOCOUNT ON;

INSERT INTO HDR4201(RequestDate,OrderID,OrderDate,BillToID,ShipToID,BillToName,BillToCity,BillToState,BillToPostal,BillToCountry,ShipToName,ShipToCity,ShipToState,ShipToPostal,ShipToCountry,Comments,TotalAmount,TotalTax,Currency)
Values(@RequestDate,@OrderID,@OrderDate,@BillToID,@ShipToID,@BillToName,@BillToCity,@BillToState,@BillToPostal,@BillToCountry,@ShipToName,@ShipToCity,@ShipToState,@ShipToPostal,@ShipToCountry,@Comments,@TotalAmount,@TotalTax,@Currency);

 END

Stored Procedure to insert in Detail Table:


 CREATE PROCEDURE [dbo].[uspInsertOrderDetail] 
@LineNumber float,
@OrderID nchar(50),
@OrderDate nchar(50),
@RequestDate date,
@Quantity int,
@UnitPrice decimal(18,2),
@Tax decimal(18,2),
@TotalLineAmount decimal(18,2),
@UnitOfMeasure nchar(4),
@CurrentDate datetime,
@ACKSentDate datetime,
@ShipmentDate datetime,
@ItemDescription nchar(50),
@LineComment nchar(50),
@ItemID nchar(20)
AS
BEGIN

SET NOCOUNT ON;
DECLARE @doco bigint;
set @doco = (SELECT DOCO FROM HDR4201 WHERE OrderID = @OrderID AND OrderDate =@OrderDate);

INSERT INTO DTL4211(Doco,LineNumber,OrderID,OrderDate,RequestDate,Quantity,UnitPrice,Tax,TotalLineAmount,UnitOfMeasure,CurrentDate,ACKSentDate,ShipmentDate,ItemDescription,LineComment,ItemID)
Values(@doco,@LineNumber,@OrderID,@OrderDate,@RequestDate,@Quantity,@UnitPrice,@Tax,@TotalLineAmount,@UnitOfMeasure,@CurrentDate,@ACKSentDate,@ShipmentDate,@ItemDescription,@LineComment,@ItemID);

  END


 Cool ... Now to make it happen actually ... we need to consume the service which is leveraged by WCF-SQL Adapter

Steps in creating solution


 1.Create BizTalk Project (Name it what you desire)
2.Create schema for the incoming Purchase Order

 3.In schema shown above , ShipTo and BillTo have same structure
4.Now we need the destination schema and let's add it:
  •  Right click on  the project and select Add->Add Generated Items
  • A wizard will pop out and will give you options to select from it.
  • Select Consume Adapter Service ( you can also opt for Add Adapter)
  • Difference is very small and should be opted as per requirement:
  • Consume Adapter Service : Creates schema(XSD) as per adapter metadata
  • Add Adapter Metadata: Creates orchestration and schema as per WSDL
  • And both will make you go through same Wizard (Shown Below)
  • In Security tab select Windows from drop down list 
  • In URI Properties tab in front of Server
  • 1.Enter "dot(.)" (If your SQL server is on  same machine) 2. Else the name of the server machine
  • I front of InitialCatalog mention your database name





  • After you say OK on the configure adapter page , click connect . And as shown above all the details are filled .
  • It tells you the type of binding ( sqlBinding) , URI(which you configured), contract type (Client(Outbound operations )) and all the categories and operations available on URI that we configured.
  • As it can be seen above , I have added the operations which I need for inserting into tables from Procedures category. 
  • I have given a prefix (Its not mandatory) which will be prefixed to the schema's we are about to create.
  • After I click OK , two xsd get added to my project(shown below)

    5. Now we have  the destination  schemas as shown below :


6. As you can see we get four root elements here.Now I want to insert OrderHeader and OrderDetails in both the table at once.To do so, I would need a dedicated schema record for Request and for Response. 
So lets add it in the two records namely OrderRequest and OrderRequestResponse.In OrderRequest record add uspOrderInsertHeader and uspOrderInsertDetail.Make sure that you change the max occurrence property of uspOrderInsertDerail to unbounded.

7.The reason for doing this is as we will have multiple line item in a single order.Also we are inserting both header and detail records together .

8.Under OrderRequestResponse add uspOrderInsertHeaderResponse and uspOrderInsertDerailResponse
also make max occurrence property of uspOrderInsertDerailResponse to unbounded.

Note: You can name any thing you want but remember the second record name should be suffixed with Response. Because SQL sends response with  request name suffixed with "Response" 
 example: Suppose you submit request with name "A" then  SQL will send response as "AResponse"



9.Remember to set MaxOccur as unbounded for "uspInsertOrderDetail" , "uspInsertOrderDetailResponse" as this will be multiple times.
10.Now we are ready with the resources required for mapping, lets do it:
11.Do test your map , it is best practice and does help in reducing problems which might arise later.
12. Now Deploy the project.
13. The flow is :
  •  Purchase Order is received at receive port.
  • A map which we saw above will be applied where we transform incoming message to traget(SQL) form.
  • The message is published in the MessageBox, thereafter look up for subscription is done and copy of this message is sent to subscriber ( here it will be Send Port which we will create in some time)
14.Now its time to configure the application
  • Create one way Receive port with a file location where you want the input message to be picked
  • And add the map which will transform IncomingOrder to SQL expected form.

  • Create one Send port of type Static One Way Type 
  • Select WCF-SQL Adapter as a Transport type
  • Select XMLTransmit 














  • Click configure
  • Perform same operation as we did at step 4
  • Do not forget to Add Action and here it should be "CompositeOperation".
  • Click ok 


      •

      • Add filter on the send port:  BTS.ReceivePortName==ReceiveOrderFile
      • Adding filter means creating subscription, so here our send port is subscribing  to the messages which are published by our receive port "ReceiveOrderFile" in MessageBox.









      16. Start the application and test it .
      Will keep on posting as an when I find something to share!!!!!!!!!!!!



      If you have questions or suggestions, feel free to do in comments section below !!!


      Do share if you find this helpful .......
       
                                Knowledge Sharing is Caring !!!!!!




      Related Post 


      Tags:

      Post a Comment

      If you have any suggestions or questions or want to share something then please drop a comment

      Previous Post Next Post