Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

C#
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:

C#
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?

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


What I have tried:

I've tried the below code as stated in the problem description:

C#
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();
        }
    }
Posted
Updated 10-Oct-22 6:43am

1 solution

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!

C#
//int cnt = adapter.Update(changes); // doesn't work
int cnt = adapter.Update(dataset, tableName); // works perfectly!
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900