Appeler une procédure stockée SQL IBM i/DB2 for i en .NET avec NTi

L’utilisation des procédures stockées est une pratique largement répandue pour centraliser et encapsuler la logique métier dans un environnement sécurisé et optimisé. Elles permettent de déplacer une partie de la logique applicative vers le serveur de base de données, ce qui réduit la complexité du code côté client, améliore les performances en diminuant le trafic réseau, et renforce la sécurité en limitant l'accès direct aux tables.

La procédure utilisée dans cet exemple est directement issue de la documentation officielle IBM i : DB2 for i SQL Reference (page 979). Elle calcule la médiane des salaires du personnel et retourne la liste des employés dont le salaire est supérieur à cette médiane.

Étape 1 - Préparation de l'environnement IBM i

Avant d'appeler une procédure stockée depuis .NET, il faut comprendre ce qu'elle fait, quels éléments sont nécessaires à son exécution, et préparer l'environnement en conséquence.

Voici le code SQL de la procédure :

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

Cette procédure calcule le salaire médian des employés. Elle retourne ce salaire via un paramètre de sortie medianSalary, et ouvre un curseur c2 qui retourne la liste des employés dont le salaire est supérieur à cette médiane.

💡 Il n'y a pas de paramètre en entrée. La procédure effectue son calcul directement à partir de la table staff, qui doit contenir au minimum les colonnes salary, name et job.

Création de la bibliothèque

La bibliothèque MDSALARY isole les éléments de test. À exécuter depuis ACS ou un écran 5250 :

CRTLIB MDSALARY

Définir le schéma courant

SET CURRENT SCHEMA = MDSALARY;

Toutes les tables et procédures créées ensuite seront automatiquement placées dans la bibliothèque MDSALARY.

Création de la table STAFF

La procédure attend une table staff avec les colonnes suivantes :

CREATE TABLE staff (
    name VARCHAR(50),
    job VARCHAR(50),
    salary DECIMAL(7,2)
);

Insertion des données de test

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);

Script complet pour ACS

Le script suivant peut être copié-collé et exécuté en une seule fois depuis l'outil « Exécuteur de scripts SQL » d'ACS :

-- Création de la bibliothèque
CL: CRTLIB MDSALARY;

-- Définition du schéma courant
SET CURRENT SCHEMA = MDSALARY;

-- Création de la table STAFF
CREATE TABLE staff (
    name VARCHAR(50),
    job VARCHAR(50),
    salary DECIMAL(7,2)
);

-- Insertion des données de test
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);

-- Création de la procédure stockée 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;

Vérification sur IBM i

Vérifier que la bibliothèque MDSALARY existe bien, qu'elle contient la table staff avec les données insérées, et la procédure MEDIAN_RESULT_SET sous forme de programme SQL (type *PGM) :

Étape 2 - Appeler la procédure stockée depuis .NET

L'environnement IBM i est prêt. Le projet est une Blazor Web App en .NET 8. Les packages suivants sont nécessaires :

dotnet add package Aumerial.Data.Nti
dotnet add package Dapper

Service de connexion

Un service DB2Service.cs centralise la gestion des connexions :

using Aumerial.Data.Nti;

public class DB2Service
{
    private readonly string _connectionString = "server=Server;user=User;password=Pwd;trim=true";

    public NTiConnection CreateConnection()
    {
        var conn = new NTiConnection(_connectionString);
        conn.Open();
        return conn;
    }
}

À enregistrer dans Program.cs :

builder.Services.AddSingleton();

Entité Employee

public class Employee
{
    public string Name { get; set; }
    public string Job { get; set; }
    public decimal Salary { get; set; }
}

Approche 1 - DataReader

L'approche classique avec DataReader donne un contrôle complet sur l'exécution et la lecture des résultats. Le paramètre de sortie medianSalary est défini explicitement, et les résultats sont lus séquentiellement ligne par ligne :

private decimal median;
private List employees = new();

private async Task LoadDataWithDataReader()
{
    using var conn = Db2Service.CreateConnection();
    using var cmd = new NTiCommand("MDSALARY.MEDIAN_RESULT_SET", conn);

    cmd.CommandType = System.Data.CommandType.StoredProcedure;

    var param = new NTiParameter();
    param.ParameterName = "medianSalary";
    param.Direction = System.Data.ParameterDirection.Output;
    cmd.Parameters.Add(param);

    using var reader = await cmd.ExecuteReaderAsync();

    median = Convert.ToDecimal(param.Value);

    while (await reader.ReadAsync())
    {
        employees.Add(new Employee
        {
            Name = reader.GetString(0),
            Job = reader.GetString(1),
            Salary = reader.GetDecimal(2)
        });
    }
}

Approche 2 - Dapper

Avec Dapper, le code est considérablement réduit. DynamicParameters gère le paramètre de sortie, et Dapper se charge de l'exécution et du mapping des résultats vers la liste d'objets Employee :

private decimal median;
private List employees = new();

private async Task LoadDataWithDapper()
{
    using var conn = Db2Service.CreateConnection();

    var parameters = new DynamicParameters();
    parameters.Add("medianSalary", dbType: DbType.Decimal, direction: ParameterDirection.Output);

    employees = (await conn.QueryAsync(
        "MDSALARY.MEDIAN_RESULT_SET",
        parameters,
        commandType: CommandType.StoredProcedure)).ToList();

    median = parameters.Get("medianSalary");
}

Affichage dans Blazor

Une fois les données récupérées, elles sont affichées dans un tableau Blazor :

Conclusion

Deux approches, un même résultat : DataReader pour un contrôle détaillé étape par étape, Dapper pour une implémentation plus concise. Dans les deux cas, NTi Data Provider permet d'intégrer des procédures stockées SQL IBM i dans une application .NET en quelques lignes de code, sans complexité ajoutée.

Quentin Destrade