ADO.NET Cookbook

6.7. Enforcing Business Rules with Column Expressions

Problem

You need to enforce a business rule based on multiple columns in a table at the user interface tier.

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 schema of table TBL0607 used in this solution is shown in Table 6-5.

Table 6-5. TBL0607 schema

Column name	Data type	Length	Allow nulls?
Id	int	4	No
Field1	nvarchar	4	No
Field2	nvarchar	4	No

The sample uses four stored procedures, which are shown in Example 6-21 through Example 6-24:

SP0607_Delete

Used to delete a record from the table TBL0607 for a specified Id

SP0607_Get

Used to retrieve a record for a specified Id or all records from the table TBL0607

SP0607_Insert

Used to insert a record into the table TBL0607

SP0607_Update

Used to update a record in the table TBL0607

Example 6-21. Stored procedure: SP0607_Delete

CREATE PROCEDURE SP0607_Delete
    @Id int
AS
    SET NOCOUNT ON
    
    delete
    from
        TBL0607
    where
        Id=@Id
        
    return 0

Example 6-22. Stored procedure: SP0607_Get

CREATE PROCEDURE SP0607_Get
    @Id int=null
AS
    SET NOCOUNT ON
    
    if @Id is not null
    begin
        select
            Id,
            Field1,
            Field2
        from
            TBL0607
        where
            Id=@Id
            
        return 0
    end
    
    select
        Id,
        Field1,
        Field2
    from
        TBL0607
        
    return 0

Example 6-23. Stored procedure: SP0607_Insert

CREATE PROCEDURE SP0607_Insert @Id int, @Field1 int, @Field2 int AS SET NOCOUNT ON insert TBL0607( Id, Field1, Field2) values ...