Get actual values from DB after INSERT with TableAdapter

Get actual values from DB after INSERT with TableAdapter

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

Postby 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: 7608
Joined: Thu 14 Aug 2008 12:44

Postby 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");
        }
Shalex
Devart Team
 
Posts: 7608
Joined: Thu 14 Aug 2008 12:44

Postby trygvelo » Wed 01 Oct 2008 09:21

Thanks, I'll look into it.
trygvelo
 
Posts: 13
Joined: Wed 19 Jan 2005 10:53


Return to dotConnect for MySQL