BizTalk Two-Way WCF-SQL Solution for Send Port
1. Open Microsoft Visual Studio as an Administrator user. Click on File, then select New and Project. In the Installed Templates section, select BizTalk Projects, then choose Empty BizTalk Server Project. Name the project “BizTalkLive-WCF-SQL” and click OK.
2. Refer to the picture below and create separate folders for Schema, Map, Orchestration, Pipeline, and Binding.
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: <EStudy247.com>
— 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
7. Click on the “Next” button and select the target namespace as http://BizTalkLive-SQL.GetAccountResponse. Then, choose the send port type and fill in the request root as “procGetAccountResponseDataRequest” and response root as “procGetAccountResponseDataResponse“. Click on the “Next” button.
8. Select the type of statement as “Stored Procedure” and click on the “Next” button. Now, choose your SQL procedure from the list and click on the “Generate” button. Finally, click on the “Next” button to complete the process.
9. After completing the above steps, one schema and one orchestration will be created in your solution. You can delete the newly created orchestration. After that, click on your schema and delete the “procGetAccountResponseDataRequest” root from your schema. Keep only one root – “procGetAccountResponseDataResponse“.
10. To import a SQL data type schema into the newly created schema for getting SQL response, you can download it from the following link: SQLType
12. If you already have an SQL schema and have also created a map for SQL response, then the only changes you need to make are to delete the SQL request root from the schema and import the SQL type schema as suggested in the picture above.
Now, assuming that you have already installed WCF SQL LOB SDK, I will guide you through the process of creating a schema for WCF-SQL request outbound procedure.
13. To do this, first, right-click on the schema folder and select Add > Add Generated Item. From Consume adapter service, double-click it and select sqlBindung. Configure the URI and click the Connect button. Then select the contract type as client (Outbound Operations) and click Procedures, which should be available in the right-side list. Click the Add button to add it to the added categories and operations. Also, check the Generated unique schema type so that all added items are clubbed in one schema. Finally, set the filename prefix as shown in the picture below and click OK.
15. I have completed the deployment of the BizTalk application and opened the BizTalk Server Administrator to create all the necessary receive ports, receive locations, and send ports. I have also posted an article that provides step-by-step instructions for creating a one-way WCF-SQL receive location. You can find the article at this link: https://estudy247.com/courses/biztalk/lessons/one-way-xmlpolling-in-wcf-sql-adapter-for-biztalk-receive-locations/
16. To create a static solicit-response send port, I right-clicked on the send port of the newly deployed BizTalk application and selected “New.” I filled in the name as “SP_WCFSQL_AccReq,” chose the transport type as “WCF-SQL,” and left the default handler. I also selected the send pipeline as XML Transmit and the receive pipeline as XMLReceive, as shown in the following picture:
17. Click the Configure button and fill in the Endpoint address URI and Soap action header in the General tab, as shown in the image below.
<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>
Set the value “procGetAccountResponseDataResponse” in XmlStoredProcedureRootNodeName and “http://BizTalkLive-SQL.GetAccountResponse” in XmlStoredProcedureRootNodeNamespace tab of Binding. Both values are taken from SQL response schema root and target namespace value respectively.
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, the process has finally been completed, and I’ve found the best way to achieve Two-Way WCF-SQL XmlPolling and develop a new BizTalk application. It is also the best way to convert old SQL Two-Way request-response without any change in your SQL response mapping.
If you want to better understand how to achieve one way WCF-SQL XmlPolling in Receive location, please click on this link: https://estudy247.com/courses/biztalk/lessons/one-way-xmlpolling-in-wcf-sql-adapter-for-biztalk-receive-locations/.
Download all the source code, including SQL scripts, BizTalk application binding, and MSI.