Querying a SQL Server table using Business Rules

Pre-Requisites:

  • You will need some experience using the Business Rules Composer (some basic staff is taken for obvious)

Benefits:

  • There is no need to call a .NET component with SQL database helper functions or whatever
  • Business rules engine will catch the results.
  • If the requirement change, you can adapt the business rules on the fly with no need to redeploy the orchestration.

 

Use Case

The application must retrieve the picture path of a given customer from table tblCustomers on database Test and then perform several actions (no matter what)

Table looks like:

image

 

How to do it using Business Rules:

Steps

#1# You must create the XSD to call the Business rule engine in our case could be:

image

Where CustomerID will be an input parameter and Picture and Output.

 

#1# Add a New Definition to the desired already created vocabulary

#2# The vocabulary Definition Wizard will appear.  Select the option Database Table or Colum and click Next

image

#3# Give a Definition Name a description

image

#4# Click on browse button to select the binding field in the database

image

#5# Click OK and the definition wizard window will change to reflect:

image

#6# Select Perform a Get Operation since we are just querying the database

#7# Click on Finish and do the same steps for the Picture field

#8#Save the vocabulary definition and publish it.

 

Let’s create the policy!

#1# Add a new Policy called BRCustomers

#2# Add a new Rule to the policy and call it BRCustomers

#3# under the Facts Explorer, got to XML Schemas tab and right click the Schemas node to browse for the BR_Customers.XSD file

image

This is the fact we are going to use with the policy.

 

#4# This is what we are going to:

image

What is this?

Textual this means:

If the CustomerId field of the incoming BizTalk Message is equal to CustomerID Field on the table i will return the corresponding Picture field

So.. it is just a seek for the Customer ID Field.

The magic in here: the BusinessRule enginge will perform internally the T-QUERY :

SELECT Picture FROM tblCustomers

WHERE CustomerID = @CustomerId

And you do not need to do nothing   :-)

So…

#5#  From the Facts Explorer Drag the CustomerID Field  and drop  in the first argument of the is equal to predicate (already inserted)

image

#6# Now Click on vocabularies and Drag the CustomerID database field in the second argument of the is equal to predicate .

image

#7# Now Go to XML Schemas again, drag the Picture field  and drop it in the THEN Actions Section

image

#8# Go to Vocabularies TAB , drag the Picture database field to the assignment side of the THEN expression.

image

#9# Save Everything, publish and deploy de Business Rule

 

How to call this Business rule form BizTalk

Overall picture:

image

#1# add the Following references to the BizTalk project:

  • (Microsoft.RuleEngine) C:\Program Files (x86)\Common Files\Microsoft BizTalk\Microsoft.RuleEngine.dll
  • (System.Data) C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Data.dll

#2# Create the BusinessRule Scope (transactiontype = Atomic)

#3# At BusinessRule scope level create the following variables:

  • RulesDataConn of type Microsoft.RuleEngine.DataConnection
  • SQLconn of type  System.Data.SqlClient.SqlConnection
  • SQLTran of type System.Data.SqlClient.SqlTransaction

 

#4# IN the BusinessRulesConnect Expresion:

SQLconn  = new System.Data.SqlClient.SqlConnection(Connection string value);  
SQLconn.Open();
SqlTran = SQLconn.BeginTransaction();
RulesDataConn =new Microsoft.RuleEngine.DataConnection ("DataBaseName", "TableName", SQLconn,SqlTran);

#5# Insert a call BusinessRule Shape and configure it:

 

#6# Insert the UpdateComClose Expresion and type:

RulesDataConn.Update();
SqlTran.Commit();
SQLconn.Close();

 

Done! Enjoy BusinessRule querying!   ;-)