Get actual values from DB after INSERT with TableAdapter

[trygvelo]

Hi,

with a strongly typed dataset, how do I get the actual values (f.ex auto_increment, timestamp columns) from the database after an INSERT with TableAdapters.

* The "Refresh the data table" Advanced TableAdapter configuration option does not work. Bug? Missing feature?
* Temporarily I have tried to solve this by creating a custom property PublicAdapter in the dataset.cs class (not overwritten by dataset generation) that exposes the TableAdapters DataAdapter. Then I use the RowUpdated eventhandler of the DataAdapter to process each updated row and fetch the actual values from DB. My problem here is that I can't check that the RowState of the recently updated row is RowState.Added because after the update it is set to "RowState.Unchanged". How do I work around this so that I don't refresh both inserted and updated rows with data from db?

Any more elegant ways of doing this???

Best regards,
Trygve

[Shalex]

There is a solution with using the RefreshMode property of MySqlDataTable class. But now we are experiencing some problems with it, and we need time for the investigation. We will notify you on the results as soon as possible.

[Shalex]

The following example demonstrates how to use the RefreshMode and RefreshingFields properties of MySqlDataTable class. The RefreshingFields property sets the columns whose fields must be reselected after calling the Update() method in the MySqlDataTable object.

SQL script

Code: Select all

create table autoincTable(
id int auto_increment primary key not null,
val int
)

C# code

Code: Select all

using (MySqlConnection connection = new MySqlConnection(...)) {
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "select * from autoincTable";

        MySqlDataTable table = new MySqlDataTable(command);
        table.Open();  // id column IsAutoIncrement

        for (int i = 0; i < 2; i++) {
          DataRow row = table.NewRow();
          row["val"] = i + 1;
          table.Rows.Add(row);
        }

        table.Update();

        // change autoincrement seed by deleting the second row
        MySqlCommand deleteCommand = connection.CreateCommand();
        deleteCommand.CommandText = "delete from autoincTable where val = 2";
        connection.Open();
        deleteCommand.ExecuteNonQuery();
        table.Rows.RemoveAt(1);
        table.AcceptChanges();

        table.RefreshMode = CoreLab.Common.RefreshRowMode.Both;
        table.RefreshingFields = "id";

        DataRow refreshedRow = table.NewRow();
        refreshedRow["val"] = refreshedRow["id"];
        table.Rows.Add(refreshedRow);
        table.Update();

        MySqlCommand checkCommand = connection.CreateCommand();
        checkCommand.CommandText = "select last_insert_id()";
        int lastInsertId = Convert.ToInt32(checkCommand.ExecuteScalar());

        if (Convert.ToInt32(refreshedRow["id"]) == lastInsertId) {
          MessageBox.Show("Refreshed");
        }

[trygvelo]

Thanks, I'll look into it.