Working with DataAdapter Events
The DataAdapter has a FillError event that occurs when there's an error during a Fill method. It allows a user to determine whether a fill operation should continue. FillError may occur when data from a database can't be converted to a Common Language Runtime (CLR) type without losing precision; or it may occur when data type casting isn't valid.
FillErrorEventHandler handles the FillError event. The event handler receives an argument of type FillErrorEventArgs containing data related to this event. The FillErrorEventHandler is defined as follows:
Public Delegate Sub FillErrorEventHandler(_ ByVal sender As Object, _ ByVal e As FillErrorEventArgs _ )
In this example, sender is the source of the event and e is the FillErrorEventArgs that contains the event data.
Table 5-3 describes the FillErrorEventArgs properties.
| PROPERTY | DESCRIPTION |
|---|---|
| Continue | Represents a value indicating whether to continue the Fill operation |
| DataTable | Returns the data table being updated when the error occurred |
| Errors | Returns the errors |
| Values | Returns the rows being updated when the error occurred |
The RowUpdated and RowUpdating events are two more events supported by a DataAdapter. The RowUpdating event occurs when an Update method is called before a command is executed against the data source. The RowUpdated event occurs when a command is executed.
The RowUpdated event handler receives an argument SqlRowUpdatedEventArgs containing data related to this event. The SqlRowUpdatedEventArgs contains the members in Table 5-4.
| PROPERTY | DESCRIPTION |
|---|---|
| Command | Returns the Command object executed when Update is called |
| Errors | Returns errors generated during the Update operation |
| RecordsAffected | Returns the number of rows affected during inserting, updating, and deleting |
| Row | Returns the data row sent through an Update |
| StatementType | Returns the SQL statement type |
| Status | Returns the UpdateStatus of a command |
| TableMapping | Returns the DataTableMapping sent through an Update |
Similarly to the RowUpdated event, the RowUpdating event handler receives an argument of type SqlRowUpdatedEventArgs that defines the same properties as the SqlRowUpdated with the same meaning.
To test the DataAdapter events and event handlers, create a Windows application, add a Button control to the form, and change its name to DataAdapterEventsTestBtn. Also add a SqlDataAdapter by dragging the SqlDataAdapter from the Toolbox Data tab to the form and configure the DataAdapter using the Data Adapter Configuration Wizard. Select the CompanyName, ContactName, and CustomerID columns from the Customers table. The final SELECT statement looks like the following:
SELECT CustomerID, CompanyName, ContactName FROM Customers
| Note | See Chapter 2 for more details about the Data Adapter Configuration Wizard. |
Now add all three FillError, RowUpdating, and RowUpdated event handlers manually. Listing 5-3 defines the event handler bodies for these three methods.
Listing 5-3: SqlDataAdapter Event Handler Bodies
 |
'FillError event handler Private Shared Sub FillError(ByVal sender As Object, _ ByVal args As FillErrorEventArgs) End Sub 'RowUpdating event handler Protected Shared Sub OnRowUpdating(ByVal sender As Object, _ ByVal e As SqlRowUpdatingEventArgs) End Sub 'RowUpdated event handler Protected Shared Sub OnRowUpdated(ByVal sender As Object, _ ByVal e As SqlRowUpdatedEventArgs) End Sub
|
Now you need to write code on these event handlers. Listing 5-4 shows the source code for the FillError event. You can also use other members of EventArgs to get more details. This event handler will execute when an error occurs during the Fill method.
Listing 5-4: DataAdapter FillError Event Handler Code
|
'FillError event handler Private Shared Sub FillError(ByVal sender As Object, _ ByVal args As FillErrorEventArgs) If args.Errors.GetType() Is Type.GetType("System.OverflowException") Then MessageBox.Show("Error in Fill operation for table " & _ args.DataTable.TableName & _ ", Error Message: " + args.Errors.Message.ToString() & _ ", Source: " + args.Errors.Source.ToString()) args.Continue = True End If End Sub |
Listing 5-5 and Listing 5-6 show the code for the RowUpdated and RowUpdating event handlers. The RowUpdated event handler is called when a row is updated using the Update method of a DataAdapter, and RowUpdating is called when a row is updating.
Listing 5-5: The RowUpdated Event Handler
|
'RowUpdated event handler Protected Shared Sub OnRowUpdated(ByVal sender As Object, _ ByVal e As SqlRowUpdatedEventArgs) If e.Status = UpdateStatus.ErrorsOccurred Then MessageBox.Show("Error Message: " + e.Errors.Message.ToString() & _ ", Source: " + e.Errors.Source.ToString()) e.Row.RowError = e.Errors.Message e.Status = UpdateStatus.SkipCurrentRow Else MessageBox.Show("Updated") End If End Sub |
Listing 5-6: The RowUpdating Event Handler
|
'RowUpdating event handler Protected Shared Sub OnRowUpdating(ByVal sender As Object, _ ByVal e As SqlRowUpdatingEventArgs) ' Inserting If e.StatementType = StatementType.Insert Then MessageBox.Show("Inserting") End If ' Updating If e.StatementType = StatementType.Update Then MessageBox.Show("Updating") End If 'Deleting If e.StatementType = StatementType.Delete Then MessageBox.Show("Deleting") End If ' Selecting If e.StatementType = StatementType.Select Then MessageBox.Show("Selecting") End If End Sub |
As you can see from Listing 5-6, you can compare the StatementType member of OleDbRowUpdatingEventArgs to the StatementType enumeration to find out the statement type. The StatementType enumeration defines Select, Insert, Update, and Delete members.
To test these events you can write code that fills a DataSet and calls the Update method of the DataAdapter. You add an event handler for the button by double-clicking it and writing the code in Listing 5-7. As you can see, you call AddHandler and RemoveHandler.
Listing 5-7: Calling the DataAdapter's Fill and Update Methods
|
Private Sub DataAdapterEventsTestBtn_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles DataAdapterEventsTestBtn.Click ' Create InsertCommand SqlDataAdapter1.InsertCommand = New SqlCommand _ ("INSERT INTO Customers (CustomerID, CompanyName)" & _ "VALUES(@CustomerID, @CompanyName)", SqlConnection1) SqlDataAdapter1.InsertCommand.Parameters.Add("@CustomerID", _ SqlDbType.VarChar, 5, "CustomerID") SqlDataAdapter1.InsertCommand.Parameters.Add("@CompanyName", _ SqlDbType.VarChar, 30, "CompanyName") ' Opening Connection SqlConnection1.Open() ' Create and Fill DataSet Dim custDS As DataSet = New DataSet() SqlDataAdapter1.Fill(custDS, "Customers") ' add handlers AddHandler SqlDataAdapter1.RowUpdating, AddressOf OnRowUpdating AddHandler SqlDataAdapter1.RowUpdated, AddressOf OnRowUpdated ' Add a new data row and call Update method ' of data adapter Dim custRow As DataRow = custDS.Tables("Customers").NewRow() custRow("CustomerID") = "NEWCO" custRow("CompanyName") = "New Company" custDS.Tables("Customers").Rows.Add(custRow) SqlDataAdapter1.Update(custDS, "Customers") RemoveHandler SqlDataAdapter1.RowUpdating, AddressOf OnRowUpdating RemoveHandler SqlDataAdapter1.RowUpdated, AddressOf OnRowUpdated ' Close the connection SqlConnection1.Close() SqlConnection1.Dispose() End Sub |
You can also use SelectCommand, UpdateCommand, and DeleteCommand objects to select, update, and delete data from the table. See the "The Command: Executing SQL Statements" section of Chapter 4 for more on using commands.
Now if you run the application, you'll see the insert message; if you click the button more than once, you'll get the following error message: "Violation of PRIMARY KEY constraint 'PK_Customers.' Cannot insert duplicate key in object 'Customers.'"
EAN: 2147483647
Pages: 214