Creating custom formula on table columns

Question

My app. is in C# 2008 and back-end is SQL Server. A table in my database has following columns:

TotalAmount

Discount

SpecialDiscount

Tax

NetAmount

I want my app. to be customizable by the user. A user can create a formula from the above columns to calculate NetAmount according to his need.

For example: One user may use simply:

(TotalAmount - Discount - SpecialDiscount) + Tax

But other users might use Tax/2 or Tax * 3.5

How to work with such scenario? Where to store the custom formula and how to substitute values at run-time?

Answer 1

I have a similar thing going on with regularly used search criteria.

The easiest way, if you have access, is to store the formulas into their own DB Table, they can then be selected by the user, displayed to give the user the option of which to use.

Answer 2

In the past I've solved this in two ways: Firs was to allow users to define formulas that are stored in the database in a single table, Formula with Columns: UserName - as each user can define the formulas FormulaHeading - to show in the GUI FormulaText - SQL expression TableName the name of the table to which the forumla is applied

in code, Select FormulaHeading, FormulaText from Formula and add each formula text as a field to the selection SQL. So your code will look something like this:

string query = string.Format("SELECT TotalAmount, Discount, SpecialDiscount, Tax, NetAmount {0} From SomeTable", GetUserFormula(userName, tableName));

private string GetUserFormula(string userName, string tableName)
    {
        StringBuilder result = new StringBuilder();
        using (SqlConnection con = new SqlConnection("someConnectionString"))
        {
            using (SqlCommand com = new SqlCommand("Select FormulaText, FormulaHeading FROM Formula WHERE UserName = @userName AND TableName =@TableName"))
            {
                com.Parameters.AddWithValue("userName", userName);
                com.Parameters.AddWithValue("tableName", tableName);
                SqlDataReader reader = com.ExecuteReader();
                if (reader == null)
                    return result.ToString();
                while (reader.NextResult())
                {
                    result.AppendFormat(", ({0}) AS [{1}]", reader["FormulaText"], reader["FormulaHeading"]);
                }
                return result.ToString();
            }
        }
    }

If your GUI is databound you should get a few new correctly named columns with the data worked out by SQL server.

The other approach I've used is to allow the user to define formulas and wrap them in a view within SQL server. The logic is similar but the view is stoerd on the server and you will need to create Instead of triggers on the view for each user with formula.

Answer 3

re: Where to store the custom formula and how to substitute values at run-time?

I agree with the answers so far, in that the formula must be stored externally to the source code, in a database table or even a text file.

To substitute the values at run-time, you have a couple of options.

• Store the literal sql syntax
• Store a tokenized version of the sql

You also need to decide what to do with the operations. If you store the literal sql you can execute directly, you're good, but your end users have to learn sql syntax.

If you want to code a builder for them, you can have each operation in a row of a child table to the table with their userID.

Parent table has columns

userID
formulaID
formulaName

Child table has columns

userID
formulaID
operationSequence
field
operator
value

for your example,

(TotalAmount - Discount - SpecialDiscount) + Tax / 2 

the rows in the child table would break down the calculation one arithmetic operation at a time:

userID  formulaID  operationSequence  field     operator  value
1       1          1                  tax       /         2
1       1          2                  netamount +         totalAmount
1       1          3                  netamount -         discount
1       1          4                  netamount -         specialDiscount

your code knows to store the results of each operation in the netamount field.

If there's only a few things that vary with custom operations, such as the multiplier on the tax field, you could also just store that (1, 0.5, or 3.5 in your examples)