Odd behaviour with autoincrement keys

Postby Alexey » Wed 14 Jun 2006 08:19

Send us your test project with all sources, compiled binaries and SQL statements for creating and populating database objects.
Last edited by Alexey on Thu 29 Jun 2006 10:22, edited 1 time in total.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby MrBishop » Wed 28 Jun 2006 02:50

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.
MrBishop
 

Previous

Return to dotConnect for MySQL