Appeler une procédure stockée SQL IBM i en C# (.NET) avec NTi

Introduction

Les procédures stockées permettent de centraliser et d'encapsuler la logique métier dans un environnement sécurisé et optimisé. Elles déplacent une partie de la logique applicative vers le serveur de base de données, réduisant la complexité du code côté client, améliorant les performances en diminuant le trafic réseau, et renforçant la sécurité en limitant l'accès direct aux tables.

Ce tutoriel montre comment appeler une procédure stockée SQL IBM i depuis une application C# (.NET) en utilisant NTi.

La procédure utilisée est issue de la documentation officielle IBM i : DB2 for i SQL Reference (page 1141). 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.

Deux approches d'appel depuis .NET sont présentées :

Étape 1 - Préparer l'environnement IBM i

Avant d'appeler la procédure depuis .NET, il faut comprendre ce qu'elle fait, quels éléments elle nécessite (tables, données), et préparer l'environnement IBM i.

Voici le code SQL de la procédure, issu du manuel DB2 for i SQL Reference (page 1141) :

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 dépasse cette médiane.

Paramètre	Type	Direction	Description
medianSalary	Decimal(7,2)	OUT	Salaire médian calculé par la procédure

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

Pour que la procédure fonctionne, une table staff doit exister dans le même schéma, avec au moins les colonnes salary (DECIMAL), name et job.

Créer la bibliothèque

Créez une bibliothèque MDSALARY pour isoler les éléments de test, depuis ACS

CRTLIB MDSALARY

Définir le schéma courant

Définissez le schéma courant 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.

Créer la table STAFF

Créez la table staff selon les colonnes requises :

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

name - nom de l'employé
job - fonction
salary - salaire

Insérer un jeu de données

Insérez quelques données représentatives pour calculer un salaire médian pertinent :

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

Copiez-collez ce script dans l'outil Exécuteur de scripts SQL d'ACS et exécutez le en une seule fois :

-- 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érifier sur l'IBM i

Vérifiez que la bibliothèque MDSALARY existe, qu'elle contient la table staff avec les données insérées, et la procédure MEDIAN_RESULT_SET (type *PGM).

Ecran 5250

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

Créez un projet Blazor Web App en .NET 8 et installez les packages suivants :

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

Créer un service de connexion

Créez un service DB2Service.cs pour centraliser la gestion des connexions :

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

Enregistrez ensuite ce service dans Program.cs :

builder.Services.AddSingleton();

Créer l'entité Employee

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

Méthode 1 - Approche classique (DataReader)

Créez explicitement une connexion via DB2Service, configurez une commande NTi pour appeler MEDIAN_RESULT_SET, définissez le paramètre de sortie medianSalary, puis lisez les résultats via un DataReader :

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

Méthode 2 - Approche simplifiée (Dapper)

Avec Dapper, définissez le paramètre de sortie via DynamicParameters. Dapper gère automatiquement l'exécution et mappe les résultats directement en 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");
}

Afficher les résultats dans un composant Blazor

Ecran Appli .NET

Et maintenant ?

Appeler un programme - appel de programme RPG avec paramètres d'entrée/sortie
Exécuter une commande CL - exécuter une commande CL et gérer les erreurs
Connexion - chaîne de connexion, pool, MFA
NTiConnection - référence complète de la classe de connexion