Enforcing Business Rules with Column Expressions

[ Team LiB ] 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.
Nội dung trích xuất từ tài liệu:
Enforcing Business Rules with Column Expressions[ Team LiB ]Recipe 6.7 Enforcing Business Rules with Column ExpressionsProblemYou need to enforce a business rule based on multiple columns in a table at the userinterface tier.SolutionUse expression-based columns to enforce business rules at the user interface tier. Thebusiness rule for this solution is that the sum of Field1 and Field2 for a row in the tablemust 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 NoField1 nvarchar 4 NoField2 nvarchar 4 NoThe sample uses four stored procedures, which are shown in Example 6-21 throughExample 6-24:SP0607_Delete Used to delete a record from the table TBL0607 for a specified IdSP0607_Get Used to retrieve a record for a specified Id or all records from the table TBL0607SP0607_Insert Used to insert a record into the table TBL0607SP0607_Update Used to update a record in the table TBL0607Example 6-21. Stored procedure: SP0607_DeleteCREATE PROCEDURE SP0607_Delete @Id intAS SET NOCOUNT ON delete from TBL0607 where Id=@Id return 0Example 6-22. Stored procedure: SP0607_GetCREATE PROCEDURE SP0607_Get @Id int=nullAS 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 0Example 6-23. Stored procedure: SP0607_InsertCREATE PROCEDURE SP0607_Insert @Id int, @Field1 int, @Field2 intAS SET NOCOUNT ON insert TBL0607( Id, Field1, Field2) values ( @Id, @Field1, @Field2) if @@rowcount=0 return 1 return 0Example 6-24. Stored procedure: SP0607_UpdateCREATE PROCEDURE SP0607_Update @Id int, @Field1 int, @Field2 intAS SET NOCOUNT ON update TBL0607 set Field1=@Field1, Field2=@Field2 where Id=@Id if @@rowcount=0 return 1 return 0The 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 constantsusing 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 constantsprivate 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 constantsprivate 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 tableprivate 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 comma ...