Recipe 2.15 Retrieving Update Errors

Problem

You want to access all of the error information available after an update fails.

Solution

Use one of the available properties (such as HasErrors) and methods (such as GetErrors( )) to obtain the error information.

The schema of table TBL0215 used in this solution is shown in Table 2-14.

Table 2-14. TBL0215 schema

Column name	Data type	Length	Allow nulls?
UniqueId	int	4	No
NumericField	int	4	No
StringNoNullsField	nvarchar	50	No
ConstrainedNegativeField	int	4	No

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a table to demonstrate error information retrieval, using a DataAdapter to fill it with sample data, and adding it to a DataSet. The ContinueUpdateOnError property of the DataAdapter is set to true so that an exception is not raised if an error is encountered during an update. Finally, the default view of the table is bound to the data grid on the form.

Update Button.Click

Sets an error description for the first column of the first row using the SetColumnError( ) method if the user has checked the Test Column Error check box. The Update( ) method of the DataAdapter is called to update changes that the user has made to the data back to the database. After the update, the HasErrors property of the DataSet is tested to determine if the DataSet has any errors. The collection of DataTable objects in the DataSet (in this case there is only one) is iterated again and the HasErrors property for each table is checked to see if the table has any errors. The collection of DataRow objects having errors in the table are accessed using the GetErrors( ) method on the table. For each row in error, the HasErrors property is checked to determine if the row has any errors and the RowError property of a row having errors is accessed to get the text describing the error. The collection of DataColumn objects having errors in the row are accessed using the row's GetColumnsInError( ) method. For each column having an error, the row's GetColumnError( ) method is used to get the error description for the column.

The C# code is shown in Example 2-20.

Example 2-20. File: RetrieveProviderErrorsForm.cs

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

private const String TABLENAME = "TBL0215";
private DataSet ds;
private SqlDataAdapter da;

//  . . . 

private void RetrieveProviderErrorsForm_Load(object sender,
    System.EventArgs e)
{
    String sqlText = "SELECT * FROM " + TABLENAME;

    // Create the DataAdapter and its CommandBuilder.
    da = new SqlDataAdapter(sqlText,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    da.ContinueUpdateOnError = true;
    SqlCommandBuilder cb = new SqlCommandBuilder(da);

    // Create a table and fill it.
    DataTable dt = new DataTable(TABLENAME);
    dt.Columns.Add("UniqueId", typeof(Int32));
    dt.Columns.Add("NumericField");    // Data type not specified
    dt.Columns.Add("StringNoNullsField", typeof(String));
    dt.Columns.Add("ConstrainedNegativeField", typeof(Int32));
    da.Fill(dt);
    
    // Create the DataSet and add the table.
    ds = new DataSet( );
    ds.Tables.Add(dt);

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

private void updateButton_Click(object sender, System.EventArgs e)
{
    StringBuilder result = new StringBuilder( );

    if (testColumnErrorCheckBox.Checked)
        // Set a column error on to demonstrate.
        ds.Tables[0].Rows[0].SetColumnError(0, "test error");
    else
        // Clear the demonstration column error.
        ds.Tables[0].Rows[0].SetColumnError(0, "");

    da.Update(ds, TABLENAME);

    result.Append("Dataset.HasErrors = " + ds.HasErrors +
        Environment.NewLine);

    // Iterate over the collection of tables in the DataSet.
    foreach(DataTable dt in ds.Tables)
    {
        // Display whether the table has errors.
        result.Append("\tTable [" + dt.TableName + "] HasErrors = " +
            dt.HasErrors + Environment.NewLine);

        int rowCount = 0;
        // Iterate over the rows in the table having errors.
        foreach(DataRow row in dt.GetErrors( ))
        {
            // Display whether error information for the row.
            result.Append("\t\tRow [" + (++rowCount) +
                "] HasErrors = " + row.HasErrors +
                Environment.NewLine);
            if(row.RowError != "")
                result.Append("\t\t" + row.RowError +
                    Environment.NewLine);

            // Iterate over the column errors for the row.
            foreach(DataColumn col in row.GetColumnsInError( ))
            {
                // Display error information for the column.
                result.Append("\t\t\tColumn [" + col.ColumnName +
                    "]: " + row.GetColumnError(col) +
                    Environment.NewLine);
            }
        }
    }

    resultTextBox.Text = result.ToString( );
}

Discussion

The Update( ) method of the DataAdapter is used to reconcile changes made to a DataSet back to the underlying data source. If errors occur during the reconciliation, the ContinueUpdateOnError property of the DataAdapter specifies whether the update continues with remaining rows or stops processing:

• If ContinueUpdateOnError is true and an error is encountered during the update, the RowError property of the DataRow causing the error is set to the error message that would have been raised, the update of the row is skipped, and updating continues with subsequent rows. No exception is raised.

• If ContinueUpdateOnError is false, the DataAdapter raises a DBConcurrencyException when a row update attempt fails.

Once the update has completed, there are a number of properties and methods you can use to investigate errors in the DataSet, DataTable, DataRow, and DataColumn objects:

HasErrors

This property exists for the DataSet, DataTable, and DataRow objects. It returns a Boolean value indicating whether there are any errors within the object. Checking the HasErrors property for an object before calling specific error retrieval methods can improve performance by eliminating unnecessary calls to the more time consuming error retrieval methods.

GetErrors( )

This method of the DataTable returns an array of DataRow objects having errors.

RowError

This property of the DataRow returns the custom error description for the row.

GetColumnsInError( )

This method of the DataRow returns an array of columns containing errors. Rather than iterating over the collection of columns in a row and checking each for an error, this method reduces error processing by returning only those columns having an error.

GetColumnError( )

This method of the DataRow returns the error description for a column. Column errors are set using the SetColumnError( ) method of the DataRow; errors for individual columns are not set by the DataAdapter.

ClearErrors( )

This method of the DataRow clears all errors for a row including both the RowError and errors set using the SetColumnError( ) method.

The solution code demonstrates how to use these error checking methods and properties when updates are made with user-specified data.