A single-file function parser to use with WPF, Silverlight, and SQL Server CLR

• Download source - 256.4 KB
• Download demo - 205.3 KB

Introduction

Some time ago I faced an interesting problem while I was working on classical 3-tier architecture. I discovered that on every tier there was a very similar necessity for dynamically extending Xml and html data with variables and calculated values taken from the database and from customer input. This automatically led to the idea of using something like a math parser. Ideally this parser should be able to process expressions like “IIF([var1] > 5, ‘OK’, ‘To Low’)” to make using them easy and understandable for normal Excel users.

No problem, I thought, certainly I will find a neat and ready to use solution on my favorite coding portal – codeproject, but …

After investing some time to test the code of three very promising solutions I always stumbled over the same critical points:

• My requirement was to use exactly the same code in all my tiers, to ensure the same behavior within all parts of the application without having to test it over and over after a small change. That meant the same code should work without changes for a SQL Server CLR, a WPF Service, a WPF Client and a Silverlight Client.
• I didn’t want to have a complete parsing “framework” or an extra assembly, but a small solution, at best a single file. 

Adapting the existing solutions to these requirements turned out to be very time consuming. So, particularly because there was no need for a full featured math parser, I decided to write my own simple function parser and to share my experience with the community.

Background

The goal of this implementation is:

• To keep all functionality in a single-file to allow simple sharing between different projects. 
• To be fully compatible with WPF, Silverlight and SQL Server CLR applications. 
• To use a simple function syntax, so that a regular user with EXCEL skills is able to understand it. 
• To be able to use Xml Path queries in an easy way. 

The goal of this implementation is not:

• To be a full featured math parser. 
• To claim the speed record in parsing millions of lines. 
• To impress with its complexity and tons of functions. 

Using the code

All you need to do is to add the FunctionParser.cs into your project, so let’s start with a simple example:

Adding variables manually  

Create a dictionary:

Dictionary<string, object> variables = new Dictionary<string, object>();

Add some values:

variables.Add("doubleVar1", 3.0);
variables.Add("doubleVar2", 7.5);

And be happy:

string result = FunctionParser.Parse("The sum of [doubleVar1] and " + 
      "[doubleVar2] is {SUM([doubleVar1], [doubleVar2])}\n",  variables);

You will get the string “The sum of [doubleVar1] and [doubleVar2] is 10.5”.

Using Xml data (1) 

Now we can dare to play with a more complex example. Assuming we have some Xml data from the database which is stored in a string variable called customData that we want to use e.g. to complete a form letter:

<Columns>
    <Contact>
        <Column Id="Gender">Female</Column>
        <Column Id="FirstName">Carol</Column>
        <Column Id="LastName">Holland</Column>
    </Contact>
    <Address>
        <Column Id="StreetNumber">456</Column>
        <Column Id="Street">School Road</Column>
        <Column Id="ZIP">GA 50001</Column>
        <Column Id="City">Marietta</Column>
    </Address>
    <Company>
        <Column Id="Position">Director of Education</Column>
        <Column Id="Name">The Wontimal School</Column>
    </Company>
</Columns>

So how can we get it to work together with our function parser? To simplify it, there is a class called XmlVariableContainer:

XmlVariableContainer container = new XmlVariableContainer(customData, "//Columns//Column", true);

The first parameter is our Xml data. The elements used as variables must have an Id attribute, because we need a variable key, but it isn’t mandatory to call them „columns“. The second parameter is an XPath query to enumerate these elements. The last parameter determines if the parent node name will also be used as a key part. If this parameter is set to true, we can access the generated variables using a [Parent.child] notation, so that the text remains more readable.

The XmlVariableContainer uses a simple Dictionary<string, object>, so that we can access the generated values like this:

string firstName = container.Variables["Contact.FirstName"];

Now we can easily use the parser again:

string sampleLetter = File.ReadAllText(@"Debug\Form Letter Example\SampleLetter.html");
string result = FunctionParser.Parse(sampleLetter, container.GetValue); 

Using Xml data (2) 

The <column Id=“..“ > notation is sometimes unpractical, e.g. if you want to extract your xml data from the database (see „SQL Server CLR example“). In this case we also can use following notation:

<Data>
    <Contact>
        <Gender>Male</Gender>
        <FirstName>Carol</FirstName>
        <LastName>Holland</LastName>
    </Contact>
    <Address>
        <StreetNumber>456</StreetNumber>
        <Street>School Road</Street>
        <ZIP>GA 50001</ZIP>
        <City>Marietta</City>
    </Address>
    <Company>
        <Position>Director of Education</Position>
        <Name>The Wontimal School</Name>
    </Company>
</Data>

To achieve the same result we got in the previous example („Using Xml data 1“) we have to change the construction of our XmlVariableContainer a little bit:

XmlVariableContainer container = new XmlVariableContainer(customData, "Data//*//*", true);

Using Xml data with Xml variable content

Until now we talked about generating variables like strings or numbers via Xml, but what happens if we need Xml Data as variable content?

<Variables>
    <Column Id="DoubleValue1">12.33</Column>
    <Column Id="DoubleValue2">0.5</Column>
    <Column Id="XmlValue" Type="xml">
        <Rows>
            <Row>
                <Column Id="Gender">Female</Column>
                <Column Id="FirstName">Carol</Column>
                <Column Id="LastName">Holland</Column>
                <Column Id="StreetNumber">456</Column>
                <Column Id="Street">School Road</Column>
                <Column Id="ZIP">GA 50001</Column>
                <Column Id="City">Marietta</Column>
            </Row>
            <Row>
                <Column Id="Gender">Male</Column>
                <Column Id="FirstName">John</Column>
                <Column Id="LastName">James</Column>
                <Column Id="StreetNumber">22</Column>
                <Column Id="Street">Maple Street</Column>
                <Column Id="ZIP">11111</Column>
                <Column Id="City">Independence</Column>
            </Row>
        </Rows>
    </Column>
</Variables>

To generate Xml content instead of text or numbers, simply add the Type="xml" attribute. This variable can now easily be used as a parameter for the Xml functions XQUERY() and XVALUE():

XVALUE([XmlValue], 'Rows/Row/Column[@Id=\"FirstName\"]/text()')

Which results in the array of strings {„Carol“, „John“}.

SQL Server CLR example

The parser can easyly be used together with the XmlVariableContainer class to create a simple Evaluation function:

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlChars Evaluate(SqlChars text, SqlXml columns, 
       SqlString columnPath, SqlBoolean useParentIdentifier)
{
    if (!text.IsNull && !columns.IsNull && !columnPath.IsNull)
    {
        bool useIdentifier = useParentIdentifier.IsNull || useParentIdentifier.IsFalse ? false : true;
        XmlVariableContainer container = new XmlVariableContainer();
        XDocument columnsDocument = XDocument.Load(columns.CreateReader(), LoadOptions.None);
        container.AddColumns(columnsDocument.XPathSelectElements(columnPath.ToString()), useIdentifier);
        return new SqlChars(FunctionParser.Parse(text.ToSqlString().ToString(), 
                            container.GetValue).ToCharArray());
    }
    return new SqlChars();
}

Assuming we have a table called „Contact“ with at least the columns [Salutation], [FirstName] and [LastName], we can create the needed xml data using the FOR XML syntax:

declare @Contacts xml = 
    (SELECT
        [Salutation]
        ,[FirstName]
        ,[LastName]
    FROM
        [Data].[Contact]
    FOR XML PATH('Contact'), ROOT('Contacts'))

Now we can call the previously created Sql function:

declare @formLetter nvarchar(max) = '...'
SELECT [Common].[dbo].[Evaluate](@formLetter, @Contacts, 'Contacts/Contact[1]/*', 0)

 You will find more examples and a full reference when starting the demo application.

Adding new functions 

I tried to make adding new functions as easy as possible. So let’s assume we need a function like ISEMPTY() which determines if the transfered parameter is null or empty. This can be achieved by adding the following code to the FunctionParser.Function class:

// class FunctionParser. Function

/* Public Evaluation Methods */
...

[ParserFunction]
public bool ISEMPTY()
{
    if (this.Parameters.Length == 1)
    {
        this.Value = string.IsNullOrEmpty(this.Parameters[0].StringValue);
        return true;
    }
    return false;
}

All parser functions are marked with a [ParserFunctionAttribute] and return true if the parsing has been successful, otherwise false. The parameters can be accessed by using the Parameters array. The return value can be set assigning the Value property (it’s a little bit like the VBA syntax).

In our example the implementation is limited to checking the Parameters length (it should be exactly one) and setting the Value parameter to true if the corresponding string is null or empty.

History

• 04/26/2013: Initial version released.