Inserting values into a mysql database does not work as expected

Question

I try to fill a mySQL/ MariaDB table from within C#. Reading the table and populating a dataset works.

the table structure and its keys etc. are given and cannot be changed:

CREATE TABLE `fhem`.`history` (TIMESTAMP TIMESTAMP, DEVICE varchar(32), TYPE varchar(32), EVENT varchar(512), READING varchar(32), VALUE varchar(32), UNIT varchar(32));
CREATE TABLE `fhem`.`current` (TIMESTAMP TIMESTAMP, DEVICE varchar(32), TYPE varchar(32), EVENT varchar(512), READING varchar(32), VALUE varchar(32), UNIT varchar(32));
CREATE INDEX Search_Idx ON `fhem`.`history` (DEVICE, READING, TIMESTAMP);

This is my code to connect to the database, create the sql commands and adapters, and then fill the datasets with the data from the database tables. Then i create a new datarow, fill the columns with values and add this to the DataTable of th DataSet.

    class Program
    {
        static void Main(string[] args)
        {
            MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();

            connBuilder.Server = "ds2";
            connBuilder.Database = "fhem";
            connBuilder.UserID = "fhemdbuser";
            connBuilder.Port = 3307;
            connBuilder.Password = "!2345Abcde";
            //connBuilder.DefaultCommandTimeout = 120;
            //connBuilder.UseDefaultCommandTimeoutForEF = true;
            //connBuilder.AllowUserVariables = true;

            MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
            conn.Open();
            MySqlCommand selectCmdCurrent = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from current;", conn);
            MySqlCommand selectCmdCurrentTest = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from currentTest;", conn);
            MySqlCommand updateCmd = new MySqlCommand("update `currentTest` set `TIMESTAMP`=@p1,`DEVICE`=@p2,`TYPE`=@p3,`EVENT`=@p4,`READING`=@p5,`VALUE`=@p6,`UNIT`=@p7", conn);
            updateCmd.Parameters.Add("@p1", MySql.Data.MySqlClient.MySqlDbType.DateTime);
            updateCmd.Parameters.Add("@p2", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            updateCmd.Parameters.Add("@p3", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            updateCmd.Parameters.Add("@p4", MySql.Data.MySqlClient.MySqlDbType.VarString, 512);
            updateCmd.Parameters.Add("@p5", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            updateCmd.Parameters.Add("@p6", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            updateCmd.Parameters.Add("@p7", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);

            MySqlCommand insertCmd = new MySqlCommand("insert into `currentTest` values(@p1, @p2, @p3, @p4, @p5, @p6, @p7)", conn);
            insertCmd.Parameters.Add("@p1", MySql.Data.MySqlClient.MySqlDbType.DateTime);
            insertCmd.Parameters.Add("@p2", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            insertCmd.Parameters.Add("@p3", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            insertCmd.Parameters.Add("@p4", MySql.Data.MySqlClient.MySqlDbType.VarString, 512);
            insertCmd.Parameters.Add("@p5", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            insertCmd.Parameters.Add("@p6", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            insertCmd.Parameters.Add("@p7", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            MySqlDataAdapter dataAdapterCurrent = new MySqlDataAdapter(selectCmdCurrent);
            MySqlDataAdapter dataAdapterCurrentTest = new MySqlDataAdapter(selectCmdCurrentTest);
            // MySqlCommandBuilder builder = new MySqlCommandBuilder(dataAdapterCurrent);
            dataAdapterCurrentTest.InsertCommand = insertCmd;
            dataAdapterCurrentTest.UpdateCommand = updateCmd;
            DataSet ds = new DataSet();
            MySqlCommand prepareCmd = new MySqlCommand("truncate currentTest;", conn);
            prepareCmd.ExecuteNonQuery();
            //prepareCmd = new MySqlCommand("insert into currentTest select * from current;", conn);
            //prepareCmd.ExecuteNonQuery();
            prepareCmd = new MySqlCommand("INSERT INTO `currentTest` VALUES(\"2020-06-06 12:45:23\",\"DEVICE\", \"TYPE\", \"EVENT\", \"READING\", \"VALUE\", \"UNIT\")", conn);
            for (int i = 0; i < 10; i++)
                prepareCmd.ExecuteNonQuery();
            dataAdapterCurrentTest.Fill(ds, "currentTest");
            dataAdapterCurrent.Fill(ds, "current");
            int row = 0;

            Stopwatch sw = new Stopwatch();
            for (int i = 0; i < 10; i++)
            {

                DataRow newrow = ds.Tables["currentTest"].NewRow();
                newrow["Timestamp"] = DateTime.Now;
                newrow["Device"] = "Device";
                newrow["TYPE"] = "Type";
                newrow["EVENT"] = "Event";
                newrow["READING"] = "Reading";
                newrow["VALUE"] = "Value";
                newrow["UNIT"] = "Unit";
                ds.Tables["currentTest"].Rows.Add(newrow);
            }
            dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
            ds.Tables["currentTest"].AcceptChanges();
        }
    }

Whereas the manual insert command puts the correct values into the database, the programatic approach via

DataRow newrow = ds.Tables["currentTest"].NewRow();
ds.Tables["currentTest"].Rows.Add(newrow);
dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
ds.Tables["currentTest"].AcceptChanges();

only adds rows with a valid date, but the other columns are NULL.

[Edit #1] : I did some changes to my code, basically I modified the second for loop to insert new data like i did in the first loop. Then the data gets written correctly into the database. But I doubt, if it is a performant way to call .ExecuteNonQuery() several thousand times to put my log file into the database...

[Edit #2] : The 100 000 inserts into the database with .ExecuteNonQuery() are still running after 120 Minutes. This is pretty slow.

In "*the programmatic approach*" you create a new data row but you don't give it any values. Is that the actual code or is it just an abridged version? By the way, you don't need to call AcceptChanges(), calling Update() will mark all rows as unmodified. — Crowcoder, Jun 06 '20 at 11:34
@Crowcoder: the actual code is in the big code section above, the small code sample below only sumarizes the actions... — Wolfgang Roth, Jun 06 '20 at 12:04
@Strawberry: As i said in my question, the table and its indices can not be altered. I only have this schema. If I understand the FHEM server correctly, every column in a row accounts to the primary key, because at any given timestamp any number of devices could create a specific event, that then leads to a concrete reading, value and unit... — Wolfgang Roth, Jun 06 '20 at 12:07
@strawberry, Even with another test table that has an autoincrement column with primary unique key, i cannot insert/update column values... Seems to be a bug in MySqlClient? — Wolfgang Roth, Jun 06 '20 at 16:19

Akhilesh Mishra · Accepted Answer · 2020-06-06T20:57:49.403

With MysqlDataAdapter we need to set the selectCommand only. It will generate the insertCommand automatically by MySqlCommandBuilder

Your code is almost correct only 2 changes are required.

1. Add following line before dataAdapterCurrentTest.Update(ds.Tables["currentTest"])

new MySqlCommandBuilder(dataAdapterCurrentTest);

2. Comment folloing line of Code

dataAdapterCurrentTest.InsertCommand=insertCmd

So the final code is

 class Program
    {
        static void Main(string[] args)
        {
            MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder();

            connBuilder.Server = "ds2";
            connBuilder.Database = "fhem";
            connBuilder.UserID = "fhemdbuser";
            connBuilder.Port = 3307;
            connBuilder.Password = "!2345Abcde";
            //connBuilder.DefaultCommandTimeout = 120;
            //connBuilder.UseDefaultCommandTimeoutForEF = true;
            //connBuilder.AllowUserVariables = true;

            MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString);
            conn.Open();
            MySqlCommand selectCmdCurrent = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from current;", conn);
            MySqlCommand selectCmdCurrentTest = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from currentTest;", conn);
            MySqlCommand updateCmd = new MySqlCommand("update `currentTest` set `TIMESTAMP`=@p1,`DEVICE`=@p2,`TYPE`=@p3,`EVENT`=@p4,`READING`=@p5,`VALUE`=@p6,`UNIT`=@p7", conn);
            updateCmd.Parameters.Add("@p1", MySql.Data.MySqlClient.MySqlDbType.DateTime);
            updateCmd.Parameters.Add("@p2", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            updateCmd.Parameters.Add("@p3", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            updateCmd.Parameters.Add("@p4", MySql.Data.MySqlClient.MySqlDbType.VarString, 512);
            updateCmd.Parameters.Add("@p5", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            updateCmd.Parameters.Add("@p6", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            updateCmd.Parameters.Add("@p7", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);

            MySqlCommand insertCmd = new MySqlCommand("insert into `currentTest` values(@p1, @p2, @p3, @p4, @p5, @p6, @p7)", conn);
            insertCmd.Parameters.Add("@p1", MySql.Data.MySqlClient.MySqlDbType.DateTime);
            insertCmd.Parameters.Add("@p2", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            insertCmd.Parameters.Add("@p3", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            insertCmd.Parameters.Add("@p4", MySql.Data.MySqlClient.MySqlDbType.VarString, 512);
            insertCmd.Parameters.Add("@p5", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            insertCmd.Parameters.Add("@p6", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            insertCmd.Parameters.Add("@p7", MySql.Data.MySqlClient.MySqlDbType.VarString, 128);
            MySqlDataAdapter dataAdapterCurrent = new MySqlDataAdapter(selectCmdCurrent);
            MySqlDataAdapter dataAdapterCurrentTest = new MySqlDataAdapter(selectCmdCurrentTest);
            // MySqlCommandBuilder builder = new MySqlCommandBuilder(dataAdapterCurrent);
           // dataAdapterCurrentTest.InsertCommand = insertCmd;
            dataAdapterCurrentTest.UpdateCommand = updateCmd;
            DataSet ds = new DataSet();
            MySqlCommand prepareCmd = new MySqlCommand("truncate currentTest;", conn);
            prepareCmd.ExecuteNonQuery();
            //prepareCmd = new MySqlCommand("insert into currentTest select * from current;", conn);
            //prepareCmd.ExecuteNonQuery();
            prepareCmd = new MySqlCommand("INSERT INTO `currentTest` VALUES(\"2020-06-06 12:45:23\",\"DEVICE\", \"TYPE\", \"EVENT\", \"READING\", \"VALUE\", \"UNIT\")", conn);
            for (int i = 0; i < 10; i++)
                prepareCmd.ExecuteNonQuery();
            dataAdapterCurrentTest.Fill(ds, "currentTest");
            dataAdapterCurrent.Fill(ds, "current");
            int row = 0;

            Stopwatch sw = new Stopwatch();
            for (int i = 0; i < 10; i++)
            {

                DataRow newrow = ds.Tables["currentTest"].NewRow();
                newrow["Timestamp"] = DateTime.Now;
                newrow["Device"] = "Device";
                newrow["TYPE"] = "Type";
                newrow["EVENT"] = "Event";
                newrow["READING"] = "Reading";
                newrow["VALUE"] = "Value";
                newrow["UNIT"] = "Unit";
                ds.Tables["currentTest"].Rows.Add(newrow);
            }

            new MySqlCommandBuilder(dataAdapterCurrentTest);

            dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
            ds.Tables["currentTest"].AcceptChanges();
        }
    }

I tried this also, even before using parameterized commands. Does not work either. — Wolfgang Roth, Jun 07 '20 at 13:59
I uploaded my code so far to http://wowogiengen.de/MySqLTest.zip — Wolfgang Roth, Jun 07 '20 at 14:07
But it has worked for me. with your code it was adding 20 rows where last 10 rows only timestamp was inserted. but with my code it is inserting full rows — Akhilesh Mishra, Jun 07 '20 at 14:07
I suggest, Create new Console Project in VS, Copy my code in main function, add references and run the code you will get the clarity. Please check the SQL before doing it. then update your code. it will work definitely. Your code is having same issue what i have rectified here. please read the answer carefully "No need to define separate Insert Command for data adapter" — Akhilesh Mishra, Jun 07 '20 at 15:03
Mea culpa :-) You are right, now it seems to work. 20 rows of data, 10 of them in upper case and the other 10 in mixed case :-) — Wolfgang Roth, Jun 08 '20 at 06:51
Now I can insert the rows, but it seems to be pretty slow :-( — Wolfgang Roth, Jun 08 '20 at 08:31

Inserting values into a mysql database does not work as expected

1 Answers1

Linked