I cant get my mySQL Delete command to work

Question

With that i could insert rows into my mySQL Database. Now i am trying to delete rows from the database

I reduced my code to the minimum what should work:

you need a reference to MySql.Data.MySqlClient and use .Net 4.5.2

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace StackOverFlow_mySQLTest
{
    class Program
    {
        private static string server = "ds2";
        private static string database = "fhem";
        private static string user = "fhemdbuser";
        private static string passwort = "!2345Abcde";
        static void Main(string[] args)
        {
            MySqlCommand cmd;
            MySqlTransaction trans;

            MySqlConnection connection;

            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
            builder.Server = server;
            builder.Database = database;
            builder.UserID = user;
            builder.Port = 3307;
            builder.Password = passwort;
            builder.DefaultCommandTimeout = 120;
            builder.UseDefaultCommandTimeoutForEF = true;
            connection = new MySqlConnection(builder.ConnectionString);


            // Datenbank öffnen
            connection.Open();


            MySqlCommand selectCmdCurrentTest = new MySqlCommand("select `TIMESTAMP`, `DEVICE`, `TYPE`, `EVENT`, `READING`, `VALUE`, `UNIT` from currentTest;", connection);
            MySqlDataAdapter dataAdapterCurrentTest = new MySqlDataAdapter(selectCmdCurrentTest);
            MySqlCommand updateCmd = new MySqlCommand("update `currentTest` set `TIMESTAMP`= @p1,`DEVICE`= @p2,`TYPE`= @p3,`EVENT`= @p4,`READING`= @p5,`VALUE`= @p6,`UNIT`= @p7", connection);
            MySqlParameter p1, p2, p3, p4, p5, p6, p7;

            p1 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Timestamp", ParameterName = "@p1", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.DateTime};
            p2 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Device", ParameterName = "@p2", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString};
            p3 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Type", ParameterName = "@p3", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString};
            p4 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Event", ParameterName = "@p4", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString};
            p5 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Reading", ParameterName = "@p5", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString};
            p6 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Value", ParameterName = "@p6", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString};
            p7 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Unit", ParameterName = "@p7", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString};

            updateCmd.Parameters.AddRange(new[] { p1, p2, p3, p4, p5, p6, p7 });
            dataAdapterCurrentTest.UpdateCommand = updateCmd;
            MySqlCommand deleteCmd = new MySqlCommand("delete from  `currentTest` where `TIMESTAMP`like @p1 and `DEVICE` like @p2 and `TYPE` like  @p3 and `EVENT` like  @p4 and `READING` like  @p5 and `VALUE` like  @p6 and `UNIT` like  @p7", connection);
            p1 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Timestamp", ParameterName = "@p1", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.DateTime, SourceVersion = DataRowVersion.Original };
            p2 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Device", ParameterName = "@p2", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p3 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Type", ParameterName = "@p3", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p4 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Event", ParameterName = "@p4", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p5 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Reading", ParameterName = "@p5", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p6 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Value", ParameterName = "@p6", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p7 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Unit", ParameterName = "@p7", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };

            deleteCmd.Parameters.AddRange(new[] { p1, p2, p3, p4, p5, p6, p7 });
            dataAdapterCurrentTest.DeleteCommand = deleteCmd;

            DataSet ds = new DataSet();
            dataAdapterCurrentTest.Fill(ds, "currentTest");

Here the table in ds is still empty


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

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

            new MySqlCommandBuilder(dataAdapterCurrentTest);

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

here ds has 10 rows with the expected data.

            for (int i = 0; i < ds.Tables["currentTest"].Rows.Count; i++)
            {
                ds.Tables["currentTest"].Rows[i]["Timestamp"] = new DateTime(2020, 7, 1, 0, 0, 0);
            }
            new MySqlCommandBuilder(dataAdapterCurrentTest);
            dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
            ds.Tables["currentTest"].AcceptChanges();

still, ds has 10 rows with the expected data (same as before, but date set to 2020-07-01


            for (int i = 0; i < ds.Tables["currentTest"].Rows.Count; i++)
            {
                ds.Tables["currentTest"].Rows[i].Delete();
            }
            new MySqlCommandBuilder(dataAdapterCurrentTest);
            dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);

line above throws an exception "System.Data.DBConcurrencyException" with message "Parallelitätsverletzung: Der DeleteCommand hat sich auf 0 der erwarteten 1 Datensätze ausgewirkt.", meaning, 0 of 1 expected rows have been affected

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

I don't understand how to create the delete command so that it works correctly

For what it's worth, your data access strategy is *really* out of date. Consider using Entity Framework Core or a micro-ORM like Dapper instead. — Robert Harvey, Jun 11 '20 at 20:23
I think you can find your answer here [https://stackoverflow.com/questions/3388100/concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records](https://stackoverflow.com/questions/3388100/concurrency-violation-the-updatecommand-affected-0-of-the-expected-1-records) — Ramin, Jun 11 '20 at 22:17
@RobertHarvey: I would have used EFC, if it was easy. Using EF with MSSQL or LocalDB worked, but i could not get it to work with mySQL. So I am stuck here. But considering the purpose of my implementation, it still does the job. — Wolfgang Roth, Jun 12 '20 at 07:57
@Ramin, I read this, but there you delete one data row at a time, wouldn't that be very inefficient? I wanted to have an implementation using DataSets, DataTables and DataRows to handle my data. — Wolfgang Roth, Jun 12 '20 at 07:59
Dapper works with any database implementing a `DbConnection`, [including MySql](https://dev.mysql.com/doc/dev/connector-net/8.0/html/T_MySql_Data_MySqlClient_MySqlConnection.htm). — Robert Harvey, Jun 12 '20 at 13:38

score 0 · Accepted Answer · answered Jun 12 '20 at 08:33

Ok, I found a solution myself. Problem seems to be that you have to fill the dataset again with data from the database... So here are the additional code parts:

before DataSet ds = new DataSet();:

            MySqlParameter p1, p2, p3, p4, p5, p6, p7;

            MySqlCommand deleteCmd = new MySqlCommand("delete from  `currentTest` where `TIMESTAMP`like @p1 and `DEVICE` like @p2 and `TYPE` like  @p3 and `EVENT` like  @p4 and `READING` like  @p5 and `VALUE` like  @p6 and `UNIT` like  @p7", conn);
            p1 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Timestamp", ParameterName = "@p1", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.DateTime, SourceVersion = DataRowVersion.Original };
            p2 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Device", ParameterName = "@p2", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p3 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Type", ParameterName = "@p3", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p4 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Event", ParameterName = "@p4", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p5 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Reading", ParameterName = "@p5", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p6 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Value", ParameterName = "@p6", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };
            p7 = new MySql.Data.MySqlClient.MySqlParameter() { SourceColumn = "Unit", ParameterName = "@p7", MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString, SourceVersion = DataRowVersion.Original };

            deleteCmd.Parameters.AddRange(new[] { p1, p2, p3, p4, p5, p6, p7 });
            deleteCmd.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
            dataAdapterCurrentTest.DeleteCommand = deleteCmd;

after the rows have been added:

            new MySqlCommandBuilder(dataAdapterCurrentTest);

            dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
            ds.Tables["currentTest"].AcceptChanges();
            ds.Tables["currentTest"].Clear();
            dataAdapterCurrentTest.Fill(ds, "currentTest");
            for (int i = 0; i < 20; i++)
            {
                ds.Tables["currentTest"].Rows[i].Delete();
            }
            new MySqlCommandBuilder(dataAdapterCurrentTest);

            dataAdapterCurrentTest.Update(ds.Tables["currentTest"]);
            ds.Tables["currentTest"].AcceptChanges();
            ds.Tables["currentTest"].Clear();
            dataAdapterCurrentTest.Fill(ds, "currentTest");

I cant get my mySQL Delete command to work

1 Answers1