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:

 

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:

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

So…

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

Comments

• Anonymous
  May 03, 2010
  Hello,In Step #5# I only see one parameter (RuleDataConn) in BTS 2009. In BTS 2006 I saw 2 (RuleDataConn & SQLConn).When I profile my database, my query never gets launched and I don't get a valid result or error. Am I missing something?Greetings,Sven
• Anonymous
  May 03, 2010
  Hey Sven,Have you try to test the bussines rule?I'm assuming you are not getting event log errors or whatever!Thankyou for taking the time!Agustín
• Anonymous
  May 25, 2012
  Is there any way I can access a view instead of a table?