How to allow users to define financial formulas in a C# app
- by Peter Morris
I need to allow my users to be able to define formulas which will calculate values based on data. For example
//Example 1
return GetMonetaryAmountFromDatabase("Amount due") * 1.2;
//Example 2
return GetMonetaryAmountFromDatabase("Amount due") * GetFactorFromDatabase("Discount");
I will need to allow / * + - operations, also to assign local variables and execute IF statements, like so
var amountDue = GetMonetaryAmountFromDatabase("Amount due");
if (amountDue > 100000) return amountDue * 0.75;
if (amountDue > 50000) return amountDue * 0.9;
return amountDue;
The scenario is complicated because I have the following structure..
Customer (a few hundred)
Configuration (about 10 per customer)
Item (about 10,000 per customer configuration)
So I will perform a 3 level loop. At each "Configuration" level I will start a DB transaction and compile the forumlas, each "Item" will use the same transaction + compiled formulas (there are about 20 formulas per configuration, each item will use all of them).
This further complicates things because I can't just use the compiler services as it would result in continued memory usage growth. I can't use a new AppDomain per each "Configuration" loop level because some of the references I need to pass cannot be marshalled.
Any suggestions?
