Step by step solution of BizTalk Two-Way WCF-SQL XmlPolling in Send Port

I am going to share most awaited Two way WCF_SQL Two Way XmlPolling so that you can easily enhance your old SQL procedure and BizTalk Map without any big change. I know there is no content available in any website to how work with XmlPolling in WCF-SQL two-way adapter. In the last of this post you can download complete source code with an example.

Please follow carefully all steps –

1. Open Microsoft Visual Studio as Administrator user and Click File>New>Project… In Installed Templates click BizTalk Projects then Empty BizTalk Server Project and Name as “BizTalkLive-WCF-SQL” and click OK.

2. Now see the below picture and create all respective folders separately for Schema, Map, Orchestration, Pipeline, Binding-

3. Create Schemas in Schema folder as shown below picture to get Account request using One Way WCF-SQL XmlPolling

a) Create Envelope Schema first with Target NameSpace - http://BizTalkLive_WCF_SQL.AccountRequest

b) Create Second Schema to envelope above schema with same target namespace, elements and records and I assume that you are aware that how to create envelope schema, Pl see following picture-

3. Now Create 3^rd schema to get SQL response using Two-Way WCF-SQL adapter:

Right Click Schema Folder>Add>Add Generated Item… then form Add generated Items click Add adapter meta data then double click add adapter metadata after add adapter wizard will be open after that select SQL adapter from installed list, also provide SQL server and biztalk management database name then click next. Now to set connection string click set button from right side and fill all details to connect sql server after click ok as shown following picture.

4. Before move next process to complete generate sql schema now move toward sql procedure and create sql procedure as shown below-

USE [BizTalkLive]

GO

/****** Object:  StoredProcedure [dbo].[procGetAccountResponseData]    Script Date: 08-09-2017 17:32:20 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:           <Jitendra Yadav from BizTalkLive>

-- Create date: <02-Sep-2017>

-- Description:      <Get Account Response data from requested accounts>

-- =============================================

Create PROCEDURE [dbo].[procGetAccountResponseData]

       @AccountNumber VARCHAR(50),

       @IFSCCode VARCHAR(11)

AS

BEGIN

       SET NOCOUNT ON;

       SELECT * FROM

       (

       SELECT Id, AccountNumber, IFSCCode, AccountType, AccountStatus, AccountValidity

       FROM tblAccountMaster WITH(NOLOCK) WHERE AccountNumber = @AccountNumber AND IFSCCode = @IFSCCode

       ) AccountsResponse

       LEFT JOIN

       (

       SELECT AccountId, Name, Address, PAN, TAN, UID

       FROM tblAccountHolderMaster WITH(NOLOCK)

       )AHDetails

       ON AccountsResponse.Id = AHDetails.AccountId

       FOR XML AUTO, XMLDATA

          

END

5. Click next then target namespace as http://BizTalkLive-SQL.GetAccountResponse and chose send port type and also fill request root as procGetAccountResponseDataRequest and response root as procGetAccountResponseDataResponse then click next button.

6. Now select type of statement as Stored Procedure then click next. Now choose your sql procedure from list and click generate button then at last click next button to complete process.

7. Now One Schema and one orchestration has created in your solution and delete newly created orchestration then click your schema and delete procGetAccountResponseDataRequest root from your schema then remain only one root procGetAccountResponseDataResponse

8. I want to import a sql data type schema into above newly create schema for get sql response and you can download from the below following link: SqlType

9. Now Import SqlType schema into the procGetAccountResponseDataResponse schema and changes base data type of every elements and attributes as” sqltypes:varchar (SimpleType)” as shown in following picture-

10. If you have already sql schema and also create map for sql response then only changes for you first delete sql request root from schema and then import sqltype schema as suggested in above picture that’s it.

11. So you have created Schema for WCF-SQL adapter response now I am going to create schema for WCF-SQL request outbound procedure before to do I am assuming that you have already install WCF SQL LOB SDK.

12. Again Right click schema folder>Add>Add Generated Item… then from Consume adapter service, double click it. Then select sqlBindung and configure URI then click connect button then select contract type as client (Outbound Operations). Now click Procedures, it available in right side list then click it add button so now it added in added categories and operations. Also check Generated unique schema type so all added clubbed in one schema and finally set filename prefix as shown below picture then click OK.

13. So Finally I am assuming that you map your schema and create orchestration logic to get sql request then construct map and finally send sql request and get back sql response after create a schema to send it to your destination as show following images and also in the last all source code, sql script, BizTalk binding and msi is available for download.

14. Finally deployed BizTalk application and open BizTalk Server Administrator and create all respective receive port, receive location  and send port. I have already post an article to step by step create one way WCF-SQL receive location (See from this link: https://biztalklive.blogspot.com/2017/07/step-by-step-configuration-one-way.html)

15. Now Right click on your send port of newly deployed BizTalk application >New> Static Solicit-Response Send Port then fill the name as “SP_WCFSQL_AccReq” then chose Transport type as “WCF-SQL” and remain default handler and also select send pipeline as XML Transmit and Receive pipeline as XMLReceive as shown following picture-

16. Click Configure… button then in General tab of fill Endpoint address URI and Soap action header as shown in below image-

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <Operation Name="procGetAccountResponseData" Action="XmlProcedure/dbo/procGetAccountResponseData" />

</BtsActionMapping>

17. Go to Binding tab and set value “procGetAccountResponseDataResponse” in  XmlStoredProcedureRootNodeName and set value “http://BizTalkLive-SQL.GetAccountResponse” in XmlStoredProcedureRootNodeNamespace. We have taken both value from sql response schema root and target napespace value respectively.

18. Finally Leave remaining default setting and click OK and also do not forget to add default namespace of sql response schema and same elements, attribute and records as shown in the following picture-

Create PROCEDURE [dbo].[procGetAccountResponseData]

       @AccountNumber VARCHAR(50),

       @IFSCCode VARCHAR(11)

AS

BEGIN

       SET NOCOUNT ON;

       ;WITH XMLNAMESPACES(DEFAULT 'http://BizTalkLive-SQL.GetAccountResponse')

       SELECT * FROM

       (

       SELECT Id, AccountNumber, IFSCCode, AccountType, AccountStatus, AccountValidity

       FROM tblAccountMaster WITH(NOLOCK) WHERE AccountNumber = @AccountNumber AND IFSCCode = @IFSCCode

       ) AccountsResponse

       LEFT JOIN

       (

       SELECT AccountId, Name, Address, PAN, TAN, UID

       FROM tblAccountHolderMaster WITH(NOLOCK)

       )AHDetails

       ON AccountsResponse.Id = AHDetails.AccountId

       FOR XML AUTO --, XMLDATA

          

END

All right finally all process has completed and this is the best way to achieve Two-Way WCF-SQL XmlPolling to develop BizTalk new Application and also best way to convert old SQL Two-Way request-response without any change in your SQL response mapping.

Note: To better understand Please click this link to achieve one way WCF-SQL XmlPolling in Receive location: https://biztalklive.blogspot.com/2017/07/step-by-step-configuration-one-way.html

Download all source code including SQL Script, BizTalk Application Binding and MSI

!! Keep visiting BizTalkLive !!