c# mysql Start Transaction

[der henry]

Hallo,

ich möchte mit c# aus der Datenbank lesen und "zugleich" den gelesenen Datensatz löschen. Dies habe ich mit Node schon einmal realisiert.

    let sql = 'START TRANSACTION'; 
    let transactionresult = mysqlconnection.query( sql, [] ); 
 
    sql = 'SELECT * FROM writefield WHERE plcvarname LIKE ?';
    let selectresult = mysqlconnection.query( sql, [akt_plcname + '%'] ); 

    if (selectresult.length > 0) { 
        sql = 'DELETE FROM writefield WHERE plcvarname LIKE ?';
        let deleteresult = mysqlconnection.query( sql, [akt_plcname + '%'] );
    
    sql = 'COMMIT';
    let commitresult = mysqlconnection.query( sql, [] ); 
 

Wie sieht die Syntax unter C# aus ? aktuell habe dies so ...

	string sql = "SELECT * FROM writefield WHERE LEFT(plcvarname," + plcname.Length + ") = " + "'" + plcname + "'";
			
	MySqlCommand cmd = new MySqlCommand(sql, connection);

Muss ich MySqlCommand hier mehrmals ausführen ?? Ich finde auch nichts passendes im Internet dazu ...

[Rolf B]

Hallo Henry,

die Doku vom MySqlConnector ist einfach nur Käse, sowohl bei MySQL wie auch bei der Ersatzimplementierung, die Du verwendest.

Grundsätzlich bietet .net eine BeginTransaction-Methode dafür an. Die kümmert sich um das Handling und ich nehme an, die schickt unter der Haube ein START TRANSACTION Statement an den Server. Oder es gibt dafür eine spezielle Funktion im Treiber.

Wie auch immer.

MySqlConnection conn = new MySqlConnection(connectionString);

conn.Open();

using (MySqlTransaction trans = conn.BeginTransaction()) {

   // MySqlCommands ausführen 

   trans.Commit();
}
conn.Close();

Falls es zu einem SQL Error kommt, fliegt eine Exception und using sorgt für einen Dispose() der Transaktion. Wenn Du keinen Commit gemacht hast, führt das zum Rollback.

MySqlCommands steht im Plural. Für ein einzelnes Command ergibt eine Transaktion keinen Sinn.

Beschäftige Dich mit den IsolationLevels. Du kannst BeginTransaction einen dieser Werte als Argument mitgeben. Der IsolationLevel gibt an, wie radikal MySQL Datenbanksperren setzt. MySql unterstützt nicht jeden Wert. Isolation Levels in MySql

Rolf

[MudGuard]

Hi,

ich möchte mit c# aus der Datenbank lesen und "zugleich" den gelesenen Datensatz löschen. Dies habe ich mit Node schon einmal realisiert.

    let sql = 'START TRANSACTION'; 
    let transactionresult = mysqlconnection.query( sql, [] ); 
 
    sql = 'SELECT * FROM writefield WHERE plcvarname LIKE ?';
    let selectresult = mysqlconnection.query( sql, [akt_plcname + '%'] ); 

    if (selectresult.length > 0) { 
        sql = 'DELETE FROM writefield WHERE plcvarname LIKE ?';
        let deleteresult = mysqlconnection.query( sql, [akt_plcname + '%'] );
    
    sql = 'COMMIT';
    let commitresult = mysqlconnection.query( sql, [] ); 
 

Benutzt Du das Select-Ergebnis noch irgendwo anders oder nur für die Bedingung beim delete?

Falls letzteres: laß den select einfach weg.

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo MudGuard,

Falls letzteres: laß den select einfach weg

Im konkreten Fall: ja, stimmt. Darauf habe ich gar nicht geachtet.

Rolf

[der henry]

Hallo,

aktuell bin ich so weit gekommen.

   MySqlCommand cmd = connection.CreateCommand();
			
   MySqlTransaction transaction = connection.BeginTransaction();

   cmd.Connection = connection;
   cmd.Transaction = transaction;

   try
	 {
        cmd.CommandText = "SELECT * FROM writeplc WHERE LEFT(plcvarname," + plcname.Length + ") = " + "'" +
				                  plcname + "'";
	    cmd.ExecuteNonQuery();

	    cmd.CommandText = "DELETE FROM writeplc WHERE LEFT(plcvarname," + plcname.Length + ") = " + "'" +
				                  plcname + "'";
        cmd.ExecuteNonQuery();

        if (Program.debuglevel == 2)
	     { Console.WriteLine("Write data read and deleted."); }
     }
			catch (Exception ex)
			{
				Console.WriteLine("Error reading write data for PLC {0}", ex.GetType());
				Console.WriteLine("Message: {0}", ex.Message);

				// Attempt to roll back the transaction.
				try 
				{ transaction.Rollback(); }
				catch (Exception ex2)
				{
					Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
					Console.WriteLine("  Message: {0}", ex2.Message);
				}
			}

			// gelesene Schreibwerte auswerten
			MySqlDataReader rdr = cmd.ExecuteReader();

Beim 2. cmd.ExecuteNonQuery(); stützt das Programm ab. Wenn ich die "DELETE .... " in meinen mysql Script Editor eingeben, wird alles wie gewünsch gelöscht.

Bitte um Hiiiiiiiilfe 😉

Danke !!

[Rolf B]

Hallo Henry,

was tust Du denn da Merkwürdiges?!

• Wenn Du das Command mit der CreateCommand-Methode erzeugst, ist die Connection schon drin, die musst Du nicht mehr zuweisen.

• Es ist sinnlos, einen SELECT mit ExecuteNonQuery auszuführen. Du möchtest ja Ergebnisdaten bekommen, und die kriegst Du nur mit ExecuteReader oder ExecuteScalar(). Mehr dazu weiter unten.

• Beim 2. cmd.ExecuteNonQuery(); stützt das Programm ab - was stützt es denn? 😉
  Aber wenn es abstürzt, dann verrate uns bloß nicht, wie. Sonst könnte man ja auf eine Idee kommen, was los ist. Mein Versuch, einen Absturz an dieser Stelle mit einem Spielprogramm nachzuvollziehen (.net Core 8, C# 12, MySqlConnection gegen MariaDB 10.6), gelingt nicht.

• Kreative Einrückung macht das Lesen von Code spannend. Ich habe eine Weile gebraucht, um festzustellen, dass dein cmd.ExecuteReader() außerhalb des catch steht.

• Der ExecuteReader ist sinnlos. Zum einen sehe ich keinen rdr.Read(), und zum anderen ist der CommandText in diesem Moment ein DELETE-Statement, das kein Resultset liefert. Aber er wirft keine Exception, soweit ich das erkennen kann.

• Du machst im Fehlerfall einen Rollback, im Erfolgsfall aber keinen Commit! Das musst Du definitiv tun, sonst wird die DB nicht verändert und Du kannst auch keinen weiteren BeginTransaction() aufrufen (weil ja schon eine offen ist).

• Dass Du vor dem DELETE keinen SELECT machen musst, haben wir Dir schon gesagt. Der DELETE tut einfach nichts, wenn die WHERE-Bedingung nicht erfüllt ist, und du fängst mit dem SELECT ja auch nichts an, du lässt ihn einfach laufen ohne das Resultset abzuholen. Ich hatte schon gedacht, dass er darüber abstürzt, dass es ein schwebendes Resultset gibt - aber nein, war nicht so, gerade ausprobiert.

• Wenn Du auf den SELECT vor dem DELETE verzichtest, brauchst Du auch keine Transaktion.

• Wenn Du schon UNBEDINGT vor dem DELETE per SELECT abfragen willst, ob was zum Löschen da ist (wie gesagt: ES! IST! NICHT!! NÖTIG!!!), dann verwende SELECT COUNT(*) und führe das Command mit ExecuteScalar() aus. Damit bekommst Du die erste Spalte der ersten Ergebniszeile - mehr gibt's bei SELECT COUNT(*) auch nicht.

Rolf

[MudGuard]

Hi,

• Wenn Du schon UNBEDINGT vor dem DELETE per SELECT abfragen willst, ob was zum Löschen da ist (wie gesagt: ES! IST! NICHT!! NÖTIG!!!), dann verwende SELECT COUNT(*)

Die Anzahl der gelöschten Zeilen sollte auch nach dem Delete (per affected rows - wie auch immer das in Zeh Lattenzaun geholt wird) verfügbar sein …

cu,
Andreas a/k/a MudGuard

[Rolf B]

Hallo MudGuard,

das kommt von ExecuteNonQuery zurück.

Auf die Gefahr hin, mich zu wiederholen: Ein SELECT vorweg ergibt einfach keinen Sinn.

Rolf

[der Henry]

Hallo,

Ich möchte die Daten erst lesen, um Sie später weiter zu verarbeiten zu können und dann sofort löschen.

Also lesen, löschen, verarbeiten, dazu brauche ich doch ein SELECT davor ?

[Rolf B]

Hallo Henry,

okay, wenn das, was Du SELECTest, eingelesen und gespeichert wird, ist das was anderes. Das hat dein Code nicht gezeigt.

Aber jedenfalls musst Du dieses Einlesen und Speichern mit ExecuteReader tun und den Reader wieder schließen, bevor der DELETE läuft.

Du solltest Dir aber auch Gedanken über Fehlersituationen machen. Stell Dir vor:

• Daten lesen
• Daten speichern
• Daten löschen
• Daten weiterverabei.... CRASH

Alles weg.

Ist es für Dich praktikabel, die Weiterverarbeitung durchzuführen und das Ergebnis zu speichern, bevor Du löschst? Wenn das Ergebnis in der DB landet, dann am besten alles in einer Transaktion.

Wenn die Verarbeitung lange dauert und viele Leute auf der DB hantieren, musst Du abschätzen, ob das Ganze zu lange dauert.

Achso, apropos Transaktion. Verwendest Du InnoDB als Engine? Mit MyISAM gibt's keine Row Locks und damit auch keine brauchbare Transaktionalität.

Rolf

[der henry]

Guten Morgen,

jetzt habe ich meinen Code angepasst und die Funktion hier komplett eingestellt.

1. die connection habe ich kommentiert

2. rdr = cmd.ExecuteReader(); nach dem select

3. Ich nutze Raider und hier kommt im Debugger beim Aufruf cmd.ExecuteNonQuery();

Was auch immer an der Transaktion falsch ist, warum verschachtelt ???

4. Einrückung, ich kopiere den Code einfach aus Raider heraus. In Raider ist alles OK / Normal

5. siehe 2.

6. Das mit dem Rollback habe ich nur kopiert ... so richtig verstehe ich das nicht. 😉 Den Rollback brauche ich eigentlich nur, wenn beim DELETE etwas fehl schlägt ... sonst nicht, oder ?

   Ich habe doch ein transaction.Commit() drin ... hier verstehe ich deine Aussage nicht ... oder das Konzept 😟

Die letzten Punkte sollten erledigt sein ... ich brauche das SELECT um erst Daten auszulesen, bevor diese sofort und in einem Zug gelöscht werden (Transaktion)

Ich haben jetzt den rdr.Read einfach mit dran gehängt ... kann man sicher schöner in eine Funktion auslagern und dann gleich in der Transaction (try) mit aufrufen oä.

public static bool read_writedatapoints(string plcname)
		{
			MySqlCommand cmd = connection.CreateCommand();
			
			// Start a local transaction.
			MySqlTransaction transaction = connection.BeginTransaction();

			// cmd.Connection = connection;
			cmd.Transaction = transaction;	
			
			MySqlDataReader rdr = null;
			
			try
			{
				cmd.CommandText = "SELECT * FROM writeplc WHERE LEFT(plcvarname," + plcname.Length + ") = " + "'" +
				                  plcname + "'";
				
				// gelesene Schreibwerte auswerten
				rdr = cmd.ExecuteReader();

				if (rdr.HasRows)
				{
					cmd.CommandText = "DELETE FROM writeplc WHERE LEFT(plcvarname," + plcname.Length + ") = " + "'" +
					                  plcname + "'";

					cmd.ExecuteNonQuery();
					if (Program.debuglevel == 2) { Console.WriteLine("WriteData read and deleted."); }
				}
				transaction.Commit();
			}
			catch (Exception ex)
			{
				Console.WriteLine("Fehler beim lesen der Schreibdaten für SPS {0}", ex.GetType());
				Console.WriteLine("Message: {0}", ex.Message);

				// Attempt to roll back the transaction.
				try { transaction.Rollback(); }

				catch (Exception ex2)
				{
					Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType()); 
					Console.WriteLine("  Message: {0}", ex2.Message);
				}
			}

		

			if (rdr.HasRows)
			{
				while (rdr.Read())
				{
					WRITEPLCdatapoint item  = new WRITEPLCdatapoint();
					item.Plcvarname      = (string)rdr["plcvarname"];
					item.Mpi             = (sbyte)rdr["mpi"];
					item.Setpoint        = (string)rdr["setpoint"];
					writeplcdatapoints.Add(item);

					if (Program.debuglevel == 2)
					{ Console.WriteLine("Write command were found for the selected PLC.: " + item.Plcvarname + " , MPI: " + item.Mpi + " , Setpont: " + item.Setpoint); }
				}
				rdr.Close();
				return true;
			}
			else
			{
				Console.WriteLine("No write command were found for the selected PLC.");
				rdr.Close();
				return false;
			}
		}

[der henry]

Hallo,

ich habe den Fehler gefunden.

			cmd.Transaction = transaction;	

muss innerhalb des try stehen.

Vielen Danke !!

[Rolf B]

Hallo Henry,

Du machst mich echt fertig.

Am 23.10. frug ich:

Ich nehme an, du verwendest das MySqlConnector Nuget-Paket? Oder verwendest Du MySql.Data?

Das hast Du nicht beantwortet und ich bin von MySqlConnector.net ausgegegangen. Jetzt sehe ich in deinem Stacktrace, dass Du die MySql.Data Implementierung verwendest. Na gut. Dann ziehe ich mir diese NuGet-Paketflut auch noch rein.

Ich habe doch ein transaction.Commit() drin

Ja, jetzt sieht man eins. Vorher nicht.

cmd.Transaction = transaction; muss innerhalb des try stehen.

Äh, wie bitte? Ob die Zuweisung an cmd.Transaction direkt vor oder direkt hinter dem try erfolgt, sollte komplett schnuppe sein. Das ist doch bloß ein Property, und im GitHub sieht man, dass da keine Logik in Setter ist, die eine Exception werden könnte. Da musst Du noch andere Dinge geändert haben.

Und da dein Code nur einmal BeginTransaction macht, muss der Fehler mit der nested transaction woanders her kommen. Startest Du noch anderswo Transaktionen, die Du zu beenden vergisst?

Aber das ist gar nicht wirklich das Problem.

Wenn ich versuche, deine Logik bei mir laufen zu lassen (also SELECT mit ExecuteReader ausführen und dann den Reader erstmal ignorieren und den CommandText auf DELETE ändern), bekomme ich eine von mir erwartete und sehr klar formulierte Exception (ich habe keinen try/catch drin):

Unhandled exception. System.InvalidOperationException: Cannot set  
  MySqlCommand.CommandText when there is an open DataReader for  
  this command; it must be closed first.  
   at MySqlConnector.MySqlCommand.set_CommandText(String value)  
     in /_/src/MySqlConnector/MySqlCommand.cs:line 207  
   at Program.<Main>$(String[] args)  
     in C:\Users\Rolf\source\repos\SqlConsoleApp1\Program.cs:line 41

Zeile 41 ist die Zuweisung des DELETE Statements an cmd.CommandText. Er kommt überhaupt nicht bis zum ExecuteNonQuery für den DELETE, er bricht ab weil das Command gerade vom Reader "in Arbeit" ist. Dass das bei Dir nicht passiert, ist höchst merkwürdig. Der Reader muss geschlossen sein (d.h. fertig eingelesen), bevor Du das Command wiederverwenden kannst. Und ihn nach dem Commit einzulesen kann auch nicht funktionieren.

Damit wir vergleichbare Umgebungen haben: Bei mir ist das ein Kommandozeilenprogramm unter Windows 10, mit .net 8, C# 12, MySqlConnector.net, gegen MariaDB 10.6, getestet mit einer InnoDB und eine MyISAM Tabelle.

Interessant ist: Ich habe erst später gemerkt, dass Du MySql.Data verwendest, nicht MySqlConnector.net. Da stürzt er auch ab, aber erst beim ExecuteNonQuery des DELETE.

Er müsste bei Dir also auch abstürzen. Auf welchem Betriebssystem und mit welcher .net Version programmierst Du da? Falls Du das nicht weißt: gib System.Environment.Version aus. Achso, welche MySql-Implementierung verwendest Du?

Rolf

[Rolf B]

Hallo Henry,

noch was anderes, so von C#+SQL Veteran zu C# Newbie. Die folgende Zeile kann man gar nicht rot genug machen…

cmd.CommandText = "SELECT * FROM writeplc WHERE LEFT(plcvarname," + plcname.Length + ") = " + "'" +
				                  plcname + "'";

• SELECT * verwendet man bei interaktiven Abfragen, aber niemals in Programmcode. Gib die Spalten immer explizit an
• Dein LEFT möchte vielleicht ein LIKE sein?
• Ohne Parameter muss plcname auf Kontextwechsel behandelt werden. Eigentlich sind Parameter die bessere Idee.
• Verwende keine Stringverkettung, sondern string.Format oder String-Interpolation (in JavaScript nennt man das Template-Strings):

cmd.CommandText = string.Format(
      "SELECT plcvarname, mpi, setpoint FROM writeplc WHERE plcvarname LIKE '{0}%'",
      MySqlHelper.EscapeString(plcname));

oder (beachte das $ vor den String-Teilen)

cmd.CommandText = $"SELECT plcvarname, mpi, setpoint FROM writeplc WHERE plcvarname LIKE '{MySqlHelper.EscapeString(plcname)}%'";

Die langen Zeilen kannst Du mit "verbatim strings" auf mehrere Zeilen verteilen, ähnlich wie in Java-Script Template-Strings oder in PHP den Heredoc-Strings:

cmd.CommandText = string.Format(
      @"SELECT plcvarname, mpi, setpoint 
        FROM writeplc 
        WHERE plcvarname LIKE '{0}%'",
      MySqlHelper.EscapeString(plcname));

@ und $ kann man auch kombinieren:

cmd.CommandText = $@"SELECT plcvarname, mpi, setpoint 
                     FROM writeplc 
                     WHERE plcvarname LIKE '{MySqlHelper.EscapeString(plcname)}%'";

Und wo wir gerade von Heredoc-Syntax sprechen, die hat man C# ab .net 7 (Compilerversion C# 11) als "raw string literals" eingeführt. Zusammen mit einem $ vorneweg ist auch String-Interpolation möglich – für SQL einfach ideal.

cmd.CommandText = $"""
   SELECT plcvarname, mpi, setpoint 
   FROM writeplc 
   WHERE plcvarname LIKE '{MySqlHelper.EscapeString(plcname)}%'
   """;

Wie in PHP bestimmt die Einrückung des abschließenden """, wieviele Leerstellen am Anfang der Zeilen des Raw String Literals entfernt werden.

Rolf

[der Henry]

Hallo Rolf,

mea culpa ;-)

noch was anderes, so von C#+SQL Veteran zu C# Newbie. Die folgende Zeile kann man gar nicht rot genug machen…

cmd.CommandText = "SELECT * FROM writeplc WHERE LEFT(plcvarname," + plcname.Length + ") = " + "'" +
				                  plcname + "'";

• SELECT * verwendet man bei interaktiven Abfragen, aber niemals in Programmcode. Gib die Spalten immer explizit an

Das "SELECT *" gibt mir ein Array von Datensätzen/Records zurück ... also nicht nur einen Datensatz ... das will ich auch.

Wie sollte ich das sonst machen ??

• Dein LEFT möchte vielleicht ein LIKE sein?

Ja, OK ... hatte schon davon gehört 😉, sicher die bessere Wahl

• Ohne Parameter muss plcname auf Kontextwechsel behandelt werden. Eigentlich sind Parameter die bessere Idee.

OK, da hatte ich mir gar keine Gedanken gemacht.

cmd.CommandText = string.Format(
      "SELECT plcvarname, mpi, setpoint FROM writeplc WHERE plcvarname LIKE '{0}%'",
      MySqlHelper.EscapeString(plcname));

oder (beachte das $ vor den String-Teilen)

cmd.CommandText = $"SELECT plcvarname, mpi, setpoint FROM writeplc WHERE plcvarname LIKE '{MySqlHelper.EscapeString(plcname)}%'";

Die langen Zeilen kannst Du mit "verbatim strings" auf mehrere Zeilen verteilen, ähnlich wie in Java-Script Template-Strings oder in PHP den Heredoc-Strings:

cmd.CommandText = string.Format(
      @"SELECT plcvarname, mpi, setpoint 
        FROM writeplc 
        WHERE plcvarname LIKE '{0}%'",
      MySqlHelper.EscapeString(plcname));

@ und $ kann man auch kombinieren:

cmd.CommandText = $@"SELECT plcvarname, mpi, setpoint 
                     FROM writeplc 
                     WHERE plcvarname LIKE '{MySqlHelper.EscapeString(plcname)}%'";

Und wo wir gerade von Heredoc-Syntax sprechen, die hat man C# ab .net 7 (Compilerversion C# 11) als "raw string literals" eingeführt. Zusammen mit einem $ vorneweg ist auch String-Interpolation möglich – für SQL einfach ideal.

cmd.CommandText = $"""
   SELECT plcvarname, mpi, setpoint 
   FROM writeplc 
   WHERE plcvarname LIKE '{MySqlHelper.EscapeString(plcname)}%'
   """;

Das sind wirklich sehr... sehr gute Tips, vielen Dank.

Gruß

[Rolf B]

Hallo der Henry,

Wie sollte ich das sonst machen ??

Wie ich schrieb: Die Spalten explizit angeben. So, wie ich das in den nachfolgenden Beispielen gemacht habe.

Warum? Es kann immer passieren, dass man eine Tabelle modifiziert. Die Liste der Spalten, die SELECT * liefert, ist daher nicht garantiert zukunftssicher. Und jemand, der den Code liest, bekommt so auch einen Hinweis auf die erwarteten Spalten. Deshalb gilt es als schlechter Stil, im Programm SELECT * zu verwenden.

Rolf

[der henry]

OK, verstanden.

Übrigends, NET 9, OS => Linux