Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
Windows Server

BizTalk 2010 : ASDK SQL adapter examples (part 3) - Query notification and multiple result sets

12/2/2012 11:42:27 AM

Example 3 - Query notification and multiple result sets

In this example we will introduce two advanced features of the ASDK SQL Adapter. We will use query notifications to replace the typed polling used in previous examples to retrieve sales orders that haven't yet been processed. We will also use multiple result sets to retrieve information from the sales order and replace the lookup operations created in Example 2.

Although polling inbound operations are very common, the polling principle itself requires some consideration. Polling frequently when there is no data to be returned is a waste of systems resources. Polling infrequently when there is data to be returned means the solution is not as efficient as it could be. Careful consideration of the polling interval and polling statement can mitigate these constraints but notification is an alternate and often better option.

ADO.NET provides the ASDK SQL adapter with a notification option: query notifications. Query notifications are built on top of SQL Server Service Broker queues and raise events into BizTalk Server whenever the results of a SQL Server query changes. The adapter encapsulates this feature, using the ADO.NET SqlDependency notification object to receive the notification information when a query result changes.

We can configure the notification properties in the Configure Adapter window and the Binding properties tab. The two available properties are NotificationStatement and NotifyOnListenerStart. These settings have no impact on the SQL Schemas that we will generate in the Consume Adapter Service wizard and will be discussed in greater detail when we configure our Receive Location.

Once the Connection URI has been entered, we need to click Connect and ensure Service (Inbound operations) is selected as the contract type since Query Notification is an inbound operation. Next, we select the Notification operation and click the OK button.

The schema created for the Notification operation is universal and serves to simply tell your BizTalk Server solution that there is data for it to process:

The elements of the schema relate to information received by ADO.NET about the query change contains:

  • Info This one element holds a value of the System.Data.SqlClient.SqlNotificationInfo enumeration providing additional information about notification received by the adapter.

  • Source This will contain a value of the System.Data.SqlClient.SqlNotificationSource enumeration indicating the source of the notification received by the adapter.

  • Type This will contain a value of the System.Data.SqlClient.SqlNotificationType enumeration. Most of the time this will be a change, indicating that there was a change on the data of the notification query.

    Please refer to the MSDN for the detailed information on these enumerations.

When the NotifyOnListenerStart binding option, in our Receive Location, is set to true, a notification message with hardcoded values of Info = ListenerStarted, Source = SqlBinding, and Type = Startup is generated by the adapter.

Our orchestration will filter out the unwanted notification messages, retrieve any sales orders to be processed, and then debatch the sales orders. Note how we have distinguished all three elements of the notification schema. This is because we will use them in a Decide shape in this orchestration. Here is the beginning of the orchestration, including the expression we use to filter out only insert, update, or listener started notifications:

If the notification matches our decide shape criteria, the orchestration executes a stored procedure called usp_GetSalesOrdersToProcess that retrieves the list of sales orders to be processed. Before debatching, via an Xpath statement within a Message Assignment shape, the orchestration first checks if there is actual data to be processed by counting the number of sales orders returned by the stored procedure. This is important since a raised notification does not always mean there are sales orders to be processed because we have NotifiyOnListenerStart set to True. Here is the part of the orchestration that debatches the orders into individual messages:

We provide the response message msgGetSalesOrdersToProcessResp to the xpath method available in orchestration expression shape. This xpath expression will count the number of nodes returned by the stored procedure. Here is the full expression used to determine if we have sales orders that need to be processed:

recCount = System.Convert.ToInt32(xpath(msgGetSalesOrdersToProcessResp, 
"count(/*[local-name()='usp_GetSalesOrdersToProcessResponse' and namespace-uri()=
'http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()=
'http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo']/*[local-name()='StoredProcedureResultSet0' and namespace-uri()=

Use the Instance XPath property of the element you would like to evaluate in the BizTalk schema editor to find out the correct xpath to use.

Now we have query notifications replacing typed polling. The development implementation is more complex but the main advantages of the changes made to the solution are as follows:

  • Once the process indicator has been set, our outstanding Sales Orders will be processed immediately

  • There are fewer calls to the database since we are not polling anymore, keeping system resource utilization to a minimum

The next improvement we will make to the solution is related to how we enrich the sales order information before sending out the final sales order message. A great feature of the ASDK SQL Adapter is that it can handle multiple resultsets. We have created a stored procedure called usp_GetSalesOrderDetails that takes in the sales order ID and performs three distinct select statements, one for each of the three lookups of Example2—the function call, the select statement, and the view call. Here is the stored procedure:

CREATE PROCEDURE [dbo].[usp_GetSalesOrderDetails]
@SalesOrderId [int]
SET no count on
DECLARE @ContactId int
SELECT * FROM Sales.vSalesOrderDetailProduct
WHERE SalesOrderID = @SalesOrderId
at.Name, a.AddressLine1
, a.AddressLine2, a.City
, sp.StateProvinceCode, cr.Name as Country
Sales.SalesOrderHeader soh
inner join Sales.Customer c on soh.CustomerID = c.CustomerID
inner join Sales.CustomerAddress ca ON C.CustomerID = ca.CustomerID
inner join Person.Address a ON ca.AddressID = a.AddressID
inner join Person.AddressType at ON ca.AddressTypeID = at.AddressTypeID
inner join Person.StateProvince sp ON a.StateProvinceID = sp.StateProvinceID
innerjoin Person.CountryRegion cr ON sp.CountryRegionCode = cr.CountryRegionCode
WHERE soh.SalesOrderID = @SalesOrderId
SELECT @ContactId = ContactId FROM Sales.SalesOrderHeader
WHERE SalesOrderID = @SalesOrderId
SELECT * FROM dbo.ufnGetContactInformation(@ContactId)


The ASDK SQL adapter will handle the generation of a schema that conforms to all three result sets. Here is what the stored procedure schema looks like after it is selected in the Consume Adapter Service wizard as the Strongly-Typed Procedures outbound operation. Note how it has SalesOrderId as an input under the usp_GetSalesOrderDetails node, and returns all three strongly typed results:

By creating this stored procedure and using the multiple result sets feature we see the following advantages:

  • Only one connection is made to the database to extract all the sales order details

  • Only one schema is created for all three results, making it easier to use in maps

Once we have enriched our sales order information, we are going to write our SQL Response from SQL Server to disk so that we can see an XML document containing three different result sets.

The next step in the process is to update our SalesOrderTable with the status "Processed" for the processed orders. To accomplish this, we have a very simple map:

The final step in our orchestration is to write the result of our SalesOrderStatus table update to a file folder via the FILE adapter:

After the solution has been deployed, we need to configure our SQL Notification Receive Location. The sqlBinding settings is used to configure query notifications are:

  1. 1. InboundOperationType must be set to Notification.

  2. 2. NotificationStatement: This is where we set the SQL statement that the adapter will monitor for changes. When the results of the statement change, the adapter raises a notification. In this example we will configure it with a query very similar to the PolledDataAvailableStatement that was used for typed polling in Example 1. For the notification query we use a select statement that checks for sales orders not yet processed:

    SELECT SalesOrderID FROM Sales.SalesOrderStatus
    WHERE ProcessingIndicator IS NULL and OrderStatus ='Pending'
  3. The NotifiyOnListenerStart property generates a notification whenever a receive location is enabled. This ensures that BizTalk will retrieve any updated data from SQL Server while the Receive Location was disabled. In our solution we want to process any outstanding sales order as soon as possible so we will leave it set to True.

Here is what the Binding Properties tab looks like for our Notification Receive Location after it has been configured as discussed above:

Other -----------------
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Sending email using Outlook
- Microsoft Dynamics AX 2009 : Integration with Microsoft Office - Exporting data to Microsoft Project
- Microsoft Content Management Server : The ASP.NET Stager Application (part 3) - Staging Attachments
- Microsoft Content Management Server : The ASP.NET Stager Application (part 2) - Staging Channels and Postings
- Microsoft Content Management Server : The ASP.NET Stager Application (part 1) - The DotNetSiteStager Project, Recording Messages to a Log File
- Microsoft Content Management Server : Staging Static Pages - Site Stager in Brief
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 2)
- BizTalk 2010 : WCF LOB SQL Adapter - Consuming ASDK SQL Adapter in Visual Studio (part 1)
- Windows Server 2008 Server Core : Renaming a File with the Ren and Rename Commands, Sorting File Content with the Sort Utility
- Windows Server 2008 Server Core : Moving Files and Renaming Files and Directories with the Move Command, Recovering Lost Files with the Recover Utility
Video tutorials
- How To Install Windows 8 On VMware Workstation 9

- How To Install Windows 8

- How To Install Windows Server 2012

- How To Disable Windows 8 Metro UI

- How To Change Account Picture In Windows 8

- How To Unlock Administrator Account in Windows 8

- How To Restart, Log Off And Shutdown Windows 8

- How To Login To Skype Using A Microsoft Account

- How To Enable Aero Glass Effect In Windows 8

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen
programming4us programming4us
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone