Delete rows from a table without checking that they exist first

Question

I have a WCF service that accepts a DataTable, and merges them into the existing data. Previously, this has just required adding rows, which works beautifully, but with this new requirement of deleting rows (whether or not they actually exist), I'm running into a problem.

Due to the fact that the amount of rows in question can be quite large, both in the SQL server and in the DataTable, I do not want to have to load in existing rows and compare them with my DataTable.

My code does the following:

public Statistics ApplyChanges(DataTable changeData)
{
    var stats = new Statistics();

    if (changeData.IsEmpty)
    {
         Trace.WriteLine(string.Format("Client {0} had nothing to do; called ApplyChanges anyway. (Should normally not happen.)", ClientId));
         return stats;
    }

    FooDataSet ds = new FooDataSet();
    ds.Bar.Merge(changeData, false, MissingSchemaAction.Ignore);

    foreach (var row in ds.Bar)
    {
        // This is the new requirement. If the incoming row's 'Value' is null,
        // delete the row from the database, or, if the row doesn't exist, do
        // nothing.
        if (row.Field<string>("Value") == null)
            row.Delete();
        else
            row.SetAdded();
    }

    int changesApplied;
    using (var scope = new TransactionScope())
    {
        BarTableAdapter barAdapter = new BarTableAdapter();
        changesApplied = barAdapter.Update(ds.Bar);
        scope.Complete();
    }

    stats.ChangesApplied = changesApplied;
    stats.ChangesFailed = ds.Bar.Count(r => r.HasErrors);

    Trace.WriteLine(string.Format("Client {0} applied {1} changes, had {2} changes fail.", ClientId, changesApplied, stats.ChangesFailed));

     return stats;
}

Now, I (perhaps naïvely) thought like with adding, that if a row didn't exist, it would either be silently ignored, or at the worst, have the HasErrors property set, but no. Instead, the line

changesApplied = barAdapter.Update(ds.Bar);

Throws an exception, DBConcurrencyException, with the following message: "Concurrency violation: the DeleteCommand affected 0 of the expected 1 records."

I can see why that's a nice notification to get when you care about concurrency, but I don't need to. I just want to either delete the row, or ignore that it's missing.

Answer 1

Here is a helpful article according to this issue. Quoting:

If the DataAdapter executes an update command and detects that the number of affected rows is 0, it throws a DBConcurrencyException . The entire update operation will be aborted, and no further rows in the DataSet will be examined. Usually, a DBConcurrencyException occurs for one of two reasons:

• You have incorrectly written the SQL for a custom UPDATE , INSERT , or DELETE command.
• The row can't be found because the information being used to find it doesn't match the current values. This problem signals that another user has changed the row since the last time you retrieved the information.

The second is your issue which you want to ignore.

There are two choices for handling the error. One option is to handle the DataAdapter.RowUpdated event that fires after a command has been executed but before an error has been raised. You can use this event handler to log problems, and programmatically instruct the DataAdapter to ignore the error and continue processing other errors. Here's an example that displays and skips all errors:

protected void OnRowUpdated(object sender, SqlRowUpdatedEventArgs e) // handles DataAdapter.RowUpdated
{
    // Check how many records were affected. ' If no records were affected, there was an error. 
    if (e.RecordsAffected == 0) {
        // log?
        // Don't throw an exception, continue with following 
        e.Status = UpdateStatus.SkipCurrentRow;
    }
}

Another, simpler choice is to set the DataAdapter.ContinueUpdateOnError property to true . Then, after the update is complete, you can investigate errors, log them, or display them to the user. The DataAdapter will attempt every change.

barAdapter.ContinueUpdateOnError = true; 

---

Since you're using a strongly typed TableAdapter which just holds the DataAdapter as protected property, you cannot change this setting directly. What you can do is to extend this autogenerated class(it's a partial class). Therefore create another class with the same name in the same directory, for example: public partial class BarTableAdapter.

Now you're able to create new properties or methods which can access the DataDapter. Note that the class has to sit in the same (autogenerated) namespace. For example:

namespace ApplicationName.DataSetNameTableAdapters
{
    public partial class BarTableAdapter 
    {
        public bool ContinueUpdateOnError
        {
            get
            {
                return this.Adapter.ContinueUpdateOnError;
            }
            set
            {
                this.Adapter.ContinueUpdateOnError = value;
            }
        }
    }
}

Don't extend the original class (.designer.cs), it will be overwritten on every change in the designer.

Now you are able to do:

BarTableAdapter barAdapter = new BarTableAdapter();
barAdapter.ContinueUpdateOnError = true;
changesApplied = barAdapter.Update(ds.Bar);