Store a calculation expression in SQL Server and evaluate that in C#?

Question

I want to store simple mathematic calculations (formulas) in database, then somehow interpret and calculate it.

The problem is, I have no idea, how to do so. So I may have something like this:

CREATE TABLE Values (
    [ValuesId] [int] NOT NULL PRIMARY KEY,
    [Name] [nvarchar](250) NOT NULL,
    [Value] [decimal](18, 2) NOT NULL
)

And, for calculations, I may want to use something like this:

CREATE TABLE Calculations (
    [Id] int NOT NULL PRIMARY KEY,
    [Calc] [nvarchar](100) NOT NULL
)

The Calc column is where I normally want to store the calculations where each number in the calculation means the Id of the ValuesId of the Value table (operands). For example, a typical Calc value should be something like this : '274 + 277 - 273', so the value is calculated by: taking the 'Value' from the Values table with the id of 274, add it to the corresponding 'Value' of id 275 and finally subtract the corresponding 'Value' of id 277. Those calculations can contain all of the 4 basic operators (+,-,*,/) and, unfortunately the number of the operands could be varied.

The final goal is evaluate that stored expression.

If you can provide some code which solves this would be fantastic, but giving me the right direction is also helpful.

Answer 1

I end up using regex with the combination of datatable.

//id is the primary key of whatever data I want
 string calculations = calculationsService.GetById(id).Calc;

//extract the ids into an array
 string[] numbers_in_formula = Regex.Split(calculations, @"[^0-9\.]+").Where(c => c != "." && c.Trim() != "").ToArray();

//substitute the ids to values

        foreach(string number in numbers_in_formula)
        {

                    //lets search data value
                    decimal tempvalue = Values.Find(Convert.ToInt32(number)).Value;

                    //replace the id with the value in the string 
                    calculations = calculations.Replace(number, tempvalue.ToString(CultureInfo.InvariantCulture));
                
            }
        }
        
        // compute that
        using (DataTable dt = new DataTable())
        {
            decimal result = (decimal)dt.Compute(calculations, "");

        }

Of course, if numbers needed besides the ids, the ids should be signed with a token to distinguish them from the values.