AutoGet auto_increment value

[grigansky]

Hi, first of all, I will describe the problem:

-->I have next structure of the table

Code: Select all

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name    | varchar(100) | YES  |     | NULL    |                |
| Address | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

-->This table is used for the project



LoadMethod()

Code: Select all

{
     uniDataAdapter1.FillSchema(dataTable1, SchemaType.Source);
     uniDataAdapter1.Fill(dataTable1);
}

InsertMethod()

Code: Select all

InsertRecord(string name, string address)
{
……………
DataRow row = dataTable1.NewRow();
            //row["id"] = GetAutoIncrementValue();
            row["name"] = name;
            row["address"] = address;
            dataTable1.Rows.Add(row);

            uniDataAdapter1.Update(dataTable1);
……………
}

DeleteMethod()

Code: Select all

DeleteRecord()
{
……
    gridView1.DeleteSelectedRows();

    try
    {
       uniDataAdapter1.Update(dataTable1);
    }
    catch (System.Exception ex)
    {
       MessageBox.Show(ex.Message, "MYSQL ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
……
}

-->After creating new record I have:

Code: Select all

mysql> select * from test;
+-----+-------------+--------------+
| id  | Name        | Address      |
+-----+-------------+--------------+
| 274 | Lev Tolstoy | Bukgakova 12 |
+-----+-------------+--------------+
1 row in set (0.00 sec)

-->If I delete this record I have next exception:

Concurrency violation: the DeleteCommand affected {0} of the expected {1} records.

-->For solving problem I use GetAutoIncrementValue():

Code: Select all

GetAutoIncrementValue()
{
…………………
MySqlCommand command = connection.CreateCommand();
command.CommandText = String.Format("select auto_increment from information_schema.TABLES where TABLE_NAME='test' and TABLE_SCHEMA='devart';");
object result = command.ExecuteScalar();
if (result!=null)
{
   autoIncrementValue = Convert.ToInt32(result);
}
……………..
return autoIncrementValue;
}

After uncommenting of GetAutoIncrementValue():

Code: Select all

.....
row["id"] = GetAutoIncrementValue();
.....

How I can synchronize the local auto_increment value and the auto_increment value of the real data base, using universal components without GetAutoIncrementValue()?

[Shalex]

Thank you for the detailed description of the issue. UniDataAdapter doesn't support RefreshingMode at the moment. We will investigate the possibility of implementing this feature and post here about the results. As a temporary workaround, please use your GetAutoIncrementValue() method.

[Shalex]

Here is a temporary workaround to implement RefreshMode manually:

Code: Select all

        //CREATE TABLE IF NOT EXISTS aitable(
        //  id INT(11) NOT NULL AUTO_INCREMENT,
        //  `data` VARCHAR(255) DEFAULT NULL,
        //  PRIMARY KEY (id)
        //);

        string insertSQl = @"INSERT INTO aitable (data) VALUES (:p1);
               SELECT id FROM aitable WHERE id = last_insert_id()";

        UniConnection conn = new UniConnection("Provider=MySQL; user=root;pwd=root;host=db;port=3308;database=test");
        conn.Open();

        DataTable table = new DataTable();
        UniDataAdapter da = new UniDataAdapter("select * from aitable", conn);

        da.InsertCommand = new UniCommand(insertSQl, conn);
        UniParameter p = new UniParameter("p1", UniDbType.VarChar, 0, "data");
        da.InsertCommand.Parameters.Add(p);

        // da.FillSchema(table, SchemaType.Source);
        da.Fill(table);

        DataRow row = table.NewRow();
        row["data"] = "some string";
        table.Rows.Add(row);

        da.Update(table);

[Shalex]

The UniCommandBuilder.RefreshMode functionality is implemented. We will notify you when the corresponding build of dotConnect Universal is available for download.

[Shalex]

Starting from the next build of dotConnect Universal, the RefreshMode will be supported only for bundled Devart providers and only in runtime:

Code: Select all

        UniConnection conn = new UniConnection("Provider=MySQL; user=root;pwd=root;host=db;port=3308;database=test");
        conn.Open();

        DataTable table = new DataTable();
        UniDataAdapter da = new UniDataAdapter("select * from aitable", conn);
        UniCommandBuilder builder = new UniCommandBuilder(da);
        builder.RefreshMode = Devart.Common.RefreshRowMode.AfterInsert;
        builder.RefreshingFields = "id";

        da.Fill(table);

        DataRow row = table.NewRow();
        row["data"] = "some string";
        table.Rows.Add(row);
        da.Update(table);

We are going to release the new public build this week.

[grigansky]

I have used RefreshMode, but have same problem.

LoadMethod()

Code: Select all

{
uniDataAdapter1.FillSchema(dataTable1, SchemaType.Source);

Devart.Data.Universal.UniCommandBuilder builder = new Devart.Data.Universal.UniCommandBuilder(uniDataAdapter1);
builder.RefreshMode = Devart.Common.RefreshRowMode.AfterInsert;
builder.RefreshingFields = "id"; 

uniDataAdapter1.Fill(dataTable1);
}

Can you create a sample project for illustration of this functionality?

[Shalex]

New build of dotConnect Universal 3.20.65 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/univer ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22386 .

Have you tried the 3.20.65 build?

[grigansky]

....Have you tried the 3.20.65 build?

Yes, I have used 3.20.65 build.

[Shalex]

We have sent a sample WinForms project to the e-mail address you have specified at your forum profile.