I have a dataset with multiple related tables, all connected to my MySQL database. I'm using TableAdapters to perform all Fill and Update procedures.
When I insert a new row into a datatable, it auto-generates a new ID. When the data for this row is inserted into my database, the database ignores the value in my ID field, and replaces it with its own value. This value is accessible manually using SELECT LAST_INSERT_ID();. This is the behaviour I expected, and it is why I'm using negative numbers as my internally generated IDs.
How can I properly sync up my datatable with the table in the database, so the row data is automatically updated with the new ID? I have seen solutions with other providers where a SELECT LAST_INSERT_ID(); command is inserted after the Insert command, and the value is automatically updated.
I would like to be able to call tableadapter.update, and have all values taken care of automatically. I could also do some event handling, and process it this way. I absolutely do not want to do it manually, on a row-by-row basis.
Any suggestions?
Updating Primary Key with auto-generated ID
Re: Updating Primary Key with auto-generated ID
Solved my own problem. Putting a SELECT command after the INSERT command is the way to go, but the problem is that .NET needs to know how to match the returned columns to the datatable.
If my datatable has 2 columns: NodeID, and NodeName, then the easiest way to update the row is to use one of the following commands after the INSERT command:
SELECT NodeID, NodeName FROM mytable WHERE NodeID = LAST_INSERT_ID();
or
SELECT LAST_INSERT_ID() AS NodeID;
The first form will also update the datatable with any default values generated by the database.
Using SELECT LAST_INSERT_ID(); would only work if I had a column named LAST_INSERT_ID()
If my datatable has 2 columns: NodeID, and NodeName, then the easiest way to update the row is to use one of the following commands after the INSERT command:
SELECT NodeID, NodeName FROM mytable WHERE NodeID = LAST_INSERT_ID();
or
SELECT LAST_INSERT_ID() AS NodeID;
The first form will also update the datatable with any default values generated by the database.
Using SELECT LAST_INSERT_ID(); would only work if I had a column named LAST_INSERT_ID()
The issue is for any table with a autoincrementing primary key generated by the database. It is of primary importance when there are children tables for the table in question, joined using the primary key.
Let me provide a concrete example - it's easier to explain that way. Say we have 2 tables: Authors and Books, where there is a 1-to-many relationship between Authors and Books. (1 author writes many books)
Authors table: AuthorID, AuthorName
Books table: BookID, BookTitle, AuthorID
A new row is added to the authors datatable, and a new child row is added to the books datatable. New primary key values are generated by .NET - I'm using autoincrementing integers. When this dataset is then updated to the database, MySQL ignores the primary key values in my dataset, and provides its own values. This is because my application may assign AuthorID = 1, but this value may already be in use by the database.
To avoid conflicts, I start at -1 and decrement in my .NET dataset. My database starts at 1 and increments. This way, there can be no conflict. This is a standard solution.
The issue is that when I try to add the new Book row to the database, the authorID in my dataset is still -1, when the database may have assigned it 27. My dataset and database are out of sync - I will get a foreign key violation.
The simple way to fix this is to update the rows one at a time, and between each one get the LAST_INSERT_ID(), and update my authors datatable. With cascading turned on, both my authors and books datatables will be updated. This is a very inelegant and inefficient solution.
If I use the dataadapter, I can customize the INSERT statement to be something like:
INSERT INTO authors (AuthorName) VALUES (:authorname);
SELECT AuthorID, AuthorName FROM authors WHERE AuthorID = LAST_INSERT_ID();
This is done in a single command. .NET recognizes that data is being returned by the INSERT command, and uses it to update the row just inserted. This can be done to update any default or calculated column, including autoincrementing primary keys.
The Core Labs tableadapter for MySQL doesn't like this solution. The designer complains about the extra SELECT statement, the graphical designer is disabled, etc. etc. It isn't handled well at all. Contrast this with the SQL server version, which has a checkbox in the designer to automatically update all values after an INSERT command.
Let me provide a concrete example - it's easier to explain that way. Say we have 2 tables: Authors and Books, where there is a 1-to-many relationship between Authors and Books. (1 author writes many books)
Authors table: AuthorID, AuthorName
Books table: BookID, BookTitle, AuthorID
A new row is added to the authors datatable, and a new child row is added to the books datatable. New primary key values are generated by .NET - I'm using autoincrementing integers. When this dataset is then updated to the database, MySQL ignores the primary key values in my dataset, and provides its own values. This is because my application may assign AuthorID = 1, but this value may already be in use by the database.
To avoid conflicts, I start at -1 and decrement in my .NET dataset. My database starts at 1 and increments. This way, there can be no conflict. This is a standard solution.
The issue is that when I try to add the new Book row to the database, the authorID in my dataset is still -1, when the database may have assigned it 27. My dataset and database are out of sync - I will get a foreign key violation.
The simple way to fix this is to update the rows one at a time, and between each one get the LAST_INSERT_ID(), and update my authors datatable. With cascading turned on, both my authors and books datatables will be updated. This is a very inelegant and inefficient solution.
If I use the dataadapter, I can customize the INSERT statement to be something like:
INSERT INTO authors (AuthorName) VALUES (:authorname);
SELECT AuthorID, AuthorName FROM authors WHERE AuthorID = LAST_INSERT_ID();
This is done in a single command. .NET recognizes that data is being returned by the INSERT command, and uses it to update the row just inserted. This can be done to update any default or calculated column, including autoincrementing primary keys.
The Core Labs tableadapter for MySQL doesn't like this solution. The designer complains about the extra SELECT statement, the graphical designer is disabled, etc. etc. It isn't handled well at all. Contrast this with the SQL server version, which has a checkbox in the designer to automatically update all values after an INSERT command.
You should set MySqlDataAdapter.UpdateCommand.UpdatedRowSource to Both.A new row is added to the authors datatable, and a new child row is added to the books datatable. New primary key values are generated by .NET - I'm using autoincrementing integers. When this dataset is then updated to the database, MySQL ignores the primary key values in my dataset, and provides its own values.
If this doesn't help, please create a small test project to reproduce the problem and send it to me. Include definition of your database objects.
Use e-mail address provided in the Readme file.
Do not use third party components.