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 as follows:
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();
}
}
Now, the above fetches the last_insert_rowid() because I'm able to see it when I debug the above 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"];
What I have tried:
I've tried the below code as stated in the problem description:
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();
}
}