Skip to content

10. Datenbankzugriff mit ADO.NET

10.1. Der ADO.NET-Connector

Kehren wir zur Schichtenarchitektur zurück, die seit Kapitel 6 wiederholt verwendet wird. Bisher hat die Schicht [dao] der Anwendung [Tax] drei Arten von Datenquellen genutzt:

  • im Code fest eingebaute Daten ([HardwiredTax]);
  • Daten aus einer Textdatei ([FileTax]);
  • Daten aus einer JSON-Datei ([JsonTax]).

In diesem Kapitel untersuchen wir den Fall, dass die Daten aus einer Datenbank stammen. Die Architektur sieht dann wie folgt aus:

Image

Im obigen Schema kommuniziert die Schicht [dao] [1] mit dem DBMS (Datenbankmanagementsystem) [3], das die Datenbank [4] verwaltet, und zwar über eine Klassenbibliothek, die für das verwendete DBMS spezifisch ist [2]. Diese Bibliothek implementiert Standardfunktionen, die unter dem Namen ADO.NET zusammengefasst sind (ADO steht für „ActiveX Data Objects“, den Namen der Datenzugriffstechnologie, die ihr unter Windows voranging). Man spricht von einem „Datenprovider“ oder einem „Connector“. Alle gängigen DBMS besitzen einen ADO.NET-Connector.

Die Klassen eines Connectors sind nach dem DBMS benannt: [MySqlConnection] für MySQL, [SqlConnection] für SQL Server, [NpgsqlConnection] für PostgreSQL … Verwendet die Schicht [dao] diese Klassen, enthält ihr Code den Namen des Connectors, und ein Wechsel des DBMS erfordert ihre Änderung. Diese Architektur ist zugleich effizient – weil ein für ein bestimmtes DBMS geschriebener Connector es bestmöglich nutzen kann – und starr, weil ein Wechsel des DBMS eine Änderung der Schicht [dao] erfordert. Dieser zweite Punkt ist zu relativieren: Unternehmen wechseln ihr DBMS nicht sehr oft. Außerdem werden wir sehen, dass ADO.NET ab .NET 2.0 einen „generischen Connector“ bietet, der Flexibilität ermöglicht, ohne Leistung zu opfern.

Der Kurs von 2008 verwendete nacheinander die DBMS SQL Server Compact (von Microsoft inzwischen eingestellt), SQL Server 2005 Express, MySQL 5 und anschließend die ODBC- und OLE-DB-Treiber, wobei die Datenbanken mit den Assistenten von Visual Studio erstellt wurden. Hier verwenden wir ein einziges DBMS, MySQL 8, das der Anhang mit Laragon installiert, und einen seiner heute am weitesten verbreiteten ADO.NET-Connectors, MySqlConnector. Die Datenbanken werden durch SQL-Skripte erstellt, die mit dem Kommandozeilenclient [mysql] ausgeführt werden; der Anhang zeigt außerdem, wie man sie mit dem grafischen Client HeidiSQL erstellt, der in Laragon enthalten ist.

10.2. Die zwei Arten, eine Datenquelle zu nutzen

ADO.NET erlaubt es, eine Datenquelle auf zwei unterschiedliche Arten zu nutzen:

  • im verbundenen Modus;
  • im getrennten Modus.

Im verbundenen Modus führt die Anwendung Folgendes aus:

  • Sie öffnet eine Verbindung zur Datenquelle;
  • sie arbeitet mit der Datenquelle im Lese-/Schreibmodus;
  • sie schließt die Verbindung.

Im getrennten Modus führt die Anwendung Folgendes aus:

  • Sie öffnet eine Verbindung zur Datenquelle;
  • sie ruft eine Kopie aller oder eines Teils der Daten der Quelle in den Speicher ab;
  • sie schließt die Verbindung;
  • sie arbeitet mit der Kopie der Daten im Speicher und liest und schreibt dort;
  • wenn die Arbeit beendet ist, öffnet sie eine Verbindung, sendet die geänderten Daten zur Aktualisierung an die Datenquelle und schließt die Verbindung.

Der getrennte Modus beruhte auf den Klassen [DataSet] (einer kleinen Datenbank im Speicher mit ihren Tabellen [DataTable], ihren Zeilen [DataRow] und ihren Beziehungen) und [DataAdapter] (der Brücke zwischen einem [DataSet] und der Datenbank). Diese Klassen existieren in .NET 10 weiterhin, werden in neuen Anwendungen aber nur selten verwendet: Die Arbeit mit einer Kopie der Daten im Speicher und das anschließende Speichern der Änderungen übernimmt heute ein ORM („Object-Relational Mapper“), das gewöhnliche C#-Objekte statt Tabellenzeilen bearbeitet. Das ORM von .NET, Entity Framework Core, ist Gegenstand von Kapitel 11. Wie im Kurs von 2008 konzentrieren wir uns hier nur auf den verbundenen Modus, auf dem Entity Framework Core beruht.

10.3. Grundlegende Konzepte des Datenbankbetriebs

10.3.1. Die Beispieldatenbank

Wir erläutern die wichtigsten Konzepte der Nutzung einer Datenbank anhand einer MySQL-Datenbank namens [dbarticles]. Sie enthält eine einzige Tabelle, [articles], in der jede Zeile einen Artikel eines Geschäfts beschreibt:

  • [ID]: ein eindeutiger Bezeichner des Artikels, der der Primärschlüssel der Tabelle ist;
  • [NAME]: der Name des Artikels, der ebenfalls eindeutig ist;
  • [PRICE]: sein Preis;
  • [CURRENTSTOCK]: sein aktueller Lagerbestand;
  • [MINIMUMSTOCK]: der Mindestbestand, unterhalb dessen der Artikel nachbestellt werden muss.

Die Datenbank wird durch das folgende SQL-Skript [sql/dbarticles.sql] erstellt:

-- Erstellung der Datenbank [dbarticles] und ihrer Tabelle [articles]
-- Ausführung: mysql -uroot < dbarticles.sql

-- das Skript ist in UTF-8 geschrieben
SET NAMES utf8mb4;

-- die Datenbank wird bei jeder Ausführung des Skripts neu erstellt
DROP DATABASE IF EXISTS dbarticles;
CREATE DATABASE dbarticles CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- Wahl der aktuellen Datenbank
USE dbarticles;

-- Erstellung der Artikeltabelle
CREATE TABLE articles (
    ID            INT PRIMARY KEY AUTO_INCREMENT,
    NAME          VARCHAR(30) NOT NULL,
    PRICE         DECIMAL(10,2) NOT NULL,
    CURRENTSTOCK  INT NOT NULL,
    MINIMUMSTOCK  INT NOT NULL,
    -- Integritätsbedingungen
    CONSTRAINT UNQ_NAME UNIQUE (NAME),
    CONSTRAINT CHK_NAME CHECK (NAME <> ''),
    CONSTRAINT CHK_PRICE CHECK (PRICE > 0),
    CONSTRAINT CHK_CURRENTSTOCK CHECK (CURRENTSTOCK >= 0),
    CONSTRAINT CHK_MINIMUMSTOCK CHECK (MINIMUMSTOCK >= 0)
);

-- Einfügen von Daten in die Tabelle
INSERT INTO articles (NAME, PRICE, CURRENTSTOCK, MINIMUMSTOCK) VALUES
    ('bike', 500, 10, 5),
    ('pump', 10, 10, 2),
    ('bow', 600, 4, 1),
    ('arrows - set of 6', 100, 12, 20),
    ('diving suit', 300, 8, 2),
    ('diver''s oxygen tanks', 120, 10, 5);
  • Zeile 5: Das Skript ist eine UTF-8-codierte Textdatei. [SET NAMES utf8mb4] teilt dies dem Server mit; andernfalls könnten Zeichen mit Akzenten vom Client [mysql] falsch interpretiert werden;
  • Zeilen 8–9: Die Datenbank wird gelöscht, falls sie existiert, und anschließend neu erstellt. Das Skript kann somit mehrfach ausgeführt werden, um die Datenbank in ihren Anfangszustand zurückzuversetzen. [utf8mb4] ist die vollständige Unicode-Codierung von MySQL (die historische Codierung [utf8] von MySQL codiert nur Zeichen mit bis zu drei Bytes);
  • Zeile 12: [dbarticles] wird zur aktuellen Datenbank: Die folgenden SQL-Befehle werden auf ihr ausgeführt;
  • Zeilen 15–26: die Definition der Tabelle [articles]:
    • Zeile 16: Die Spalte [ID] ist der Primärschlüssel der Tabelle ([PRIMARY KEY]). Das MySQL-spezifische Attribut [AUTO_INCREMENT] gibt an, dass ihr Wert beim Einfügen einer Zeile vom DBMS selbst erzeugt wird: 1, 2, 3 … Andere DBMS kennen gleichwertige Konzepte, jedoch mit anderer Syntax ([IDENTITY] bei SQL Server, [GENERATED … AS IDENTITY] bei PostgreSQL);
    • Zeile 18: Der Preis ist eine exakte Dezimalzahl mit 10 Stellen und 2 Nachkommastellen. Das Skript von 2008 verwendete den Typ [DOUBLE PRECISION], eine Gleitkommazahl, die für Geldbeträge ungeeignet ist (siehe den Unterschied zwischen [double] und [decimal] in Kapitel 3). Der SQL-Typ [DECIMAL] entspricht dem C#-Typ [decimal];
    • Zeilen 22–26: Integritätsbedingungen. [UNQ_NAME] verhindert, dass zwei Artikel denselben Namen haben; die [CHECK]-Bedingungen verhindern einen leeren Namen, einen negativen oder null betragenden Preis oder einen negativen Bestand. Das Skript von 2008 fügte diese Bedingungen bereits hinzu, doch MySQL ignorierte sie stillschweigend; MySQL erzwingt sie seit Version 8.0.16;
  • Zeilen 30–36: Einfügen von sechs Zeilen. Die Spalte [ID] wird nicht initialisiert: Das DBMS weist ihr einen Wert zu.

Das Skript wird mit dem Kommandozeilenclient [mysql] im Ordner [sql] ausgeführt (der MySQL-Benutzer [root] hat bei der Installation im Anhang kein Passwort; andernfalls fügen Sie die Option [-p] hinzu, und der Client fragt danach):

> mysql -uroot < dbarticles.sql

Der Befehl zeigt nichts an, wenn alles gut geht. Sie können Struktur und Inhalt der Tabelle mit demselben Client prüfen:

> mysql -uroot --default-character-set=utf8mb4 -t dbarticles -e "describe articles; select * from articles"
+--------------+---------------+------+-----+---------+----------------+
| Field        | Type          | Null | Key | Default | Extra          |
+--------------+---------------+------+-----+---------+----------------+
| ID           | int           | NO   | PRI | NULL    | auto_increment |
| NAME         | varchar(30)   | NO   | UNI | NULL    |                |
| PRICE        | decimal(10,2) | NO   |     | NULL    |                |
| CURRENTSTOCK | int           | NO   |     | NULL    |                |
| MINIMUMSTOCK | int           | NO   |     | NULL    |                |
+--------------+---------------+------+-----+---------+----------------+
+----+----------------------+--------+--------------+--------------+
| ID | NAME                 | PRICE  | CURRENTSTOCK | MINIMUMSTOCK |
+----+----------------------+--------+--------------+--------------+
|  1 | bike                 | 500.00 |           10 |            5 |
|  2 | pump                 |  10.00 |           10 |            2 |
|  3 | bow                  | 600.00 |            4 |            1 |
|  4 | arrows - set of 6    | 100.00 |           12 |           20 |
|  5 | diving suit          | 300.00 |            8 |            2 |
|  6 | diver's oxygen tanks | 120.00 |           10 |            5 |
+----+----------------------+--------+--------------+--------------+
  • Zeile 1: Die Option [-t] fordert eine tabellarische Anzeige an; die Option [-e] gibt die auszuführenden SQL-Befehle an, und die Option [--default-character-set=utf8mb4] stellt sicher, dass Zeichen mit Akzenten in einem UTF-8-Terminal korrekt angezeigt werden;
  • Zeilen 3–9: die Struktur der Tabelle, ermittelt mit dem MySQL-Befehl [describe];
  • Zeilen 12–19: der Inhalt der Tabelle. Die Werte der Spalte [ID] wurden vom DBMS erzeugt.

Ein Hinweis zur Groß- und Kleinschreibung: Spaltennamen unterscheiden nie zwischen Groß- und Kleinschreibung ([name] und [NAME] bezeichnen dieselbe Spalte), Tabellennamen sind jedoch Dateinamen auf dem Server. Unter Windows wird dabei nicht zwischen Groß- und Kleinschreibung unterschieden, unter Linux hingegen schon. Damit die Beispiele überall funktionieren, heißt die Tabelle [articles] und ist kleingeschrieben, und die Abfragen verweisen so auf sie.

Nachdem wir nun eine Datenbank haben, können wir sie nutzen. Zuvor wollen wir einige Konzepte von SQL wiederholen.

10.3.2. Die vier grundlegenden Befehle der Sprache SQL

SQL („Structured Query Language“) ist eine teilweise standardisierte Sprache zum Abfragen und Aktualisieren von Datenbanken. Alle DBMS-Dialekte halten sich an den standardisierten Teil von SQL, ergänzen die Sprache aber um proprietäre Erweiterungen, die ihre jeweiligen Besonderheiten nutzen. Zwei Beispiele dafür sind uns bereits begegnet: Die automatische Erzeugung von Primärschlüsseln und die zulässigen Datentypen für Tabellenspalten hängen häufig vom DBMS ab.

Die vier grundlegenden Befehle der Sprache SQL, die wir hier vorstellen, sind standardisiert und werden von allen DBMS unterstützt:

Befehl
Aufgabe
[select col1, col2, … from table1, table2, … where condition order by expression]
Die Abfrage, mit der Daten aus einer Datenbank abgerufen werden. Nur die Schlüsselwörter [select] und [from] sind obligatorisch. Mit allen Tabellen, die auf das Schlüsselwort [from] folgen, wird ein Join durchgeführt; nur die Spalten, die auf das Schlüsselwort [select] folgen, werden beibehalten; nur die Zeilen, die die Bedingung des Schlüsselworts [where] erfüllen, werden beibehalten; die resultierenden Zeilen, sortiert nach dem Ausdruck im Schlüsselwort [order by], bilden das Ergebnis der Abfrage. Dieses Ergebnis ist eine Tabelle.
[insert into table(col1, col2, …) values (val1, val2, …)]
Fügt eine Zeile in [table] ein. [(col1, col2, …)] gibt die Spalten der Zeile an, die mit den Werten aus [(val1, val2, …)] gefüllt werden.
[update table set col1=val1, col2=val2, … where condition]
Aktualisiert die Zeilen in [table] anhand von [condition] (alle Zeilen, wenn kein [where] angegeben ist). Für diese Zeilen wird die Spalte [coli] auf [vali] gesetzt.
[delete from table where condition]
Löscht alle Zeilen aus [table] , die [condition] erfüllen (alle Zeilen, wenn kein [where] angegeben ist).

Wir schreiben eine Konsolenanwendung, die SQL-Befehle auf der Grundlage von [dbarticles] absetzt. Hier ein Beispiel für die Ausgabe. Dem Leser wird empfohlen, die abgesetzten SQL-Befehle und ihre Ergebnisse nachzuvollziehen:

> dotnet run adonet-01.cs
Database connection string: [Server=localhost;Port=3306;Database=dbarticles;User=root;Password=]

SQL query (nothing to stop): select id,name,price,currentstock,minimumstock from articles

---------------------------------------
id,name,price,currentstock,minimumstock
---------------------------------------

1 bike 500.00 10 5 
2 pump 10.00 10 2 
3 bow 600.00 4 1 
4 arrows - set of 6 100.00 12 20 
5 diving suit 300.00 8 2 
6 diver's oxygen tanks 120.00 10 5 

SQL query (nothing to stop): insert into articles(name,price,currentstock,minimumstock) values('x',100,10,1)
1 row(s) modified

SQL query (nothing to stop): select * from articles

---------------------------------------
ID,NAME,PRICE,CURRENTSTOCK,MINIMUMSTOCK
---------------------------------------

1 bike 500.00 10 5 
2 pump 10.00 10 2 
3 bow 600.00 4 1 
4 arrows - set of 6 100.00 12 20 
5 diving suit 300.00 8 2 
6 diver's oxygen tanks 120.00 10 5 
7 x 100.00 10 1 

SQL query (nothing to stop): update articles set price=price*1.1 where name='x'
1 row(s) modified

SQL query (nothing to stop): select id,name,price from articles where name='x'

-------------
id,name,price
-------------

7 x 110.00 

SQL query (nothing to stop): delete from articles where name='x'
1 row(s) modified

SQL query (nothing to stop): select count(*) from articles

--------
count(*)
--------

6 

SQL query (nothing to stop): selectt * from articles
Invalid query. Use select, insert, update, delete or nothing to stop

SQL query (nothing to stop): select * from article
Database access error (Table 'dbarticles.article' doesn't exist)

SQL query (nothing to stop): insert into articles(name,price,currentstock,minimumstock) values('bike',100,10,1)
Database access error (Duplicate entry 'bike' for key 'articles.UNQ_NAME')

SQL query (nothing to stop): 
  • Zeile 2: die Verbindungszeichenfolge: Sie enthält alle Parameter, die für die Verbindung zur Datenbank erforderlich sind;
  • Zeile 4: Der Inhalt der Tabelle [articles] wird abgerufen. Die Preise werden mit Dezimalpunkt angezeigt;
  • Zeile 17: Eine neue Zeile wird eingefügt. Beachten Sie, dass die Spalte [id] bei dieser Operation nicht initialisiert wird, weil das DBMS ihren Wert erzeugt;
  • Zeile 20: Überprüfung. Zeile 32: Die Zeile wurde tatsächlich hinzugefügt, mit [id] 7. Bei [select *] sind die Spaltennamen die der Tabelle, in Großbuchstaben;
  • Zeile 34: Wir erhöhen den Preis des soeben hinzugefügten Artikels um 10 %;
  • Zeile 43: Die Preiserhöhung ist wirksam geworden;
  • Zeile 45: Der zuvor hinzugefügte Artikel wird gelöscht;
  • Zeile 48: Wir überprüfen, dass nur noch sechs Artikel vorhanden sind;
  • Zeile 56: Das Programm akzeptiert nur die vier Befehle [select], [insert], [update], und [delete];
  • Zeile 60: ein vom DBMS gemeldeter Fehler: Die Tabelle [article] existiert nicht;
  • Zeile 63: Die Eindeutigkeitsbedingung [UNQ_NAME] verhindert das Hinzufügen eines zweiten Artikels [bike]. Die Fehlermeldungen von MySQL sind wie die von .NET in englischer Sprache.

10.3.3. Basisklassen von ADO.NET für den verbundenen Modus

Kehren wir zum Schema einer Anwendung zurück, die über einen ADO.NET-Connector auf eine Datenbank zugreift:

Image

Im verbundenen Modus führt die Anwendung Folgendes aus:

  • Sie öffnet eine Verbindung zur Datenquelle;
  • sie arbeitet mit der Datenquelle im Lese-/Schreibmodus;
  • sie schließt die Verbindung.

An diesen Operationen sind im Wesentlichen drei Arten von Objekten beteiligt:

  • die Verbindung, die die Eigenschaften und Methoden der Datenbankverbindung kapselt;
  • der Befehl, der die Eigenschaften und Methoden des ausgeführten SQL-Befehls kapselt;
  • der Data Reader, der die Eigenschaften und Methoden des Ergebnisses eines SQL-Befehls [select] kapselt.

Jeder Connector definiert seine eigenen Klassen ([MySqlConnection], [MySqlCommand], [MySqlDataReader] für MySqlConnector), die von abstrakten Klassen abgeleitet sind, die allen Connectors gemeinsam sind und im Namespace [System.Data.Common] definiert werden: [DbConnection], [DbCommand], [DbDataReader]. Der Kurs von 2008 stellte die Schnittstellen [IDbConnection], [IDbCommand] und [IDataReader] aus dem Namespace [System.Data] vor, die diese Klassen implementieren. Heute werden abstrakte Klassen bevorzugt: Sie bieten mehr Funktionalität als Schnittstellen, denen sich keine Methoden mehr hinzufügen ließen, ohne bestehende Connectors zu beschädigen (Standardschnittstellenmethoden gab es noch nicht). Die asynchronen Versionen der Methoden ([OpenAsync], [ExecuteReaderAsync], [ReadAsync] …) beispielsweise existieren nur in den abstrakten Klassen.

10.3.3.1. Die Klasse DbConnection

Sie dient zur Verwaltung der Verbindung zur Datenbank. Die Methoden M und Eigenschaften P dieser Klasse, die wir verwenden werden, sind die folgenden:

Name
Typ
Rolle
[ConnectionString]
P
Verbindungszeichenfolge der Datenbank. Sie legt alle Parameter fest, die zum Aufbau einer Verbindung zu einer bestimmten Datenbank erforderlich sind.
[Open]
M
Öffnet die Verbindung zu der Datenbank, die durch [ConnectionString] definiert ist.
[Close]
M
schließt die Verbindung. [Dispose] schließt sie ebenfalls.
[BeginTransaction]
M
startet eine Transaktion.
[CreateCommand]
M
erstellt einen Befehl, der dieser Verbindung zugeordnet ist.
[State]
P
Verbindungsstatus: [ConnectionState.Closed], [ConnectionState.Open], [ConnectionState.Connecting], [ConnectionState.Executing], [ConnectionState.Fetching], [ConnectionState.Broken].

Mit MySqlConnector kann eine Verbindung wie folgt hergestellt werden:

1
2
3
DbConnection connection = new MySqlConnection();
connection.ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
connection.Open();

oder, einfacher, indem die Verbindungszeichenfolge an den Konstruktor übergeben wird:

var connection = new MySqlConnection("Server=localhost;Port=3306;Database=dbarticles;User=root;Password=");
connection.Open();

10.3.3.2. Die Klasse DbCommand

Sie dient zum Ausführen eines SQL-Befehls oder einer gespeicherten Prozedur. Die Methoden M und Eigenschaften P dieser Klasse, die wir verwenden werden, sind die folgenden:

Name
Typ
Rolle
[CommandType]
P
legt fest, was ausgeführt werden soll. Seine Werte stammen aus der Enumeration [CommandType]: [CommandType.Text] führt den SQL-Befehl aus, der in der Eigenschaft [CommandText] definiert ist (dies ist der Standardwert); [CommandType.StoredProcedure] führt eine gespeicherte Prozedur der Datenbank aus.
[CommandText]
P
der Text des auszuführenden SQL-Befehls, wenn [CommandType] gleich [CommandType.Text] ist, bzw. der Name der auszuführenden gespeicherten Prozedur, wenn [CommandType] gleich [CommandType.StoredProcedure] ist.
[Connection]
P
die Verbindung [DbConnection] , die zum Ausführen des SQL-Befehls verwendet werden soll.
[Transaction]
P
die Transaktion [DbTransaction] , in der der SQL-Befehl ausgeführt werden soll.
[Parameters]
P
die Liste der Parameter eines parametrisierten SQL-Befehls. Der Befehl [update articles set price=price*1.1 where id=@id] hat den Parameter [@id].
[ExecuteReader]
M
Führt einen SQL-Befehl [select] aus. Dabei wird ein Objekt [DbDataReader] zurückgegeben, das das Ergebnis von [select] darstellt.
[ExecuteNonQuery]
M
führt einen SQL-Befehl [update], [insert] oder [delete] aus. Dabei wird die Anzahl der von der Operation betroffenen Zeilen zurückgegeben (Aktualisierungen, Einfügungen, Löschungen).
[ExecuteScalar]
M
führt einen SQL-Befehl [select] aus, der nur ein einziges Ergebnis liefert, wie etwa [select count(*) from articles].
[CreateParameter]
M
erstellt einen Parameter [DbParameter] für einen parametrisierten SQL-Befehl.
[Prepare]
M
ermöglicht es, die Ausführung einer parametrisierten Abfrage zu optimieren, wenn sie mehrfach mit unterschiedlichen Parametern ausgeführt wird.

Die Ausführung eines SQL-Befehls ohne Transaktion hat die folgende Form:

// Öffnen der Verbindung
var connection = new MySqlConnection(connectionString);
connection.Open();
// Vorbereitung des Befehls
var command = new MySqlCommand();
command.Connection = connection;
// Ausführung einer select-Anweisung
command.CommandText = "select ...";
MySqlDataReader reader = command.ExecuteReader();
...
// Ausführung einer update-, insert- oder delete-Anweisung
command.CommandText = "insert ...";
int nbInsertedRows = command.ExecuteNonQuery();
...
// Schließen der Verbindung
connection.Close();

10.3.3.3. Die Klasse DbDataReader

Sie kapselt die Ergebnisse eines SQL-Befehls [select]. Ein Objekt [DbDataReader] stellt eine Tabelle mit Zeilen und Spalten dar, die sequenziell verarbeitet werden: zuerst die erste Zeile, dann die zweite und so weiter, ohne die Möglichkeit, zurückzugehen. Nicht alle Zeilen werden in den Speicher geladen: Der Reader erhält sie vom Server, während sie gelesen werden. Die Methoden M und Eigenschaften P dieser Klasse, die wir verwenden werden, sind die folgenden:

Name
Typ
Rolle
[FieldCount]
P
Die Anzahl der Spalten der Ergebnistabelle.
[GetName]
M
[GetName(i)] gibt den Namen der Spalte mit der Nummer i der Ergebnistabelle zurück (die Spalten werden ab 0 nummeriert).
[GetOrdinal]
M
[GetOrdinal(name)] gibt die Nummer der Spalte mit dem Namen [name] zurück.
[Item]
P
Der Indexer [reader[i]] stellt die Spalte mit der Nummer i der aktuellen Zeile dar, während [reader["name"]] die Spalte mit dem Namen [name] darstellt, und zwar in Form eines [object].
[Read]
M
geht zur nächsten Zeile der Ergebnistabelle weiter. Gibt [true] zurück, wenn die Zeile erfolgreich gelesen wurde, und [false] , wenn keine Zeilen mehr vorhanden sind.
[Close]
M
schließt den Reader. [Dispose] schließt ihn ebenfalls.
[GetInt32]
M
[GetInt32(i)] gibt den ganzzahligen Wert der Spalte i der aktuellen Zeile zurück. Ähnliche Methoden sind [GetBoolean], [GetDateTime], [GetDecimal], [GetDouble], [GetFloat], [GetInt16], [GetInt64], [GetString] …
[GetValue]
M
[GetValue(i)] gibt den Wert der Spalte i der aktuellen Zeile als Typ [object] zurück.
[IsDBNull]
M
[IsDBNull(i)] gibt [true] zurück, wenn die Spalte i der aktuellen Zeile keinen Wert hat, was durch den SQL-Wert [NULL] dargestellt wird. Der entsprechende Wert in C# ist [DBNull.Value].

Die Verarbeitung eines Objekts [DbDataReader] sieht häufig wie folgt aus:

// Öffnen der Verbindung
var connection = new MySqlConnection(connectionString);
connection.Open();
// Vorbereitung des Befehls
var command = new MySqlCommand("select ...", connection);
// Ausführung der select-Anweisung
MySqlDataReader reader = command.ExecuteReader();
// Verarbeitung der Ergebnisse
while (reader.Read())
{
    // aktuelle Zeile verarbeiten
    ...
}
// Schließen des Readers
reader.Close();
// Schließen der Verbindung
connection.Close();

10.3.4. Fehlerbehandlung

Die Schicht [dao] kann beim Zugriff auf die Datenbank auf zahlreiche Fehler stoßen: Server nicht erreichbar, Datenbank existiert nicht, falsches Passwort, SQL-Syntaxfehler, Verletzung einer Integritätsbedingung usw. Diese Fehler werden als Ausnahmen gemeldet, die der ADO.NET-Konnektor auslöst. Der Code der Schicht [dao] muss sie behandeln und unabhängig vom Ergebnis die belegten Ressourcen freigeben: Die Verbindung und der Reader müssen geschlossen werden. Der oben gezeigte Code zur Verarbeitung des Ergebnisses eines Befehls [select] sieht dann wie folgt aus:

MySqlConnection? connection = null;
try
{
    // Öffnen der Verbindung
    connection = new MySqlConnection(connectionString);
    connection.Open();
    // Ausführung der select-Anweisung
    var command = new MySqlCommand("select ...", connection);
    MySqlDataReader reader = command.ExecuteReader();
    try
    {
        // Verarbeitung der Ergebnisse
        while (reader.Read())
        {
            ...
        }
    }
    finally
    {
        // Schließen des Readers
        reader.Close();
    }
}
catch (DbException ex)
{
    // Behandlung der Ausnahme
    ...
}
finally
{
    // Schließen der Verbindung
    connection?.Close();
}

Das Schließen der Verbindung und des Readers lässt sich mit der Anweisung [using] automatisieren: Die Verbindung, der Befehl und der Reader implementieren die Schnittstelle [IDisposable], und ihre Methode [Dispose] schließt sie. Ab C# 8 gibt die [using]-Deklaration (ohne Block) die Ressource am Ende des Blocks frei, der die Deklaration enthält:

try
{
    // die Verbindung wird beim Verlassen des try-Blocks geschlossen, egal auf welchem Weg wir ihn verlassen
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    using var command = new MySqlCommand("select ...", connection);
    using MySqlDataReader reader = command.ExecuteReader();
    // Verarbeitung der Ergebnisse
    while (reader.Read())
    {
        ...
    }
}
catch (DbException ex)
{
    // Behandlung der Ausnahme
    ...
}
  • Zeile 4: Die [using]-Deklaration stellt sicher, dass die Verbindung beim Verlassen des [try]-Blocks geschlossen wird, unabhängig davon, ob er normal oder über eine Ausnahme verlassen wird. Das erspart eine [finally]-Klausel, doch der Nutzen liegt nicht in dieser kleinen Ersparnis: Die [using]-Deklaration enthebt den Entwickler der Pflicht, die Verbindung manuell zu schließen. Eine vergessene Schließung kann jedoch unbemerkt bleiben und die Anwendung scheinbar zufällig „abstürzen“ lassen, sobald das DBMS die maximale Anzahl offener Verbindungen erreicht, die es unterstützen kann;
  • Zeile 7: Für den Reader gilt dasselbe Vorgehen. Die Ressourcen werden in umgekehrter Reihenfolge ihrer Deklaration freigegeben: zuerst der Reader, dann der Befehl, dann die Verbindung.

Die von den Konnektoren ausgelösten Ausnahmen erben alle von der abstrakten Klasse [DbException] (Namespace [System.Data.Common]). Jeder Konnektor hat seine eigene abgeleitete Klasse, [MySqlException] bei MySqlConnector, die zusätzliche Informationen zum Fehler liefert: seine MySQL-Nummer ([Number]), dieselbe Nummer in Form einer Enumeration ([ErrorCode], vom Typ [MySqlErrorCode]) sowie den standardisierten SQL-Fehlercode ([SqlState]). Das Programm [adonet-02.cs] erzeugt verschiedene Fehler und zeigt sie an:

#:package MySqlConnector@2.4.0

// Behandlung von Fehlern beim Datenbankzugriff
using System.Data.Common;
using MySqlConnector;

// die Verbindungszeichenfolge, am Anfang des Programms
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

// Operationen, die fehlschlagen, jeweils aus einem anderen Grund
Execute("nonexistent database", "Server=localhost;Port=3306;Database=dbunknown;User=root;Password=",
    "select * from articles");
Execute("unreachable server", "Server=localhost;Port=3399;Database=dbarticles;User=root;Password=;Connection Timeout=5",
    "select * from articles");
Execute("syntax error", ConnectionString, "select * fromm articles");
Execute("nonexistent table", ConnectionString, "select * from article");
Execute("duplicate name", ConnectionString,
    "insert into articles(name, price, currentstock, minimumstock) values('bike', 100, 10, 1)");
Execute("negative price", ConnectionString,
    "insert into articles(name, price, currentstock, minimumstock) values('canoe', -100, 10, 1)");

// führt eine SQL-Anweisung aus und zeigt den möglichen Fehler an
static void Execute(string title, string connectionString, string query)
{
    Console.WriteLine($"--- {title}");
    try
    {
        using var connection = new MySqlConnection(connectionString);
        connection.Open();
        using var command = new MySqlCommand(query, connection);
        command.ExecuteNonQuery();
        Console.WriteLine("no error");
    }
    catch (MySqlException ex) when (ex.ErrorCode == MySqlErrorCode.DuplicateKeyEntry)
    {
        // ein spezifischer Fehler, erkannt an seinem Code
        Console.WriteLine($"this article already exists ({ex.Message})");
    }
    catch (MySqlException ex)
    {
        // die übrigen MySQL-Fehler
        Console.WriteLine($"{ex.GetType().Name} : Number={ex.Number}, ErrorCode={ex.ErrorCode}, SqlState={ex.SqlState}");
        Console.WriteLine($"  {ex.Message}");
    }
    catch (DbException ex)
    {
        // ein Fehler eines anderen ADO.NET-Connectors (tritt hier nicht auf)
        Console.WriteLine($"{ex.GetType().Name} : {ex.Message}");
    }
}

Ergebnisse:

> dotnet run adonet-02.cs
--- nonexistent database
MySqlException : Number=1049, ErrorCode=UnknownDatabase, SqlState=42000
  Unknown database 'dbunknown'
--- unreachable server
MySqlException : Number=1042, ErrorCode=UnableToConnectToHost, SqlState=
  Unable to connect to any of the specified MySQL hosts.
--- syntax error
MySqlException : Number=1064, ErrorCode=ParseError, SqlState=42000
  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fromm articles' at line 1
--- nonexistent table
MySqlException : Number=1146, ErrorCode=NoSuchTable, SqlState=42S02
  Table 'dbarticles.article' doesn't exist
--- duplicate name
this article already exists (Duplicate entry 'bike' for key 'articles.UNQ_NAME')
--- negative price
MySqlException : Number=3819, ErrorCode=3819, SqlState=HY000
  Check constraint 'CHK_PRICE' is violated.
  • Zeile 1: Die Direktive [#:package] in einer Einzeldatei-Anwendung (Kapitel 2) fügt dem Programm das NuGet-Paket [MySqlConnector] in Version 2.4.0 hinzu. Darauf kommen wir im nächsten Abschnitt zurück;
  • Zeile 8: die Verbindungszeichenfolge der Datenbank, am Anfang des Programms angegeben;
  • Zeilen 11–20: sechs Operationen, die fehlschlagen: Datenbank existiert nicht; Server nicht erreichbar (auf Port 3399 lauscht kein MySQL-Server, was denselben Fehler erzeugt wie ein angehaltener Server); Syntaxfehler; Tabelle existiert nicht; doppelter Artikelname; negativer Preis;
  • Zeile 23: Die Methode [Execute] ist eine lokale Funktion (C# 7) des Hauptprogramms. Das Schlüsselwort [static] (C# 8) verhindert, dass sie die lokalen Variablen des Hauptprogramms verwendet: Sie arbeitet nur mit ihren Parametern (und Konstanten);
  • Zeile 34: Ein Ausnahmefilter (C# 6) erlaubt es, einen bestimmten Fehler gesondert zu behandeln, hier die Verletzung einer Eindeutigkeitsbedingung (MySQL-Fehler Nr. 1062). In diesem Fall würde eine Anwendung eine Meldung anzeigen, die der Benutzer verstehen kann;
  • Zeile 39: Die übrigen MySQL-Fehler. Die Ergebnisse zeigen ihre Nummern und Codes: [UnknownDatabase] (1049), [UnableToConnectToHost] (1042), [ParseError] (1064), [NoSuchTable] (1146). Die Verletzung einer [CHECK]-Bedingung (Fehler 3819, Zeile 17) hat in der Enumeration [MySqlErrorCode] von MySqlConnector 2.4.0 keinen Namen: Stattdessen wird die Nummer angezeigt;
  • Zeile 45: die Klausel, die die Ausnahme eines beliebigen Konnektors abfangen würde. Sie ist hier ohne Nutzen, aber genau diese würde eine Schicht [dao] verwenden, die unabhängig vom Konnektor geschrieben ist. Beachten Sie die Reihenfolge der [catch]-Klauseln: vom spezifischsten ([MySqlException]) zum allgemeinsten ([DbException]).

10.3.5. Konfiguration des Beispielprojekts

Der Konnektor MySqlConnector ist ein NuGet-Paket. In einer Einzeldatei-Anwendung wird er mit der folgenden Direktive hinzugefügt:

#:package MySqlConnector@2.4.0

In einem Projekt wird er mit dem Befehl [dotnet add package] hinzugefügt, der im Projektordner eingegeben wird:

> dotnet add package MySqlConnector --version 2.4.0

der den Verweis auf das Paket in die Datei [.csproj] schreibt:

1
2
3
<ItemGroup>
  <PackageReference Include="MySqlConnector" Version="2.4.0" />
</ItemGroup>

Es gibt zwei ADO.NET-Konnektoren für MySQL:

  • [MySql.Data] („Connector/NET“), herausgegeben von Oracle, dem Eigentümer von MySQL. Dies ist der Konnektor, der im Kurs von 2008 verwendet wurde, in Version 5.2;
  • [MySqlConnector], ein Open-Source-Konnektor (unter MIT-Lizenz), der unabhängig von Oracle entwickelt wird. Er ist vollständig asynchron (seine [XxxAsync]-Methoden sind wirklich asynchron, während die von [MySql.Data] lange Zeit nur als asynchron getarnte synchrone Methoden waren), schneller, und er funktioniert mit MySQL und MariaDB. Er wird von [Pomelo.EntityFrameworkCore.MySql] verwendet, dem MySQL-Provider für Entity Framework Core, den wir in Kapitel 11 einsetzen werden.

Die Klassen beider Konnektoren haben dieselben Namen ([MySqlConnection], [MySqlCommand] …), liegen aber in unterschiedlichen Namespaces: [MySqlConnector] beim ersten, [MySql.Data.MySqlClient] beim zweiten.

Die Verbindungszeichenfolge sollte in einer echten Anwendung nicht fest im Code stehen: Sie hängt von dem Rechner ab, auf dem die Anwendung installiert ist. Der Kurs von 2008 legte sie im Abschnitt [connectionStrings] der XML-Datei [App.config] ab und las sie mit der Klasse [ConfigurationManager]. Die aktuelle Konfigurationsdatei für .NET ist die JSON-Datei [appsettings.json], die bereits in Kapitel 6 verwendet wurde und ebenfalls einen eigenen Abschnitt für Verbindungszeichenfolgen enthält, [ConnectionStrings]. Das erste Programm dieses Kapitels verwendet die folgende Datei [appsettings.json], die im Ordner [10-adonet] liegt:

1
2
3
4
5
{
  "ConnectionStrings": {
    "dbarticles": "Server=localhost;Port=3306;Database=dbarticles;User=root;Password="
  }
}
  • Zeile 2: der Abschnitt der Verbindungszeichenfolgen. Er kann mehrere Zeichenfolgen enthalten, die jeweils durch einen Namen identifiziert werden;
  • Zeile 3: die Verbindungszeichenfolge mit dem Namen [dbarticles]. Eine Verbindungszeichenfolge hat die Form „Parameter1=Wert1;Parameter2=Wert2;…“. Ihre Parameter hängen vom Konnektor ab; die für MySqlConnector lauten wie folgt:
    • [Server]: der Name des Rechners, auf dem sich das DBMS MySQL befindet, hier [localhost], also der Rechner, auf dem das Programm läuft;
    • [Port]: der Port, auf dem der MySQL-Server auf Verbindungen lauscht; der Standardwert ist 3306;
    • [Database]: der Name der Datenbank, hier [dbarticles];
    • [User] (oder [Uid]): der MySQL-Benutzer, hier [root], der Administrator des DBMS;
    • [Password] (oder [Pwd]): sein Passwort, hier leer gelassen.

Die Website connectionstrings.com liefert das Format der Verbindungszeichenfolgen der wichtigsten DBMS, und die Dokumentation von MySqlConnector enthält die vollständige Liste seiner Einstellungen: MySqlConnector Connection Options.

Die anderen Programme dieses Kapitels, die kürzer sind, legen ihre Verbindungszeichenfolge in einer Konstanten am Anfang des Programms ab, wie [adonet-02.cs]: Das ist für Beispiele einfacher, sollte aber in einer echten Anwendung nicht so gemacht werden.

10.3.6. Das Beispielprogramm

Das Programm [adonet-01.cs], dessen Ausführung wir bereits an einem Beispiel gesehen haben, lautet wie folgt (Hauptprogramm):

#:package MySqlConnector@2.4.0
#:package Microsoft.Extensions.Configuration.Json@10.0.12

// Konsolenanwendung: führt über die Tastatur eingegebene SQL-Anweisungen aus
// auf einer Datenbank, deren Verbindungszeichenfolge aus einer Konfigurationsdatei gelesen wird
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.Extensions.Configuration;
using MySqlConnector;

// Lesen der Verbindungszeichenfolge aus [appsettings.json] (aktueller Ordner)
string? connectionString;
try
{
    IConfiguration config = new ConfigurationBuilder()
        .SetBasePath(Directory.GetCurrentDirectory())
        .AddJsonFile("appsettings.json", optional: false)
        .Build();
    connectionString = config.GetConnectionString("dbarticles");
}
catch (Exception e)
{
    Console.WriteLine($"Configuration error: {e.Message}");
    return;
}
if (connectionString is null)
{
    Console.WriteLine("Configuration error: the connection string [dbarticles] is missing");
    return;
}

// Anzeige der Verbindungszeichenfolge
Console.WriteLine($"Database connection string: [{connectionString}]");

// die zulässigen SQL-Anweisungen
HashSet<string> sqlCommands = ["select", "insert", "update", "delete"];

// Eingabe-Ausführungs-Schleife für die über die Tastatur eingegebenen SQL-Anweisungen
while (true)
{
    // Abfrage der Anweisung
    Console.Write("\nSQL query (nothing to stop): ");
    string query = Console.ReadLine()?.Trim() ?? "";
    // fertig?
    if (query == "")
    {
        break;
    }
    // das erste Wort der Abfrage muss eine der vier zulässigen Anweisungen sein
    string firstWord = Regex.Match(query, @"^\S+").Value.ToLower();
    if (!sqlCommands.Contains(firstWord))
    {
        Console.WriteLine("Invalid query. Use select, insert, update, delete or nothing to stop");
        continue;
    }
    // Ausführung der Abfrage
    if (firstWord == "select")
    {
        ExecuteSelect(connectionString, query);
    }
    else
    {
        ExecuteUpdate(connectionString, query);
    }
}
  • Zeilen 1–2: die beiden verwendeten Pakete: der MySQL-Konnektor und die JSON-Konfigurationsdatei;
  • Zeile 10: der Namespace der Klassen des Konnektors. Dies stellt eine Abhängigkeit von einem konnektorspezifischen Namespace dar: Das Programm müsste geändert werden, wenn der Konnektor des DBMS gewechselt wird;
  • Zeilen 16–19: Lesen der Datei [appsettings.json], wie in Kapitel 6 beschrieben. Die Datei wird im aktuellen Verzeichnis gesucht ([Directory.GetCurrentDirectory]), und zwar in dem, aus dem [dotnet run adonet-01.cs] gestartet wird. Eine Einzeldatei-Anwendung wird nämlich in einem temporären Ordner kompiliert, der vom Quellcode getrennt ist: [AppContext.BaseDirectory] würde nicht auf den Ordner [10-adonet] verweisen;
  • Zeile 20: [GetConnectionString(name)] ist eine Kurzform für [config["ConnectionStrings:name"]]. Sie gibt [null] zurück, wenn die Zeichenfolge nicht existiert, ein Fall, der in den Zeilen 27–29 behandelt wird;
  • Zeile 37: die vier akzeptierten SQL-Befehle in einer Menge [HashSet] (Kapitel 5), initialisiert durch einen Collection-Ausdruck (C# 12). Der Kurs von 2008 verwendete ein Wörterbuch, dessen Werte keinen Zweck erfüllten;
  • Zeile 40: die Schleife zur Eingabe der SQL-Befehle über die Tastatur und zu ihrer Ausführung auf der Datenbank;
  • Zeile 51: das erste Wort der Abfrage, ermittelt mit einem regulären Ausdruck (Kapitel 5): [\S+] bezeichnet eine Folge von Zeichen, die keine Leerzeichen sind. Es wird für den Vergleich in Kleinbuchstaben umgewandelt, die Abfrage selbst bleibt aber unverändert: Das Programm von 2008 wandelte die gesamte Abfrage in Kleinbuchstaben um, was das Einfügen eines Artikels mit dem Namen „Bike“ verhinderte;
  • Zeilen 52–55: Ist die Abfrage ungültig, wird eine Fehlermeldung angezeigt, und das Programm geht zur nächsten Abfrage über;
  • Zeilen 58–64: Der eingegebene SQL-Befehl wird ausgeführt. Die Ausführung nimmt je nach Befehl eine andere Form an: Es kann ein Befehl [select] oder ein Befehl [insert], [update] oder [delete] sein: Im ersten Fall ruft der Befehl Daten aus der Datenbank ab, ohne sie zu ändern; im zweiten aktualisiert er die Datenbank, ohne Daten abzurufen. In beiden Fällen wird die Ausführung an eine Methode delegiert, die die Verbindungszeichenfolge und den auszuführenden SQL-Befehl erhält.

10.3.7. Ausführen einer SELECT-Abfrage

Das Ausführen von SQL-Befehlen umfasst die folgenden Schritte:

  • Verbindung zur Datenbank herstellen;
  • die SQL-Befehle an die Datenbank senden;
  • die Ergebnisse der SQL-Befehle verarbeiten;
  • die Verbindung schließen.

Die Schritte 2 und 3 können wiederholt werden; die Verbindung wird erst am Ende der Datenbankverarbeitung geschlossen. Offene Verbindungen sind in einem DBMS begrenzte Ressourcen: Man muss sparsam mit ihnen umgehen und bemüht sich stets, die Lebensdauer einer offenen Verbindung zu begrenzen. In unserem Programm wird die Verbindung nach jedem SQL-Befehl geschlossen, und für den nächsten Befehl wird eine neue Verbindung geöffnet.

Das Öffnen einer Verbindung ist jedoch aufwendig: Es erfordert den Aufbau einer Netzwerkverbindung zum Server, gegebenenfalls das Aushandeln einer Verschlüsselung und eine Authentifizierung. Um diesen Aufwand zu verringern, verwalten die Konnektoren einen Verbindungspool: Wenn die Anwendung eine Verbindung schließt, schließt der Konnektor sie nicht wirklich, sondern gibt sie an einen Pool zurück; wenn die Anwendung mit derselben Verbindungszeichenfolge eine neue Verbindung öffnet, erhält sie eine der bereits aufgebauten Verbindungen aus dem Pool. Der Vorteil dieses Systems ist, dass es für den Entwickler transparent ist: Das Programm muss nicht geändert werden, um es zu nutzen. Der Pool ist in MySqlConnector (wie in anderen aktuellen Konnektoren) standardmäßig aktiviert und wird über Parameter der Verbindungszeichenfolge konfiguriert ([Pooling], [MaximumPoolSize], [MinimumPoolSize] …). Es empfiehlt sich daher, zu Beginn jeder Operation eine Verbindung zu öffnen und sie am Ende mit einem [using] zu schließen.

Zunächst befassen wir uns mit der Ausführung von SQL-Befehlen [select]. Die Methode [ExecuteSelect] unseres Beispielprogramms und die von ihr verwendete Methode [DisplayReader] lauten wie folgt:

// Ausführung einer select-Anweisung
static void ExecuteSelect(string connectionString, string query)
{
    try
    {
        using var connection = new MySqlConnection(connectionString);
        // Öffnen der Verbindung
        connection.Open();
        // Ausführung der SQL-Anweisung
        using var command = new MySqlCommand(query, connection);
        using MySqlDataReader reader = command.ExecuteReader();
        // Anzeige der Ergebnisse
        DisplayReader(reader);
    }
    catch (DbException ex)
    {
        Console.WriteLine($"Database access error ({ex.Message})");
    }
}

// Anzeige der Zeilen eines select-Ergebnisses
static void DisplayReader(DbDataReader reader)
{
    // die Namen der Spalten
    var names = new string[reader.FieldCount];
    for (int i = 0; i < reader.FieldCount; i++)
    {
        names[i] = reader.GetName(i);
    }
    string header = string.Join(",", names);
    string dashes = new('-', header.Length);
    Console.WriteLine($"\n{dashes}\n{header}\n{dashes}\n");
    // die Zeilen, eine nach der anderen
    while (reader.Read())
    {
        var row = new StringBuilder();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            row.Append(reader[i]).Append(' ');
        }
        Console.WriteLine(row);
    }
}
  • Zeile 89: Die Methode erhält zwei Parameter:
    • die Verbindungszeichenfolge [connectionString], die es ermöglicht, sich mit der Datenbank zu verbinden;
    • den auszuführenden SQL-Befehl [select] [query] über diese Verbindung;
  • Zeile 91: Jede Datenbankoperation kann eine Ausnahme auslösen. Das ist hier umso wahrscheinlicher, als die vom Benutzer gelieferten SQL-Befehle Syntaxfehler enthalten können. Wir müssen dies dem Benutzer mitteilen können: Daher muss der gesamte Code in einem [try] / [catch] stehen;
  • Zeile 93: hier gibt es mehrere Punkte:
    • Die Verbindung zur Datenbank wird mit der Verbindungszeichenfolge [connectionString] initialisiert. Sie ist noch nicht geöffnet; das geschieht in Zeile 95;
    • Die [using]-Deklaration stellt sicher, dass die Verbindung beim Verlassen des [try]-Blocks geschlossen (an den Pool zurückgegeben) wird;
    • Die Verbindung hat einen konnektorspezifischen Typ: [MySqlConnection];
  • Zeile 95: Die Verbindung wird geöffnet. Dabei werden die Parameter der Verbindungszeichenfolge verwendet, und hier treten Verbindungsfehler auf;
  • Zeile 97: Der SQL-Befehl wird einem Objekt [MySqlCommand] zugewiesen, das mit zwei Angaben initialisiert wird: dem abzusetzenden SQL-Befehl und der zu verwendenden Verbindung. Derselbe Objekttyp dient zum Ausführen eines Befehls [select] und der Befehle [update], [insert] und [delete] ;
  • Zeile 98: Der Befehl [select] wird von der Methode [ExecuteReader] des Befehls ausgeführt, die ein Objekt [MySqlDataReader] zurückgibt, das von [DbDataReader] abgeleitet ist;
  • Zeile 100: Die Anzeige der Ergebnisse übernimmt die Methode [DisplayReader];
  • Zeile 102: Jede Ausnahme wird unter ihrem Basistyp [DbException] abgefangen;
  • Zeile 109: Die Methode erhält ein Objekt [DbDataReader]. Beachten Sie, dass sie die abstrakte Klasse verwendet und nicht die Klasse [MySqlDataReader] des Konnektors: Sie wäre für jeden Konnektor geeignet;
  • Zeilen 112–119: Die Spaltennamen der Ergebnistabelle des Befehls [select] werden angezeigt. Es handelt sich um die Spalten [coli] der Abfrage [select col1, col2, … from table …]. [string.Join] verkettet sie und trennt sie durch Kommas, und der Konstruktor [new string('-', n)] erzeugt eine Zeichenfolge aus Bindestrichen;
  • Zeilen 121–128: Die Ergebnistabelle wird durchlaufen, und die Werte jeder Zeile werden angezeigt;
  • Zeile 126: Der Typ der Spalte i des Ergebnisses ist unbekannt, weil die vom Benutzer eingegebene Abfrage unbekannt ist. Daher kann die Syntax [reader.GetXxx(i)], wobei [Xxx] der Spaltentyp ist, nicht verwendet werden. Wir verwenden stattdessen den Indexer [reader[i]], der den Wert in Form eines [object] zurückgibt; [StringBuilder.Append(object)] fügt ihn über seine Methode [ToString] hinzu. Für die Spalte [price] vom SQL-Typ [DECIMAL] ist das Objekt ein [decimal], das mit zwei Nachkommastellen und einem Dezimalpunkt angezeigt wird.

10.3.8. Ausführen eines Änderungsbefehls: INSERT, UPDATE, DELETE

Der Code der Methode [ExecuteUpdate] lautet wie folgt:

// Ausführung einer update-Anweisung (insert, update, delete)
static void ExecuteUpdate(string connectionString, string query)
{
    try
    {
        using var connection = new MySqlConnection(connectionString);
        // Öffnen der Verbindung
        connection.Open();
        // Ausführung der SQL-Anweisung
        using var command = new MySqlCommand(query, connection);
        int nbRows = command.ExecuteNonQuery();
        // Anzeige des Ergebnisses
        Console.WriteLine($"{nbRows} row(s) modified");
    }
    catch (DbException ex)
    {
        Console.WriteLine($"Database access error ({ex.Message})");
    }
}

Das Ausführen eines Änderungsbefehls unterscheidet sich vom Ausführen eines Befehls [select] nur durch die verwendete Methode des Befehls: [ExecuteReader] für [select], [ExecuteNonQuery] für [update], [insert], [delete]:

  • Zeile 78: Der Befehl [update], [insert] oder [delete] wird von der Methode [ExecuteNonQuery] des Befehls ausgeführt. Bei Erfolg gibt diese Methode die Anzahl der aktualisierten ([update]), eingefügten ([insert]) oder gelöschten ([delete]) Zeilen zurück;
  • Zeile 80: Diese Anzahl von Zeilen wird angezeigt.

Dem Leser wird empfohlen, sich das Ausführungsbeispiel im Abschnitt „Die vier grundlegenden Befehle der Sprache SQL“ noch einmal anzusehen.

10.4. Weitere ADO.NET-Konnektoren

10.4.1. Aktuelle Konnektoren

Der Code, den wir untersucht haben, ist konnektorspezifisch: Er verwendet Klassen aus dem Namespace [MySqlConnector]. Der Kurs von 2008 baute dasselbe Programm anschließend mit den Konnektoren für SQL Server Compact, für SQL Server 2005 und für MySQL 5 sowie mit den ODBC- und OLE-DB-Treibern auf, um zu zeigen, dass sich nur die Klassennamen ändern. Diese Feststellung gilt weiterhin, und wir werden die Übung nicht wiederholen, da dafür zusätzliche DBMS installiert werden müssten. Hier sind die wichtigsten heutigen ADO.NET-Konnektoren, die alle als NuGet-Pakete vertrieben werden:

DBMS
NuGet-Paket
Verbindung
Hinweise
MySQL, MariaDB
[MySqlConnector]
[MySqlConnection]
Unabhängiger Konnektor, der in diesem Kurs verwendet wird
MySQL
[MySql.Data]
[MySqlConnection]
der Konnektor von Oracle; Namespace [MySql.Data.MySqlClient]
SQL Server, Azure SQL
[Microsoft.Data.SqlClient]
[SqlConnection]
Ersetzt das alte [System.Data.SqlClient] des .NET Framework, das 2008 verwendet wurde
PostgreSQL
[Npgsql]
[NpgsqlConnection]
 
SQLite
[Microsoft.Data.Sqlite]
[SqliteConnection]
Eine Datenbank in einer Datei, ohne Server: der Nachfolger von SQL Server Compact für kleine Anwendungen
Oracle
[Oracle.ManagedDataAccess.Core]
[OracleConnection]
Namespace [Oracle.ManagedDataAccess.Client]

Sofern nichts anderes angegeben ist, trägt der Namespace der Klassen den Namen des Pakets.

Um von einem DBMS zu einem anderen zu wechseln, ändert man das Paket, den Namespace ([using]), das Klassenpräfix ([MySqlCommand] wird zu [SqlCommand], [NpgsqlCommand] …) und die Verbindungszeichenfolge; der Rest des Codes bleibt derselbe.

ODBC („Open DataBase Connectivity“) und OLE DB („Object Linking and Embedding DataBase“) waren 2008 eine Möglichkeit, mit einem einzigen Code auf jedes DBMS zuzugreifen, das über einen solchen Treiber verfügt, wenn auch auf Kosten einer geringeren Leistung. Es sind Technologien, die älter als .NET sind und bei OLE DB auf Windows beschränkt bleiben. Sie sind weiterhin verfügbar (Pakete [System.Data.Odbc] und [System.Data.OleDb] ), um auf Datenquellen zuzugreifen, die keinen ADO.NET-Konnektor haben, werden aber für neue Anwendungen nicht mehr gewählt: Alle gängigen DBMS haben ihren eigenen ADO.NET-Konnektor, und für die DBMS-Unabhängigkeit sorgt der unten beschriebene generische Konnektor.

10.4.2. Der generische Konnektor

Die verwendete Architektur sieht wie folgt aus:

Image

Der „generische Konnektor“ stellt den Anwendungen, die ihn nutzen, eine Standardschnittstelle zur Verfügung, ohne Leistung einzubüßen, da er auf den DBMS-spezifischen Konnektoren aufsetzt. Die Anwendung verwendet nur die abstrakten Klassen von [System.Data.Common] ([DbConnection], [DbCommand], [DbDataReader], [DbParameter] …). Die konkreten Objekte werden von einer Factory erzeugt, die der Konnektor bereitstellt: einer von [DbProviderFactory] abgeleiteten Klasse, deren Methoden [CreateConnection], [CreateCommand], [CreateParameter] … Objekte des Konnektors in Form abstrakter Klassen zurückgeben. Jeder Konnektor hat seine eigene Factory, die über seine statische Eigenschaft [Instance] zugänglich ist: [MySqlConnectorFactory.Instance] für MySqlConnector, [SqlClientFactory.Instance] für SQL Server und [NpgsqlFactory.Instance] für PostgreSQL.

Die statische Klasse [DbProviderFactories] verwaltet ein Register von Factories, die jeweils durch einen sogenannten Invariant-Namen identifiziert werden. Beim .NET Framework wurde dieses Register durch die Installation der Konnektoren in der Konfigurationsdatei des Rechners gefüllt, und der Kurs von 2008 zeigte die sechs Konnektoren an, die auf dem Rechner des Autors installiert waren. Bei .NET ist das Register beim Start leer: Die Anwendung selbst fügt die Konnektoren, die sie unterstützt, mit der Methode [DbProviderFactories.RegisterFactory] hinzu. Der Konnektor kann dann über die Konfiguration anhand seines Invariant-Namens ausgewählt werden. Genau das tut das Programm [adonet-03.cs]; es erhält den Invariant-Namen des zu verwendenden Konnektors als Argument:

#:package MySqlConnector@2.4.0

// der „generische Connector“: der Code verwendet nur die abstrakten Klassen von System.Data.Common
// der tatsächlich verwendete Connector wird über seinen invarianten Namen (1. Argument) bestimmt
using System.Data;
using System.Data.Common;
using MySqlConnector;

// der invariante Name des Connectors und die Verbindungszeichenfolge
string connectorName = args.Length > 0 ? args[0] : "MySqlConnector";
const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

// Registrierung der der Anwendung bekannten Connectors unter ihrem invarianten Namen
DbProviderFactories.RegisterFactory("MySqlConnector", MySqlConnectorFactory.Instance);

// die Liste der registrierten Connectors
Console.WriteLine("Registered connectors:");
DataTable connectors = DbProviderFactories.GetFactoryClasses();
foreach (DataRow row in connectors.Rows)
{
    Console.WriteLine($"  {row["InvariantName"]} : {row["AssemblyQualifiedName"]}");
}

// wir holen die Factory des angeforderten Connectors
DbProviderFactory factory;
try
{
    factory = DbProviderFactories.GetFactory(connectorName);
}
catch (ArgumentException ex)
{
    Console.WriteLine($"Configuration error: {ex.Message}");
    return;
}
Console.WriteLine($"Connector [{connectorName}]: factory of type {factory.GetType().Name}");

// der übrige Code kennt nur die abstrakten Klassen DbConnection, DbCommand, DbDataReader
try
{
    using DbConnection connection = factory.CreateConnection()!;
    connection.ConnectionString = ConnectionString;
    connection.Open();
    using DbCommand command = factory.CreateCommand()!;
    command.Connection = connection;
    command.CommandText = "select id, name, price from articles where price > @price";
    // ein von der Factory erzeugter Parameter
    DbParameter parameter = factory.CreateParameter()!;
    parameter.ParameterName = "@price";
    parameter.Value = 200;
    command.Parameters.Add(parameter);
    using DbDataReader reader = command.ExecuteReader();
    Console.WriteLine($"Objects created: {connection.GetType().Name}, {command.GetType().Name}, {reader.GetType().Name}");
    while (reader.Read())
    {
        Console.WriteLine($"  {reader.GetInt32(0)} {reader.GetString(1)} {reader.GetDecimal(2)}");
    }
}
catch (DbException ex)
{
    Console.WriteLine($"Database access error ({ex.Message})");
}

Ergebnisse mit dem Konnektor [MySqlConnector] (Standardwert):

1
2
3
4
5
6
7
8
> dotnet run adonet-03.cs
Registered connectors:
  MySqlConnector : MySqlConnector.MySqlConnectorFactory, MySqlConnector, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92
Connector [MySqlConnector]: factory of type MySqlConnectorFactory
Objects created: MySqlConnection, MySqlCommand, MySqlDataReader
  1 bike 500.00
  3 bow 600.00
  5 diving suit 300.00

Ergebnisse mit einem nicht registrierten Konnektor:

1
2
3
4
> dotnet run adonet-03.cs -- Npgsql
Registered connectors:
  MySqlConnector : MySqlConnector.MySqlConnectorFactory, MySqlConnector, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d33d3e53aa5f8c92
Configuration error: The specified invariant name 'Npgsql' wasn't found in the list of registered .NET Data Providers.
  • Zeile 10: der Invariant-Name des Konnektors, als Argument übergeben; der Standardwert ist [MySqlConnector];
  • Zeile 14: die Registrierung der Factory von MySqlConnector im Register unter dem Invariant-Namen [MySqlConnector]. Dies ist die einzige Zeile des Programms, die eine Klasse eines Konnektors benennt. Eine Anwendung, die zusätzlich SQL Server unterstützt, würde die Zeile [DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)] (und den Verweis auf das Paket [Microsoft.Data.SqlClient]) hinzufügen;
  • Zeilen 18–21: [GetFactoryClasses] gibt die Liste der registrierten Konnektoren als Tabelle im Speicher zurück, als [DataTable]-Objekt im Offline-Modus: Seine Zeilen [DataRow] werden über den Spaltennamen indiziert. Die Spalte [AssemblyQualifiedName] enthält den vollständigen Namen der Factory-Klasse; beachten Sie, dass die Version der Assembly MySqlConnector 2.0.0.0 ist: Nur die Paketversion (2.4.0) ändert sich von einem Minor-Update zum nächsten;
  • Zeile 28: Die Factory des Konnektors wird anhand ihres Invariant-Namens abgerufen. Wurde der Name nicht registriert, löst [GetFactory] eine [ArgumentException] aus (Zeile 4 der zweiten Ergebnisreihe);
  • Zeilen 40–42: Die Verbindung wird von der Factory erstellt, mit der Verbindungszeichenfolge konfiguriert und dann geöffnet. Die Methoden [CreateXxx] geben einen Nullable-Typ zurück ([DbConnection?]): Eine Factory kann bestimmte Objekte möglicherweise nicht erstellen. Der Operator [!] teilt dem Compiler mit, dass das Ergebnis nach unserem Wissen nicht [null] ist;
  • Zeilen 43–45: Der Befehl wird auf dieselbe Weise erstellt und erhält dann seine Verbindung und seinen Text. Wir hätten auch [connection.CreateCommand()] schreiben können;
  • Zeilen 47–50: Die Abfrage enthält einen Parameter, [@price]. Parametrisierte Abfragen werden im folgenden Abschnitt behandelt; beachten Sie vorerst, dass auch der Parameter von der Factory erstellt wird;
  • Zeile 52: Die Ergebnisse zeigen, dass die über die abstrakten Klassen verwendeten Objekte Objekte des Konnektors MySqlConnector sind ([MySqlConnection], [MySqlCommand], [MySqlDataReader]);
  • Zeile 55: Da die Spaltentypen bekannt sind, werden die Methoden [GetInt32], [GetString] und [GetDecimal] verwendet, die direkt einen Wert des erwarteten Typs zurückgeben.

Ab .NET 7 stellt ADO.NET außerdem die abstrakte Klasse [DbDataSource] bereit, die eine Factory und eine Verbindungszeichenfolge vereint: [factory.CreateDataSource(connectionString)] gibt eine Datenquelle zurück, deren Methode [OpenConnection] eine geöffnete Verbindung zurückgibt. Wir werden dies mit asynchronen Methoden verwenden.

10.4.3. Welchen Konnektor sollte man wählen?

Wir haben zwei Möglichkeiten kennengelernt, einen ADO.NET-Konnektor zu verwenden:

  • die Klassen des Konnektors direkt verwenden: Die Schicht [dao] hängt vom Konnektor ab. Ein Wechsel des DBMS erfordert eine Änderung der Schicht [dao];
  • den generischen Konnektor verwenden: Die Schicht [dao] kennt nur die abstrakten Klassen und den Invariant-Namen des Konnektors, der aus einer Konfigurationsdatei gelesen werden kann.

Der generische Konnektor scheint also die ideale Lösung zu sein. In der Praxis kann er jedoch nicht alle Besonderheiten eines DBMS verbergen:

  • Das SQL selbst unterscheidet sich von einem DBMS zum anderen, sobald man über die Grundbefehle hinausgeht: Erzeugung von Primärschlüsseln, Begrenzung der Anzahl der gelieferten Zeilen ([limit 10] bei MySQL, [top 10] oder [offset … fetch] bei SQL Server), Spaltentypen, Datumsfunktionen usw.;
  • Die Syntax der Parameter parametrisierter Abfragen variiert: [@name] bei SQL Server, MySQL und SQLite, aber [?] (Positionsparameter, ohne Namen) bei den ODBC- und OLE-DB-Treibern und beim MySQL-Konnektor von 2008, und [$1], [$2] … im nativen SQL von PostgreSQL (Npgsql akzeptiert auch [@name]). Bei Positionsparametern ist die Reihenfolge wichtig, in der die Parameter der Sammlung [Parameters] hinzugefügt werden; bei benannten Parametern spielt die Reihenfolge keine Rolle;
  • Schließlich sind die Ausnahmen [DbException], deren Fehlercodes vom DBMS abhängen.

Der Kurs von 2008 nannte Frameworks, die diese Einschränkungen überwinden: Spring.NET, iBatis.NET, NHibernate und LINQ to SQL. Spring.NET und iBatis.NET werden nicht mehr gepflegt; LINQ to SQL wird nicht mehr weiterentwickelt und existiert im aktuellen .NET nicht; NHibernate gibt es weiterhin. Heute werden vor allem die folgenden verwendet:

  • Entity Framework Core, der ORM von Microsoft, eingeführt in Kapitel 11: Die Schicht [dao] arbeitet mit C#-Objekten und schreibt ihre Abfragen in LINQ; EF Core erzeugt das SQL, das für das jeweilige DBMS spezifisch ist;
  • Dapper, ein „Micro-ORM“, ergänzt die ADO.NET-Verbindungen um Erweiterungsmethoden, die die Zeilen eines [select] direkt in C#-Objekte umwandeln. Das SQL schreibt man weiterhin selbst, die Schleifen über den Reader jedoch nicht mehr.

Alle diese Werkzeuge beruhen auf ADO.NET-Konnektoren: Das Verständnis von ADO.NET bleibt nützlich, um zu verstehen, was sie tun, und um Probleme mit ihnen zu beheben.

10.5. Parametrisierte Abfragen

10.5.1. Das Konzept

Eine parametrisierte Abfrage ist ein SQL-Befehl, in dem bestimmte Werte durch Parameter ersetzt sind, deren Werte getrennt vom Abfragetext angegeben werden. Das Programm [adonet-04.cs] setzt dies um: Es leert die Tabelle [articles], fügt mit einer parametrisierten Abfrage fünf Datensätze ein und liest sie dann mit einer weiteren parametrisierten Abfrage wieder aus:

#:package MySqlConnector@2.4.0

// parametrisierte Abfragen
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
Console.WriteLine($"Database connection string: [{ConnectionString}]");

// Erstellung eines Arrays mit 5 Artikeln
var articles = new Article[5];
for (int i = 1; i <= articles.Length; i++)
{
    articles[i - 1] = new Article(0, $"article{i}", i * 100, i * 10, i);
}

try
{
    // wir löschen die vorhandenen Artikel
    Console.WriteLine($"{ExecuteUpdate(ConnectionString, "delete from articles")} row(s) deleted");
    // wir fügen das Artikel-Array ein
    InsertArticles(ConnectionString, articles);
    // wir zeigen die Artikel der Tabelle an
    DisplayArticles(GetArticles(ConnectionString, "%"));
    // die Artikel, deren Name auf 3 endet
    Console.WriteLine("Articles whose name ends with 3:");
    DisplayArticles(GetArticles(ConnectionString, "%3"));
}
catch (MySqlException ex)
{
    Console.WriteLine($"Database access error ({ex.Message})");
}

// Einfügen eines Artikel-Arrays mit einer parametrisierten Abfrage
static void InsertArticles(string connectionString, Article[] articles)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    // die parametrisierte Abfrage: @name, @price, @cs, @ms sind ihre Parameter
    const string Query = "insert into articles(name, price, currentstock, minimumstock) values(@name, @price, @cs, @ms)";
    using var command = new MySqlCommand(Query, connection);
    // Deklaration der Parameter: Name und SQL-Typ
    command.Parameters.Add("@name", MySqlDbType.VarChar, 30);
    command.Parameters.Add("@price", MySqlDbType.Decimal);
    command.Parameters.Add("@cs", MySqlDbType.Int32);
    command.Parameters.Add("@ms", MySqlDbType.Int32);
    // Vorbereitung (Kompilierung) der Abfrage durch das DBMS
    command.Prepare();
    // Einfügen der Zeilen: nur der Wert der Parameter ändert sich
    foreach (Article article in articles)
    {
        command.Parameters["@name"].Value = article.Name;
        command.Parameters["@price"].Value = article.Price;
        command.Parameters["@cs"].Value = article.CurrentStock;
        command.Parameters["@ms"].Value = article.MinimumStock;
        command.ExecuteNonQuery();
    }
    Console.WriteLine($"{articles.Length} article(s) inserted");
}

// die Artikel, deren Name zum [like]-Muster passt
static List<Article> GetArticles(string connectionString, string pattern)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    using var command = new MySqlCommand(
        "select id, name, price, currentstock, minimumstock from articles where name like @pattern order by id", connection);
    // Deklaration des Parameters und seines Werts in einer einzigen Anweisung
    command.Parameters.AddWithValue("@pattern", pattern);
    using MySqlDataReader reader = command.ExecuteReader();
    // jede Zeile des Ergebnisses wird zu einem Article-Objekt
    List<Article> list = [];
    while (reader.Read())
    {
        list.Add(new Article(reader.GetInt32(0), reader.GetString(1), reader.GetDecimal(2),
            reader.GetInt32("currentstock"), reader.GetInt32("minimumstock")));
    }
    return list;
}

// Ausführung einer update-Anweisung
static int ExecuteUpdate(string connectionString, string query)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    using var command = new MySqlCommand(query, connection);
    return command.ExecuteNonQuery();
}

// Anzeige einer Artikelliste
static void DisplayArticles(List<Article> articles)
{
    foreach (Article article in articles)
    {
        Console.WriteLine(article);
    }
}

// ein Artikel: eine Zeile der Tabelle [articles]
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);

Ergebnisse:

> dotnet run adonet-04.cs
Database connection string: [Server=localhost;Port=3306;Database=dbarticles;User=root;Password=]
6 row(s) deleted
5 article(s) inserted
Article { Id = 7, Name = article1, Price = 100.00, CurrentStock = 10, MinimumStock = 1 }
Article { Id = 8, Name = article2, Price = 200.00, CurrentStock = 20, MinimumStock = 2 }
Article { Id = 9, Name = article3, Price = 300.00, CurrentStock = 30, MinimumStock = 3 }
Article { Id = 10, Name = article4, Price = 400.00, CurrentStock = 40, MinimumStock = 4 }
Article { Id = 11, Name = article5, Price = 500.00, CurrentStock = 50, MinimumStock = 5 }
Articles whose name ends with 3:
Article { Id = 9, Name = article3, Price = 300.00, CurrentStock = 30, MinimumStock = 3 }
  • Zeile 99: Ein Objekt [Article] stellt eine Zeile der Tabelle [articles] dar. Es ist ein [record] (C# 9, Kapitel 4): eine Klasse, deren Primärkonstruktor die Eigenschaften definiert, mit einer Methode [ToString], die diese Eigenschaften anzeigt (Zeilen 5–9); der Kurs von 2008 behandelte eine Klasse mit 25 Zeilen. In einer Einzeldatei-Anwendung werden Typen nach den Top-Level-Anweisungen deklariert;
  • Zeilen 10–13: ein Array mit fünf Artikeln;
  • Zeile 19: Alle Artikel werden aus der Tabelle gelöscht. Diesmal gibt die Methode [ExecuteUpdate] die Anzahl der gelöschten Zeilen zurück;
  • Zeile 34: die Methode zum Einfügen eines Arrays von Artikeln:
    • Zeile 39: die Abfrage zum Einfügen eines Artikels. Sie hat vier Parameter: [@name], [@price], [@cs] (aktueller Bestand) und [@ms] (Mindestbestand). Bei MySqlConnector ist ein Parameter ein Name, dem das Zeichen [@] vorangestellt ist; der Name kann beliebig sein;
    • Zeilen 42–45: Jeder der vier Parameter wird deklariert und der Parameterliste des Befehls hinzugefügt. Die Methode [Parameters.Add(string name, MySqlDbType type)] gibt den Namen des Parameters an, der einer der Namen aus der Abfrage sein muss, sowie den SQL-Typ der betreffenden Spalte; eine dritte Form, [Add(name, type, size)], legt zusätzlich die Breite der Spalte fest, was bei Zeichenfolgen nützlich ist ([VarChar]);
    • Zeile 47: Die parametrisierte Abfrage wird „vorbereitet“, das heißt, sie wird vom DBMS kompiliert. Dieser Vorgang ist nicht erforderlich; er verbessert die Leistung. Wenn ein DBMS einen SQL-Befehl ausführt, nimmt es vor der Ausführung eine Analyse und Optimierung vor (es erstellt einen „Ausführungsplan“). Eine parametrisierte Abfrage ist dafür gedacht, mehrfach mit unterschiedlichen Parametern ausgeführt zu werden, wobei ihr Text unverändert bleibt: Diese Vorbereitungsarbeit muss nur einmal erledigt werden. Nach der Vorbereitung wird die Abfrage wiederholt mit neuen Parameterwerten, aber demselben Ausführungsplan ausgeführt;
    • Zeilen 49–55: Die Artikel werden einzeln eingefügt. Jedem der vier Parameter wird über seine Eigenschaft [Value] ein Wert zugewiesen, und dann wird die nun vollständige Abfrage auf die übliche Weise ausgeführt;
  • Zeile 61: die Methode, die die Artikel zurückgibt, deren Namen einem SQL-Muster [like] entsprechen (das Zeichen [%] steht für eine beliebige Zeichenfolge: [%3] steht für Namen, die auf 3 enden):
    • Zeile 68: [AddWithValue(name, value)] deklariert den Parameter und weist ihm in einer einzigen Anweisung einen Wert zu. Der SQL-Typ des Parameters wird aus dem C#-Typ des Werts abgeleitet, hier [string]. Dies ist die gängigste Form, wenn die Abfrage nur einmal ausgeführt wird;
    • Zeilen 72–75: Jede Zeile des Ergebnisses wird zu einem Objekt [Article]. Die Spaltentypen sind bekannt: Es werden die Methoden [GetXxx] verwendet. Diese Methoden akzeptieren entweder die Spaltennummer oder, bei MySqlConnector, den Spaltennamen. Bei der abstrakten Klasse [DbDataReader] wird der Name von gleichnamigen Erweiterungsmethoden akzeptiert (Klasse [DataReaderExtensions], Namespace [System.Data]), die äquivalent sind zu [reader.GetInt32(reader.GetOrdinal("currentstock"))].

Die SQL-Typen, die mit MySqlConnector verwendet werden, sind die Werte der Enumeration [MySqlDbType]. Hier sind die gängigsten und die entsprechenden C#-Typen:

Typ MySqlDbType
C#-Typ
Spaltentyp in MySQL
[Int32]
[int]
[INT]
[Int64]
[long]
[BIGINT]
[Decimal]
[decimal]
[DECIMAL(p,d)]
[Double]
[double]
[DOUBLE]
[VarChar]
[string]
[VARCHAR(n)], Zeichenfolge variabler Länge
[DateTime]
[DateTime]
[DATETIME]
[Date]
[DateOnly] oder [DateTime]
[DATE]
[Bool]
[bool]
[BOOL] (Synonym für [TINYINT(1)])

Die abstrakten Klassen verwenden dagegen die allen Konnektoren gemeinsame Enumeration [DbType] ([DbType.Int32], [DbType.String], [DbType.Decimal] …) über die Eigenschaft [DbParameter.DbType].

10.5.2. Die SQL-Injection

Die Vorbereitung ist nicht der einzige Vorteil parametrisierter Abfragen, und auch nicht der wichtigste. Kehren wir zur Einfügeabfrage zurück. Man könnte versucht sein, ihren Text programmgesteuert zusammenzusetzen, indem man die Werte einfügt:

string query = "insert into articles(name, price, currentstock, minimumstock) values('" + name + "', " + price + ", " + sa + ", " + sm + ")";

Wenn (name, price, sa, sm) gleich ("article1", 100, 10, 1) ist, lautet die Abfrage:

insert into articles(name, price, currentstock, minimumstock) values('article1', 100, 10, 1)

Lautet der Name jedoch „l'article1“, wird daraus:

insert into articles(name, price, currentstock, minimumstock) values('l'article1', 100, 10, 1)

und sie ist wegen des Apostrophs im Namen syntaktisch falsch. Es kommt noch schlimmer: Stammt der Name aus einer Benutzereingabe, kann der Benutzer einen Text eingeben, der die Bedeutung der Abfrage verändert. Dies nennt man eine SQL-Injection, eine der am weitesten verbreiteten und schwerwiegendsten Sicherheitslücken in datenbankgestützten Anwendungen. Das Programm [adonet-05.cs] zeigt dies bei der Suche nach einem Artikel anhand seines Namens:

#:package MySqlConnector@2.4.0

// SQL-Injection: warum parametrisierte Abfragen verwendet werden müssen
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

// Artikelnamen, wie ein Benutzer sie eingeben könnte
string[] names = ["bike", "diver's oxygen tanks", "x' or '1'='1"];

Console.WriteLine("1 - query built by concatenation");
foreach (string name in names)
{
    SearchByConcatenation(name);
}
Console.WriteLine("\n2 - parameterized query");
foreach (string name in names)
{
    SearchWithParameter(name);
}

// Suche eines Artikels anhand seines Namens: der Name wird in den Text der Abfrage eingefügt
static void SearchByConcatenation(string name)
{
    string query = "select id, name, price from articles where name = '" + name + "'";
    Console.WriteLine($"[{name}] -> {query}");
    try
    {
        using var connection = new MySqlConnection(ConnectionString);
        connection.Open();
        using var command = new MySqlCommand(query, connection);
        Display(command);
    }
    catch (MySqlException ex)
    {
        Console.WriteLine($"   error: {ex.Message}");
    }
}

// Suche eines Artikels anhand seines Namens: der Name ist der Wert eines Parameters
static void SearchWithParameter(string name)
{
    Console.WriteLine($"[{name}]");
    try
    {
        using var connection = new MySqlConnection(ConnectionString);
        connection.Open();
        using var command = new MySqlCommand("select id, name, price from articles where name = @name", connection);
        command.Parameters.AddWithValue("@name", name);
        Display(command);
    }
    catch (MySqlException ex)
    {
        Console.WriteLine($"   error: {ex.Message}");
    }
}

// Anzeige des Ergebnisses eines select
static void Display(MySqlCommand command)
{
    using MySqlDataReader reader = command.ExecuteReader();
    int nbRows = 0;
    while (reader.Read())
    {
        Console.WriteLine($"   {reader.GetInt32(0)} {reader.GetString(1)} {reader.GetDecimal(2)}");
        nbRows++;
    }
    Console.WriteLine($"   {nbRows} row(s)");
}

Ergebnisse:

> dotnet run adonet-05.cs
1 - query built by concatenation
[bike] -> select id, name, price from articles where name = 'bike'
   1 bike 500.00
   1 row(s)
[diver's oxygen tanks] -> select id, name, price from articles where name = 'diver's oxygen tanks'
   error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's oxygen tanks'' at line 1
[x' or '1'='1] -> select id, name, price from articles where name = 'x' or '1'='1'
   1 bike 500.00
   2 pump 10.00
   3 bow 600.00
   4 arrows - set of 6 100.00
   5 diving suit 300.00
   6 diver's oxygen tanks 120.00
   6 row(s)

2 - parameterized query
[bike]
   1 bike 500.00
   1 row(s)
[diver's oxygen tanks]
   6 diver's oxygen tanks 120.00
   1 row(s)
[x' or '1'='1]
   0 row(s)
  • Zeile 9: drei Namen, die ein Benutzer eingeben könnte: ein gewöhnlicher Name, ein Name mit einem Apostroph und ein bösartiger Text;
  • Zeile 25: Die erste Suchmethode fügt den Namen in den Abfragetext ein:
    • Zeile 3: Bei einem gewöhnlichen Namen funktioniert alles einwandfrei;
    • Zeile 6: Der Apostroph im Namen beendet die SQL-Zeichenfolge vorzeitig; die Abfrage ist ungültig (Zeile 7). Ein Artikel, dessen Name einen Apostroph enthält, kann nicht gefunden werden;
    • Zeile 8: Der Benutzer hat einen Text mit Apostrophen eingegeben, die so platziert sind, dass sich eine gültige Abfrage ergibt, für die die Bedingung [name = 'x' or '1'='1'] aber für alle Zeilen wahr ist: Die Abfrage gibt die gesamte Tabelle zurück (Zeile 15). Würde die Abfrage einen Benutzernamen und ein Passwort prüfen, könnte sich ein Benutzer mit demselben Verfahren anmelden, ohne das Passwort zu kennen. Bei einem Konnektor, der mehrere SQL-Befehle in einer einzigen Anweisung akzeptiert (wie es bei MySqlConnector der Fall ist), könnte der Benutzer nach einem Semikolon sogar einen Befehl seiner Wahl hinzufügen, zum Beispiel ein [delete];
  • Zeile 49: Die zweite Methode verwendet eine parametrisierte Abfrage. Der Wert des Parameters wird niemals als SQL interpretiert, sondern ist unabhängig von seinem Inhalt einfach nur ein Datum. Der Name mit Apostroph wird gefunden (Zeile 22), und der bösartige Text ist schlicht der Name eines Artikels, der nicht existiert (Zeile 25).

Man könnte die Apostrophe „entschärfen“, indem man sie verdoppelt, doch die Regeln hängen vom DBMS ab, und man übersieht leicht einen Fall. Die Regel ist einfach: Vom Benutzer (oder aus einer beliebigen externen Quelle) gelieferte Werte dürfen niemals mit dem Text einer SQL-Anfrage verkettet werden – es müssen immer Parameter verwendet werden. Das interaktive Programm [adonet-01.cs] hält sich schon seiner Natur nach nicht an diese Regel: Es führt die vom Benutzer eingegebenen SQL-Befehle aus. Es ist ein Administrationswerkzeug, kein Beispiel, dem man folgen sollte.

10.6. Transaktionen

10.6.1. Allgemeines

Eine Transaktion ist eine Folge von SQL-Befehlen, die „atomar“ ausgeführt werden:

  • entweder gelingen alle Operationen;
  • oder eine von ihnen schlägt fehl, und dann werden alle vorhergehenden Operationen rückgängig gemacht.

Am Ende werden entweder alle Operationen einer Transaktion erfolgreich angewendet oder keine. Das klassische Beispiel ist eine Banküberweisung: Die Abbuchung von einem Konto und die Gutschrift auf einem anderen müssen beide ausgeführt werden oder keine von beiden. Steuert die Anwendung die Transaktion selbst, bestätigt sie sie mit einem Befehl [COMMIT] oder macht sie mit einem Befehl [ROLLBACK] rückgängig.

In unseren bisherigen Beispielen haben wir keine Transaktion verwendet. Und doch gab es Transaktionen, denn in einem DBMS wird ein SQL-Befehl immer innerhalb einer Transaktion ausgeführt. Startet die Anwendung nicht selbst eine explizite Transaktion, verwendet das DBMS eine implizite Transaktion. Dann gibt es zwei Fälle:

  • Jeder SQL-Befehl ist Teil einer Transaktion, die das DBMS vor dem Befehl öffnet und danach bestätigt. Man spricht vom Autocommit-Modus. Er verhält sich so, als würde die Anwendung für jeden SQL-Befehl eine Transaktion erstellen;
  • Das DBMS befindet sich nicht im Autocommit-Modus: Es startet mit dem ersten SQL-Befehl, der außerhalb einer Transaktion abgesetzt wird, eine implizite Transaktion und überlässt es der Anwendung, sie abzuschließen. Alle folgenden SQL-Befehle sind Teil dieser impliziten Transaktion, die durch verschiedene Ereignisse (Schließen der Verbindung, Start einer neuen Transaktion usw.) nach DBMS-spezifischen Regeln beendet werden kann. Dieser Modus sollte vermieden werden.

Der Standardmodus wird durch die Konfiguration des DBMS bestimmt. MySQL arbeitet mit der standardmäßig verwendeten Speicher-Engine InnoDB im Autocommit-Modus.

Befehle verschiedener Benutzer werden gleichzeitig in Transaktionen ausgeführt, die parallel laufen. Operationen, die eine Transaktion durchführt, können die einer anderen beeinflussen. Es gibt vier Isolationsstufen zwischen den Transaktionen verschiedener Benutzer:

  • Read Uncommitted;
  • Read Committed;
  • Repeatable Read;
  • Serializable.

Read Uncommitted

Diese Isolationsstufe ist auch als „Dirty Read“ bekannt. Hier ein Beispiel dafür, was in diesem Modus passieren kann:

  • Ein Benutzer namens U1 startet eine Transaktion auf der Tabelle T;
  • ein Benutzer namens U2 startet eine Transaktion auf derselben Tabelle T;
  • Benutzer U1 ändert Zeilen der Tabelle T, hat die Änderungen aber noch nicht bestätigt;
  • Benutzer U2 „sieht“ diese Änderungen und trifft Entscheidungen auf der Grundlage dessen, was er sieht;
  • Benutzer U1 macht seine Transaktion mit [ROLLBACK] rückgängig.

In Schritt 4 hat Benutzer U2 eine Entscheidung auf der Grundlage von Daten getroffen, die sich später als falsch herausstellten.

Read Committed

Diese Isolationsstufe vermeidet die vorherige Falle: In Schritt 4 „sieht“ Benutzer U2 die Änderungen, die Benutzer U1 an der Tabelle T vorgenommen hat, nicht. Er sieht sie erst, nachdem U1 seine Transaktion mit einem [COMMIT] bestätigt hat. In diesem Modus kann jedoch die folgende Situation eintreten:

  • Ein Benutzer U1 startet eine Transaktion auf der Tabelle T;
  • ein Benutzer U2 startet eine Transaktion auf derselben Tabelle T;
  • Benutzer U2 führt ein [select] aus, um den Durchschnitt der Spalte C für die Zeilen von T zu berechnen, die eine bestimmte Bedingung erfüllen;
  • Benutzer U1 ändert ([update]) bestimmte Werte der Spalte C von T und bestätigt die Änderungen ([commit]);
  • Benutzer U2 wiederholt dasselbe [select] wie in Schritt 3. Er stellt fest, dass sich der Durchschnitt der Spalte C aufgrund der von U1 vorgenommenen Änderungen geändert hat.

Benutzer U2 sieht nun nur noch die von U1 bestätigten Änderungen, doch obwohl er sich weiterhin in derselben Transaktion befindet, liefern zwei identische Operationen unterschiedliche Ergebnisse. Diese Situation nennt man „Non-repeatable Read“ (nicht wiederholbares Lesen). Sie ist für jeden problematisch, der eine konsistente Sicht auf die Tabelle T benötigt.

Repeatable Read

Bei dieser Isolationsstufe ist garantiert, dass ein Benutzer bei Lesezugriffen auf die Datenbank dieselben Ergebnisse erhält, solange er sich in derselben Transaktion befindet. Er arbeitet mit einem „Schnappschuss“ der Daten, der die Änderungen anderer Transaktionen nicht widerspiegelt, auch wenn diese bestätigt wurden. Er sieht diese Änderungen erst, nachdem er seine eigene Transaktion beendet hat.

Diese Isolationsstufe ist in der Theorie noch nicht perfekt: Eine andere Transaktion kann Zeilen hinzufügen ([insert]), die die Bedingung aus Schritt 3 erfüllen, was das Ergebnis eines späteren Lesezugriffs verändern würde. Diese Zeilen nennt man „Phantomzeilen“ (Phantom Reads). Dies ist die Standard-Isolationsstufe von MySQL (InnoDB), die bei einfachen Lesezugriffen auch Phantomzeilen verhindert.

Serializable

In diesem Isolationsmodus sind die Transaktionen vollständig voneinander isoliert: Das Ergebnis zweier gleichzeitig ausgeführter Transaktionen ist dasselbe, als wären sie nacheinander ausgeführt worden. Dazu sperrt das DBMS die gelesenen Daten und lässt Transaktionen, die versuchen, sie zu ändern, warten (oder fehlschlagen). Diese Stufe verringert die Parallelität und damit die Leistung.

Die vier Isolationsstufen sind nicht in allen DBMS verfügbar. Die Standard-Isolationsstufe ist in der Regel Read Committed (SQL Server, PostgreSQL, Oracle) oder Repeatable Read (MySQL). Die gewünschte Isolationsstufe einer Transaktion kann ausdrücklich angegeben werden, wenn die Anwendung die Transaktion erstellt.

10.6.2. Die API zur Transaktionsverwaltung

Die Klasse [DbConnection] hat die folgende Methode:

Name
Typ
Rolle
[BeginTransaction]
M
startet eine Transaktion.

Diese Methode hat zwei Signaturen:

  • [DbTransaction BeginTransaction()]: Startet eine Transaktion und gibt das Objekt [DbTransaction] zurück, mit dem sie gesteuert wird;
  • [DbTransaction BeginTransaction(IsolationLevel level)]: legt zusätzlich die gewünschte Isolationsstufe der Transaktion fest. [level] nimmt seine Werte aus der Enumeration [IsolationLevel] (Namespace [System.Data]):
Wert
Bedeutung
[ReadUncommitted]
Die Transaktion kann Daten lesen, die eine andere Transaktion geschrieben, aber noch nicht bestätigt hat. Sollte vermieden werden.
[ReadCommitted]
Die Transaktion kann keine Daten lesen, die eine andere Transaktion geschrieben, aber noch nicht bestätigt hat. Daten, die innerhalb der Transaktion zweimal hintereinander gelesen werden, können sich jedoch ändern, weil eine andere Transaktion sie inzwischen geändert haben kann; eine andere Transaktion kann auch Zeilen hinzugefügt haben (Phantomzeilen).
[RepeatableRead]
Die von der Transaktion gelesenen Daten ändern sich nicht, bis die Transaktion abgeschlossen ist. Theoretisch verhindert dies nicht, dass eine andere Transaktion Zeilen hinzufügt.
[Serializable]
Alles läuft so ab, als würde die Transaktion allein ausgeführt. Dies verringert die Leistung, weil die Transaktionen nicht mehr parallel laufen.
[Snapshot]
Die Transaktion arbeitet mit einer Kopie der Daten, die zu Beginn der Transaktion erstellt wird. Verfügbar bei SQL Server; bei MySQL geschieht dies bei Lesezugriffen bereits auf der Stufe [RepeatableRead].

Sobald die Transaktion gestartet ist, wird sie über das Objekt [DbTransaction] (hier [MySqlTransaction]) gesteuert, von dem wir die folgenden Eigenschaften P und Methoden M verwenden werden:

Name
Typ
Rolle
[Connection]
P
die Verbindung [DbConnection], die die Transaktion trägt.
[Commit]
M
bestätigt die Transaktion: Die Ergebnisse der in der Transaktion abgesetzten SQL-Befehle werden endgültig und für andere Transaktionen sichtbar.
[Rollback]
M
bricht die Transaktion ab: Die Ergebnisse der in der Transaktion abgesetzten SQL-Befehle werden rückgängig gemacht.
[Dispose]
M
gibt die Transaktion frei. Eine Transaktion, die freigegeben wird, ohne bestätigt worden zu sein, wird zurückgerollt.

Die SQL-Befehle der Transaktion müssen auf der Verbindung der Transaktion ausgeführt werden, und zwar mit Befehlen, deren Eigenschaft [Transaction] auf die Transaktion verweist.

10.6.3. Das Beispielprogramm

Das Programm [adonet-06.cs] zeigt die Wirkung einer Transaktion:

#:package MySqlConnector@2.4.0

// Transaktionen
using System.Data;
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";
Console.WriteLine($"Database connection string: [{ConnectionString}]");

// zwei Artikel mit demselben Namen: die Eindeutigkeitsbedingung UNQ_NAME lehnt den zweiten ab
Article[] articles = [new(0, "article", 100, 10, 1), new(0, "article", 200, 20, 2)];

try
{
    // 1 - Einfügen ohne explizite Transaktion
    Console.WriteLine("\nInsertion without a transaction...");
    ExecuteUpdate(ConnectionString, "delete from articles");
    try
    {
        InsertArticlesOutOfTransaction(ConnectionString, articles);
    }
    catch (MySqlException ex)
    {
        Console.WriteLine($"Database access error ({ex.Message})");
    }
    DisplayArticles(ConnectionString);

    // 2 - dasselbe innerhalb einer Transaktion
    Console.WriteLine("\nInsertion inside a transaction...");
    ExecuteUpdate(ConnectionString, "delete from articles");
    InsertArticlesInTransaction(ConnectionString, articles);
    DisplayArticles(ConnectionString);
}
catch (MySqlException ex)
{
    Console.WriteLine($"Database access error ({ex.Message})");
}

// Einfügen eines Artikel-Arrays ohne explizite Transaktion
static void InsertArticlesOutOfTransaction(string connectionString, Article[] articles)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    using MySqlCommand command = CreateInsertCommand(connection);
    foreach (Article article in articles)
    {
        InsertArticle(command, article);
    }
}

// Einfügen eines Artikel-Arrays innerhalb einer Transaktion
static void InsertArticlesInTransaction(string connectionString, Article[] articles)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    // Beginn der Transaktion
    using MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    try
    {
        using MySqlCommand command = CreateInsertCommand(connection);
        // der Befehl muss innerhalb der Transaktion ausgeführt werden
        command.Transaction = transaction;
        foreach (Article article in articles)
        {
            InsertArticle(command, article);
        }
        // alles ist gut gegangen: wir bestätigen die Transaktion (Commit)
        transaction.Commit();
        Console.WriteLine("transaction committed...");
    }
    catch (MySqlException ex)
    {
        // ein Einfügen ist fehlgeschlagen: wir machen die Transaktion rückgängig
        transaction.Rollback();
        Console.WriteLine($"transaction rolled back ({ex.Message})");
    }
}

// der parametrisierte Befehl zum Einfügen eines Artikels
static MySqlCommand CreateInsertCommand(MySqlConnection connection)
{
    var command = new MySqlCommand(
        "insert into articles(name, price, currentstock, minimumstock) values(@name, @price, @cs, @ms)", connection);
    command.Parameters.Add("@name", MySqlDbType.VarChar, 30);
    command.Parameters.Add("@price", MySqlDbType.Decimal);
    command.Parameters.Add("@cs", MySqlDbType.Int32);
    command.Parameters.Add("@ms", MySqlDbType.Int32);
    return command;
}

// Einfügen eines Artikels mit dem parametrisierten Befehl
static void InsertArticle(MySqlCommand command, Article article)
{
    command.Parameters["@name"].Value = article.Name;
    command.Parameters["@price"].Value = article.Price;
    command.Parameters["@cs"].Value = article.CurrentStock;
    command.Parameters["@ms"].Value = article.MinimumStock;
    command.ExecuteNonQuery();
    Console.WriteLine($"article [{article.Name}, {article.Price}] inserted");
}

// Ausführung einer update-Anweisung
static void ExecuteUpdate(string connectionString, string query)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    using var command = new MySqlCommand(query, connection);
    Console.WriteLine($"{command.ExecuteNonQuery()} row(s) modified");
}

// Anzeige des Inhalts der Tabelle
static void DisplayArticles(string connectionString)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    using var command = new MySqlCommand("select id, name, price, currentstock, minimumstock from articles", connection);
    using MySqlDataReader reader = command.ExecuteReader();
    Console.WriteLine("Content of the table [articles]:");
    while (reader.Read())
    {
        Console.WriteLine($"  {reader.GetInt32(0)} {reader.GetString(1)} {reader.GetDecimal(2)} {reader.GetInt32(3)} {reader.GetInt32(4)}");
    }
}

// ein Artikel: eine Zeile der Tabelle [articles]
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);

Ergebnisse:

> dotnet run adonet-06.cs
Database connection string: [Server=localhost;Port=3306;Database=dbarticles;User=root;Password=]

Insertion without a transaction...
6 row(s) modified
article [article, 100] inserted
Database access error (Duplicate entry 'article' for key 'articles.UNQ_NAME')
Content of the table [articles]:
  7 article 100.00 10 1

Insertion inside a transaction...
1 row(s) modified
article [article, 100] inserted
transaction rolled back (Duplicate entry 'article' for key 'articles.UNQ_NAME')
Content of the table [articles]:
  • Zeile 11: ein Array mit zwei Artikeln mit demselben Namen, [article]. Die Eindeutigkeitsbedingung [UNQ_NAME] der Tabelle [articles] verhindert, dass beide gleichzeitig in der Datenbank existieren: Das Einfügen des zweiten löst eine Ausnahme aus, die vom DBMS geworfen und vom Konnektor weitergereicht wird. Um die Wirkung einer Transaktion zu zeigen, werden die beiden Artikel auf zwei Arten eingefügt:
    • zunächst außerhalb jeder expliziten Transaktion. Da sich MySQL im Autocommit-Modus befindet, erfolgt jedes Einfügen in einer impliziten Transaktion. Der erste Datensatz wird eingefügt, der zweite nicht;
    • dann innerhalb einer expliziten Transaktion, die beide Einfügungen umfasst. Da das zweite Einfügen fehlschlägt, wird das erste zurückgerollt: Am Ende wird nichts eingefügt;
  • Zeilen 15–26: Die Tabelle wird geleert, dann werden beide Artikel ohne explizite Transaktion eingefügt. Da wir wissen, dass das zweite Einfügen eine Ausnahme auslöst, wird sie von einem [try] / [catch] abgefangen; schließlich wird die Tabelle angezeigt;
  • Zeilen 28–32: Dieselbe Abfolge wird wiederholt, aber mit einer expliziten Transaktion. Diesmal wird die Ausnahme in der Methode [InsertArticlesInTransaction] behandelt;
  • Zeile 40: die Methode zum Einfügen ohne Transaktion. Sie verwendet zwei Methoden: [CreateInsertCommand] (Zeile 80), die den parametrisierten Einfügebefehl erstellt, und [InsertArticle] (Zeile 92), die den Parametern Werte zuweist und den Befehl ausführt;
  • Zeile 52: die Methode zum Einfügen innerhalb einer Transaktion:
    • Zeile 57: Die Transaktion wird mit der Isolationsstufe [ReadCommitted] erstellt, wie im Kurs von 2008 angegeben. Sie gehört zur Verbindung. Die Anweisung [using] stellt sicher, dass sie zurückgerollt und damit aufgehoben wird, wenn sie nicht bestätigt wurde;
    • Zeile 62: Der Befehl wird in die Transaktion eingebunden. Diese Zeile ist zwingend erforderlich: Bei MySqlConnector löst ein Befehl, der auf einer Verbindung mit laufender Transaktion ausgeführt wird, ohne diese Transaktion anzugeben, eine [InvalidOperationException] aus;
    • Zeilen 63–65: die Einfügungen;
    • Zeile 68: Alles ist gut gegangen: Die Transaktion wird bestätigt, und die Einfügungen werden dauerhaft in die Datenbank übernommen;
    • Zeile 74: Ein Einfügen ist fehlgeschlagen: Die Transaktion wird zurückgerollt. Das ausdrückliche [Rollback] ist nicht unbedingt notwendig, da die Freigabe der nicht bestätigten Transaktion sie ohnehin zurückrollen würde, macht den Code aber klarer.

In den Ergebnissen:

  • Zeile 5: vom Befehl [delete] ausgegeben: Die Tabelle enthielt sechs Zeilen;
  • Zeile 7: die Ausnahme, die durch das zweite Einfügen verursacht wurde. Die Meldung besagt, dass die Bedingung [UNQ_NAME] der Tabelle [articles] nicht erfüllt war;
  • Zeile 9: Die Tabelle ist nicht leer: Der erste Datensatz wurde eingefügt;
  • Zeile 12: vom Befehl [delete] ausgegeben: Die Tabelle enthielt eine Zeile;
  • Zeile 13: Das erste Einfügen war erfolgreich … innerhalb der Transaktion;
  • Zeile 14: die Meldung, die angezeigt wird, wenn die Transaktion fehlschlägt;
  • Zeile 15: Die Tabelle ist leer: Das [Rollback] der Transaktion hat das erste Einfügen rückgängig gemacht.

10.7. Die Methode ExecuteScalar

Unter den Methoden der Klasse [DbCommand] gab es die folgende:

Name
Typ
Rolle
[ExecuteScalar]
M
führt einen SQL-Befehl [select] aus, der nur ein einziges Ergebnis liefert, wie etwa [select count(*) from articles].

Genauer gesagt gibt [ExecuteScalar] die erste Spalte der ersten Zeile des Ergebnisses in Form eines [object?] zurück. Das Programm [adonet-07.cs] zeigt einige Anwendungen davon:

#:package MySqlConnector@2.4.0

// die Methode ExecuteScalar
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

try
{
    // die Einzelwerte, die von select-Anweisungen zurückgegeben werden
    object? averagePrice = ExecuteScalar(ConnectionString, "select avg(price) from articles");
    Console.WriteLine($"Average price of the articles = {averagePrice} (type {averagePrice?.GetType().Name})");
    object? nbArticles = ExecuteScalar(ConnectionString, "select count(id) from articles");
    Console.WriteLine($"Number of articles = {nbArticles} (type {nbArticles?.GetType().Name})");
    object? name = ExecuteScalar(ConnectionString, "select name from articles where price = (select max(price) from articles)");
    Console.WriteLine($"Most expensive article = {name} (type {name?.GetType().Name})");
    object? missing = ExecuteScalar(ConnectionString, "select name from articles where price > 10000");
    Console.WriteLine($"Article over 10000 euros: {missing ?? "null"}");
    object? empty = ExecuteScalar(ConnectionString, "select max(price) from articles where price > 10000");
    Console.WriteLine($"Maximum price beyond 10000 euros: {empty} (type {empty?.GetType().Name})");

    // Umwandlung des Ergebnisses in den erwarteten Typ
    decimal average = Convert.ToDecimal(averagePrice);
    int count = Convert.ToInt32(nbArticles);
    Console.WriteLine($"Rounded average = {Math.Round(average, 2)}, count = {count}");
    // ein direkter Cast schlägt fehl: count liefert einen Int64 (long) und keinen Int32 (int)
    int n = (int)nbArticles!;
    Console.WriteLine($"n = {n}");
}
catch (Exception ex) when (ex is MySqlException or InvalidCastException)
{
    Console.WriteLine($"{ex.GetType().Name} : {ex.Message}");
}

// Ausführung eines select, das einen einzelnen Wert zurückgibt
static object? ExecuteScalar(string connectionString, string query)
{
    using var connection = new MySqlConnection(connectionString);
    connection.Open();
    using var command = new MySqlCommand(query, connection);
    return command.ExecuteScalar();
}

Ergebnisse:

1
2
3
4
5
6
7
8
> dotnet run adonet-07.cs
Average price of the articles = 271.666667 (type Decimal)
Number of articles = 6 (type Int64)
Most expensive article = bow (type String)
Article over 10000 euros: null
Maximum price beyond 10000 euros:  (type DBNull)
Rounded average = 271.67, count = 6
InvalidCastException : Unable to cast object of type 'System.Int64' to type 'System.Int32'.
  • Zeile 36: Die Methode [ExecuteScalar] des Programms öffnet eine Verbindung und führt die Abfrage mit der Methode [ExecuteScalar] des Befehls aus;
  • Zeile 11: der Durchschnittspreis der Artikel. Der C#-Typ des Ergebnisses hängt vom SQL-Typ des Werts ab: Der Durchschnitt einer Spalte [DECIMAL] ist ein [decimal] (Zeile 2);
  • Zeile 13: die Anzahl der Artikel. MySQL gibt eine ganze Zahl vom Typ [BIGINT] zurück, die in C# zu einem [long] ([Int64]) wird (Zeile 3);
  • Zeile 15: der Name des teuersten Artikels, eine Zeichenfolge. Die Abfrage enthält eine Unterabfrage in Klammern;
  • Zeile 17: Liefert die Abfrage keine Zeilen, gibt [ExecuteScalar] [null] zurück (Zeile 5);
  • Zeile 19: Liefert die Abfrage eine Zeile mit dem SQL-Wert [NULL] (dem Maximum einer leeren Menge), gibt [ExecuteScalar] das Objekt [DBNull.Value] zurück, das als leere Zeichenfolge angezeigt wird (Zeile 6). Wir müssen also zwei Fälle unterscheiden: keine Zeile ([null]) und einen Wert [NULL] ([DBNull]);
  • Zeilen 23–24: Um das Ergebnis im erwarteten Typ zu erhalten, verwenden wir die Methoden der Klasse [Convert], die ein [long] in ein [int] umwandeln kann;
  • Zeile 27: Eine direkte Typumwandlung schlägt fehl, weil das Objekt ein „geboxtes“ [long] ist: Es kann nur in seinen exakten Typ entpackt werden. Das Programm von 2008 schrieb [(int)ExecuteScalar(…, "select count(id) from articles")]: Das funktionierte mit SQL Server Compact, dessen [count] ein [int] liefert, und würde mit MySQL fehlschlagen (Zeile 8). Dies ist ein Beispiel für die Eigenheiten der DBMS, die der generische Konnektor nicht verbirgt.

10.8. Asynchrone Methoden

In Kapitel 9 wurde gezeigt, dass Ein- und Ausgabeoperationen in Anwendungen, die viele Benutzer bedienen (Server, Webservices) oder über eine grafische Benutzeroberfläche verfügen, asynchron sein müssen: Während ein SQL-Befehl auf dem Server ausgeführt wird, darf der Thread der Anwendung nicht blockiert sein. Alle ADO.NET-Methoden, die mit dem DBMS kommunizieren, haben eine asynchrone Version, die eine Task zurückgibt ([Task], [Task<T>], oder [ValueTask]) und einen optionalen [CancellationToken] akzeptiert:

Klasse
Synchrone Methode
Asynchrone Methode
[DbConnection]
[Open], [Close], [BeginTransaction]
[OpenAsync], [CloseAsync], [BeginTransactionAsync]
[DbCommand]
[ExecuteReader], [ExecuteNonQuery], [ExecuteScalar], [Prepare]
[ExecuteReaderAsync], [ExecuteNonQueryAsync], [ExecuteScalarAsync], [PrepareAsync]
[DbDataReader]
[Read], [NextResult], [Close]
[ReadAsync], [NextResultAsync], [CloseAsync]
[DbTransaction]
[Commit], [Rollback]
[CommitAsync], [RollbackAsync]

Die ADO.NET-Objekte implementieren außerdem die Schnittstelle [IAsyncDisposable]: Sie werden mit [await using] (C# 8) freigegeben, das ihre Methode [DisposeAsync] aufruft. Das Programm [adonet-08.cs] verwendet diese Methoden:

#:package MySqlConnector@2.4.0

// die asynchronen Versionen der ADO.NET-Methoden
using System.Data.Common;
using System.Diagnostics;
using System.Runtime.CompilerServices;
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

// die Datenquelle: sie stellt (bereits geöffnete) Verbindungen zu derselben Datenbank her
await using var dataSource = new MySqlDataSource(ConnectionString);

try
{
    // 1 - eine update-Anweisung innerhalb einer Transaktion
    await using (MySqlConnection connection = await dataSource.OpenConnectionAsync())
    {
        await using MySqlTransaction transaction = await connection.BeginTransactionAsync();
        await using var command = new MySqlCommand("update articles set price = price * 1.1 where price < @price", connection, transaction);
        command.Parameters.AddWithValue("@price", 200);
        int nbRows = await command.ExecuteNonQueryAsync();
        await transaction.CommitAsync();
        Console.WriteLine($"{nbRows} price(s) increased by 10 %");
    }

    // 2 - ein select, dessen Zeilen so zurückgegeben werden, wie sie gelesen werden
    await foreach (Article article in GetArticlesAsync(dataSource))
    {
        Console.WriteLine(article);
    }

    // 3 - ein einzelner Wert
    await using (DbCommand command = dataSource.CreateCommand("select sum(price * currentstock) from articles"))
    {
        decimal stockValue = Convert.ToDecimal(await command.ExecuteScalarAsync());
        Console.WriteLine($"Stock value: {stockValue} euros");
    }

    // 4 - eine von einer anderen Transaktion blockierte Abfrage, nach einer Sekunde abgebrochen
    // die [blocking]-Transaktion ändert Artikel Nr. 1 und endet nicht: die Zeile bleibt gesperrt
    await using MySqlConnection connection1 = await dataSource.OpenConnectionAsync();
    await using MySqlTransaction blocking = await connection1.BeginTransactionAsync();
    await using (var command1 = new MySqlCommand("update articles set currentstock = currentstock - 1 where id = 1", connection1, blocking))
    {
        await command1.ExecuteNonQueryAsync();
    }
    // eine andere Verbindung will dieselbe Zeile ändern: sie muss auf das Ende der [blocking]-Transaktion warten
    using var cancellation = new CancellationTokenSource(TimeSpan.FromSeconds(1));
    var stopwatch = Stopwatch.StartNew();
    try
    {
        await using DbCommand command = dataSource.CreateCommand("update articles set price = price * 2 where id = 1");
        await command.ExecuteNonQueryAsync(cancellation.Token);
    }
    catch (OperationCanceledException ex)
    {
        Console.WriteLine($"{ex.GetType().Name} after {stopwatch.Elapsed.TotalSeconds:F0} s : {ex.Message}");
    }
    // die [blocking]-Transaktion wurde nicht bestätigt: sie wird rückgängig gemacht, wenn [blocking] freigegeben wird
}
catch (MySqlException ex)
{
    Console.WriteLine($"Database access error ({ex.Message})");
}

// die Artikel der Tabelle, einzeln zurückgegeben (asynchroner Datenstrom)
static async IAsyncEnumerable<Article> GetArticlesAsync(MySqlDataSource dataSource,
    [EnumeratorCancellation] CancellationToken token = default)
{
    await using MySqlConnection connection = await dataSource.OpenConnectionAsync(token);
    await using var command = new MySqlCommand("select id, name, price, currentstock, minimumstock from articles", connection);
    await using MySqlDataReader reader = await command.ExecuteReaderAsync(token);
    while (await reader.ReadAsync(token))
    {
        yield return new Article(reader.GetInt32(0), reader.GetString(1), reader.GetDecimal(2),
            reader.GetInt32(3), reader.GetInt32(4));
    }
}

// ein Artikel: eine Zeile der Tabelle [articles]
record Article(int Id, string Name, decimal Price, int CurrentStock, int MinimumStock);

Ergebnisse:

> dotnet run adonet-08.cs
3 price(s) increased by 10 %
Article { Id = 1, Name = bike, Price = 500.00, CurrentStock = 10, MinimumStock = 5 }
Article { Id = 2, Name = pump, Price = 11.00, CurrentStock = 10, MinimumStock = 2 }
Article { Id = 3, Name = bow, Price = 600.00, CurrentStock = 4, MinimumStock = 1 }
Article { Id = 4, Name = arrows - set of 6, Price = 110.00, CurrentStock = 12, MinimumStock = 20 }
Article { Id = 5, Name = diving suit, Price = 300.00, CurrentStock = 8, MinimumStock = 2 }
Article { Id = 6, Name = diver's oxygen tanks, Price = 132.00, CurrentStock = 10, MinimumStock = 5 }
Stock value: 12550.00 euros
OperationCanceledException after 1 s : Query execution was interrupted
  • Zeile 12: eine Datenquelle [MySqlDataSource], eine von [DbDataSource] (.NET 7) abgeleitete Klasse. Sie wird ein für alle Mal mit der Verbindungszeichenfolge erstellt und stellt dann Verbindungen und Befehle bereit. In einer Anwendung wird sie statt der Verbindungszeichenfolge im Dependency-Injection-Container registriert. Da sie den Verbindungspool verwaltet, wird sie am Ende des Programms freigegeben, hier über [await using];
  • Zeilen 16–24: ein Änderungsbefehl innerhalb einer Transaktion:
    • Zeile 17: [OpenConnectionAsync] gibt eine bereits geöffnete Verbindung zurück. Das Öffnen einer Verbindung ist eine Netzwerkoperation: Sie ist asynchron. Der Block [await using (…) { … }] gibt die Verbindung beim Verlassen frei;
    • Zeilen 19–23: Die Transaktion wird erstellt, der Befehl [update] wird von [ExecuteNonQueryAsync], ausgeführt, und die Transaktion wird von [CommitAsync] bestätigt. Beachten Sie den Konstruktor von [MySqlCommand] mit drei Parametern: Abfrage, Verbindung, Transaktion;
  • Zeilen 68–76: eine Methode, die die Datensätze als asynchronen Datenstrom [IAsyncEnumerable<Article>] (C# 8, Kapitel 9) zurückgibt:
    • [ExecuteReaderAsync] führt die Abfrage aus, dann liest [await reader.ReadAsync(token)] die Zeilen nacheinander. [Read] kann nämlich auf das Netzwerk warten: Der Reader erhält die Zeilen in Blöcken vom Server;
    • [yield return] gibt jeden Datensatz an den aufrufenden Code zurück, sobald er gelesen ist, ohne zu warten, bis die gesamte Tabelle gelesen wurde: Ein [select] , das eine Million Zeilen liefert, benötigt nicht mehr Speicher als ein [select] , das zehn liefert;
    • Die Verbindung, der Befehl und der Reader werden freigegeben, wenn der Datenstrom beendet ist (oder vom aufrufenden Code verlassen wird);
    • Das Attribut [[EnumeratorCancellation]] ermöglicht es dem aufrufenden Code, über [GetArticlesAsync(dataSource).WithCancellation(token)] ein Abbruchtoken zu übergeben;
  • Zeile 28: Der aufrufende Code durchläuft den Datenstrom mit [await foreach]. Die Ergebnisse zeigen, dass die drei Preise unter 200 Euro um 10 % erhöht wurden;
  • Zeilen 33–37: [dataSource.CreateCommand] erstellt einen Befehl, der seine eigene Verbindung öffnet (und wieder schließt); er gibt einen [DbCommand] zurück. [ExecuteScalarAsync] gibt den einzelnen Wert zurück;
  • Zeilen 40–58: Abbrechen einer Abfrage:
    • Zeilen 42–46: Eine erste Verbindung startet eine Transaktion, die den Artikel Nr. 1 ändert, aber nicht bestätigt wird. Das DBMS hat die geänderte Zeile bis zum Ende der Transaktion gesperrt;
    • Zeile 49: eine Abbruchquelle, die ihr Token nach einer Sekunde abbricht (Kapitel 9);
    • Zeile 54: Eine zweite Verbindung versucht, dieselbe Zeile zu ändern. Sie muss warten, bis die erste Transaktion abgeschlossen ist … was nie geschieht. Nach einer Sekunde wird das Token abgebrochen: MySqlConnector weist den Server dann an, die Abfrage abzubrechen (MySQL-Befehl [KILL QUERY]), und [ExecuteNonQueryAsync] löst eine [OperationCanceledException] aus (Zeile 10). Ohne den Abbruch hätte die Abfrage bis zum Sperr-Timeout von MySQL gewartet – standardmäßig 50 Sekunden – und wäre dann fehlgeschlagen;
    • Die Transaktion [blocking] wird nie bestätigt: Sie wird zurückgerollt, wenn [blocking] am Ende des Blocks [try] freigegeben wird.

Die asynchronen Versionen bieten einem kleinen Konsolenprogramm, das jeweils nur eine Aufgabe erledigt, keinen Vorteil: Sie machen eine Abfrage nicht schneller. Sie sind jedoch der Standard in Serveranwendungen, etwa in ASP.NET-Core-Webservices (Kapitel 13), die viele Benutzer gleichzeitig bedienen.

10.9. Stapelausführung

Jede Ausführung eines Befehls erfordert einen Hin- und Rückweg zwischen der Anwendung und dem Server. Müssen mehrere SQL-Befehle ausgeführt werden, können sie in einem einzigen Austausch als Stapel (Batch) an das DBMS gesendet werden. .NET 6 hat ADO.NET um die abstrakten Klassen [DbBatch] und [DbBatchCommand] ergänzt: Ein Batch ist eine Liste von Befehlen mit jeweils eigenem Text und eigenen Parametern, die durch einen einzigen Aufruf ausgeführt werden. MySqlConnector implementiert sie mit den Klassen [MySqlBatch] und [MySqlBatchCommand]. Das Programm [adonet-09.cs] verwendet sie:

#:package MySqlConnector@2.4.0

// die Batch-API (DbBatch, .NET 6): mehrere SQL-Anweisungen, die in einem einzigen Austausch an das DBMS gesendet werden
using MySqlConnector;

const string ConnectionString = "Server=localhost;Port=3306;Database=dbarticles;User=root;Password=";

try
{
    await using var connection = new MySqlConnection(ConnectionString);
    await connection.OpenAsync();
    Console.WriteLine($"The connector can execute batches: {connection.CanCreateBatch}");

    // 1 - ein Batch aus drei update-Anweisungen, jede mit ihren Parametern
    await using var batch = new MySqlBatch(connection)
    {
        BatchCommands =
        {
            new MySqlBatchCommand("insert into articles(name, price, currentstock, minimumstock) values(@name, @price, 5, 1)")
            {
                Parameters = { new MySqlParameter("@name", "canoe"), new MySqlParameter("@price", 800) }
            },
            new MySqlBatchCommand("update articles set currentstock = currentstock + @addition where currentstock < minimumstock")
            {
                Parameters = { new MySqlParameter("@addition", 10) }
            },
            new MySqlBatchCommand("delete from articles where name = 'pump'"),
        }
    };
    int total = await batch.ExecuteNonQueryAsync();
    Console.WriteLine($"Total number of modified rows: {total}");

    // 2 - ein Batch aus zwei select-Anweisungen: ein Reader, zwei aufeinanderfolgende Ergebnisse
    await using var batch2 = new MySqlBatch(connection)
    {
        BatchCommands =
        {
            new MySqlBatchCommand("select name, currentstock from articles where currentstock < minimumstock + 5"),
            new MySqlBatchCommand("select count(*), sum(price * currentstock) from articles"),
        }
    };
    await using MySqlDataReader reader = await batch2.ExecuteReaderAsync();
    Console.WriteLine("Articles to watch:");
    while (await reader.ReadAsync())
    {
        Console.WriteLine($"  {reader.GetString(0)} : {reader.GetInt32(1)}");
    }
    // zum nächsten Ergebnis wechseln
    await reader.NextResultAsync();
    await reader.ReadAsync();
    Console.WriteLine($"{reader.GetInt64(0)} articles, stock value: {reader.GetDecimal(1)} euros");
}
catch (MySqlException ex)
{
    Console.WriteLine($"Database access error ({ex.Message})");
}

Ergebnisse:

1
2
3
4
5
6
7
8
> dotnet run adonet-09.cs
The connector can execute batches: True
Total number of modified rows: 3
Articles to watch:
  bow : 4
  arrows - set of 6 : 22
  canoe : 5
6 articles, stock value: 17200.00 euros
  • Zeile 12: Die Eigenschaft [CanCreateBatch] von [DbConnection] gibt an, ob der Konnektor Batches ausführen kann. Bei einem Konnektor, der das nicht kann, löst die generische Methode [connection.CreateBatch()] eine [NotSupportedException] aus;
  • Zeilen 15–27: ein Batch aus drei Änderungsbefehlen. Der Batch wird mit Objekt- und Collection-Initialisierern aufgebaut: Die Sammlung [BatchCommands] erhält drei Objekte [MySqlBatchCommand], von denen die ersten beiden ihre eigene Parametersammlung [Parameters] haben. Jeder Befehl des Batches hat seine eigenen Parameter, auch wenn sie denselben Namen wie ein Parameter eines anderen Befehls tragen;
  • Zeile 30: Die drei Befehle werden in einem einzigen Austausch gesendet und ausgeführt. [ExecuteNonQueryAsync] gibt die Gesamtzahl der geänderten Zeilen zurück: ein Einfügen ([canoe]), eine Änderung (der Bestand an Pfeilen, der unter dem Mindestbestand liegt) und ein Löschen ([pump]);
  • Zeilen 34–39: ein Batch aus zwei Befehlen [select];
  • Zeile 42: Der Batch wird von [ExecuteReaderAsync] ausgeführt, das einen einzigen Reader für beide Ergebnisse zurückgibt;
  • Zeile 49: Nachdem die Zeilen des ersten Ergebnisses gelesen wurden, setzt [NextResultAsync] den Reader auf das nächste Ergebnis.

Dieselbe Wirkung ließe sich erzielen, indem man mehrere SQL-Befehle durch Semikolons getrennt in einen einzigen Befehl schreibt, was MySqlConnector akzeptiert. Die Parameter aller Befehle würden dann jedoch vermischt, und diese Syntax wird nicht von allen DBMS akzeptiert; die Batch-API ist die von ADO.NET festgelegte Standardform.

10.10. Beispielanwendung – Version 6

Kehren wir zur Beispielanwendung [Tax] zurück. Die letzte Version, Version 5, wurde in Kapitel 6 behandelt. Es war eine dreischichtige Anwendung, deren Schicht [dao] ihre Daten aus einer Textdatei, einer JSON-Datei oder aus ihrem eigenen Code bezog und deren Schichten über einen Dependency-Injection-Container integriert wurden. Wir ändern die Schicht [dao] so, dass sie ihre Daten aus einer MySQL-Datenbank bezieht:

Image

Der Kurs von 2008 (Version 7) übernahm die Schichten [ui] und [business] der vorherigen Version, indem er ihren Quellcode in einer neuen Projektmappe neu kompilierte, und integrierte die Schichten mit Spring.NET. Wir verwenden die Projekte von Version 5 unverändert über Projektverweise weiter und schreiben nur die neuen Teile: die Schicht [dao] und das Programm, das die Schichten integriert.

10.10.1. Die Datenbank

Die Steuerstufen werden in der Tabelle [brackets] einer MySQL-Datenbank mit dem Namen [dbtaxes] gespeichert, die vom Skript [sql/dbtaxes.sql] erstellt wird:

-- Erstellung der Datenbank [dbtaxes] der Anwendung [Tax] (Version 6)
-- Ausführung: mysql -uroot < dbtaxes.sql

-- das Skript ist in UTF-8 geschrieben
SET NAMES utf8mb4;

-- die Datenbank wird bei jeder Ausführung des Skripts neu erstellt
DROP DATABASE IF EXISTS dbtaxes;
CREATE DATABASE dbtaxes CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE dbtaxes;

-- die Tabelle der Steuerstufen (Tabelle 2004 auf Einkommen 2003)
CREATE TABLE brackets (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    limits  DECIMAL(10,2) NOT NULL,
    coeffr  DECIMAL(6,3) NOT NULL,
    coeffn  DECIMAL(10,2) NOT NULL
);
INSERT INTO brackets (limits, coeffr, coeffn) VALUES
    (4962, 0, 0),
    (8382, 0.068, 291.09),
    (14753, 0.191, 1322.92),
    (23888, 0.283, 2668.39),
    (38868, 0.374, 4846.98),
    (47932, 0.426, 6883.66),
    (0, 0.481, 9505.54);

-- ein für die Anwendung spezifischer Benutzer, der nur Rechte auf die Datenbank [dbtaxes] hat
CREATE USER IF NOT EXISTS 'admtaxes'@'localhost' IDENTIFIED BY 'pwdtaxes';
GRANT ALL PRIVILEGES ON dbtaxes.* TO 'admtaxes'@'localhost';
  • Zeilen 13–17: Die Tabelle [brackets] hat vier Spalten: den vom DBMS erzeugten Primärschlüssel [id], gefolgt von den drei Spalten [limits], [coeffr] und [coeffn], die eine Steuerstufe vom Typ [DECIMAL] darstellen: Es handelt sich um Beträge und Koeffizienten, die exakt sein müssen. Das Skript von 2008 verwendete den Typ [DOUBLE];
  • Zeilen 19–26: die sieben Stufen des Steuertarifs 2004 für die Einkommen des Jahres 2003, der seit Beginn des Kurses verwendet wird (dies ist eine Übung: Der Steuertarif ist nicht aktuell). Die Stufen werden in aufsteigender Reihenfolge ihrer Schwellenwerte eingefügt, und die Spalte [id] spiegelt diese Reihenfolge wider;
  • Zeilen 29–30: Anlegen eines MySQL-Benutzers [admtaxes] mit dem Passwort [pwdtaxes], dem alle Rechte ([GRANT ALL PRIVILEGES]) für alle Objekte der Datenbank [dbtaxes] ([dbtaxes.*]) und nur für diese Objekte gewährt werden. Die Anwendung läuft unter diesem Benutzer statt unter dem Administrator [root]: Würde das Passwort des Administrators kompromittiert, wäre nur die Datenbank [dbtaxes] gefährdet. [IF NOT EXISTS] erlaubt es, das Skript mehrfach auszuführen.

Das Skript wird vom Administrator ausgeführt:

> mysql -uroot < dbtaxes.sql

dann prüfen wir, ob der Benutzer [admtaxes] Zugriff auf die Steuerstufen hat (die Option [-p] allein fordert zur Eingabe des Passworts auf, das bei der Eingabe nicht angezeigt wird):

> mysql -uadmtaxes -p -t dbtaxes -e "select * from brackets"
Enter password: 
+----+----------+--------+---------+
| id | limits   | coeffr | coeffn  |
+----+----------+--------+---------+
|  1 |  4962.00 |  0.000 |    0.00 |
|  2 |  8382.00 |  0.068 |  291.09 |
|  3 | 14753.00 |  0.191 | 1322.92 |
|  4 | 23888.00 |  0.283 | 2668.39 |
|  5 | 38868.00 |  0.374 | 4846.98 |
|  6 | 47932.00 |  0.426 | 6883.66 |
|  7 |     0.00 |  0.481 | 9505.54 |
+----+----------+--------+---------+

10.10.2. Die Projektmappe TaxV6

Version 6 ist eine Projektmappe aus drei Projekten, die im Ordner [10-adonet/TaxV6] liegen:

csharp-oct-2026/
  06-architectures/TaxV5/             version 5, reused
    Tax.Dao/                            entities, ITaxDao interface, implementations of version 5
    Tax.Business/                       [business] layer
    Tax.Ui/                             [ui] layer (Dialog class) and program of version 5
  10-adonet/
    sql/
      dbtaxes.sql                       database creation script
    TaxV6/
      TaxV6.slnx
      Tax.Dao.MySql/                    library: the new [dao] layer
        Tax.Dao.MySql.csproj
        Dao/
          MySqlTax.cs
          DbTax.cs
      Tax.Tests.MySql/                  MSTest tests of the new [dao] layer
        Tax.Tests.MySql.csproj
        MSTestSettings.cs
        MySqlTaxTests.cs
      Tax.Ui.MySql/                     executable: integration of the layers
        Tax.Ui.MySql.csproj
        Program.cs
        appsettings.json
        appsettings-db.json
        appsettings-base.json
        appsettings-server.json
        appsettings-query.json

Sie wurde mit den folgenden Befehlen erstellt, die im Ordner [TaxV6] eingegeben wurden:

1
2
3
4
5
6
7
8
9
> dotnet new classlib -n Tax.Dao.MySql -o Tax.Dao.MySql
> dotnet new console -n Tax.Ui.MySql -o Tax.Ui.MySql
> dotnet new mstest -n Tax.Tests.MySql -o Tax.Tests.MySql
> dotnet new sln --format slnx -n TaxV6
> dotnet sln TaxV6.slnx add Tax.Dao.MySql Tax.Ui.MySql Tax.Tests.MySql
> dotnet add Tax.Dao.MySql reference ../../06-architectures/TaxV5/Tax.Dao
> dotnet add Tax.Dao.MySql package MySqlConnector --version 2.4.0
> dotnet add Tax.Ui.MySql reference Tax.Dao.MySql ../../06-architectures/TaxV5/Tax.Business ../../06-architectures/TaxV5/Tax.Ui
> dotnet add Tax.Tests.MySql reference Tax.Dao.MySql ../../06-architectures/TaxV5/Tax.Business

Die Projektmappendatei enthält nur die drei neuen Projekte:

1
2
3
4
5
<Solution>
  <Project Path="Tax.Dao.MySql/Tax.Dao.MySql.csproj" />
  <Project Path="Tax.Tests.MySql/Tax.Tests.MySql.csproj" />
  <Project Path="Tax.Ui.MySql/Tax.Ui.MySql.csproj" />
</Solution>

Der Befehl [dotnet build TaxV6.slnx] kompiliert auch die Projekte von Version 5, auf die diese Projekte verweisen.

Hier zeigt sich der Wert einer Schichtenarchitektur und von Klassenbibliotheken. 2008 enthielten die DLL-Dateien der Schichten [business] und [ui] von Version 5 den Namen und die Version der DLL-Datei der Schicht [dao], mit der sie kompiliert worden waren, und ihr Quellcode musste neu kompiliert werden, damit sie mit einer neuen Schicht [dao] funktionierten. Hier kennen die Schichten [business] und [ui] nur die Schnittstelle [ITaxDao], die in Version 5 im Projekt [Tax.Dao] definiert ist; die neue Schicht [dao] implementiert diese Schnittstelle in einem neuen Projekt. Keine Codezeile von Version 5 wird geändert.

10.10.3. Die Schicht [dao]

Die Projektdatei [Tax.Dao.MySql.csproj] lautet wie folgt:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <!-- die Entitäten und die Schnittstelle ITaxDao der Version 5 -->
    <ProjectReference Include="../../../06-architectures/TaxV5/Tax.Dao/Tax.Dao.csproj" />
  </ItemGroup>

  <ItemGroup>
    <!-- der ADO.NET-Connector für MySQL -->
    <PackageReference Include="MySqlConnector" Version="2.4.0" />
  </ItemGroup>

</Project>
  • Zeile 11: der Verweis auf das Projekt [Tax.Dao] aus Version 5, das die Entitäten ([TaxBracket], [TaxException]) und die Schnittstelle [ITaxDao] enthält. Der Pfad ist relativ zum Projektordner;
  • Zeile 16: der Konnektor MySqlConnector.

Die Klasse [MySqlTax] implementiert die Schnittstelle [ITaxDao]:

using Entities;
using MySqlConnector;

namespace Dao;

// eine [dao]-Schicht, die die Steuerstufen aus einer MySQL-Datenbank liest
public class MySqlTax : ITaxDao
{
    // für diese Implementierung spezifische Fehlercodes
    public enum ErrorCodes { Access = 101, Data = 102 }

    // die Standardabfrage: die Stufen müssen in aufsteigender Reihenfolge der Grenzen zurückgegeben werden
    public const string DefaultQuery = "select limits, coeffr, coeffn from brackets order by id";

    // Steuerstufen
    public TaxBracket[] TaxBrackets { get; }

    // Konstruktor: die Stufen werden ein für alle Mal gelesen
    public MySqlTax(string connectionString, string query = DefaultQuery)
    {
        List<TaxBracket> brackets = [];
        try
        {
            using var connection = new MySqlConnection(connectionString);
            connection.Open();
            using var command = new MySqlCommand(query, connection);
            using MySqlDataReader reader = command.ExecuteReader();
            // eine Stufe pro Zeile des Ergebnisses: limit, coeffR, coeffN
            while (reader.Read())
            {
                brackets.Add(new TaxBracket(reader.GetDecimal(0), reader.GetDecimal(1), reader.GetDecimal(2)));
            }
        }
        catch (MySqlException e)
        {
            // Fehler beim Datenbankzugriff: wir verpacken ihn in eine TaxException
            throw new TaxException("Error accessing the tax brackets database", e) { Code = (int)ErrorCodes.Access };
        }
        catch (Exception e) when (e is InvalidCastException or ArgumentOutOfRangeException)
        {
            // eine fehlende Spalte, vom falschen Typ oder mit dem Wert NULL
            throw new TaxException("The tax brackets of the database are incorrect", e) { Code = (int)ErrorCodes.Data };
        }
        // auch eine leere Tabelle ist ein Fehler
        if (brackets.Count == 0)
        {
            throw new TaxException("The database contains no tax bracket") { Code = (int)ErrorCodes.Data };
        }
        TaxBrackets = [.. brackets];
    }
}
  • Zeile 7: Die Klasse [MySqlTax] implementiert die Schnittstelle [ITaxDao] aus Version 5: eine schreibgeschützte Eigenschaft [TaxBrackets] (Zeile 16);
  • Zeile 10: Fehlercodes, die für diese Implementierung spezifisch sind, nach dem Vorbild von [FileTax] und [JsonTax]: 101 (der im Kurs von 2008 verwendete Code) für einen Fehler beim Datenbankzugriff, 102 für fehlerhafte Daten;
  • Zeile 13: die Abfrage, die die Stufen zurückgibt. Die Klausel [order by id] ist wichtig: Die Schicht [business] erwartet die Stufen in aufsteigender Reihenfolge ihrer Grenzen, und ein [select] ohne [order by] liefert die Zeilen in einer Reihenfolge, die SQL nicht garantiert (die Abfrage von 2008 ließ sie weg);
  • Zeile 19: Der Konstruktor erhält die Verbindungszeichenfolge und die Abfrage, die einen Standardwert hat (optionaler Parameter, C# 4). Wie in der Version von 2008 werden die Stufen einmalig und endgültig im Konstruktor gelesen: Die Schicht [dao] ist in der Anwendung ein Singleton, und die Datenbank wird danach nicht erneut abgefragt. Da ein Konstruktor nicht asynchron sein kann, verwendet der Lesevorgang die synchronen Methoden von ADO.NET;
  • Zeilen 24–27: die Verbindung, der Befehl und der Reader wie in den vorherigen Beispielen;
  • Zeilen 29–31: Jede Zeile des Ergebnisses wird zu einem [TaxBracket]-Objekt: [reader.GetDecimal(i)] liest die Spalte mit der Nummer i als [decimal];
  • Zeilen 34–37: Ein Fehler beim Datenbankzugriff wird in eine [TaxException] mit dem Code [Access] gekapselt, genau wie [FileTax] und [JsonTax] es bei Ein-/Ausgabefehlern taten. Die [ui]-Schicht muss die Ausnahmen von MySqlConnector nicht kennen;
  • Zeilen 39–42: Eine Abfrage, die weniger als drei Spalten liefert ([ArgumentOutOfRangeException]), eine Spalte mit dem Wert [NULL], oder ein nicht numerischer Typ ([InvalidCastException]): Die Daten sind fehlerhaft;
  • Zeilen 45–47: Auch eine leere Tabelle ist ein Datenfehler;
  • Zeile 49: Die Liste wird durch einen Collection-Ausdruck in ein Array umgewandelt.

10.10.4. Tests für die [dao]-Schicht

Der Kurs von 2008 testete die [dao]-Schicht mit NUnit. Wie in Kapitel 6 verwenden wir MSTest. Das Testprojekt referenziert die neue [dao]-Schicht und die [business]-Schicht aus Version 5:

  <ItemGroup>
    <!-- das Testframework MSTest -->
    <PackageReference Include="MSTest" Version="4.0.2" />
  </ItemGroup>

  <ItemGroup>
    <!-- die getestete [dao]-Schicht und die [business]-Schicht der Version 5 -->
    <ProjectReference Include="../Tax.Dao.MySql/Tax.Dao.MySql.csproj" />
    <ProjectReference Include="../../../06-architectures/TaxV5/Tax.Business/Tax.Business.csproj" />
  </ItemGroup>

Die Testklasse sieht wie folgt aus:

using Dao;
using Entities;
using Business;
using MySqlConnector;

namespace Tests;

// Tests der [dao]-Schichten MySqlTax und DbTax (die Datenbank [dbtaxes] muss existieren)
[TestClass]
public class MySqlTaxTests
{
    // die Testdatenbank
    private const string ConnectionString = "Server=localhost;Port=3306;Database=dbtaxes;User=admtaxes;Password=pwdtaxes";

    // die aus der Datenbank gelesenen Stufen sind die der „fest verdrahteten“ Version
    [TestMethod]
    public void SameBrackets()
    {
        TaxBracket[] reference = new HardwiredTax().TaxBrackets;
        CollectionAssert.AreEqual(reference, new MySqlTax(ConnectionString).TaxBrackets);
        CollectionAssert.AreEqual(reference,
            new DbTax(MySqlConnectorFactory.Instance, ConnectionString, MySqlTax.DefaultQuery).TaxBrackets);
    }

    // die [business]-Schicht der Version 5 mit der neuen [dao]-Schicht
    [TestMethod]
    [DataRow(true, 2, 60000, 4282)]
    [DataRow(true, 3, 60000, 2959)]
    [DataRow(false, 0, 60000, 11519)]
    public void CalculateTax(bool married, int nbChildren, int salary, int tax)
    {
        ITaxBusiness business = new TaxBusiness(new MySqlTax(ConnectionString));
        Assert.AreEqual(tax, business.CalculateTax(married, nbChildren, salary));
    }

    // nicht existierende Datenbank (oder für den Benutzer gesperrt)
    [TestMethod]
    public void MissingDatabase()
    {
        TaxException e = Assert.ThrowsExactly<TaxException>(
            () => new MySqlTax(ConnectionString.Replace("dbtaxes", "dbtaxesunknown")));
        Assert.AreEqual((int)MySqlTax.ErrorCodes.Access, e.Code);
        Assert.IsInstanceOfType<MySqlException>(e.InnerException);
    }

    // nicht erreichbarer Server
    [TestMethod]
    public void UnreachableServer()
    {
        TaxException e = Assert.ThrowsExactly<TaxException>(
            () => new MySqlTax(ConnectionString.Replace("3306", "3399")));
        Assert.AreEqual((int)MySqlTax.ErrorCodes.Access, e.Code);
    }

    // Abfrage, die nur zwei Spalten zurückgibt
    [TestMethod]
    public void IncorrectQuery()
    {
        TaxException e = Assert.ThrowsExactly<TaxException>(
            () => new MySqlTax(ConnectionString, "select limits, coeffr from brackets"));
        Assert.AreEqual((int)MySqlTax.ErrorCodes.Data, e.Code);
    }

    // Abfrage, die keine Zeile zurückgibt
    [TestMethod]
    public void NoBracket()
    {
        TaxException e = Assert.ThrowsExactly<TaxException>(
            () => new MySqlTax(ConnectionString, "select limits, coeffr, coeffn from brackets where id < 0"));
        Assert.AreEqual((int)MySqlTax.ErrorCodes.Data, e.Code);
    }
}
  • Zeile 13: Die Tests verwenden die Datenbank [dbtaxes], die zuvor mit dem Skript [dbtaxes.sql] erstellt worden sein muss. Es handelt sich also nicht mehr um Unit-Tests im strengen Sinne – die eine Klasse isoliert testen würden –, sondern um Integrationstests: Sie prüfen, dass die [dao]-Schicht mit dem DBMS funktioniert;
  • Zeilen 17–22: Die aus der Datenbank gelesenen Steuerstufen (durch [MySqlTax], dann durch die weiter unten beschriebene Klasse [DbTax]) sind identisch mit denen der „fest codierten“ Version [HardwiredTax]. [CollectionAssert.AreEqual] vergleicht die Elemente einzeln mithilfe ihrer [Equals]-Methode; die Methode des [record struct] vergleicht die Eigenschaften. Beachten Sie, dass die aus der Datenbank gelesenen [decimal]-Werte zwei Nachkommastellen haben ([4962.00]), die von [HardwiredTax] dagegen keine ([4962]): Für den Typ [decimal] sind diese beiden Werte gleich;
  • Zeilen 27–33: Die [business]-Schicht aus Version 5 berechnet die erwarteten Steuern mithilfe der neuen [dao]-Schicht;
  • Zeilen 38–43: Eine nicht vorhandene Datenbank führt zu einer [TaxException] mit dem Code [Access], die die ursprüngliche [MySqlException] kapselt;
  • Zeilen 48–51: Ein nicht erreichbarer Server (kein Server lauscht auf Port 3399) führt ebenfalls zu einem Zugriffsfehler;
  • Die Methoden [IncorrectQuery] (Zeile 57) und [NoBracket] (Zeile 66): Eine Abfrage, die nur zwei Spalten oder keine Zeilen liefert, führt zu einer [TaxException] mit dem Code [Data].

Die Ausführung der Tests liefert folgende Ergebnisse:

> dotnet test --logger "console;verbosity=normal"
Test run for bin/Debug/net10.0/Tax.Tests.MySql.dll (.NETCoreApp,Version=v10.0)
VSTest version 18.0.2 (x64)

Starting test execution, please wait...
A total of 1 test files matched the specified pattern.
  Passed SameBrackets [273 ms]
  Passed CalculateTax (True,2,60000,4282) [2 ms]
  Passed CalculateTax (True,3,60000,2959) [1 ms]
  Passed CalculateTax (False,0,60000,11519) [< 1 ms]
  Passed MissingDatabase [12 ms]
  Passed UnreachableServer [4 ms]
  Passed IncorrectQuery [1 ms]
  Passed NoBracket [< 1 ms]

Test Run Successful.
Total tests: 8
     Passed: 8
 Total time: 1.1413 Seconds

10.10.5. Integration der Schichten

Das Projekt [Tax.Ui.MySql] enthält das Programm, das die Schichten integriert. Seine Projektdatei sieht wie folgt aus:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <!-- die neue [dao]-Schicht -->
    <ProjectReference Include="../Tax.Dao.MySql/Tax.Dao.MySql.csproj" />
    <!-- die [business]- und [ui]-Schichten der Version 5, unverändert -->
    <ProjectReference Include="../../../06-architectures/TaxV5/Tax.Business/Tax.Business.csproj" />
    <ProjectReference Include="../../../06-architectures/TaxV5/Tax.Ui/Tax.Ui.csproj" />
  </ItemGroup>

  <ItemGroup>
    <!-- Dependency-Injection-Container und Lesen einer JSON-Konfigurationsdatei -->
    <PackageReference Include="Microsoft.Extensions.DependencyInjection" Version="10.0.12" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="10.0.12" />
  </ItemGroup>

  <ItemGroup>
    <!-- die Konfigurationsdateien werden neben die ausführbare Datei kopiert -->
    <None Update="appsettings*.json" CopyToOutputDirectory="PreserveNewest" />
  </ItemGroup>

</Project>
  • Zeile 12: die neue [dao]-Schicht;
  • Zeilen 14–15: die Schichten [business] und [ui] aus Version 5. Das Projekt [Tax.Ui] ist eine ausführbare Datei, aber ein Projekt kann eine ausführbare Datei wie eine Bibliothek referenzieren: Wir verwenden ihre öffentliche Klasse [Dialog]. Ihre Konfigurationsdateien werden ebenfalls in den Ausgabeordner kopiert, werden dort aber von denen des gleichnamigen Projekts [Tax.Ui.MySql] ersetzt;
  • Zeilen 20–21: der Dependency-Injection-Container und das Lesen der Konfiguration, wie in Version 5.

Die Konfigurationsdatei [appsettings.json] sieht wie folgt aus:

1
2
3
4
5
6
7
8
9
{
  "ConnectionStrings": {
    "dbtaxes": "Server=localhost;Port=3306;Database=dbtaxes;User=admtaxes;Password=pwdtaxes"
  },
  "Dao": {
    "Implementation": "MySqlTax",
    "Query": "select limits, coeffr, coeffn from brackets order by id"
  }
}
  • Zeile 3: die Verbindungszeichenfolge für den Benutzer [admtaxes] auf die Datenbank [dbtaxes];
  • Zeile 6: die Implementierung der [dao]-Schicht;
  • Zeile 7: die Abfrage, die die Steuerstufen liefert. Dadurch, dass sie in der Konfiguration steht, können Sie eine andere Datenbank verwenden – sogar eine mit anders benannten Tabellen oder Spalten –, ohne den Code zu ändern.

Das Programm [Program.cs] ist das aus Version 5, mit Ausnahme der Registrierung der [dao]-Schicht:

using System.Data.Common;
using Dao;
using Entities;
using Business;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using MySqlConnector;
using Ui;

// die Konfigurationsdatei: standardmäßig appsettings.json oder die als Argument übergebene
string configFile = args.Length > 0 ? args[0] : "appsettings.json";

// die ADO.NET-Connectors, die von der Implementierung DbTax verwendet werden können
DbProviderFactories.RegisterFactory("MySqlConnector", MySqlConnectorFactory.Instance);

// Integration der Schichten
ITaxBusiness business;
try
{
    // Lesen der Konfiguration, neben der ausführbaren Datei
    IConfiguration config = new ConfigurationBuilder()
        .SetBasePath(AppContext.BaseDirectory)
        .AddJsonFile(configFile, optional: false)
        .Build();
    string implementation = config["Dao:Implementation"] ?? "MySqlTax";
    string connectionString = config.GetConnectionString("dbtaxes")
        ?? throw new TaxException("The connection string [dbtaxes] is missing from the configuration");
    string query = config["Dao:Query"] ?? MySqlTax.DefaultQuery;

    // Registrierung der Schichten im Container
    var services = new ServiceCollection();
    // die Schicht [dao]: die Implementierung wird durch die Konfiguration gewählt
    services.AddSingleton<ITaxDao>(_ => implementation switch
    {
        "MySqlTax" => new MySqlTax(connectionString, query),
        "DbTax" => new DbTax(DbProviderFactories.GetFactory(config["Dao:Connector"] ?? ""), connectionString, query),
        _ => throw new TaxException($"Unknown implementation [{implementation}] of the [dao] layer")
    });
    // die Schicht [business]: der Container übergibt ihr die Schicht [dao]
    services.AddSingleton<ITaxBusiness, TaxBusiness>();

    // Erzeugung des Containers, dann der Schicht [business] (und damit der Schicht [dao])
    ServiceProvider provider = services.BuildServiceProvider();
    business = provider.GetRequiredService<ITaxBusiness>();
}
catch (Exception e)
{
    // Anzeige des Fehlers und Programmende
    Dialog.DisplayError(e);
    return 1;
}

// der Dialog mit dem Benutzer: die [ui]-Schicht der Version 5
Dialog.Run(business);
return 0;
  • Zeile 14: die Registrierung des MySqlConnector-Konnektors, der von der im folgenden Absatz beschriebenen Implementierung [DbTax] verwendet wird;
  • Zeilen 26–28: die Verbindungszeichenfolge und die Abfrage, gelesen aus der Konfiguration. Das Fehlen der Verbindungszeichenfolge ist ein Fehler; die Abfrage hat einen Standardwert. Beachten Sie den Operator [??] gefolgt von einem [throw]-Ausdruck (C# 7);
  • Zeilen 33–37: Die [dao]-Schicht ist ein [MySqlTax] (oder ein [DbTax]), erstellt mit der Verbindungszeichenfolge und der Abfrage;
  • Zeile 54: der Dialog aus Version 5, unverändert.

Ausführung mit der Datei [appsettings.json]:

1
2
3
4
5
6
7
8
> dotnet run
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: y 2 60000
Tax=4282 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: y 3 60000
Tax=2959 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: n 0 60000
Tax=11519 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: 

Dies sind die Referenzwerte des Kurses: 4.282 Euro für ein Paar mit zwei Kindern, 2.959 Euro für ein Paar mit drei Kindern und 11.519 Euro für eine alleinstehende Person ohne Kinder, bei einem Jahresgehalt von 60.000 Euro.

Mit der Datei [appsettings-base.json], deren Verbindungszeichenfolge auf eine Datenbank namens [dbtaxesunknown] verweist:

1
2
3
4
5
6
7
> dotnet run -- appsettings-base.json
Exception chain:
----------------------------------------
Entities.TaxException: Error accessing the tax brackets database
----------------------------------------
MySqlConnector.MySqlException: Access denied for user 'admtaxes'@'localhost' to database 'dbtaxesunknown'
----------------------------------------

Der Benutzer [admtaxes] hat nur Rechte auf die Datenbank [dbtaxes]: MySQL verweigert den Zugriff auf jede andere Datenbank, unabhängig davon, ob sie existiert oder nicht (Zeile 6).

Mit der Datei [appsettings-server.json], deren Verbindungszeichenfolge Port 3399 angibt – auf dem kein Server lauscht (so würde es aussehen, wenn ein MySQL-Server angehalten wäre) –:

1
2
3
4
5
6
7
> dotnet run -- appsettings-server.json
Exception chain:
----------------------------------------
Entities.TaxException: Error accessing the tax brackets database
----------------------------------------
MySqlConnector.MySqlException: Unable to connect to any of the specified MySQL hosts.
----------------------------------------

Mit der Datei [appsettings-query.json], deren Abfrage nur die Spalten [limits] und [coeffr] anfordert:

1
2
3
4
5
6
7
8
> dotnet run -- appsettings-query.json
Exception chain:
----------------------------------------
Entities.TaxException: The tax brackets of the database are incorrect
----------------------------------------
System.ArgumentOutOfRangeException: ordinal ('2') must be less than '2'. (Parameter 'ordinal')
Actual value was 2.
----------------------------------------

In allen drei Fällen hat die [dao]-Schicht die ursprüngliche Ausnahme in eine [TaxException] gekapselt, die in Version 5 von der Methode [Dialog.DisplayError] angezeigt wird, und das Programm wird beendet.

10.10.6. Die Datenbank wechseln

Die [dao]-Schicht [MySqlTax] verwendet die Klassen des Konnektors MySqlConnector. Wenn Sie das DBMS wechseln, müssen Sie eine neue [dao]-Schicht schreiben … oder den generischen Konnektor verwenden, wie es die [dao]-Schicht des Kurses von 2008 tat. Das ist die Aufgabe der Klasse [DbTax] im Projekt [Tax.Dao.MySql]:

using System.Data.Common;
using Entities;

namespace Dao;

// eine [dao]-Schicht, die die Steuerstufen aus einer beliebigen Datenbank liest
// sie kennt nur die abstrakten Klassen von ADO.NET: der Connector wird ihr über seine Factory übergeben
public class DbTax : ITaxDao
{
    // Fehlercodes: dieselben wie bei MySqlTax
    public enum ErrorCodes { Access = 101, Data = 102 }

    // Steuerstufen
    public TaxBracket[] TaxBrackets { get; }

    // Konstruktor: die Factory des Connectors, die Verbindungszeichenfolge, die Abfrage, die die Stufen zurückgibt
    public DbTax(DbProviderFactory factory, string connectionString, string query)
    {
        List<TaxBracket> brackets = [];
        try
        {
            using DbConnection connection = factory.CreateConnection()
                ?? throw new InvalidOperationException("the connector cannot create a connection");
            connection.ConnectionString = connectionString;
            connection.Open();
            using DbCommand command = connection.CreateCommand();
            command.CommandText = query;
            using DbDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                brackets.Add(new TaxBracket(reader.GetDecimal(0), reader.GetDecimal(1), reader.GetDecimal(2)));
            }
        }
        catch (DbException e)
        {
            throw new TaxException("Error accessing the tax brackets database", e) { Code = (int)ErrorCodes.Access };
        }
        catch (Exception e) when (e is InvalidCastException or ArgumentOutOfRangeException)
        {
            throw new TaxException("The tax brackets of the database are incorrect", e) { Code = (int)ErrorCodes.Data };
        }
        if (brackets.Count == 0)
        {
            throw new TaxException("The database contains no tax bracket") { Code = (int)ErrorCodes.Data };
        }
        TaxBrackets = [.. brackets];
    }
}
  • Zeile 17: Der Konstruktor erhält die Konnektor-Factory, die Verbindungszeichenfolge und die Abfrage. Der Code verwendet nur die abstrakten Klassen aus [System.Data.Common]: Er enthält nicht den Namen irgendeines DBMS;
  • Zeilen 22–27: Die Verbindung wird von der Factory erstellt (der Ausdruck [throw] deckt den theoretischen Fall einer Factory ab, die [null] zurückgeben würde), und der Befehl wird von der Verbindung erstellt;
  • Zeile 34: die Basisausnahme aller Konnektoren.

Die Konfigurationsdatei [appsettings-db.json] wählt diese Implementierung und den Konnektor [MySqlConnector] aus:

{
  "ConnectionStrings": {
    "dbtaxes": "Server=localhost;Port=3306;Database=dbtaxes;User=admtaxes;Password=pwdtaxes"
  },
  "Dao": {
    "Implementation": "DbTax",
    "Connector": "MySqlConnector",
    "Query": "select limits, coeffr, coeffn from brackets order by id"
  }
}

und das Programm [Program.cs] erstellt die [dao]-Schicht mit der unter diesem Namen registrierten Factory:

        "DbTax" => new DbTax(DbProviderFactories.GetFactory(config["Dao:Connector"] ?? ""), connectionString, query),

Ausführung:

1
2
3
4
> dotnet run -- appsettings-db.json
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: y 2 60000
Tax=4282 euros
Tax calculation parameters in the format: Married (y/n) NbChildren Salary, or nothing to stop: 

Um eine SQL-Server-Datenbank (oder PostgreSQL) zu verwenden, müssen Sie lediglich Folgendes tun:

  • die Tabelle der Steuerstufen in einer SQL-Server-Datenbank anlegen, zum Beispiel eine Tabelle namens [data] mit den Spalten [data1], [data2] und [data3] – wie es der Kurs von 2008 tat, um den Vorteil hervorzuheben, die Abfrage in die Konfiguration zu legen;
  • das Paket [Microsoft.Data.SqlClient] (oder [Npgsql]) zum Projekt [Tax.Ui.MySql] hinzufügen;
  • seine Factory in [Program.cs] registrieren: [DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance)] (oder [DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance)]);
  • eine Konfigurationsdatei ähnlich [appsettings-db.json] schreiben:
{
  "ConnectionStrings": {
    "dbtaxes": "Server=localhost;Database=dbtaxes;User Id=admtaxes;Password=pwdtaxes;TrustServerCertificate=True"
  },
  "Dao": {
    "Implementation": "DbTax",
    "Connector": "Microsoft.Data.SqlClient",
    "Query": "select data1, data2, data3 from data order by id"
  }
}

Auf dem Rechner, auf dem dieser Kurs geschrieben wurde, ist nur MySQL installiert: Wir haben diese Konfiguration nicht ausgeführt. Die Schichten [business] und [ui] sowie die Klasse [DbTax] müssen nicht geändert werden: Nur die Konfiguration und die Registrierung des Konnektors ändern sich. Beachten Sie, dass die Abfrage von [DbTax] keine Parameter hat und nur Standard-SQL-Funktionen verwendet: Das ermöglicht es, sie von jedem DBMS ausführen zu lassen. Eine funktionsreichere [dao]-Schicht (Suchen, Aktualisierungen) würde schnell an die Grenzen des oben beschriebenen generischen Konnektors stoßen: Hier übernimmt ein ORM wie Entity Framework Core.

10.11. Fazit

Dieses Kapitel hat den Datenbankzugriff im verbundenen Modus mit ADO.NET behandelt:

  • Ein DBMS-spezifischer Konnektor (MySqlConnector für MySQL) stellt die Klassen [XxxConnection], [XxxCommand], [XxxDataReader] und [XxxTransaction] bereit, die von den abstrakten Klassen [DbConnection], [DbCommand], [DbDataReader] und [DbTransaction] aus [System.Data.Common] abgeleitet sind;
  • Eine Verbindung wird so spät wie möglich geöffnet und so früh wie möglich durch ein [using] geschlossen: Der Verbindungspool macht diese Praxis kostengünstig;
  • [ExecuteReader] führt ein [select] aus und liefert einen Reader, der mit [Read] zeilenweise durchlaufen wird; [ExecuteNonQuery] führt ein [insert], [update] oder [delete] aus und liefert die Anzahl der geänderten Zeilen; [ExecuteScalar] liefert einen einzelnen Wert;
  • Fehler sind [DbException] ([MySqlException] bei MySqlConnector);
  • Externe Werte werden immer über Parameter übergeben und niemals an den Abfragetext angehängt, was vor SQL-Injection schützt;
  • Transaktionen ([BeginTransaction], [Commit], [Rollback]) stellen sicher, dass eine Folge von Befehlen atomar ist (SQL);
  • Asynchrone Methoden ([OpenAsync], [ExecuteReaderAsync], [ReadAsync] …) und [await using] sind in Serveranwendungen Standard; Batches ([DbBatch]) verringern die Anzahl der Austauschvorgänge mit dem Server;
  • Der generische Konnektor ([DbProviderFactory], [DbProviderFactories]) macht den Code unabhängig vom DBMS, im Rahmen der Besonderheiten des jeweiligen DBMS.

Version 6 der Anwendung [Tax] hat gezeigt, dass dank der Schichtenarchitektur der Wechsel von einer Datei zu einer Datenbank nur das Schreiben einer neuen [dao]-Schicht und die Änderung der Konfiguration erfordert. Im nächsten Kapitel erstellen wir eine siebte Version dieser Schicht mit Entity Framework Core, das die SQL-Befehle selbst erzeugt.

Weiterführende Informationen: