How to get an AutoNumber from DataTable AutoIncrementColumn with a Strongly-Typed Database in c#?

Question

I have added a strongly-typed Database (MySQl) to a C# (.net 3.5) project. The database (DB.Electrophysiology_Data) has several relational tables but basically I want to add a new 'filesRow' to the 'files' table. I use something like the following code.

DB.Electrophysiology_Data.filesRow new_file = ds.files.NewfilesRow();

...set the values for the new_file (eg, filename, creationdate etc)

ds.files.AddfilesRow(new_file);

..eventually I update the Database via a DataAdaptor

file_adaptor.Update(ds.files)

The problem is that the AutoIncrement column (FileID) is set to -1. After closing my applicaiton and restarting, I see the new filesRow is added to the Database ok and now has an appropriate FileID value (about 5000, since I have about this many rows already in the files DataTable).

Is there a way of knowing the FileID value set by the database using the Strong-typed Database objects (DataSet, DataTable etc).

Thanks for any help, pete

Answer 1

In order for adaptor to retrieve an auto-generated ID from the database just after you inserted a new record, you need to perform SELECT.

In My SQL You can retrieve the most recent AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function

If the Configure Query Wizard in stong-typed datasets cannot generate a query for MySQL in proper way, you can edit INSERT command manually and add SELECT clause here Usually if you the command looks like

insert my_table (field1, field2, ...) values (@field1, @field2, ...)
select id, field1, field2 ... from my_table where id = LAST_INSERT_ID()

As you see, we need to select not only ID but other fields too in order to get last values from the table (that may be different from values in the dataset if any defaults exist).