The data
adapter has a FillError event that occurs during a fill method. It allows a user to determine whether a fill operation should continue. For example, FillError could occur when you can't convert data from data from a database to Common Language Runtime (CLR) type without losing precision or when data type casting is not valid.
FillErrorEventHandler handles the FillError event. The event handler receives an argument of type FillErrorEventArgs, which contains data related to this event. The FillErrorEventHandler is as follows:
Public Delegate Sub FillErrorEventHandler(ByVal senderAs Object,ByVal e As FillErrorEventArgs)
Where sender is the source of the event and e is FillErrorEventArgs, which contains the event data. Table 9-3 defines the FillErrorEventArgs properties.
Table 9-3. The FillErrorEventArgs members
|
Property
|
Description
|
|
Continue
|
Represent a value indicating whether to continue the fill operator
|
|
DataTable
|
Return the data table being updated when the error occurred
|
|
Errors
|
Returns the errors
|
|
Values
|
Returns the rows being updated when error occurred
|
RowUpdated and RowUpdating are two more events that a data adapter supports. The RowUpdating event occurs when an Update method is called before a command executed against the data source. The RowUpdated event occurs when a command executed.
The RowUpdated event handler receives an argument of type OleDbRowUpdatedEventArgs that contains data related to this event. Table 9-4 describes the OleDbRowUpdateEventArgs members.
Table 9-4. The OleDbRowUpdatedEventArgs Members
|
Property
|
Description
|
|
Command
|
Returns the command object executed when Update is called
|
|
Errors
|
Returns errors generated during the update operation
|
|
RecrodsAffected
|
Returns the number of rows affected during insert, update, and delete operations
|
|
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
|
Similar to RowUpdated, the RowUpdating event handler receives an argument of type OleDbRowupdatingEventArgs that defines the same properties as OleDbRowUpdated with the same meaning.
Ok, now it's time to write an application to test the data adapter events. I created a Windows application and added a button control to the form. After that I changed the button's name property to DataAdapterEventsTestBtn and wrote a button-click event handler by double-clicking on the button control.
Next, I added a data adapter control to the form by dragging the OleBbdAtaadapter component from the toolbox's data tab to the form. As you drop the OleDbDataAdapter component to the form, the data adapter configuration wizard pops up. In this application, I added the Customers table to the Query Builder and selected the CompanyName, ContactName, and CustomerID columns from the Customers table. As you can see from figure 9-8, the Query Builder shows the SQL statement.
Figure 9-8. The query builder with the SELECT statement from the Customers table
Next, I used the Properties > Event (the lightning bolt) button of the data adapter to add the event handlers. I added all three FillError, RowUpdating and RowUpdated event handlers, as shown in Figure 9-9.
Figure 9-9 AddingDataAdapter events from the Properties window
Now you can write code on the FillError event handler. You call the FillError event handler when the Fill method of data adapter of data adapter fails. Listing 9-3 shows the code for the FillError event handler. As you can see from listing 9-3, I used DataFillErrorEventArgs's members to get information about the error.
Listing 9-3. DataAdapter FillError event handler code
'FillError Event Handler
Private Sub oleDbDataAdapter1_FillError(ByVal sender As Object,ByVal e As System.Data.FillErrorEventArgs)
If e.Errors.[GetType]()Is GetType(System.OverflowException) Then
MessageBox.Show((("Error in Fill operation for table" & e.DataTable.TableName &", Error Message: ") + e.Errors.Message.ToString() & ", Source: ") + e.Errors.Source.ToString())
e.[Continue] = True
End If
End Sub
Listing 9-4 and 9-5 show the code for the RowUpdated and RowUpdating event handlers. You call the RowUpdated event handler when a row is updated using the Update method of a data adapter, and you call RowUpdating when a row is updating.
Listing 9-4. The RowUpdated event handler
' RowUpdated event handler
Private Sub oleDbDataAdapter1_RowUpdated(ByVal sender As Object,ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs)
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
As you can see from Listing 9-5, 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.
Listing 9-5, The RowUpdating event handler
Private Sub oleDbDataAdapter1_RowUpdating(ByVal sender As Object,ByVal e As System.Data.OleDb.OleDbRowUpdatingEventArgs)
' Inserting
If e.StatementType = StatementType.Insert Then
MessageBox.Show("Inserting")
' Updating
ElseIf e.StatementType = StatementType.Update Then
MessageBox.Show("Updating")
' Deleting
ElseIf e.StatementType = StatementType.Delete Then
MessageBox.Show("Deleting")
'Selecting
ElseIf e.StatementType = StatementType.[Select] Then
MessageBox.Show("Selecting")
End If
End Sub
Now, to test these events you can write code that fills a dataset and calls the update method of data adapter. I added an event handler for the button by double- clicking on it and writing the code shown in listing 9-6. As you can see, I created a new OleDbDataAdapter object and copied oleDbDataAdapter1 to it (the framework added oleDbDataAdapter1).
Listing 9-6.Calling DataAdapter's Fill and update methods
Private Sub DataAdapterEventsTestBtn_Click(ByVal sender As Object,ByVal e As System.EventArgs)
Dim conn As New OleDbConnection()
Dim strDSN As String ="Provider=Microsoft.Jet.OLEDB.4.0;" &"Data Source=C:\NorthWind.mdb"
conn.ConnectionString = strDSN
Dim da As New OleDbDataAdapter()
da = oleDbDataAdapter1
' Creating InsertCommand
da.InsertCommand = New OleDbCommand("INSERT INTO Customers(CustomerID,CompanyName)" & "VALUES(?, ?)", conn)
da.InsertCommand.Parameters.Add("@CustomerID", OleDbType.VarChar, 5, "CustomerID")
da.InsertCommand.Parameters.Add("@CompanyName", OleDbType.VarChar, 30, "CompanyName")
' Opening Connection
conn.Open()
' Create and Fill DataSet
Dim custDSAs New DataSet()
da.Fill(custDS, "Customers")
' Add a new data row and call Update method
' of data adapter
Dim custRowAs DataRow = custDS.Tables("Customers").NewRow()
custRow("CustomerID") ="NEWCO"
custRow("CompanyName") ="New Company"
custDS.Tables("Customers").Rows.Add(custRow)
da.Update(custDS, "Customers")
' Close the Connection
conn.Close()
End Sub
Note: Don't forget to add a reference to the System.Data.OleDb namespace.
You can also use the SelectCommand, UpdateCommand, and DeleteCommand objects to select, update, and delete date from the table.
Now, if you run the application, you'll see an insert message; if you click the button more than once, you'll get the error shown in Figure 9-10.
Figure 9-10. The error message generated by the RowUpdated event handler when trying to add duplicate employee records
Conclusion
Hope this article would have helped you in understanding working with DataAadapter Events in ADO.NET. See other articles on the website also for further reference.