Die Verwendung von Stored Procedures ist eine weit verbreitete Praxis, um die Geschäftslogik in einer sicheren und optimierten Umgebung zu zentralisieren und zu kapseln. Sie ermöglichen es, einen Teil der Anwendungslogik auf den Datenbankserver zu verlagern, was die Komplexität des clientseitigen Codes verringert, die Leistung durch weniger Netzwerkverkehr verbessert und die Sicherheit durch Einschränkung des direkten Zugriffs auf Tabellen erhöht.
Wir werden sehen, wie man eine auf einem IBM i-System gehostete SQL-Stored Procedure aus einer .NET-Anwendung heraus mit NTi aufruft. Wir werden eine gespeicherte Prozedur verwenden, die direkt aus der offiziellen Dokumentation stammt IBM i: DB2 for i SQL Reference - (Seite 1109).
Diese Prozedur berechnet den Median der Gehälter der Mitarbeiter und gibt eine Liste der Mitarbeiter zurück, deren Gehalt über dem Median liegt. Wir betrachten zwei Ansätze für den Aufruf aus einer .NET-Anwendung :
- Die erste mit der direkten Verwendung eines DataReader.
- Zum anderen durch die Nutzung von Dapper, einem schlanken Mikro-ORM, das die Implementierung und Nutzung der Ergebnisse noch weiter vereinfacht.
Schritt 1 - Vorbereitung der IBM i-Umgebung
Bevor wir eine gespeicherte Prozedur aus .NET aufrufen, müssen wir genau verstehen, was diese Prozedur tut, welche Elemente (Tabellen, Daten) für ihre Ausführung benötigt werden, und unsere Umgebung vorbereiten.
Die gespeicherte Prozedur, die wir verwenden werden, stammt direkt aus dem offiziellen Handbuch IBM i: DB2 for i SQL Reference - (Seite 1109). Hier ist der bereitgestellte SQL-Code:
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DECIMAL(7,2))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE v_numRecords INTEGER DEFAULT 1;
DECLARE v_counter INTEGER DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT salary
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, salary
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords FROM staff;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
ENDDieses Verfahren berechnet den Medianlohn der Angestellten des Unternehmens. Sie gibt dieses Gehalt als Ausgabeparameter (out) mit dem Namen medianSalary zurück und öffnet einen Schieberegler c2, der eine detaillierte Liste der Angestellten zurückgibt, die ein Gehalt verdienen, das über diesem berechneten Median liegt.
💡 Die Prozedur berechnet direkt aus einer bestimmten Tabelle, der Tabelle
staff.
Damit die Prozedur funktioniert, muss also eine Tabelle mit dem Namen staff in demselben Schema existieren, in dem die Prozedur erstellt werden soll. Sie muss mindestens eine Spalte salary vom Typ DECIMAL und weitere Spalten name und job enthalten, die für den Ergebniscursor benötigt werden.
Da wir nun wissen, was das Verfahren macht und was es braucht, bereiten wir unsere IBM i-Umgebung Schritt für Schritt vor.
1️⃣ Erstellen der Bibliothek
Wir erstellen eine MDSALARY-Bibliothek, um unsere Testelemente sauber zu isolieren. Führen Sie den folgenden Befehl von Ihrem IBM i aus, entweder über ACS oder den 5250-Bildschirm
CRTLIB MDSALARY2️⃣ Aktuelles Schema festlegen
Dann legt man das aktuelle Schema als Standardschema fest, damit sich alle folgenden SQL-Befehle automatisch darauf beziehen:
SET CURRENT SCHEMA = MDSALARY;Von nun an wird jede Tabelle oder Prozedur, die wir erstellen, automatisch in der LIB MDSALARY platziert.
3️⃣ Erstellen der Tabelle STAFF
Wie bereits erwähnt, erwartet die gespeicherte Prozedur eine bestimmte Tabelle mit dem Namen staff. Erstellen wir diese entsprechend der für unser Beispiel benötigten Spalten:
CREATE TABLE staff (
name VARCHAR(50),
job VARCHAR(50),
salary DECIMAL(7,2)
);- Eine Spalte
namefür den Namen des Mitarbeiters. - Eine Spalte
jobfür ihre Funktion. - Eine Spalte
salaryfür sein Gehalt.
4️⃣ Einfügen eines Beispieldatensatzes
Um einen relevanten Medianlohn zu berechnen, müssen wir einige repräsentative Daten in diese Tabelle einfügen:
INSERT INTO staff (name, job, salary) VALUES ('Alice', 'Manager', 2000.00);
INSERT INTO staff (name, job, salary) VALUES ('Bob', 'Clerk', 3000.00);
INSERT INTO staff (name, job, salary) VALUES ('Charlie', 'Analyst', 4000.00);
INSERT INTO staff (name, job, salary) VALUES ('David', 'Developer', 5000.00);
INSERT INTO staff (name, job, salary) VALUES ('Eve', 'Designer', 6000.00);
INSERT INTO staff (name, job, salary) VALUES ('Frank', 'Tester', 7000.00);5️⃣ Vollständiges Skript bereit zur Ausführung in ACS
Hier ist nun ein vollständiges Skript, das Sie direkt per Copy & Paste kopieren und in einem Rutsch aus dem ACS-Tool "SQL Script Executor" ausführen können
-- Erstellen der Bibliothek
CL: CRTLIB MDSALARY;
-- Definition des aktuellen Schemas
SET CURRENT SCHEMA = MDSALARY;
-- Erstellen der Tabelle STAFF
CREATE TABLE staff (
name VARCHAR(50),
job VARCHAR(50),
salary DECIMAL(7,2)
);
-- Einfügen von Testdaten
INSERT INTO staff (name, job, salary) VALUES ('Alice', 'Manager', 2000.00);
INSERT INTO staff (name, job, salary) VALUES ('Bob', 'Clerk', 3000.00);
INSERT INTO staff (name, job, salary) VALUES ('Charlie', 'Analyst', 4000.00);
INSERT INTO staff (name, job, salary) VALUES ('David', 'Developer', 5000.00);
INSERT INTO staff (name, job, salary) VALUES ('Eve', 'Designer', 6000.00);
INSERT INTO staff (name, job, salary) VALUES ('Frank', 'Tester', 7000.00);
-- Erstellen der gespeicherten Prozedur MEDIAN_RESULT_SET
CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DECIMAL(7,2))
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
DECLARE v_numRecords INTEGER DEFAULT 1;
DECLARE v_counter INTEGER DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT salary FROM staff ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, salary FROM staff WHERE salary > medianSalary ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords FROM staff;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END;6️⃣ Überprüfung auf IBM i
Überprüfen Sie auf Ihrem IBM i, ob die Bibliothek MDSALARY existiert, ob sie die Tabelle staff mit den eingefügten Daten enthält und ob sie die als SQL-Programm gespeicherte Prozedur mit dem Namen MEDIAN_RESULT_SET (Typ *PGM) enthält.
Schritt 2 - Aufruf der gespeicherten IBM i-Prozedur von .NET aus
Nachdem unsere IBM i-Umgebung nun bereit ist, wenden wir uns dem Aufruf der Prozedur aus unserer .NET-Anwendung zu. Wir erstellen ein BlazorWebApp-Projekt in .NET 8 und installieren die folgenden Pakete aus NuGet:
dotnet add package Aumerial.Data.Nti
dotnet add package Dapper1️⃣ Erstellen eines Verbindungsdienstes
Um die Verwaltung der Verbindungen zu vereinfachen, erstellen wir einen einfachen Dienst DB2Service.cs.
using Aumerial.Data.Nti;
public class DB2Service
{
private readonly string _connectionString = "server=myserver;user=myuser;password=mypassword;trim=true";
public NTiConnection CreateConnection()
{
var conn = new NTiConnection(_connectionString);
conn.Open();
return conn;
}
}Speichern Sie diesen Dienst dann in Ihrer Program.cs.
builder.Services.AddSingleton(); 2️⃣ Aufruf der gespeicherten Prozedur
Wir gehen:
- Eine Entität definieren, die unsere Mitarbeiter repräsentiert
- Aufrufen der gespeicherten Prozedur, um die Ergebnisse abzurufen
- Nutzung dieser Ergebnisse mit zwei Ansätzen: DataReader und Dapper
- Ergebnisse in einer Blazor-Tabelle anzeigen
3️⃣ Erstellen der Entität Employee
public class Employee
{
public string Name { get; set; }
public string Job { get; set; }
public decimal Salary { get; set; }
}4️⃣ Methode 1 - Klassischer Ansatz (DataReader)
In diesem ersten, klassischen Ansatz erstellen wir explizit eine Verbindung zum IBM i-Server über unseren dedizierten Dienst DB2Service und konfigurieren dann einen NTi-Befehl, um unsere gespeicherte Prozedur MEDIAN_RESULT_SET aufzurufen.
Wir definieren eindeutig den Ausgabeparameter medianSalary, der den von der Prozedur berechneten Medianlohn abrufen wird. Nach der Ausführung des Befehls ermöglicht uns ein DataReader, jedes von der Prozedur zurückgegebene Ergebnis sequentiell zu lesen, wobei jede Zeile in ein Objekt vom Typ Employee umgewandelt wird. Schließlich wird der Wert des Ausgabeparameters direkt extrahiert, um den genauen Median zu erfahren.
private decimal median;
private List employees = new();
private async Task LoadDatawhithDataReader()
{
// Erstellen der Verbindung über DB2Service
using var conn = Db2Service.CreateConnection();
using var cmd = new NTiCommand("MDSALARY.MEDIAN_RESULT_SET", conn);
// Erstellen des Befehls zum Aufrufen der gespeicherten Prozedur
cmd.CommandType = System.Data.CommandType.StoredProcedure;
// Definition des Ausgabeparameters 'medianSalary'.
var param = new NTiParameter();
param.ParameterName = "medianSalary";
param.Direction = System.Data.ParameterDirection.Output;
// Wir fügen dem Befehl den Ausgabeparameter hinzu
cmd.Parameters.Add(param);
// Ausführen des Befehls und Abrufen der Ergebnisse über DataReader
using var reader = await cmd.ExecuteReaderAsync();
// Abruf des Medianlohns (Ausgabeparameter)
median = Convert.ToDecimal(param.Value);
// Lesen der Daten jedes Mitarbeiters aus dem DataReader
while (await reader.ReadAsync())
{
employees.Add(new Employee
{
Name = reader.GetString(0),
Job = reader.GetString(1),
Salary = reader.GetDecimal(2)
});
}
} 5️⃣ Methode 2 - Vereinfachter Ansatz (Dapper)
Der zweite Ansatz stützt sich auf Dapper, ein schlankes ORM, um den Code zu vereinfachen, der für den Aufruf unserer gespeicherten Prozedur benötigt wird. Wir legen einfach den Ausgabeparameter mit DynamicParameters fest, dann kümmert sich Dapper automatisch um das Öffnen der Verbindung, die Ausführung des Befehls und das direkte Abrufen der Ergebnisse in Form einer Liste von Objekten des Typs Employee. Schließlich lässt sich der Wert des Ausgabeparameters, der dem Medianlohn entspricht, leicht abrufen. Dieser Ansatz vereinfacht den Code erheblich, wobei Leistung und Lesbarkeit erhalten bleiben.
private decimal median;
private List employees = new();
private async Task LoadDataWithDapper()
{
// Erstellen der Verbindung über DB2Service
using var conn = Db2Service.CreateConnection();
// Definition des Ausgabeparameters 'medianSalary' mit DynamicParameters
var parameters = new DynamicParameters();
parameters.Add("medianSalary", dbType: DbType.Decimal, direction: ParameterDirection.Output);
// Dapper verwaltet automatisch das Öffnen und Schließen der Verbindung sowie das Lesen der Ergebnisse.
employees = (await conn.QueryAsync(
"MDSALARY.MEDIAN_RESULT_SET",
parameters,
commandType: CommandType.StoredProcedure)).ToList();
// Direkter Abruf des Medianlohns (Ausgabeparameter)
median = parameters.Get("medianSalary");
} 6️⃣ Anzeige der Ergebnisse in einer Blazor-Komponente
Sobald die Daten abgerufen sind, müssen wir sie nur noch sauber in unserer Blazor-Schnittstelle über eine Tabelle anzeigen.
Schlussfolgerung
Sie haben konkret gesehen, wie Sie eine auf Ihrer IBM i gespeicherte Prozedur von einer .NET-Anwendung mit NTi aufrufen können: detaillierte Kontrolle mit einem DataReader oder schnelle Implementierung mit Dapper. In beiden Fällen können Sie mit NTi Ihre SQL Stored Procedures einfach und effizient in Ihre .NET-Anwendungen integrieren. Mit wenigen Zeilen Code erhalten Sie ein sofort verwertbares Ergebnis, das den modernen .NET-Entwicklungspraktiken entspricht.
Quentin Destrade