DataAadapter Events in ADO.NET

Mahesh Chand
14y
9.4k
0
0

Article

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