Retrieving autoincrement field values

Often people ask me how to retrieve the value of an autoincrement column when they are using the MySqlCommandBuilder object.  Generally this is done by appending something like ";SELECT last_insert_id()" onto the generated insert command text.  Command builder objects usually provide some type of syntax where a user can indicate that they want this to be done.  Connector/Net 1.0 had some syntax for this but starting with Connector/Net 5.0 that all changed. 

With the new ADO.Net 2.0 API, Microsoft did not directly provide for any type of API that users could use for selecting this.  With ADO.Net 2.0, there are two things that need to happen.  First, a SELECT statement similar to above must be attached to the INSERT command.  Second, the command object must have its UpdatedRowSource property set to Both or FirstReturnedRecord.  What this does is tell the data adapter update engine to take the first returned row and merge it's values back into the changed row.  So, if the first returned row is the result of a 'SELECT last_insert_id()', then the generated key will be returned and merged back in.  Good, huh?

Not so fast.  The UpdatedRowSource property exists on the command object.  The whole point of using MySqlCommandBuilder is to not mess with command objects.  Everything should be handled automatically with as little code as possible.  To this end, we've added a custom property to MySqlCommandBuilder called ReturnGeneratedIdentifiers.  By setting this property to true, the proper select statement will be included and the generated command object will have its UpdatedRowSource property set.