Retrieving Update Errors

[ Team LiB ] 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.
Nội dung trích xuất từ tài liệu:
Retrieving Update Errors[ Team LiB ]Recipe 2.15 Retrieving Update ErrorsProblemYou want to access all of the error information available after an update fails.SolutionUse 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 NoNumericField int 4 NoStringNoNullsField nvarchar 50 NoConstrainedNegativeField int 4 NoThe 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 rows GetColumnsInError( ) method. For each column having an error, the rows 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 constantsusing 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( );}DiscussionThe Update( ) method of the DataAdapter is used to reconcile changes made to a DataSetback to the underlying data source. If errors occur during the reconciliation, theContinueUpdateOnError property of the DataAdapter specifies whether the updatecontinues 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 ...