ADO.NET 3.5 Cookbook, 2nd Edition

7.7. Enforcing Business Rules with Column Expressions

Problem

You need to enforce a business rule based on multiple columns in a table.

Solution

Use expression-based columns to enforce business rules at the user interface tier. The business rule for this solution is that the sum of Field1 and Field2 for a row in the table must be 10.

The solution uses a table named EnforceBusinessRulesColumnExpressions in the AdoDotNet35Cookbook database. Execute the T-SQL statement in Example 7-23 to create the table.

Example 7-23. Create table EnforceBusinessRulesColumnExpressions

USE AdoDotNet35Cookbook
GO
CREATE TABLE EnforceBusinessRulesColumnExpressions (
    Id int NOT NULL PRIMARY KEY,
    Field1 int NOT NULL,
    Field2 int NOT NULL
)

The solution creates a DataTable and creates a schema for it matching the EnforceBusinessRulesColumnExpressions table in the database. An expression column is added to the table that returns a Boolean value indicating whether the sum of Field1 and Field2 is equal to 10. A DataAdapter is created and an event handler is attached to handle its RowUpdating event. A CommandBuilder is used to supply the updating logic. Four rows are added to the DataTable—two are valid and two are invalid based on the expression. The Update() method of the DataAdapter is called to update the table in the database. For each row being updated or inserted, the handler for the RowUpdating event of the DataAdapter checks whether the value of the expression column is false indicating that the data is invalid ...