Syncing DataTable autoincremented columns with Database

Question

The DBConcurrencyException issue occurs when trying to use the Update() method on my Database. I have a table in a database that has an autoincremented ID column, and a DataTable in my c# program that gets its information from this table (including the auto increment part when i use MissingSchemaAction = MissingSchemaAction.AddWithKey).

If I create rows and add them to the datatable, the datatable automatically fills in the autoincremented ID column (starting where the database table left off) for me, which is fine. However if I delete the rows I just added (without first using Update() ) and add new ones, the datatable autoincrement column is filled with a value based on where the DATATABLE is, not where the database is, which is why I get the concurrency error.

for example:

The table in the database has these records:

1 Apple
2 Orange
3 Pear

Which gets copied to the datatable, so when I add a new row with the name value "grape" I get:

1 Apple
2 Orange
3 Pear
4 Grape

Which is fine, however if don't run the Update() method, and I delete the grape row and add a new row "Melon" I get:

1 Apple
2 Orange
3 Pear
5 Melon

And when I try to run Update(), the database is expecting 4 to be the next autoincremented value but instead is getting 5. So I get the error. The Update() occurs when the user clicks a "save" button, so ideally I'd like them to be able to make lots of changes as shown above before finally saving, but is the only way to preserve the concurrency to use Update() after each row is added/deleted?

Answer 1

My first thought would have been that you should just handle the case that a row is being deleted and first update it, then delete it to keep the auto-increment IDs in sync.

However, I have run into this same situation and it seems to be caused by the 3rd party control my DataGridView is hosted in. Specifically, the problem occurs when the user has focus in the "new" row of the DataGridView, switches to another application and then clicks back into the DataGridView. At this point the original DataRow instance for the new row is deleted and a new one is created with an incremented ID value. I have not been able to figure out a way to handle the deletion of the row before it is actually deleted, nor can I figure out what the 3rd party control is doing that triggers this.

Therefore, for the moment I am handling this problem in a very heavy-handed way, by querying the correct auto-increment value from the database an correcting new DataRows if necessary. If all else fails, this solution seems to work. (Note I am using SqlCe instead of MySQL)

void OnLoad()
{
    base.OnLoad(e);
    ...
    _dataTable.TableNewRow += HandleTableNewRow;
}

void HandleTableNewRow(object sender, DataTableNewRowEventArgs e)
{
    SetAutoIncrementValues(e.Row);
}

void SetAutoIncrementValues(DataRow row)
{
    foreach (DataColumn dataColumn in _dataTable.Columns
        .OfType<DataColumn>()
        .Where(column => column.AutoIncrement))
    {
        using (SqlCeCommand sqlcmd = new SqlCeCommand(
            "SELECT AUTOINC_NEXT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" +
            Name + "' AND COLUMN_NAME = '" + dataColumn.ColumnName + "'", _connection))
        using (SqlCeResultSet queryResult =
            sqlcmd.ExecuteResultSet(ResultSetOptions.Scrollable))
        {
            if (queryResult.ReadFirst())
            {
                var nextValue = Convert.ChangeType(queryResult.GetValue(0), dataColumn.DataType);

                if (!nextValue.Equals(row[dataColumn.Ordinal]))
                {
                    // Since an auto-increment column is going to be read-only, apply
                    // the new auto-increment value via a separate array variable.
                    object[] rowData = row.ItemArray;
                    rowData[dataColumn.Ordinal] = nextValue;
                    row.ItemArray = rowData;
                }
            }
        }
    }
}

Answer 2

The expected value is 5 - it would be insanely inefficient for the database to try and fill in the holes in the column every time you do something. Once an auto_increment is used it is gone forever.

Because of this always make sure your column is big enough hold all your records. If you used TINYINT for example then you can only have a 127 record in your table.

The auto increment is stored at the table level and Mysql never looks back to see if it could be lower. You can manually change it by doing the following:

ALTER TABLE tablename AUTO_INCREMENT=2;

But if you do this and there is a collision down the road - bad things are going to happen.

Or you can inspect what it is

SHOW CREATE TABLE tablename;
CREATE TABLE `tablename` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cat_id` int(10) unsigned NOT NULL,
`status` int(10) unsigned NOT NULL,
`date_added` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `categories_list_INX` (`cat_id`,`status`),
KEY `cat_list_INX` (`date_added`,`cat_id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

And you find out what the last one is.

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)