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

{tocify} $title={Table of Contents}

Introduction

As said in the last post of mine(http://tech-findings.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

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
• 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!!!!!!

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 

• BizTalk Server: Multiple XML files to Single FlatFile Using File Adapter
• BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database
• BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database - Part 2
• Is it possible to have Map Chaining on the Port Level
• Promoting custom context property using Pipeline Component
• Custom ZipReceivePipeline to Unzip Multi-Type Messages
• Grouping and debatching Inbound Messages from WCF SQL Adapter - BizTalk 2010
• Polling data from SQL using WCF-SQL Adapter in BizTalk 2010 - TypedPolling(From Multiple table)
• Grouping XML Messages using custom XSLT- BizTalk 2010
• Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010 - Composite operation(Multiple Records insertion)
• Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010- Composite Operation (Message Transformation Pattern)
• Debatching(Splitting) XML Message - BizTalk 2010
• Debatching(Splitting) XML Message in Orchestration using DefaultPipeline - BizTalk 2010