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

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()=
'http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/usp_GetSalesOrdersToProcess'])"));


					  

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]
AS
SET no count on
setxact_aborton
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @ContactId int
SELECT * FROM Sales.vSalesOrderDetailProduct
WHERE SalesOrderID = @SalesOrderId
SELECT
at.Name, a.AddressLine1
, a.AddressLine2, a.City
, sp.StateProvinceCode, cr.Name as Country
FROM
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:

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