AutoGet auto_increment value

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
grigansky
Posts: 6
Joined: Wed 30 Mar 2011 10:00

AutoGet auto_increment value

Post by grigansky » Thu 06 Oct 2011 14:58

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

Image

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:
Image

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();
.....
Image

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

Post by Shalex » Tue 11 Oct 2011 09:56

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

Post by Shalex » Thu 13 Oct 2011 15:40

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

Post by Shalex » Fri 14 Oct 2011 13:27

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

Shalex
Devart Team
Posts: 8032
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 17 Oct 2011 15:22

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
Posts: 6
Joined: Wed 30 Mar 2011 10:00

Post by grigansky » Mon 24 Oct 2011 12:54

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

Post by Shalex » Mon 24 Oct 2011 17:05

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
Posts: 6
Joined: Wed 30 Mar 2011 10:00

Post by grigansky » Tue 25 Oct 2011 07:10

Shalex wrote:....Have you tried the 3.20.65 build?
Yes, I have used 3.20.65 build.

Shalex
Devart Team
Posts: 8032
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 01 Nov 2011 13:14

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

Post Reply