This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

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 void FillErrorEventHandler(object sender, FillErrorEventArgs e );

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.jpg

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.jpg

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 void oleDbDataAdapter1_FillError(object sender, System.Data.FillErrorEventArgs e)
        {
            if (e.Errors.GetType() == typeof(System.OverflowException))
            {
                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;
            }
        }

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 void oleDbDataAdapter1_RowUpdated(object sender, System.Data.OleDb.OleDbRowUpdatedEventArgs e)
        {
            if (e.Status == UpdateStatus.ErrorsOccurred)
            {
                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");
            }
        }

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 void oleDbDataAdapter1_RowUpdating(object sender, System.Data.OleDb.OleDbRowUpdatingEventArgs e)
        {
             // Inserting
            if (e.StatementType == StatementType.Insert)
            {
                MessageBox.Show("Inserting");
            }

             // Updating
            else if (e.StatementType == StatementType.Update)
            {
                MessageBox.Show("Updating");
            }

            // Deleting
            else if (e.StatementType == StatementType.Delete)
            {
                MessageBox.Show("Deleting");
            }

             //Selecting
            else if (e.StatementType == StatementType.Select)
            {
                MessageBox.Show("Selecting");
            }
        }

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 void DataAdapterEventsTestBtn_Click(object sender, System.EventArgs e)
        {
            OleDbConnection conn = new OleDbConnection();
            string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=C:\\NorthWind.mdb";
             conn.ConnectionString = strDSN;
            OleDbDataAdapter da = 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
            DataSet custDS = new DataSet();
            da.Fill(custDS, "Customers");

             // Add a new data row and call Update method
             // of data adapter
            DataRow custRow = 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();
        }

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.jpg

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.

adobook.jpg
This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.

Next Recommended Readings