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:
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:
- Using Expressions to 'express' what the CC is doing
- 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.