BizTalk Tips & Things

Agustin Mantaras BizTalk Blog

Querying a SQL Server table using Business Rules


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


  • 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:



How to do it using Business Rules:


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


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


#3# Give a Definition Name a description


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


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


#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


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


#4# This is what we are going to:


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   🙂


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


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


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


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


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


How to call this Business rule form BizTalk

Overall picture:


#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);  
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:



Done! Enjoy BusinessRule querying!   😉