DataAdapter_RowUpdated Event's row changes aren't reflected in DataSet and DataTable-CodePudding

My situation involves batch updates to individual tables in an SQLite database through ADO.NET objects. I use the DataAdapter.Update() method to push the changes which works well:

DataTable changes = dataset.Tables[table].GetChanges();
if (changes == null) return 0;
SQLiteCommandBuilder scb = new SQLiteCommandBuilder(adapter);
scb.ConflictOption = ConflictOption.CompareRowVersion;
int cnt = adapter.Update(changes);
return cnt;

However each time a record is inserted I also want the local DataSet tables to reflect with the newly inserted row id. For this I use the adapter_RowUpdated event :

static void adapter_RowUpdated(object sender, 
    System.Data.Common.RowUpdatedEventArgs e)
{
    if (e.StatementType == StatementType.Insert)
    {
        SQLiteCommand cmd = new SQLiteCommand("select last_insert_rowid();", conn);
        e.Row["id"] = cmd.ExecuteScalar();
    }
}

The above fetches last_insert_rowid() because I'm able to see it when I debug by putting a breakpoint. However, the assignment statement to e.Row["id"] isn't working. The id change isn't reflected in my original DataSet and DataTable objects. For example when I test the following value (N refers to the specific row index), it still has a DBNull value. What is going wrong here? How can I ensure that the specific row which just got inserted is updated with its corresponding id field value?

dataset.Tables["projects"].row[N]["id"];

CodePudding user response：

After a little experimenting, I found the solution to this myself.

As strange as it may sound but it looks like adapter.Update() requires a dataset along with the actual table name in order for this to work. I was passing the table object (DataTable.GetChanges()) so far which did the job of updating the database but failed only in this particular scenario. The moment I did that, the inserted id started reflecting in rows all over the dataset!

//int cnt = adapter.Update(changes); // doesn't work
int cnt = adapter.Update(dataset, tableName); // works perfectly!

edit

Lo and Behold! It even works when I just pass the table like this instead of entire dataset. It was only causing problem when I was just passing the changes table (got from dataset.Tables[tableName].GetChanges()).

int cnt = adapter.Update(dataset.Tables[tableName]); // works perfectly!