AutoGet auto_increment value

AutoGet auto_increment value

Postby 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()?
grigansky
 
Posts: 6
Joined: Wed 30 Mar 2011 10:00

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

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

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

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

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

Postby 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?
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

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

Postby 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.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect Universal