Odd behaviour with autoincrement keys

[Alexey]

Send us your test project with all sources, compiled binaries and SQL statements for creating and populating database objects.

[MrBishop]

Hi, I just kind of stumbled onto this topic while testing this product, and would like to add my experiences.

I was under the impression that the difficulties being expressed on the topic of autoincrementing were related to ADO.NET in general, and not limited to this product. While investigating a similar autoincrement problem, I found several topics and tutorials on the web dealing with the issue.

My understanding of the autoincrement in DataTables is that it is meant only as a placeholder that is based on what is loaded into it. It's not meant to mirror the behavior of the underlying database. The problem with relying on the "ai" value in the DT is that, unless you lock the underlying table, it's possible for another connection to add rows and invalidate all your "ai" values.

My solution to the problems this caused when inserting new rows has three parts:

1) Set both AutoIncrementSeed and AutoIncrementStep properties to -1.
2) Catch the RowUpdating event and use code similar to:
if ( e.StatementType == StatementType.Insert ) {
int oldId = Int32.Parse( e.Row[ "id" ].ToString() );

// If the id is 0, MySql will auto-assign a valid id
if ( oldId < 0 )
e.Row[ "id" ] = 0;
}

3) Catch the RowUpdated event and use code similar to:
if ( e.StatementType == StatementType.Insert &&
e.Status == UpdateStatus.Continue )
{
MySqlCommand cmd =
new MySqlCommand( "SELECT LAST_INSERT_ID()",
(MySqlConnection)conn );
e.Row[ "id" ] = cmd.ExecuteScalar();
e.Row.AcceptChanges();
}

I've been using this method with no problems with DataSets of 2 or 3 related tables.