Delete Dataset Records in ADO.NET

The code for handling the Delete button is even simpler. First, get the target row and form the delete message. You don't want to show the message until the row is deleted, but you need to get it now because after you delete the row it will be too late.

Calling AcceptChanges( ) on the DataSet causes AcceptChanges( ) to be called on each table within the DataSet. This in turn causes AcceptChanges( ) to be called on each row in those tables. Thus the one call to dataSet.AcceptChanges( ) cascades down through all the contained tables and rows.

Next, you need to call Update() and AcceptChanges( ), and then refresh the list box. However, if this operation fails, the row will still be marked for deletion. If you then try to issue a legitimate command, such as an insertion, update, or another deletion, the DataAdapter will try to commit the erroneous deletion again, and the whole batch will fail because of that delete. In order to avert this situation, wrap the remaining operations in a try block and call RejectChanges( ) if they fail.

Deleting records from the Customers database might cause an exception if the record deleted is constrained by database integrity rules. For example, if a customer has orders in the Orders table, you cannot delete the customer until you delete the orders. To solve this, the following example will create new Customer records that you can then delete at will.

					
// **** Handle the DELETE button click
protected void btnDelete_Click (object sender, System.EventArgs e)
{
    // Get the selected row
    DataRow targetRow  = _dataTable.Rows[listBoxCustomers.SelectedIndex];
    // Prepare message for user
    string msg = targetRow["CompanyName"] + " deleted. ";
    // Delete the selected row
    targetRow.Delete();
    // Update the database
    try
    {
        _dataAdapter.Update(_dataSet,"Customers");
        _dataSet.AcceptChanges();
        // Repopulate the list box without the deleted record
        PopulateListBox();
        // Inform the user
        lblMessage.Text = msg;
        Application.DoEvents();
    }
    catch (SqlException ex)
    {
        _dataSet.RejectChanges();
        MessageBox.Show(ex.Message);
    }
}