Business Rule Engine (BRE) | BizTalk Part-2

In this article, I would explain how to use database facts by using DataConnection and TypedDataTable binding in BRE.

In most cases, lookup value would be retrieved from database table and BRE provides this option to interact with database either through data connection or data set binding type. BRE has certain limitations like it can’t retrieve data from a view and even it can’t execute a store procedure, but these limitations can be overcome by using data set binding type approach. 
BRE with Data Connection Binding Type 
Let’s say we receive an inbound message and we would perform a lookup and update the message based on certain criteria as shown below,

• Rule 1: Look up based on Event Id and Document Name, if found update message; halt and exit the BRE execution. 
• Rule 2: Look up based on Document Name, if found update message; halt and exit the BRE execution. 
• Rule 3: If no match found, then finally update message with default data. 

Below is the Lookup data available in the table, 

Within the Policy Explorer, right-click the Policies node, and click Add New Policy. Give the policy name as BizTalkDJ.DBInboundDocMapping. By default, version 1.0 of the policy is created. Right-click the version, and choose Save. 

Before developing the rules, go to fact explorer then choose database tab and connect to the server then select the table against which policy would be fired from the database. In the properties window, have data connection as binding type. 

Add Rule and rename to HighPriority-DocNameWithEvent. Form the condition by choosing the appropriate predicate then drag and drop the XML Document element and SQL table column as shown below. To add Halt Function, right click on action and choose halt and select clear all rule firing. 
Add another rule and rename to MediumPriority-DocNameWithoutEvent and build the rule as shown below. 

Choose each rule and set the priority. Add Rule and rename to LowPriority-DefaultDoc and build the rule as shown.

Test Policy 
Right-clicking on Version 1.0, and then choose Test Policy, in a Test Select Facts window, under XMLDocuments node, select OrderRequest, and then click on Add Instance to choose the input request to your policy. To add Database fact, choose Database tables and then click Add Instance to add table. Then click on test to test the policy.

BRE with Dataset Binding Type 
We can go for TypedDataTable instead of DataConnection in the following instances: 
Performing complex queries to pull data from two or more table. 
Rule-chaining behavior is expected in a rule set. Calling the Update function on a DataConnection is not supported, but you could invokeDataConnection.Update in a rule using a helper method. When rule chaining is required, TypedDataTable is a better choice. 
Let’s say we receive an inbound message and we would perform a lookup and update the ProductCode based on ProductName in the message, below is the Lookup data available in the two tables, create a physical table ViewProduct with required data columns as shown below. 
This ViewProduct table will be used as dataset in BRE, where data would be loaded during running time in dataset. 

Within the Policy Explorer, right-click the Policies node, and click Add New Policy. Give the policy name as BizTalkDJ.DBDataSetLookUp.  By default, version 1.0 of the policy is created. Right-click the version, and choose Save. 

• Rule1: Perform Lookup based on ProductName, if available update ProductCode from DB. 

Go to fact explorer then choose database tab and connect to the server then select the table against which policy would be fired from the database. In the properties window, choose dataset as binding type.

Add Rule and rename to ProductLookUp and build and deploy the rule as shown by then drag and drop the XML Document element and SQL table column.

Executing Database Facts BRE in Orchestration 
In order to execute a BRE with database facts orchestration transaction level should be Atomic, develop a simple process that receives the inbound message and perform a lookup of ProductCode through BRE with Dataset then does another lookup using BRE with Data Connection, as shown below. 

Initialize Data-set and TypedDataTable
dbConnString="Data Source=localhost;Initial Catalog=BizTalkDJ;Integrated Security=SSPI;"; 
breSQLAda = new   System.Data.SqlClient.SqlDataAdapter("SELECT PC.ID as ProductID, P.ProductName, 
P.Description, PC.ProductCode FROM [Product] P inner join ProductCode pc on P.ProductID=PC.ID", dbConnString); 
breDataSet= new System.Data.DataSet("BizTalkDJ"); 
breSQLAda.Fill(breDataSet,"ViewProduct"); 
typedDataTable = new Microsoft.RuleEngine.TypedDataTable(breDataSet.Tables["ViewProduct"]); 

Initialize Data-connection 
dbConnString="Data Source= localhost;Initial Catalog=BizTalkDJ;Integrated Security=SSPI;"; sqlConn= new System.Data.SqlClient.SqlConnection(dbConnString); 
sqlConn.Open(); 
breConn= new Microsoft.RuleEngine.DataConnection("BizTalkDJ","InboundDocMap",sqlConn); 

Build and deploy the application, test the process by placing orders and verify the output file. 

Conclusion:
In this article, I covered how to develop BRE policies with database facts. Now you can apply this solution to your own business scenario. 

The video demonstration is available on the Youtube, here is the embedded video.