How to check if statements dynamically

Question

My questions as follows, I need to check conditions through the database. which means in the simple application we check if conditions like this.

private static void Main(string[] args)
{
    Console.WriteLine("Enter your age");
    var age = int.Parse(Console.ReadLine() ?? throw new InvalidOperationException());

    if (5 <= age && age <= 10)
    {
        Console.WriteLine("Range 5 - 10");
    }else if (11 <= age && age <= 20)
    {
        Console.WriteLine("Range 11 - 20");
    }
    else if (21 <= age && age <= 30)
    {
        Console.WriteLine("Range 21 - 30");
    }
    else
    {
        Console.WriteLine("Over range");
    }
}

Suppose, Client, needs to change the condition dynamically, which means he need to add additional condition to the system like,

if 31 <= age && age <= 40 => Range 31 - 40

When doing this in the client side, sometimes wrong conditions can be added to the system like,

(4 <= age && age <= 15) this condition cannot be added, because the system already have condition (5 <= age && age <= 10). When age is 7, both conditions will be true. like this type situations what is the best thing to do.

I need to store the conditions in my database,(PS:database table structure can be changed according to your answer)

as the sample table structure

ConditionID    Condition                  Details

con001         5 <= age && age <= 10      Range 5 - 10
con002         11 <= age && age <= 20     Range 11 - 20
con003         21 <= age && age <= 30     Range 21 - 30

Please give me a solution to solve this. How can I do this with C# and oracle SQL

Answer 1

Firstly you don't need to store the Details as a column, since you can build it in a dynamic way later in your code. And secondly you don't need to store the Condition as a string column also, because it makes your work more complicated when you want to parse it as conditions in your C# code. All you need is, storing two integer values in your database as Lower and Upper values of your ranges and make ConditionID identity. Your table should be something like this:

CREATE TABLE [dbo].[tblConditions] (
[ConditionID] INT IDENTITY (1, 1) NOT NULL,
[Lower]       INT NOT NULL,
[Upper]       INT NOT NULL,
PRIMARY KEY CLUSTERED ([ConditionID] ASC)
);

And then you can write something like this in your code:

Console.WriteLine(@"Enter your age");
var age = int.Parse(Console.ReadLine() ?? throw new InvalidOperationException());

using (var db = new forTestEntities())
{
    var result = db.tblConditions.Where(c => c.Lower <= age && age <= c.Upper).AsEnumerable()
        .Select(c => $"Range {c.Lower} - {c.Upper}")
        .DefaultIfEmpty("Over range").SingleOrDefault();

    Console.WriteLine(result);
}

EDIT: If you are not familiar with this $ operator, it is called String Interpolation and it is available in C# 6+, if you are using the older version of C# you can use string.Format like this:

c => string.Format("Range {0} - {1}", c.Lower, c.Upper)

Also to add dynamic condition to the database you can use the following code:

Console.WriteLine(@"Enter Lower");
var lower = int.Parse(Console.ReadLine() ?? throw new InvalidOperationException());

Console.WriteLine(@"Enter Upper");
var upper = int.Parse(Console.ReadLine() ?? throw new InvalidOperationException());

using (var db = new forTestEntities())
{
    var newCondition = new tblCondition
    {
        Lower = lower,
        Upper = upper
    };

    var range = Enumerable.Range(newCondition.Lower, 
                            newCondition.Upper - newCondition.Lower + 1);

    var check = db.tblConditions.AsEnumerable().Any(c => range
                .Intersect(Enumerable.Range(c.Lower, c.Upper - c.Lower + 1)).Any());

    if (!check)
    {
        db.tblConditions.Add(newCondition);
        db.SaveChanges();
    }
}

Please note: I have used SQL Server and Entity Framework-DB First approach, you can change it as per your requirements.