-1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Newbie1001
  • 131
  • 11
  • 4
    FYI `[Calc] [nvarchar] NOT NULL` would be a synonym of `[Calc] [nvarchar](1) NOT NULL`; you won't be fitting any expressions in there when you only have 1 character to "play" with. – Thom A Apr 12 '21 at 09:54
  • 3
    There are numberous way to compute simple math expression, https://stackoverflow.com/questions/333737/evaluating-string-342-yield-int-18 , but https://medium.com/@toptensoftware/writing-a-simple-math-expression-engine-in-c-d414de18d4ce may help you handle those numeric token as get the value of id #. I will parse it in two time to avoid ping pong to database looking for value id 1 id 2 id 3 etc – Drag and Drop Apr 12 '21 at 09:57
  • @Larnu - totally forgot that. thanks for the reminder, i updated the original question. – Newbie1001 Apr 12 '21 at 10:14

1 Answers1

0

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.

Newbie1001
  • 131
  • 11