Many .NET developers would consider BizTalk development to be a bit of a dark art.
Diving in a bit deeper, many BizTalk developers would consider development using the BizTalk Business Rules Engine to be a dark art in itself.
Just to make things even worse, even for those who have dabbled with the BizTalk Business Rules engine, using SQL based facts is a major dark art too.
I would absolutely agree with the last statement, and one of the main reasons for this is the lack of good examples to be found. I have scoured the internet and many book trying to learn about this and found very few resources that helped me fathom this beast. I’ll try to explain how you can create vocabularies and policies that can be used to query SQL Server databases and how you can call on these policies from BizTalk orchestrations. As I’ve said this is a bit of a dark art and thus this blog post might be a bit long-winded.
Before we dive into the example, let’s create a little imaginary scenario. Let’s say we have a table called persons (truly an enterprise-scale example) which contains a list of people’s names and contains a primary key. The table definition and some example records are below.
Now let’s say that we have the below XSD definition for a transaction. The idea is that we are going to have a message which contains a BuyerName and we want to use the BizTalk Business Rules Engine to resolve the BuyerID.
Well, first thing we need to do of course is to define a vocabulary for the schema. I’m not going to go over this in detail since this is a very well documented process (open any of the BizTalk cookbook type books and you’re sure to find examples of creating vocabularies based on XML schemas) but you want to create a vocabulary that contains a definition to get BuyerName and set BuyerID (make sure you publish the vocabulary after creating it), and for the sake of further examples some definitions to get SupplierName, set SupplierName, and set SupplierID.
Next up we need to define a vocabulary that represents the SQL Server database. First up let’s create a definition to get the Name from the SQL Server database, let’s call this definition GetSQLPersonName. Open the Business Rules Composer, create a new vocabulary and add a new definition. Choose to make this a Database Table or Column based definition (while Microsoft has named this a “Database” type definition, keep in mind that this will only work with SQL Server, there is no native support for any other types of database).
On the next screen let’s type in the definition name, leave the binding type at the default “Data Connection” and click browse.
On the next screen choose the appropriate SQL Server name and your authentication type and click ok.
You should now be prompted with the Bindings screen on which you need to choose which column in which database you are going to bind this particular definition to. Let’s browse to our example database and choose the appropriate column (note that the table just generically stores persons rather than Buyers so the column name is just called name) and click ok.
Now we’re back to the definition screen. Let’s choose the Get operation for this definition and we are finally done.
Now lets assume that we have repeated the same process to create a definition for GetSQLPersonID. Now that we have a well-defined vocabulary, right-click on the vocabulary version and choose to publish it so that it is now available for use within policies.
It is now time to create a policy. Luckily since we have created some vocabularies this is going to look like a very simple policy. In the business rules composer, choose to add a new policy and a new rule within the policy. Create your rule such that it looks like the below and publish your policy.
It’s now time to test out our policy. First things first let’s create an instance of the XML file that contains a BuyerName with a value of James and a blank BuyerID (note that if rather than having a blank value in this element you didn’t supply the element at all then the policy would throw an exception, there are ways around that which include having the rule adding the required XML node but that is a post for another day). Now let’s right-click on our policy version and choose to test the policy.
In order to test this policy we need to assert the appropriate facts that are expected by said policy, in this case an XML file and a link to the appropriate database tables. First of all click on the XML Document based fact (BRESQL.Transaction in the above screenshot) and choose add instance; on the next screen browse to the example XML file that we created. Now click on the Database Table based fact (BREExample:Persons in the above screenshot) and choose add instance; browse the binding screen and make a link to the persons table, much like when we created the definitions in the SQL vocabulary except in this case we are going to link to the table rather than the individual columns. You should now be able to click Test. The test output will be displayed in the business rule composer and the XML document that was asserted into the test policy should now be updated. Success!
Now the obvious question one should ask is why did this policy work at all? How did the business rules engine know to fetch an ID with a value of 3 from the record in the Persons table that contained the Name with a value of James? The real key is the “Instance ID” that was set in the definitions screen for the GetSQLPersonName and GetSQLPersonID definitions. Since they both had a value of 0, and both are on the same table, then if you use both definitions in a rule then that effectively means that they are referring to the same record. Think of the SQL definitions being used in the conditions as forming a where clause in a SQL statement, and those used in the actions section as being part of the select clause. In the case of the example rule we just created the SQL statement would look like the below.
To truly express this lets work on another example, except this time let’s try to resolve both the BuyerID (from the BuyerName) and the SupplierID (from the SupplierName) in a Transaction XML message. Go ahead and create a new version of your SQL based vocabulary and this time create an additional pair of definitions that look exactly like the GetSQLPersonName and GetSQLPersonID ones, except this time set the Instance ID on both of these to 1, lets assume the definitions are now called GetSQLPersonName1 and GetSQLPersonID1. Note that you can just copy and paste the existing definitions; you’ll be prompted to give the new definitions a name and then you can double-click on them and choose to update the Instance ID.
Let’s also create a new version of our policy with a rule that looks like the below.
Let’s test out this policy again, but this time let’s assert an instance of an XML document that contains a BuyerName of Slim, a SupplierName of Jim, and blank elements for the BuyerID and SupplierID nodes. Note that even though we now have vocabulary definitions used in our policy which refer to the Person table in the SQL database with different Instance IDs, we still only have to assert one fact that links to the Persons table. The resulting XML file should now look like the below.
That illustrates a bit further the importance of Instance IDs. The resulting SQL statements that would be executed for the above rule are as below.
Well, what good are SQL queries unless you can execute joins across multiple tables. For the purpose of this example, let’s introduce an additional table called BuyerSupplier (my table naming conventions should illustrate why I did not become a database designer) which is used to link an ID from the Persons table (in the BuyerID column) to another ID in the Persons table (in the SupplierID column). The goal of the next example is to pass in an XML Transaction message to the Business Rules Engine with the BuyerName element filled in with a value of Johnson and with blank BuyerID, SupplierID, and SupplierName elements and have the rule fill these values in.
Lets now create a new version of the SQL Vocabulary and this time create additional definitions that link to the BuyerID and SupplierID columns in the BuyerSupplier table (they should both have the same Instance ID, lets use 0), lets call these definitions GetSQLBuyerIDLink and GetSQLSupplierIDLink. Let’s publish the vocabulary and create a new version of the policy with a rule that looks like the below.
Let’s choose to test this policy, note that this time you will also have to add a fact instance for the BuyerSupplier table. You should now get the below output.
This time the rule would have resulted in the below SQL Statement.
That should now give you enough ammunition to create vocabularies and policies using the Business Rules Composer but how about actually calling on these policies within a BizTalk orchestration? There are a few additional steps to call on a policy that makes use of SQL based facts.
First of all, in your orchestration project you need to add a reference to System.Data, System.Transactions, and Microsoft.RuleEngine (the path to this would typically be in the C:\Program Files (x86)\Common Files\Microsoft BizTalk\ folder, remove the (x86) in the folder path if you’re not on a 64-bit machine).
The next thing you need to do in your orchestration is to create an atomic scope. In this atomic scope you need to create a variable of type System.Data.SqlClient.SqlConnection (let’s call this SQLConn), and two variables of the type Microsoft.RuleEngine.DataConnection (let’s call these variables PersonDataConn and BuyerSupplierDataConn). The reason we are using an atomic scope is because the SqlConnection and the DataConnection classes are not serializable and thus can only be declared within an atomic scope.
Next up we need to create an expression shape within the atomic scope which needs to look like the below.
As in the above screenshot we first need to set the ConnectionString against the SQLConn variable. I would suggest that unlike the above example you fetch the value for this from a configuration store such as the SSO database instead of hardcoding it (this is of course necessary to ensure that you can point to the relevant database for each BizTalk environment as you migrate your application from it’s dev environment through to production). You then need to open the SqlConnection. You then need to instantiate the PersonDataConn and the BuyerSupplierDataConn objects passing in the name of the database, the name of the relevant table, and an open SqlConnection into it’s constructor.
You are now ready to call on the policy within the orchestration. Drag a call rules shape into the atomic scope below the expression shape and set it up so that it looks like the below (MsgTransaction.Body is the XML message in this case).
Note that you didn’t need to (or are able to) assert the SQLConn object as a fact for this policy, you only need to assert the DataConnection objects and the XML message. Also note that you only need to assert one DataConnection object per table you are dealing with, regardless of how many definitions with different Instance IDs are being used by this policy.
Lastly you want to clean up the SQL connection within an expression shape that is located after the call rules shape that should look like the below.
A really simple orchestration that receives a Transaction message, sets up and calls the BizTalk Business Rules policy that we setup earlier, and then sends out the resulting Transaction message should look like the below.
You might ask how such an orchestration would perform? On my dev machine which is hosting BizTalk as well as the SQL Server database using a default BizTalk Server install and the default host instance I had the orchestration complete successfully within 23ms. Performance might be a tempting factor to use the Business Rules Engine when dealing with SQL Server rather than the WCF-SQL adaptor as you get to avoid hops through the Message Box to send your request (and optionally receive your response back to the orchestration). Note that because you are forced to use an atomic scope within the orchestration you will still end up incurring a persistence point (in the above screenshot this wouldn’t really have any effect as the persistence point generated by the send shape, the end of the atomic scope, and the end of the orchestration would all be rolled into one).
You of course will also need to ensure that the host instance that the orchestration is contained within has an identity with the required permissions for this database if you’re using integrated security, or that your connection string contains the appropriate credentials to connect to the database (all the more reason to think of using the SSO store to host your connection strings).
Something else to take note of is that the vocabularies can be somewhat sensitive to changes to the structure of the SQL Server database tables. I have gotten into a scenario where my policies refused to work due to some indexes not being applied. I would suggest making use of a testing framework such as BizUnit and executing the tests before deploying any changes to the database structure or to the Business Rule artifacts themselves.
In conclusion there is a lot of utility hidden deep within the recesses of the BizTalk Business Rules Engine, but much like BizTalk itself there is a lot of pain to be had while you are getting your head around the framework before you find yourself ready to take full advantage of its capabilities.
Adventures inside the Message Box 