My datagrid's selection is lost when updating my database from the grid's bound dataset. Any workarounds?

Question

I am using Xceed's Datagrid, bound to a dataset. I want to update the database when the datagrid's RowEditEnd is called. The problem is that in order to avoid concurrency violations when updating, I have to clear the dataset after update and refill it, like this:

public void UpdateDatabaseFromDataSet()
{
    adapter.Update(exampleDataSet);
    exampleDataSet.Clear();
    adapter.Fill(exampleDataSet);
}

I think I have to do this because my dataset's primary autoincrement column "ID" does not match the values generated by the database. When I clear the dataset, it clears the datagrid and its selected cell. This is very annoying since, if you edit a cell and hit enter or a directional key, the cell and database will update fine, but your selection gets reset instead of navigating to the next row/cell. This makes entering data by hand very cumbersome.

Here is the method that creates the dataset:

public void InitDataSet(int tableid)
{
    cmd = new SQLiteCommand("SELECT * FROM table_" + tableid, con);
    adapter = new SQLiteDataAdapter(cmd);
    cb = new SQLiteCommandBuilder(adapter);

    try
    {

        exampleDataSet = new DataSet();

        adapter.Fill(exampleDataSet);
        prodDataSet.Tables[0].Columns[0].AutoIncrementSeed = -1;
        prodDataSet.Tables[0].Columns[0].AutoIncrementStep = -1;

        currenttableID = tableid;
    }
    catch (ApplicationException ex)
    {
        MessageBox.Show("Encountered an error.", "Error: " + ex.ToString());
    }
}

Note that I have tried setting the tables autoincrementseed and autoincrementsteps to -1 but I still get concurrency violations if I don't refill the dataset.

I would really like my datagrid to work the way it does if I do not clear and refill the dataset. Is there anyway to avoid the concurrency violations I'm running into? Am I going about this the wrong way?

Thanks for your help. -Steven

Answer 1

I don't exactly understand the complete problem but I might be able to give you something
When you don't clear the dataset and call "fill" it'll try to "merge" the records from the DB with the existing records. The clear forces your grid to refresh I guess.. losing the selected row.

With
Adapter.FillLoadOption = LoadOption.PreserveChanges (or other);
You can influence how records from the db that are changed are merged with the dataset.

Now there is one problem left and that is to determine the new autoinc value when an insert has taken place. You can catch the Adapter.RowUpdated

void Adapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
        {
            if (e.StatementType==StatementType.Insert)
            {

                Select last autoinc value with @@Identity  
                e.Row["ID"] = last autoinc value
            } 
        }

So :

• Don't clear de datatable
• Catch rowupdated and get the latest autoinc value in the db in put it in the new row
• Evt. call fill to see if other users have made changes

Answer 2

Here is the code that ended up working for me, putting it here for other's benefits.

Here's my data providers update method (note that I was able to remove the extra fill call):

public void UpdateDatabaseFromDataSet()
{
    adapter.Update(exampleDataSet);
}

And here is the RowUpdated event I'm using:

void adapter_RowUpdated(object sender, System.Data.Common.RowUpdatedEventArgs e)
{
    if (e.StatementType == StatementType.Insert)
    {
        SQLiteCommand cmdNewID = new SQLiteCommand("SELECT last_insert_rowid()", con);
        e.Row["RowID"] = cmdNewID.ExecuteScalar();
    }
}