Friday, July 5, 2013

Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010 - Composite operation(Multiple Records insertion)

As said in the last post of mine(http://tech-findings.blogspot.com/2013/06/no-valid-adapters-installed-on-machine.html)... I am back with the progress.

Allright so lets begin....

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

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
  • In 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(Outound 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 schemas 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 lets design the flow(Orchestration):



13. The flow is very simple:
  •  Purchase Order is received 
  • Using it destination message is constructed in Construct Shape . Where we utilize the map we saw above
  • The constructed message is passed to Request-Response send port ( We will configure it after we deploy )

14.Now build the project and deploy it.
15.Now its time to configure the application
  • Create one Receive port with a file location where you want the input message to be picked
  • Create one Send port of type Static Solicit-Response Type (As we desire to get response back from SQL)
  • Select WCF-SQL Adapter as a Transport type
  • XMLsend and receive pipeline



  • 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 and you are ready to go now
      16. Start the application and test it .







      Will keep posting as an when I find something to share and thanks to Dhiraj Bhavsar for helping me with this post!!!!!!



      Related Post 






      1 comment:

      1. From the above example, I can insert only one record to detail table although I receive schema has 2 detail lines.

        ReplyDelete