Formula parsing / evaluation routine or library with generic DLookup functionality

Posted by tbone on Stack Overflow See other posts from Stack Overflow or by tbone
Published on 2009-01-12T18:30:43Z Indexed on 2010/03/15 2:49 UTC
Read the original article Hit count: 452

Filed under:
|
|
|

I am writing a .Net application where I must support user-defined formulas that can perform basic mathematics, as well as accessing data from any arbitrary table in the database.

I have the math part working, using JScript Eval().

What I haven't decided on is what a nice way is to do the generic table lookups. For example, I may have a formula something like:
Column: BonusAmount Formula: {CurrentSalary} * 1.5 * {[SystemSettings][Value][SettingName=CorpBonus AND Year={Year}]}

So, in this example I would replace {xxx} and {Year} with the value of Column xxx from the current table, and I would replace the second part with the value of (select Value from SystemSettings WHERE SettingName='CorpBonus' AND Year=2008)

So, basically, I am looking for something very much like the MS Access DLookup function:
DLookup ( expression, domain, [criteria] )
DLookup("[UnitPrice]", "Order Details", "OrderID = 10248")

But, I also need to overall parsing routine that can tell whether to just look up in the current row, or to look into another table. Would also be nice to support aggregate functions (ie: DAvg, DMax, etc), as well as all the weird edge cases handled.

So I wonder if anyone knows of any sort of an existing library, or has a nice routine that can handle this formula parsing and database lookup / aggregate function resolution requirements.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql