Using RowError of the DataSet

for RuBoard

Using RowError of the DataSet

To be much more efficient in responding to individual row error conditions, you can add error information to the row itself (even at the column level). The DataRow object allows you to do this by providing a RowError property for each row in the DataSet . As data rows are processed (for example, when updating them), you can set the RowError property for a row to indicate that it has an error. Then, simply use the HasErrors property to determine whether any error information has been added to any of the rows in the dataset. You can use the GetErrors method to return and examine only the rows with errors. It's all very slick.

Listing 13.3 is a short piece of code that adds a RowError condition to a particular row in the Customers DataSet (the first row), testing to see if any rows in the DataSet have errors, and then displaying the company name and error text of any row with an error.

Listing 13.3 Adding a RowError condition to a Row (13ADOErrors.vb)

 . . . CustomerDS.Tables("Customers").Rows(0).RowError = "Invalid Customer row - Error" if CustomerDS.Tables("Customers").HasErrors then    Dim ErrDataRows as DataRow()    ErrDataRows = CustomerDS.Tables("Customers").GetErrors()    Console.WriteLine("DataTable {0}  has {1}  Error(s)", CustomerDS.Tables("Customers").TableName, ErrDataRows.Length.ToString())     Dim i as integer     for i = 0 to ErrDataRows.Length -1       Console.WriteLine("Row Error for {0}  ** {1} ",ErrDataRows(i)("CompanyName").ToString(),ErrDataRows(i).RowError)         next else    Console.WriteLine("DataTable {0}  Has no errors", CustomerDS.Tables("Customers").TableName) end if 

When executed, the preceding code yields:

 C:\ADOSAMPLES>  13ADOErrors.exe  <press enter> DataTable Customers has 1 Error(s) Row Error for ABCDE Company ** Invalid Customer row - Error 

And lastly, we can go back and enhance our Customer Inquiry form application to include a significant validation error-handling improvement that we invoke with a new Validate button.

1. From the Windows Forms Designer, drag another Button object from the Windows Forms Toolbox onto the form.

2. Press F4 to take you directly to the properties of this button. Change the name of the button to btnValidate and the text of the button to Validate .

3. Now, double-click the Validate button object on the form so that you can add the following For Each row logic. The logic will check each row to see if the ContactTitle is "Owner", and if it finds one, it sets an error for that row and also sets a specific error for the column of the row. This will have a dramatic effect on what is displayed in the DataGrid for any data row that has this error condition.

    Dim CustomersTable As DataTable CustomersTable = Me.CustomerDS1.Tables("Customers") Dim row As DataRow For Each row In CustomersTable.Rows     If (row("ContactTitle") = "Owner") Then         row.RowError = "No Owners Please"         row.SetColumnError("ContactTitle",                    "Contact cannot be Owners")     End If Next row 

Now, once again, hit F5 and test your application by just clicking on the Validate button. You should now see red error indications for each row that has this validate error, and the ContactTitle column should also have this indication. To clear this validation indication, just click the Refresh button. Figure 13.4 shows the initial form with all customers and then the same form after the validation button was clicked.

Figure 13.4. Customer Inquiry form successful execution and Customer Inquiry form with validate exceptions displayed.

DataAdapter Events

The ADO.NET DataAdapter exposes three events that you can use to respond to changes made to data at the data source. These are the RowUpdating event, RowUpated event, and FillError event. You will typically use the status property to determine what to do about any error that has occurred during the execution of the DataAdapter .

• RowUpdating event This event is raised before any update, insert, or delete on a row has been pushed to the data source. It has only been done at the dataset.

• RowUpdated event This event is raised after any update, insert, or delete has been completed to the data source.

• FillError event This event is raised when an error occurs during a Fill operation.

By far, the RowUpdated event yields the most value, especially because most coding you will do must support the optimistic concurrency model and using the RowUpdated method to handle this condition is very straightforward. The following piece of code implements the optimistic concurrency approach for an update that utilizes the RowUpdated method for its handler.

You first have to set up a shared subroutine that will look at whether the UPDATE statement returned any rows. If it returns a row, the update was successful. If recordsaffected is 0, the WHERE comparison in the UPDATE statement failed, and this is an optimistic concurrency violation.

 Private Shared Sub OnRowUpdated(sender As Object, updevent        As SqlRowUpdatedEventArgs)      If updevent.RecordsAffected = 0         updevent.Row.RowError = "Optimistic Concurrency Violation"         updevent.Status = UpdateStatus.SkipCurrentRow      End If    End Sub 

Then in the main code, you add the handler, fill the dataset, make your updates, and issue the update back to the data source. The handler will do the rest.

 AddHandler CustomerAdapter.RowUpdated, New SqlRowUpdatedEventHandler                             (AddressOf OnRowUpdated) Dim CustomerDataSet As DataSet = New DataSet() CustomerAdapter.Fill(CustomerDataSet, "Customers") CustomerAdapter.Update(CustomerDataSet, "Customers") Dim CustRow As DataRow For Each CustRow In CustomerDataSet.Tables("Customers").Rows   If CustRow.HasErrors Then Console.WriteLine(CustRow(0) &      vbCrLf & CustRow.RowError)   if not CustRow.HasErrors then Console.Writeline                      ("No optimistic concurrency error found") Next 

XML Persisted Row Errors

As mentioned earlier, row errors can also be persisted in the XML structure that is to be passed to other consumers of XML. The following illustrates the additional XML structure as provided by the DiffGram XML option. DiffGram is great for dealing with issues such as optimistic concurrency violations.

Basically, a DiffGram is divided into three sections (blocks). The first one contains the current data (the current DataSet values block), the second is the original data block ( < diffgr :before> the data as it was read in from the data source before it was modified), and the third is the error block ( <diffgr:errors> ) for noting whether any errors (like optimistic concurrency) have occurred as you processed (updated) the data.

For a more complete explanation, take a look at Hour 14, "Managing ADO.NET Concurrency." A full example is described in that hour .

for RuBoard