Get actual values from DB after INSERT with TableAdapter

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
trygvelo
Posts: 13
Joined: Wed 19 Jan 2005 10:53

Get actual values from DB after INSERT with TableAdapter

Post by trygvelo » Tue 23 Sep 2008 07:18

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
Devart Team
Posts: 8034
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 26 Sep 2008 15:22

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
Devart Team
Posts: 8034
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 26 Sep 2008 17:08

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
Posts: 13
Joined: Wed 19 Jan 2005 10:53

Post by trygvelo » Wed 01 Oct 2008 09:21

Thanks, I'll look into it.

Post Reply