Storing Data in Memory in Microsoft ADO.NET 4

  • 10/25/2010
Contents

Validating Changes

As mentioned earlier in the “Storing Rows in a Table” section on page 40, attempting to store data of the incorrect data type in a column will throw an exception. However, this will not prevent a user from entering invalid values, such as entering 7,437 into a System.Int32 column that stores a person’s age. Instead, you must add validation code to your data table to prevent such data range errors.

Exception-Based Errors

ADO.NET monitors some data errors on your behalf. These errors include the following:

  • Assigning data of the wrong data type to a column value.

  • Supplying string data to a column that exceeds the maximum length defined in the column’s DataColumn.MaxLength property.

  • Attempting to store a NULL value in a primary key column.

  • Adding a duplicate value in a column that has its Unique property set.

  • Taking data actions that violate one of the table’s custom constraints. Constraints are discussed in Chapter 5, “Bringing Related Data Together.”

When one of these data violations occurs, ADO.NET throws an exception in your code at the moment when the invalid assignment or data use happens. The following code block generates an exception because it attempts to assign a company name that is too long for a 20-character column:

C#
DataColumn withRule = someTable.Columns.Add("FullName", typeof(string));
withRule.MaxLength = 20;
// ...later...
DataRow rowToUpdate = someTable.Rows[0];
rowToUpdate["FullName"] = "Graphic Design Institute"; // 24 characters
  // ----- Exception occurs with this assignment

Visual Basic
Dim withRule As DataColumn = someTable.Columns.Add("FullName", GetType(String))
withRule.MaxLength = 20
' ...later...
Dim rowToUpdate As DataRow = someTable.Rows(0)
rowToUpdate!FullName = "Graphic Design Institute"  ' 24 characters
  ' ----- Exception occurs with this assignment

Naturally, you would want to enclose such assignments within exception handling blocks. However, there are times when adding this level of error monitoring around every field change is neither convenient nor feasible—not to mention the impact it has on performance when adding or updating large numbers of records. To simplify exception monitoring, the DataRow class includes the BeginEdit method. When used, any data checks normally done at assignment are postponed until you issue a matching DataRow.EndEdit call.

C#
rowToUpdate.BeginEdit();
rowToUpdate["FullName"] = "Graphic Design Institute"; // 24 characters
  // ----- No exception occurs
rowToUpdate["RevisionDate"] = DateTime.Today; // Other field changes as needed
rowToUpdate.EndEdit();
  // ----- Exception for FullName field occurs here

Visual Basic
rowToUpdate.BeginEdit()
rowToUpdate!FullName = "Graphic Design Institute" ' 24 characters
  ' ----- No exception occurs
rowToUpdate!RevisionDate = Today  ' Other field changes as needed
rowToUpdate.EndEdit()
  ' ----- Exception for FullName field occurs here

To roll back any changes made to a row since using BeginEdit, use the CancelEdit method. Even when you complete a row’s changes with the EndEdit method, the changes are not yet committed. It is still necessary to call the AcceptChanges method, either at the row or the table level.

The DataTable.AcceptChanges and DataRow.AcceptChanges methods, when used, automatically call DataRow.EndEdit on all rows with pending edits. Similarly, DataTable.RejectChanges and DataRow.RejectChanges automatically issue calls to DataRow.CancelEdit on all pending rows changes.

Validation-Based Errors

For data issues not monitored by ADO.NET, including business rule violations, you must set up the validation code yourself. Also, you must manually monitor the data table for such errors and refuse to confirm changes that would violate the custom validation rules.

Validation occurs in the various event handlers for the DataTable instance that contains the rows to monitor. Validation of single-column changes typically occurs in the ColumnChanging event. For validation rules based on the interaction between multiple fields in a data row, use the RowChanging event. The RowDeleting and TableNewRow events are useful for checking data across multiple rows or the entire table. You can also use any of the other table-level events (ColumnChanged, RowChanged, RowDeleted, TableClearing, TableCleared) to execute validation code that meets your specific needs.

Inside the validation event handlers, use the Proposed version of a row value to assess its preconfirmed value. When errors occur, use the row’s SetColumnError method (with the name, position, or instance of the relevant column) to indicate the problem. For row-level errors, assign a description of the problem to the row’s RowError property. The following code applies a column-level business rule to a numeric field, setting a column error if there is a problem:

C#
private void Applicant_ColumnChanging(Object sender,
    System.Data.DataColumnChangeEventArgs e)
{
    // ----- Check the Age column for a valid range.
    if (e.Column.ColumnName == "Age")
    {
        if ((int)e.ProposedValue < 18 || (int)e.ProposedValue > 29)
            e.Row.SetColumnError(e.Column,
                "Applicant's age falls outside the allowed range.");
    }
}

Visual Basic
Private Sub Applicant_ColumnChanging(ByVal sender As Object,
        ByVal e As System.Data.DataColumnChangeEventArgs)
    ' ----- Check the Age column for a valid range.
    If (e.Column.ColumnName = "Age") Then
        If (CInt(e.ProposedValue) < 18) Or (CInt(e.ProposedValue) > 29) Then
            e.Row.SetColumnError(e.Column,
                "Applicant's age falls outside the allowed range.")
        End If
    End If
End Sub

Adding column or row-level errors sets both the DataRow.HasErrors and the DataTable.HasErrors properties to True, but that’s not enough to trigger an exception. Instead, you need to monitor the HasErrors properties before confirming data to ensure that validation rules are properly applied. Another essential method, ClearErrors, removes any previous error notices from a row.

C#
// ----- Row-level monitoring.
oneRow.ClearErrors();
oneRow.BeginEdit();
oneRow.FullName = "Tailspin Toys"; // Among other changes
if (oneRow.HasErrors)
{
    ShowFirstRowError(oneRow);
    oneRow.CancelEdit();
}
else
    oneRow.EndEdit();

// ----- Table-level monitoring. Perform row edits, then...
if (someTable.HasErrors)
{
    DataRow[] errorRows = someTable.GetErrors();
    ShowFirstRowError(errorRows[0]);
    someTable.RejectChanges();  // Or, let user make additional corrections
}
else
    someTable.AcceptChanges();

// ...later...

public void ShowFirstRowError(DataRow whichRow)
{
    // ----- Show first row-level or column-level error.
    string errorText = "No error";
    DataColumn[] errorColumns = whichRow.GetColumnsInError();
    if (errorColumns.Count > 0)
        errorText = whichRow.GetColumnError(errorColumns[0]);
    else if (whichRow.RowError.Length > 0)
        errorText = whichRow.RowError;
    if (errorText.Length == 0) errorText = "No error";
    MessageBox.Show(errorText);
}

Visual Basic
' ----- Row-level monitoring.
oneRow.ClearErrors()
oneRow.BeginEdit()
oneRow.FullName = "Tailspin Toys" ' Among other changes
If (oneRow.HasErrors = True) Then
    ShowFirstRowError(oneRow)
    oneRow.CancelEdit()
Else
    oneRow.EndEdit()
End If

' ----- Table-level monitoring. Perform row edits, then...
If (someTable.HasErrors = True) Then
    Dim errorRows() As DataRow = someTable.GetErrors()
    ShowFirstRowError(errorRows(0))
    someTable.RejectChanges()  ' Or, let user make additional corrections
Else
    someTable.AcceptChanges()
End If

' ...later...

Public Sub ShowFirstRowError(ByVal whichRow As DataRow)
    ' ----- Show first column-level or row-level error.
    Dim errorText As String = ""
    Dim errorColumns() As DataColumn = whichRow.GetColumnsInError()

    If (errorColumns.Count > 0) Then
        errorText = whichRow.GetColumnError(errorColumns(0))
    ElseIf (whichRow.RowError.Length > 0) Then
        errorText = whichRow.RowError
    End If
    If (errorText.Length = 0) Then errorText = "No error"
    MessageBox.Show(errorText)
End Sub

Validating Content in a DataRow: C#

NOTE

This exercise uses the “Chapter 3 CSharp” sample project and continues the preceding exercise in this chapter.

  1. Open the source code view for the AccountManager form.

  2. Locate the CustomerAccounts_ColumnChanging event handler, which is called whenever a column value in a CustomerAccounts table row changes. Add the following code, which checks for valid data in two of the columns:

    if (e.Column.ColumnName == "AnnualFee")
{
    // ----- Annual fee may not be negative.
    if (DBNull.Value.Equals(e.ProposedValue) == false)
    {
        if ((decimal)e.ProposedValue < 0m)
            e.Row.SetColumnError(e.Column,
                "Annual fee may not be negative.");
    }
}
else if (e.Column.ColumnName == "StartDate")
{
    // ----- Start date must be on or before today.
    if (DBNull.Value.Equals(e.ProposedValue) == false)
    {
        if (((DateTime)e.ProposedValue).Date > DateTime.Today)
            e.Row.SetColumnError(e.Column,
                "Start date must occur on or before today.");
    }
}

  3. Locate the CustomerAccounts_RowChanging event handler, which is called whenever any value in a row changes within the CustomerAccounts table. Add the following code, which checks for valid data involving multiple columns:

    if (e.Row.HasVersion(DataRowVersion.Proposed) == true)
{
    if (((bool)e.Row["Active", DataRowVersion.Proposed] == true) &&
            (DBNull.Value.Equals(e.Row["StartDate",
            DataRowVersion.Proposed]) == true))
        e.Row.RowError = "Active accounts must have a valid start date.";
}

  4. Run the program. Use its features to create, edit, and delete data rows. When you attempt to provide invalid data—incorrect data types, violations of business rules, duplicate account names—the program provides the appropriate error messages.

httpatomoreillycomsourcemspimages695933.png

Validating Content in a DataRow: Visual Basic

NOTE

This exercise uses the “Chapter 3 VB” sample project and continues the preceding exercise in this chapter.

  1. Open the source code view for the AccountManager form.

  2. Locate the CustomerAccounts_ColumnChanging event handler, which is called whenever a column value in a CustomerAccounts table row changes. Add the following code, which checks for valid data in two of the columns:

    If (e.Column.ColumnName = "AnnualFee") Then
    ' ----- Annual fee may not be negative.
    If (IsDBNull(e.ProposedValue) = False) Then
        If (CDec(e.ProposedValue) < 0@) Then _
            e.Row.SetColumnError(e.Column,
            "Annual fee may not be negative.")
    End If
ElseIf (e.Column.ColumnName = "StartDate") Then
    ' ----- Start date must be on or before today.
    If (IsDBNull(e.ProposedValue) = False) Then
        If (CDate(e.ProposedValue).Date > Today) Then _
            e.Row.SetColumnError(e.Column,
            "Start date must occur on or before today.")
    End If
End If

  3. Locate the CustomerAccounts_RowChanging event handler, which is called whenever any value in a row changes within the CustomerAccounts table. Add the following code, which checks for valid data involving multiple columns:

    If (e.Row.HasVersion(DataRowVersion.Proposed) = True) Then
    If (CBool(e.Row("Active", DataRowVersion.Proposed)) = True) And
            (IsDBNull(e.Row("StartDate",
            DataRowVersion.Proposed)) = True) Then
        e.Row.RowError = "Active accounts must have a valid start date."
    End If
End If

  4. Run the program. Use its features to create, edit, and delete data rows. When you attempt to provide invalid data—incorrect data types, violations of business rules, duplicate account names—the program provides the appropriate error messages.

httpatomoreillycomsourcemspimages695935.png
Save to your account

This chapter is from the book