Appeler une procédure stockée SQL IBM i depuis .NET avec NTi

Quentin DESTRADE

lundi 24 mars 2025

  • Tutoriels

Nous allons voir comment appeler une procédure stockée SQL hébergée sur un système IBM i depuis une application .NET, avec NTi.

Image principale de l’article « Appeler une procédure stockée SQL IBM i depuis .NET avec NTi »

Introduction

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.

Nous allons voir comment appeler une procédure stockée SQL hébergée sur un système IBM i depuis une application .NET, avec NTi. Nous utiliserons une procédure stockée directement issue de la documentation officielle IBM i: DB2 for i SQL Reference - (page 1109).

Cette procédure calcule la médiane des salaires du personnel et retourne la liste des employés dont le salaire est supérieur à cette médiane.

Nous examinerons deux approches d'appel depuis une application .NET :

  • La première avec l'utilisation directe d'un DataReader.
  • La seconde en tirant parti de Dapper, un micro-ORM léger permettant de simplifier encore davantage l'implémentation et l'exploitation des résultats.

Etape 1 - Préparation de l'environnement IBM i

Avant d'appeler une procédure stockée depuis .NET, il faut déjà comprendre précisément ce que fait cette procédure, quels sont les éléments nécessaires à son execution (tables, données), et préparer notre environnement.

La procédure stockée que nous allons utiliser provient directement du manuel officiel IBM i: DB2 for i SQL Reference - (page 1109). Voici le code SQL fourni:

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 de l'entreprise. Elle renvoie ce salaire sous forme d'un paramètre de sortie (out) nommé medianSalary, et ouvre un curseur c2 qui retourne la liste détaillée des employés gagnant un salaire supérieur à cette médiane calculée.

💡 Il n'y a pas de paramètre en entrée ici, la procédure effectue directement son calcul à partir d'une table spécifique: la table staff.

Pour que la procédure fonctionne, il faut donc qu'une table nommée staff existe dans le même schéma où la procédure sera créée. Elle doit contenir au moins une colonne salary de type DECIMAL et dautres colonnes name et job nécessaires pour le curseur de résultats.

Maintenant que l'on sait ce que fait la procédure et ce dont elle a besoin, on prépare notre environnement IBM i étape par étape.

1️⃣ Création de la bibliothèque

On crée une bibliothèque MDSALARY pour isoler proprement nos éléments de test. Exécutez la commande suivant depuis votre IBM i, soit via ACS, ou l'écran 5250

CRTLIB MDSALARY

2️⃣ Définir le schéma courant

On définit ensuite le schéma courant comme schéma par défaut pour que toutes les commandes SQL suivantes s'y réfèrent automatiquement:

SET CURRENT SCHEMA = MDSALARY;

Désormais, chaque table, ou procédure que nous créons seront automatiquement placée dans la LIB MDSALARY.

3️⃣ Création de la table STAFF

On l'a vu précédemment, La procédure stockée attend une table spécifique nommée staff. Créons-la selon les colonnes requises par notre exemple :

CREATE TABLE staff (
    name VARCHAR(50),
    job VARCHAR(50),
    salary DECIMAL(7,2)
);
  • Une colonne name pour le nom de l’employé.
  • Une colonne job pour sa fonction.
  • Une colonne salary pour son salaire.

4️⃣ Insertion d'un jeu de données d’exemple

Pour calculer un salaire médian pertinent, nous devons insérer quelques données représentatives dans cette table :

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️⃣ Script complet prêt à exécuter dans ACS

Voici maintenant un script complet que vous pouvez directement copier-coller et exécuter 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;

6️⃣ Vérification sur IBM i

Vérifiez sur votre IBM i que la bibliotèque MDSALARY existe bien, qu'elle contient la table staff avec les données insérées, et qu'elle contient la procédure stockée sous forme de programme SQL nommé MEDIAN_RESULT_SET (Type *PGM).

article 14

Etape 2 - Appeler la procédure stockée IBM i depuis .NET

Maintenant que notre environnement IBM i est prêt, passons à l'appel de la procédure depuis notre application .NET. On crée un projet BlazorWebApp en .NET 8, et nous installons depuis NuGet les packages suivant:

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

1️⃣ Création d'un service de connexion

Pour simplifier la gestion des connexions, nous créons un service simple DB2Service.cs

using Aumerial.Data.Nti;

public class DB2Service
{
    private readonly string _connectionString = "server=MON_SERVER;user=MON_USER;password=MON_MDP;trim=true";

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

Enregistrer ensuite ce service dans votre Program.cs

builder.Services.AddSingleton<DB2Service>();

2️⃣ Appel de la procédure stockée

Nous allons:

  • Définir une entité représentant nos employés
  • Appeler la procédure stockée pour récupérer les résultats
  • Exploiter ces résultats avec deux approches: DataReader et Dapper
  • Afficher les résultats dans un tableau Blazor

3️⃣ Création de l'entité Employee

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

4️⃣ Méthode 1 - Approche classique (DataReader)

Dans cette première approche, classique, nous créons explicitement une connexion au serveur IBM i via notre service dédié DB2Service, puis nous configurons une commande NTi pour appeler notre procédure stockée MEDIAN_RESULT_SET.

On définit clairement le paramètre de sortie medianSalary, qui récupérera le salaire médian calculé par la procédure. Après l'exécution de la commande, un DataReader nous permet de lire séquentiellement chaque résultat renvoyé par la procédure, transformant chaque ligne en objet de type Employee. Enfin, on extrait directement la valeur du paramètre de sortie pour connaître la médiane exacte.

private decimal median;
private List<Employee> employees = new();

    private async Task LoadDatawhithDataReader()
    {
         // Création de la connexion via DB2Service
        using var conn = Db2Service.CreateConnection();
        using var cmd = new NTiCommand("MDSALARY.MEDIAN_RESULT_SET", conn);

        // Création de la commande pour appeler la procédure stockée
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        // Définition du paramètre de sortie 'medianSalary'
        var param = new NTiParameter();
        param.ParameterName = "medianSalary";
        param.Direction = System.Data.ParameterDirection.Output;

        // On ajoute le paramètre de sortie à la commande
        cmd.Parameters.Add(param);

        // Exécution de la commande et récupération des résultats via DataReader
        using var reader = await cmd.ExecuteReaderAsync();

        // Récupération du salaire médian (paramètre de sortie)

        median = Convert.ToDecimal(param.Value);

        // Lecture des données de chaque employé depuis le DataReader
        while (await reader.ReadAsync())
        {
            employees.Add(new Employee
                {
                    Name = reader.GetString(0),
                    Job = reader.GetString(1),
                    Salary = reader.GetDecimal(2)
                });
        }
    }

5️⃣ Méthode 2 - Approche simplifiée (Dapper)

La seconde approche s'appuie sur Dapper, un ORM léger, pour simplifier le code nécessaire à l'appel de notre procédure stockée. On définit simplement le paramètre de sortie avec DynamicParameters, puis Dapper se charge automatiquement de gérer l'ouverture de la connexion, l'exécution de la commande et la récupération directe des résultats sous forme d'une liste d'objets de type Employee. Enfin, on récupère facilement la valeur du paramètre de sortie correspondant au salaire médian. Cette approche simplifie grandement le code tout en conservant performance et lisibilité.

private decimal median;
private List<Employee> employees = new();

private async Task LoadDataWithDapper()
{
    // Création de la connexion via DB2Service
    using var conn = Db2Service.CreateConnection();

     // Définition du paramètre de sortie 'medianSalary' avec DynamicParameters
    var parameters = new DynamicParameters();
    parameters.Add("medianSalary", dbType: DbType.Decimal, direction: ParameterDirection.Output);

    // Dapper gère automatiquement l'ouverture et la fermeture de la connexion, ainsi que la lecture des résultats
    employees = (await conn.QueryAsync<Employee>(
        "MDSALARY.MEDIAN_RESULT_SET",
        parameters,
        commandType: CommandType.StoredProcedure)).ToList();

     // Récupération directe du salaire médian (paramètre de sortie)
    median = parameters.Get<decimal>("medianSalary");
}

6️⃣ Affichage des résultats dans un composant Blazor

Une fois les donnes récupérées, il suffit de les afficher proprement dans notre interface Blazor au travers d'un tableau.

article 14

Conclusion

Vous avez vu concrètement comment appeler une procédure stockée sur votre IBM i depuis une application .NET avec NTi: contrôle détaillé via un DataReader, ou rapidité d'implémentation avec Dapper. Dans les deux cas, NTi vous permet d'intégrer simplement et efficacement vos procédures stockées SQL à vos applications .NET. En quelques lignes de code, vous obtenez un résultat immédiatement exploitable et adapté aux pratiques de développement .NET moderne.

Retour