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 ...

Get ADO.NET Cookbook now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.