0

I was given a more or less complex task. The goal is to interpret a SQL Check Constraint inside my C# .NET libary. In our case we have a simple UI that displays what is inside the database. We do not want out UI-Components to allow any values that wouldnt even be possible because there is a check constraint. Since everything has to be dynamic (the database can change), I cannot just hardcode the UI components.

I have managed to retrieve data about every check constraint inside my SQL Server database (Northwind) with the following query:

SELECT 
    [cck].[name] AS [CONSTRAINT_NAME],
    [s].[name] AS [SCHEMA],
    [o].[name] AS [TABLE_NAME],
    [cstcol].[name] AS [COLUMN_NAME],
    [cck].[definition] AS [DEFINITION],
    [cck].[is_disabled] [IS_DISABLED]
FROM sys.check_constraints cck
    JOIN sys.schemas s ON cck.schema_id = s.schema_id
    JOIN sys.objects o ON cck.parent_object_id = o.object_id
    JOIN sys.columns cstcol ON cck.parent_object_id = cstcol.object_id AND cck.parent_column_id = cstcol.column_id

This query gives me the following result:

enter image description here

As you can see, there is a column 'DEFINITION', which pretty much shows what the CC does in a human-readable medium. Here comes my problem: How can my .NET libary understand this check constraint so that I can adjust my UI components to now allow any values that violate the CC?

I've thought about those two possible solutions:

  1. Using Expressions to 'express' what the CC is doing
  2. Returning every single possible value of the check constraint.

Number 1 is probably the fastest if done right, but very complex (at least for me since I do not have any experience with expressions). Number 2 would be slower but the easiest way to do it, if possible.

Sadly I couldnt find any good help for both of my solutions.

Also: At least for now I will only care about CC on the column-level. Handling table-constraints will be another challenge

Now my quesion is: What is an "easy" way to do something like this. It definetly does not have to be the fastest solution.

Improve this question
6
  • Option 2 would give infinite numbers of possible values: e.g. "([UnitPrice]>=0)" Commented Jan 3, 2019 at 21:57
  • @KaspervandenBerg numbers in computers are finite, so it's only 2 billion(ish) values for "([UnitPrice]>=0)" Commented Jan 4, 2019 at 14:42
  • Is there scope for the program to be changed each time a new (kind of?) constraint is added or does it have to work for a database sight unseen? Commented Jan 4, 2019 at 14:44
  • 1
    @Caleth, you are right. However, my point remains: creating arrays with about 2 billion values and checking whether it contains a value is not a good idea (if ints don't convince you think zip codes and telephone numbers). Commented Jan 4, 2019 at 16:52
  • If all the expressions you have are easy/simple expressions to check ranges, and simple math functions around, you could check NCalc and call it a day. Or NCalc2. Commented Feb 4, 2019 at 16:24

3 Answers 3

Reset to default
1

Sounds like you will need to write a parser if things must be in C#. This should be fine for simple constraints.

For more complex constraints I would see about making a piece of code which coverts the constraint definition into a stored procedure e.g.

([BirthDate] > getDate())

becomes:

CREATE PROCEDURE CK_BirthDate_WouldPass 
@BirthDate datetime
AS
(@BirthDate > getDate())

This procedure would be called from your c# code.

Note that the constraint here is a simple one to illustrate the point. While the whole point is to have a equivalent C# check, there may come a point where handling the complex constraints requires a impractical amount of parser work and complexity which is not worth the benefits that you are meant to get from the c# check. You may also find that the more complex constraints fetch from other tables etc. so you are going to have a SQL call whether you like it or not.

Improve this answer
0

You could use an in memory database like SQLite with the same table structure. Insert into that first and if it fails throw the error.

But it's not a good idea. You would be recreating SQL Server and trying to keep up with any changes they introduce.

What you should do is remove the check constraints from the database and recreate them as code in a library class. Make an API which runs the checks and force all database access to go through the api.

You can then use the same library to add the constraints in all your applications

Improve this answer
4
  • It's not obvious whether OP can change the database structure, so removing the constraints may be a non-starter Commented Jan 4, 2019 at 14:46
  • @Caleth see my first sentence for the practical solution. But its still what they should do regardless of whether it's possible. Commented Jan 4, 2019 at 14:48
  • They could even use in-memory Entity Framework of the same table structure if their version is high enough. No duplicate information. Commented Jan 5, 2019 at 12:40
  • @nvoight interesting idea. would you even need the in memory db if the EF object generation allows model.IsValid() style calls? Commented Jan 5, 2019 at 12:53
0

You may end up with needing to write a parser for the human readable SQL Server constraint syntax.

A possible shortcut is to parse only the constructs you have encountered and update your program when someone introduces a more complex constraint.

Most of what I see in your example is {column value} should be greater than or equal to 0. You can create a simple parser with regular expressions; the following regular expression would capture it:

(?<greaterEqualConstraint>\(\[(?<columnName>\w+)\]\>\=\((?<value>\d+)\))

(See Regular Expression Language - Quick Reference | Microsoft Docs)

After a Match() on DEFINITION, you can use .Groups[groupname] on the returned Match. Use Success property to check whether a group was matched. Use Value to get the matched value as a string.

These can be used to build Expressions:

Expression<Func<int, bool>> Parse(Match constraintDefinition)
{
    if (constraintDefinition.Groups["greaterEqualConstraint"].Success &&
        constraintDefinition.Groups["columnName"].Success &&
        constraintDefinition.Groups["value"].Success)
    {
        ParameterExpression columnExpr = Expression.Parameter(
            typeof(int) /* TODO use the database schema to set the correct type */,
            constraintDefinition.Groups["columnName"].Value);
        return Expression.Lambda<Func<int,bool> /* TODO again this might depend on the schema */>(
            Expression.GreaterThanOrEqual(
                columnExpr,
                Expression.ConstantExpression(
                    int.Parse(constraintDefinition.Groups["value"].Value))),
            tailCall: false,
            parameters: new ParameterExpression[] { columnExpr });
    }
    else
    {
        throw new NotImplementedException($"Marco Siffert, database schema contains a not implemented kind of constraint: {constraintDefinition}");
    }
}

Note: this is whiteboard quality code; I currently don't have the equipment to compile or run the code.

Improve this answer
1
  • The regular expression can be made modular (use ToString() and the Regex constructor to combine them) and follow the syntax of the constraint language; this would help evolving your parser. Commented Jan 4, 2019 at 16:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.