Database Direct Access

You can have RuleLab.Net rules engine run SQL commands on your database including selects, updates, inserts and stored procedures. The rules engine will automatically execute your commands at runtime to move data between the application XML and your database. Through entering SQL commands into the Rules Designer, you can make the rules operate directly on your application’s database. 

This feature is optional. It is only intended for users who wish the rules to read/write data into the database in addition to writing it into the application XML document. If you would like to utilize this feature, you should have your SQL Server database ready for accessing through the BRE.

Database Connectivity

SQL commands can be entered and associated with Atoms via the Atoms Editor. The BRE chooses which command to run depending on the run-time operation. If a rule needs Atom value, either in a condition or action, the BRE will run the “select” command. When an Atom is assigned to, the BRE runs the “update” command. When an action is adding new nodes to application XML, the “insert” command is launched.

Database connectivity does not bypass application XML update. Your system can rely on BRE managing the application XML document regardless of database commands presence. By using SQL commands you can avoid writing code for moving data between the database and the XML document.

Data Field Mapping

The “select” commands match XML references with database fields by name. If you wish to populate an application XML with data retrieved from the database then XML References must have same names as the corresponding fields in the dataset. For example, when a query such as “select * from mytable” is executed, the BRE matches the names of the retrieved columns with the XML references of the Atom that initiated the query.

Connection String

In the client-hosted solution, database connection string must be entered via the administrator console. This setting will be utilized by the Rules Designer’s Test screen and the web service. The connection string is encrypted and stored on the web server along with other client settings.

BRE component also requires the connection string. It should be set via the AppDatabaseConnStr property of the BRE. If you do not wish to store the connection string anywhere then use the BRE component as opposed to the web service.

SQL Commands

Filling each of the available commands is optional. You do not have to specify all 3. Depending on how an Atom is utilized by the rules you can enter only the “select” or only the “update” command.

Select

The “select” command is executed when BRE is looking for data. Data values retrieved from the database is copied into the application XML. If the query was initiated by a loop atom then XML node(s) will be created in the application XML for each row in the dataset.

If the BRE encounters identical SQL “select” commands it will only run the command once and cache the dataset. Subsequent commands referring to the same query will not go to the database server but will use cached data instead. For example, if Atom A and Atom B are both utilizing “select * from mytable where DateOrdered < @CouponExpirationDate” query then the second Atom will pull data from the dataset retrieved for the first Atom. “First” vs. “second” is determined by the rules processing order.

A “select” command will not modify application data that has already been filled or changed by rules.

Update

The “update” command is executed by the action’s “Set” command when it changes data in the application XML. For example, if you have Atoms “CustomerPhoneNumber” and “CustomerID” then the “update” command could look like this: “update customers set phonenumber = @CustomerPhoneNumber where customerid = @CustomerID”.

Insert

The “insert” SQL command is triggered by the action’s “Add” command. When an action adds new XML nodes to the application XML it also runs the “insert” command to add new rows to a database table. For example, if you are adding a new customer record via a multi-reference Atom containing “CustomerID” and “CustomerName” references (marked as Atoms) then you could enter the following command: “insert into customers (customerid, name) values (@CustomerID, @CustomerName)”.

Stored Procedures

You can utilize SQL commands to call stored procedures. For example, if your database contains “AddCustomer” stored procedure then the above “insert” command could change to the following: “exec AddCustomer @CustomerID, @CustomerName”.

Command Parameters

The parameters that SQL commands recognize are names of the Atoms. In above samples we used @CustomerID and @CustomerName parameters. This implies that there are Atoms named CustomerID and CustomerName correspondingly. Before executing an SQL query containing parameters, the BRE looks up their data in application XML and replaces the parameters with data values.

If the parameter name is matching a single-reference Atom that itself contains a “select” command, then the parameter will be filled with data pulled from the database. Otherwise, the parameter will be filled with data from the application XML. Note that if this Atom has already been changed by rules then no data lookup will occur.

Example

The following online sample project will show you how to pull data into an application XML via the “select” command. The sample uses Customer table of Northwind database. You can open the sample at

http://www.rulelab.net/myrules.aspx?demo=SQLSample

Note that SQL updates and inserts are disabled in the online (ASP) demos and free trials. The “select” query is fixed to the one displayed in the Atoms editor. Changes to “select” queries will not be saved. If you wish to experiment with the direct database access in your online free trial please work the preset query into your project.