Using C#, OpenXML, and Microsoft SQL Server 2000 to create a multi-threaded Rules Engine Webservice

• Download demo project - 16.3 KB

Introduction

This articles uses C#, OpenXML, and Microsoft SQL Server 2000 to create a multi-threaded Rules Engine Webservice.

Background

Seems like, these days, there is a lot of focus on rules engines. I guess since Microsoft has been touting BizTalk for a while as the solution to the problem of business rules engines, a lot of people have taken notice. But the one persistent problem is BizTalk is very expensive. It's a solution for a large company with vast resources. It is not a solution for smaller businesses who just need a simple, functional rules engine. Now, don't get me wrong; this solution I will be laying out here is not the be-all-end-all of rules engines. There are literally hundreds of ways to perform XML rule validation, and I'm showing you one. If you want a more simplistic rules engine, you can check out Moustafa's Extensible Light XML Rules Engine Component. If you want something more robust, yet a bit more complicated, you can check out Nxbre's .NET Business Rules Engine. While more complicated to configure often times, it does adhere to RulesML 0.86 datalog, something which my example is not designed to do. The example I will be detailing here (after this longwinded preamble) is designed to read fields from any XML document, and evaluate them against static rulesets. The last piece I'll add to this section is the following; this is a prototype. It is not designed to solve your process flow problems. It is designed to help suggest a possible path to solving those problems. As I said earlier, there are many ways of accomplishing this, and if you want an out-of-the-box solution, I would suggest looking into Microsoft's BizTalk Server. This solution is one of many that will hopefully present a methodology that will be simple to implement. As such, there will be many elements that will be left out, but can easily be implemented with a little extra code.

So, why SQL based?

In my experience writing business applications, everyone has legacy data issues. Many companies have XML documents that are inflexible with RulesML 0.86. They often have their own XML formats that are consumed by legacy business applications. You could, of course, create an XSLT sheet, convert all of the XML into the necessary format for your commercial business rules engine, and go from there. I've found, though, that XSLT can be tedious and time consuming to create, especially when you have an XML document that might have thousands of fields, or one that changes with any frequency (cue the XSLT banner wavers' comments here :)). That, and I'm lazy, so XSLT is irritating to me to use effectively. I'd rather utilize the existing business XML document's schema, since without a doubt, there will be other applications that consume that XML. Additionally, I'm not entirely pleased with the performance of XSLT when dealing with documents containing more than 40 nodes.

The other crux of the issue is simple: scalability. This SQL rules engine is simple to modify on the fly. I've found that most Business Analysts are semi-knowledgeable with Structured Query Language, or, if not, can be easily taught. This rules engine can be scaled simply by editing the Stored Procedures in the SQL Server, rather than making code changes to the engine itself, and having to recompile/redeploy. In this way, rules can be added, removed, and modified on the fly. Deployment is ridiculously simple (just script the database...you don't need to modify the Webservice at all), and OpenXML is, all things considered, pretty darn quick, even when managing extremely large documents with more than 200 nodes. (See the performance results at the end of this document.)

What will I need?

Since we're designing the Webservice portion of this in C#, you will need a C# compiler. This example uses Visual Studio 2003. You will also need access to a server running MS SQL 2000, with DBO permissions on the database you will be using, and of course, a web server running at least IIS 5.1. This project is heavier on SQL than it is on C#, so a good working understanding of SQL is necessary.

Starting the project

Setting up the IDE

Open Visual Studio .NET and create a new project. Select Visual C# Projects, and select ASP.NET Webservice. Use whatever name you'd like (this example calls it XRules). Once the new project loads, go ahead and right-click on Service1.asmx, and select "Delete". Then, right-click on your solution, select "Add->New Item", select Web Service from the list, and type XRules.asmx for the name. Click OK. Next, right click again on your solution, and select "Add->New Class". Type "engine.cs" for the class name, and click OK. The IDE is now setup the way we want it to be.

How it works

Our Webservice is simply an execution interface for firing off Stored Procedures on our SQL Server. The Stored Procedure and the SQL Server do nearly all of the actual work. All we are going to do with the Webservice is provide a portal to pass in an XML document and methods for executing threads to validate the XML document.

Setting up the SQL Server

Now that we have the IDE configured, let's go ahead and setup the SQL Server. Open Enterprise Manager, and create a new database. You can also use an existing database, provided that you have DBO permissions on it. This example will refer to the XRules database (makes sense, right?). Once the database is created, open the SQL Query Analyzer, and run the following scripts:

First, we create the table that will house all of our rules. These are simply pointers to the stored procedures that house our rules, but we'll need this table to multi-thread the execution engine.

if exists (select * from dbo.sysobjects where 
       id = object_id(N'[dbo].[XRules]') and 
       OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XRules]
GO

CREATE TABLE [dbo].[XRules] (
    [RuleGUID] AS (newid()) ,
    [ExecuteSP] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Now, we'll create a table [Errors] to house all errors that result during rule execution.

if exists (select * from dbo.sysobjects where 
       id = object_id(N'[dbo].[XErrors]') and 
       OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XErrors]
GO

CREATE TABLE [dbo].[XErrors] (
    [ErrorId] [bigint] IDENTITY (1, 1) NOT NULL ,
    [ErrorMessage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ErrorTimestamp] [datetime] NULL ,
    [RuleGUID] [uniqueidentifier] NULL 
) ON [PRIMARY]
GO

Finally, we'll create this optional table that will log execution performance to our database. This is useful for determining how quickly various rulesets are executed, and also for ensuring that the engine did indeed execute your rules on multiple threads.

if exists (select * from dbo.sysobjects where 
        id = object_id(N'[dbo].[Performance]') and 
        OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Performance]
GO

CREATE TABLE [dbo].[XPerformance] (
    [PerformanceId] [bigint] IDENTITY (1, 1) NOT NULL ,
    [RuleGUID] [uniqueidentifier] NULL ,
    [ProcessStart] [datetime] NULL ,
    [ProcessEnd] [datetime] NULL 
) ON [PRIMARY]
GO

Writing the engine

OK, now that we have our general framework set up, it's time to write the execution engine. I'm going to write the more complex (in theory, only...this example will be quite non-complex to intermediate programmers) of the examples that will utilize the System.Threading namespace. Assuredly, you can perform the same executions on a single thread, but threading the application should give a nice boost in performance.

The Engine class

using System;
using System.Data;
using System.Threading;
using System.Data.SqlClient;
using System.Xml;

/// <SUMMARY>
/// Contains threaded methods for validating SQL server based rules.
/// </SUMMARY>
public class engine
{
    //The xmldocument we want to validate
    private XmlDocument xDoc;

    public engine(XmlDocument xml)
    {
        xDoc=xml;
    }

    public void validateRules()
    {
        //Instantiate our SQL connection
        string connString = 
           System.Configuration.ConfigurationSettings.AppSettings["DBConn"];
        SqlConnection cn = new SqlConnection(connString);
        string strSql = "SELECT * FROM XRules";
        SqlDataAdapter adapter = new SqlDataAdapter(strSql, cn);
        DataTable dt = new DataTable("Rules");
        try
        {
            cn.Open();
            adapter.Fill(dt);
        }
        catch (Exception exc)
        {
            //To-do: Log this exception to the database.
            throw exc;
        }
        finally
        {
            cn.Close();
        }
        foreach (DataRow r in dt.Rows)
        {
            validator v = new validator();
            v.executeSP = r["ExecuteSP"].ToString();
            v.ruleGuid = new Guid(r["RuleGUID"].ToString());
            v.connString = connString;
            v.xDoc = xDoc;
            Thread vThread = new Thread(new ThreadStart(v.validateRule));
            vThread.Start();
        }
    }
}

The Validator class

public class validator
{
    //The string of the Stored Procedure we want to execute
    public string executeSP;
    //The guid of the Stored Procedure we want to execute
    public Guid ruleGuid;
    //The xml to validate
    public XmlDocument xDoc;
    //The connection string to the server
    public string connString;

    //Validate each rule in the table on a separate thread.
    public void validateRule()
    {
        SqlConnection cn = new SqlConnection(connString);
        SqlCommand cmd = new SqlCommand(executeSP, cn);
        //Pass the GUID of the rule as a parameter (for error logging)
        cmd.Parameters.Add(new SqlParameter("@RuleGUID", ruleGuid));
        //Pass the xml document's inner XML to the SP to use in validation
        cmd.Parameters.Add(new SqlParameter("@xml", xDoc.InnerXml));
        cmd.CommandType = CommandType.StoredProcedure;
        try
        {
            cn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception exc)
        {
            //To-do: Log this exception to the database using the provided ruleGuid.
            throw exc;
        }
        finally
        {
            cn.Close();
        }            
    }
}

What's happening?

All I did was create a basic framework to execute a list of Stored Procedures on separate threads. As I said before, the brunt of the rule validation is done on the SQL Server side. All we need to pass to the Stored Procedure is the XML document (InnerXml) and the GUID of the rule we're executing. We could perform a GUID lookup in the rule Stored Procedure itself, but it saves us a SQL statement by just instantiating it when we run the Stored Procedure. So, like a delegate, we will be passing each ruleset a pair of arguments; the GUID, and the XML. In this way, we can ensure a consistent approach to designing our rulesets.

The Webservice

Now that we've written the "meat and potatoes" of our C# engine, it's time to expose these methods through a Webservice. Go ahead and open up your XRules.asmx page, and switch to the code view. You will need to include System.Xml as a reference. I've given two examples below (use either or both):

/// <SUMMARY>
/// Evaluates an Xml file with a given file path.
/// </SUMMARY>
/// <PARAM name="xmlFilePath"></PARAM>
[WebMethod]
public void Evaluate(string xmlFilePath)
{
    try
    {
        XmlDocument xDoc = new XmlDocument();
        xDoc.Load(xmlFilePath);
        engine e = new engine(xDoc);
        e.validateRules();
    }
    catch (Exception exc)
    {
        //To-do: Add exception logging to the database here.
        throw exc;
    }
}

/// <SUMMARY>
/// Evaulates a local copy of the books.xml file.
/// </SUMMARY>
[WebMethod]
public void EvaluateLocal()
{
    try
    {
        XmlDocument xDoc = new XmlDocument();
        xDoc.Load(Server.MapPath("books.xml"));
        engine e = new engine(xDoc);
        e.validateRules();
    }
    catch (Exception exc)
    {
        //To-do: Add exception logging to the database here.
        throw exc;
    }
}

We are loading our XML document from a path, or, using server mapping to load the file directly. Of course, you can change the method to accept an XML document object in the method, but we'll keep it simple for now. A possible future implementation (if you really want to get excited about threading) is to thread these methods if you need to load a pool of XML documents. If, for example, you had a directory that contained a couple hundred XML documents that you needed to process through the engine, you could loop through the directory and spawn a thread to process each document. Of course, this could also drive your DBA nuts with the quantity of connections that would be opening and closing, but sometimes it's fun to keep them on their toes.

Finally, ensure you add a key setting to your Web.config file for the connection string used to connect to your local instance of SQL Server. (See example in download.)

Writing the Rules

The XML document

OK, we're almost done! We've created our Webservice, and the engine to execute our ruleset. Now, we need to create a couple of test rulesets to execute, and of course, an XML document to utilize in our testing. We'll use the industry standard books.xml as the XML document we wish to evaluate. Just download the XML, and save it to your project as "books.xml".

The first ruleset

The first ruleset example we will assemble will be a simple ruleset that ensures that the price field for each book is between 0 dollars and 10 dollars. You can make this check a couple of ways, but for the purpose of illustration, we'll check each one separately, effectively giving us two rules to evaluate.

In Enterprise Manager, select the database you created earlier (XRules), and select the Stored Procedures node. In the window at the right, select "New Stored Procedure". The default Stored Procedure editor will open. Copy and paste the code below into the editor, then click "OK".

CREATE PROCEDURE [dbo].[XRE_Prices] 

@xml NTEXT,
@RuleGUID uniqueidentifier

AS
    --Performance Logging. You can comment this out 
    --if you don't want to log performance.
    INSERT INTO XPerformance(RuleGUID, ProcessStart) 
           VALUES (@RuleGUID, getdate())

    --Handle to the in-memory XML document
    DECLARE @idoc int

    --Create an in-memory table to store our book data
    DECLARE @Books TABLE (Price money)

    --Create an internal representation of the XML document
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

    --Execute a SELECT statement that uses the OPENXML rowset provider
    --We will select all books from the document, 
    --and insert them into our temp table for querying

    INSERT INTO @Books (Price) (SELECT * FROM OPENXML 
           (@idoc, 'bookstore/book',2) WITH (price money))

    -- Clear the XML document from memory
    EXEC sp_xml_removedocument @idoc

    /****** RULE SECTION ******/

    DECLARE @count int

    /* --- RULE 1: Check for prices <= 0 --- */
        
    SET @count = (SELECT COUNT(Price) FROM @Books WHERE Price <= 0)
    IF (@count > 0)
    BEGIN
         INSERT INTO XErrors (ErrorMessage, ErrorTimestamp, RuleGUID) 
                VALUES ('Located ' + CONVERT(varchar(10), @count) + 
                ' books with price <= 0',getdate(),@RuleGUID)
    END

    /* --- RULE 2: Check for prices >= 10 --- */

    SET @count = (SELECT COUNT(Price) FROM @Books WHERE Price >= 10)
    IF (@count > 0)
    BEGIN
         INSERT INTO XErrors (ErrorMessage, ErrorTimestamp, RuleGUID) 
                VALUES ('Located ' + CONVERT(varchar(10), @count) + 
                ' books with price >= 10',getdate(),@RuleGUID)
    END


    /****** END RULE SECTION ******/

    --Performance Logging. You can comment this out 
    --if you don't want to log performance.
    UPDATE XPerformance SET ProcessEnd = getdate() 
           WHERE RuleGUID = @RuleGUID
GO

The above ruleset should save without any problems. If you get a syntax error, chances are you have an invalid column name in a table, or an invalid table name. Just check your syntax, and save again. We'll go ahead and create one more ruleset, then I'll explain in detail what's going on behind the scenes. Since Melville's "The Confidence Man" is over $10.00, this rule should kick out one error.

Points to notice

You may have noticed the "2" parameter following bookstore/book. bookstore/book is of course the XPath of the element we are looking for. OpenXML allow us to specify element-centric, attribute-centric, or both to our XML mapping. Attribute-centric is indicated by using a "1" parameter (as you will see in the example below); "2" specifies element-centric, and "8" specifies to search for both, though attribute-centric will be utilized first. Using "0" will default to element-centric. You can find a wealth of examples on this MDSN site.

The second ruleset

In this ruleset, we'll evaluate each genre attribute. Notice the change in the XPath. We're now looking to select an attribute, instead of the node text. For more on XPath queries and OpenXML in general, there is a great article at Perfect Xml. We will be using an attribute-centric flag for this lookup.

CREATE PROCEDURE [dbo].[XRE_Genres] 

@xml NTEXT,
@RuleGUID uniqueidentifier

AS
    --Performance Logging. You can comment this
    --out if you don't want to log performance.
    INSERT INTO XPerformance(RuleGUID, ProcessStart) 
           VALUES (@RuleGUID, getdate())

    --Handle to the in-memory XML document
    DECLARE @idoc int

    --Create an in-memory table to store our genre data
    DECLARE @Genres TABLE (Genre varchar(50))
    
    --Create an internal representation of the XML document
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

    --Execute a SELECT statement that uses the OPENXML rowset provider
    --We will select all books from the document, 
    --and insert them into our temp table for querying

    DECLARE @genre varchar(50) 
    SET @genre = (SELECT * FROM OPENXML (@idoc, 'bookstore/book',1) 
                  WITH (genre varchar(50)))
    INSERT INTO @Genres (Genre) VALUES (@genre)

    -- Clear the XML document from memory
    EXEC sp_xml_removedocument @idoc


    /****** RULE SECTION ******/

    DECLARE @count int

    /* --- RULE 1: Ensure genre is not = 'Philosophy' --- */
        
    SET @count = (SELECT COUNT(Genre) FROM @Genres WHERE Genre = 'Philosophy')
    IF (@count > 0)
    BEGIN
         INSERT INTO Error (ErrorMessage, ErrorTimestamp, RuleGUID) 
                VALUES ('Located ' + @count + 
                ' books with genre of Philosophy.',getdate(),@RuleGUID)
    END


    /****** END RULE SECTION ******/

    --Performance Logging. You can comment this out 
    --if you don't want to log performance.
    UPDATE XPerformance SET ProcessEnd = getdate() WHERE RuleGUID = @RuleGUID
GO

Now, in the above ruleset, we will kick out an error message if we have any books in our XML document that have a Philosophy genre. Since Plato's "The Gorgias" is a book on Philosophy, this will kick out one error.

Incorporation

OK, our rulesets are created! Now, all we need to do is add the rulesets to our XRules table, and we'll be ready to rock and roll! Just open up your XRules table in Enterprise Manager, and add XRE_Prices and XRE_Genres to the ExecuteSP column. We have SQL Server generating our unique identifier for us already, so when you're finished, just close and save the table. You can check the table to ensure the GUIDs were properly generated by reopening it, or just running a simple SELECT * FROM XRules.

Behind the scenes

As you saw when we wrote the engine, all that is happening is that the Webservice is loading up each ruleset from the XRules table. It is then spawning a thread, and executing each Stored Procedure on its own thread. The procedure consumes the XML using OpenXML, and selects the requisite fields from the XmlDocument. We then load these values into a temporary table, query the table using our rules, and then kick out an error message if the rule fails. Once complete, we can then have a method on our Webservice that would (possibly) return a list of these errors to the user, or select an error count, and if greater than 0, return a failure to the web user. I'll leave that part to your discretion.

Frequently asked questions

Q: Why use an in-memory table instead of a cursor or a temp table?

A: Performance, primarily. When a temp table is used, the SQL server performs a lock on the tempdb database until the transaction has completed. Cursors are quick, but require plenty of disk I/O, and can be difficult to manipulate for people unfamiliar with VSAM or ISAM database environments. Creating in-memory table variables utilize only RAM memory on the SQL Server, and don't require any locks on the database (paraphrased from Peter Bromberg's article).

Q: Why do you have two separate Stored Procedures for only three rules?

A: Mainly for illustration. Presumably, architects of a business ruleset will have hundreds, if not thousands of rules to evaluate. It's organizationally best to separate out each rule to evaluate a separate section of the XML, for ease of use and efficiency reasons. If you house all the rules on one Stored Procedure, there is really no reason to have it run on multiple threads. I've provided two sample rulesets here as an example. Feel free to add your own as you go along.

Q: Has this been performance tested?

A: Only under a nominal load. In a testing environment, this rules engine evaluated 100 XML documents of equal length (200 evaluated nodes) against 10 rulesets, with 10 rules inside each set. The result was that 10 threads were spawned from the Webservice, and average execution time for each thread was between 0.2s and 0.5s, with an average completion time per document of under 1 second. This test was run using MSSQL 2000, IIS 5.1, and dual Intel Xeon processors @ 3.19 GHz, each with 1024 KB of RAM. I would assume that a production SQL Server would be capable of much higher performance.

Q: How do I know what errors (if any) were thrown?

A: Just run "SELECT Count(ErrorId) FROM XErrors" to find out how many errors are in there. Since we logged the RuleGUID when the errors were thrown, we can easily tell where the error occurred. You can also handle exceptions in the Webservice by logging the error to the database (as in the example code).

Points of interest

As I've said before, this solution may not be commercially viable. Some portions of this are still experimental at this point, and this is the first working prototype. It worked exceptionally well in cases where I did not want to have to modify an existing XML document, and in cases where I wanted a rules engine that would be publicly available via a Webservice. If any of you utilize this methodology, I'd be very interested to hear how it worked, and what changes you might have made to the code or the SQL. The provided source code is a prototype; I've only just laid out the startup concept. Everything you need to get a working model up and running is here in this article, so it should at least provide a good roadmap (I hope!). The beauty is in the simplicity. You can effectively have a working rules engine up and running in a few minutes, and you, or various Business Analysts can easily modify (or add) Stored Procedures to evaluate the proper XML node. Happy coding!