WindowsDevCenter.com

Cooking with ADO.NET
Pages: 1, 2, 3

Recipe 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 (
        @Id,
        @Field1,
        @Field2)
    
    if @@rowcount=0
        return 1
            
    return 0

Example 6-24. Stored procedure: SP0607_Update

CREATE PROCEDURE SP0607_Update
    @Id int,
    @Field1 int,
    @Field2 int
AS
    SET NOCOUNT ON
    
    update
        TBL0607
    set
        Field1=@Field1,
        Field2=@Field2
    where
        Id=@Id
    
    if @@rowcount=0
        return 1
    
    return 0

The sample code contains three event handlers:

Form.Load: Sets up the sample by creating a DataTable and creating a schema for it matching TBL0607 in the database. An expression column is added to the table. The calculation returns a Boolean value indicating whether the sum of Field1 and Field2 is equal to 10. A DataAdapter is created and event handler is attached to handle its RowUpdating event. Delete, insert, and update commands using the stored procedures in this solution are created for the DataAdapter. The DataAdapter is used to fill the table and its default view is bound to the data grid on the form.
DataAdapter.RowUpdating: Checks whether a row is being updated or inserted and whether the value of the expression column is false indicating that the data is invalid according to the business rule defined by the expression in the column. If the business rule has not been met, an error is set on the row and the update for the row is skipped.
Update Button.Click: Uses the DataAdapter to update changes made to the DataTable back to table TBL0607 in the database.

The C# code is shown in Example 6-25.

Example 6-25. File: EnforceBusinessRulesWithColumnExpressionsForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private DataTable dt;
private SqlDataAdapter da;

private const String TABLENAME             = "TBL0607";

// Table column name constants 
private const String ID_FIELD              = "Id";
private const String FIELD1_FIELD          = "Field1";
private const String FIELD2_FIELD          = "Field2";
private const String CONSTRAINT_EXPRESSION = "ConstraintExpression";

// Stored procedure name constants
private const String DELETE_SP             = "SP0607_Delete";
private const String GET_SP                = "SP0607_Get";
private const String INSERT_SP             = "SP0607_Insert";
private const String UPDATE_SP             = "SP0607_Update";

// Stored procedure parameter name constants for table
private const String ID_PARM               = "@Id";
private const String FIELD1_PARM           = "@Field1";
private const String FIELD2_PARM           = "@Field2";

//  . . . 

private void EnforceBusinessRulesWithColumnExpressionsForm_Load(
    object sender, System.EventArgs e)
{
    DataColumnCollection cols;

    // Build the table.
    dt = new DataTable(TABLENAME);
    cols = dt.Columns;
    cols.Add(ID_FIELD, typeof(Int32));
    cols.Add(FIELD1_FIELD, typeof(Int32));
    cols.Add(FIELD2_FIELD, typeof(Int32));
    // add the primary key
    dt.PrimaryKey = new DataColumn[] {cols[ID_FIELD]};
    // Expression to evaluate whether the sum of Field1 and Field2
    // equals 10
    cols.Add(CONSTRAINT_EXPRESSION, typeof(Boolean), FIELD1_FIELD +
        "+" + FIELD2_FIELD + " = 10");

    // Create the DataAdapter, handling the RowUpdating event.
    da = new SqlDataAdapter( );
    da.RowUpdating += new SqlRowUpdatingEventHandler(da_RowUpdating);
    SqlConnection conn = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);

    // Build the select command.
    SqlCommand selectCommand = new SqlCommand(GET_SP, conn);
    selectCommand.CommandType = CommandType.StoredProcedure;
    da.SelectCommand = selectCommand;

    // Build the delete command.
    SqlCommand deleteCommand = new SqlCommand(DELETE_SP, conn);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    deleteCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    da.DeleteCommand = deleteCommand;

    // Build the insert command.
    SqlCommand insertCommand = new SqlCommand(INSERT_SP, conn);
    insertCommand.CommandType = CommandType.StoredProcedure;
    insertCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    insertCommand.Parameters.Add(FIELD1_PARM, SqlDbType.Int, 0,
        FIELD1_FIELD);
    insertCommand.Parameters.Add(FIELD2_PARM, SqlDbType.Int, 0,
        FIELD2_FIELD);
    da.InsertCommand = insertCommand;

    // Build the update command.
    SqlCommand updateCommand = new SqlCommand(UPDATE_SP, conn);
    updateCommand.CommandType = CommandType.StoredProcedure;
    updateCommand.Parameters.Add(ID_PARM, SqlDbType.Int, 0, ID_FIELD);
    updateCommand.Parameters.Add(FIELD1_PARM, SqlDbType.Int, 0,
        FIELD1_FIELD);
    updateCommand.Parameters.Add(FIELD2_PARM, SqlDbType.Int, 0,
        FIELD2_FIELD);
    da.UpdateCommand = updateCommand;

    // Fill the table.
    da.Fill(dt);

    // Bind the default view of the table to the grid.
    dataGrid.DataSource = dt.DefaultView;
}

private void da_RowUpdating(object sender, SqlRowUpdatingEventArgs e)
{
    // For insert or update statements, check that the
    // calculated constraint column is true.
    if((e.StatementType == StatementType.Insert ||
        e.StatementType == StatementType.Update) &&
        !(bool)e.Row[CONSTRAINT_EXPRESSION])
    {
        // Constraint has not been met.
        // Set an error on the row and skip it.
        e.Row.RowError = "Constraint error.";
        e.Status = UpdateStatus.SkipCurrentRow;
    }
}

private void updateButton_Click(object sender, System.EventArgs e)
{
    try
    {
        da.Update(dt);
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Discussion

The RowUpdating event of the DataAdapter occurs during the Update( ) method before the command to update a row is executed against the data source. The event fires with each row update attempt.

The RowUpdating event handler receives an argument of type RowUpdatingEventArgs that provides information specifically related to the event as described in Table 6-6.

Table 6-6. RowUpdatingEventArgs properties

Property	Description
`Command`	Gets the `Command` to execute during the `Update( )` method.
`Errors`	Gets errors generated by the .NET data provider when the `Command` was executed.
`Row`	Gets the `DataRow` to send through the `Update( )` method.
`StatementType`	Gets the type of SQL statement to execute. This is one of the following values from the `StatementType` enumeration: `Select`, `Insert`, `Update`, or `Delete`.
`Status`	Gets or sets the action to take with the current and remaining rows during the `Update( )` method. This is a value from the `UpdateStatus` enumeration (described in Table 6-7).
`TableMapping`	Gets the `DataTableMapping` to send through the `Update( )` method.

Table 6-7 describes the values in the UpdateStatus enumeration used by the Status property of the RowUpdatingEventArgs object.

Table 6-7. UpdateStatus enumeration

Value	Description
`Continue`	Continue processing the rows. This is the default value.
`ErrorsOccurred`	The event handler reports that the update should be treated as an error.
`SkipAllRemainingRows`	Do not update the current row and skip updating the remaining rows.
`SkipCurrentRow`	Do not update the current row and continue updating with the subsequent row.

The Update( ) method of the DataAdapter raises two events for every row in the data source that is updated. The order of the events is:

The values in the DataRow are moved to parameter values.
The OnRowUpdating event is raised.
The update command executes against the row in the data source.
If the UpdatedRowSource property of the Command is set to FirstReturnedRecord or Both, the first returned result is placed in the DataRow.
If the UpdateRowSource property of the Command is set to OutputParameters or Both, the output parameters are placed in the DataRow.
The OnDataRowUpdated event is raised.
AcceptChanges( ) is called.

If zero rows are affected, the DBConcurrencyException is raised during the update operation on a row. This usually indicates a concurrency violation.

The solution uses the RowUpdating event of the DataAdapter to check whether the expression column in the DataTable is true, indicating that the business rule has been satisfied, before a database record is updated. If the expression if false, an error is set on the row and the Status is set to SkipCurrentRow preventing the record in the database from being updated and continuing the processing with the next row.

Stay tuned for more recipes from ADO.NET Cookbook in early January 2004.

Bill Hamilton is a technology and management consultant who specializes in assessing business objectives and company processes and designing and restructuring technology strategy and enterprise architecture.

Return to ONDotnet.com.