Chiamare una procedura SQL su IBM i/DB2 for i in .NET con NTi

L'uso di stored procedure è una pratica diffusa per centralizzare e incapsulare la logica aziendale in un ambiente sicuro e ottimizzato. Consentono di spostare parte della logica applicativa sul server di database, riducendo la complessità del codice lato client, migliorando le prestazioni grazie alla riduzione del traffico di rete e migliorando la sicurezza grazie alla limitazione dell'accesso diretto alle tabelle.

Vediamo come chiamare una stored procedure SQL ospitata su un sistema IBM i da un'applicazione .NET, utilizzando NTi. Utilizzeremo una stored procedure tratta direttamente dalla documentazione ufficiale IBM i: DB2 for i SQL Reference - (pagina 1109).

Questa procedura calcola la mediana degli stipendi del personale e restituisce l'elenco dei dipendenti il cui stipendio è superiore alla mediana. Verranno esaminati due approcci alla chiamata da un'applicazione .NET:

• Il primo prevede l'uso diretto di un DataReader.
• Il secondo è sfruttare Dapper, un micro-ORM leggero che semplifica ulteriormente l'implementazione e l'uso dei risultati.

Passo 1 - Preparazione dell'ambiente IBM i

Prima di richiamare una stored procedure da .NET, dobbiamo capire esattamente cosa fa questa procedura, quali elementi sono necessari per eseguirla (tabelle, dati) e preparare il nostro ambiente.

La stored procedure che utilizzeremo proviene direttamente dal manuale ufficiale IBM i: DB2 for i SQL Reference - (pagina 1109). Ecco il codice SQL fornito:

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

Questa procedura calcola la retribuzione mediana dei dipendenti dell'azienda. Restituisce questo stipendio sotto forma di un parametro di output chiamato salario mediano e apre un cursore c2 che restituisce un elenco dettagliato dei dipendenti che percepiscono uno stipendio superiore alla mediana calcolata.

💡 In questo caso non c'è alcun parametro di input e la procedura calcola direttamente da una tabella specifica: la tabella staff.

Affinché la procedura funzioni, deve esistere una tabella denominata staff nello stesso schema in cui verrà creata la procedura. Deve contenere almeno una colonna DECIMALE salario e le altre colonne nome e lavoro necessarie per il cursore dei risultati.

Ora che sappiamo cosa fa la procedura e di cosa ha bisogno, possiamo preparare il nostro ambiente IBM i passo dopo passo.

1️⃣ Creazione della libreria

Creiamo una libreria MDSALARY per isolare i nostri elementi di test in modo ordinato. Eseguite il seguente comando dal vostro IBM i, sia tramite ACS, sia tramite lo schermo del 5250

CRTLIB MDSALARY

2️⃣ Definire il diagramma corrente

Si definisce quindi lo schema corrente come schema predefinito, in modo che tutti i comandi SQL successivi facciano automaticamente riferimento ad esso:

SET CURRENT SCHEMA = MDSALARY;

D'ora in poi, ogni tabella o procedura creata verrà automaticamente inserita nella LIB MDSALARY.

3️⃣ Creazione della tabella STAFF

Come abbiamo visto in precedenza, la stored procedure si aspetta una tabella specifica chiamata staff. Creiamola in base alle colonne richieste dal nostro esempio:

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

• Una colonna name per il nome del dipendente.
• Una colonna job per la sua funzione.
• Una colonna "salary" per il suo stipendio.

4️⃣ Inserimento di un set di dati di esempio

Per calcolare una retribuzione mediana pertinente, dobbiamo inserire alcuni dati rappresentativi in questa tabella:

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 completo pronto per l'esecuzione in ACS

Ecco uno script completo che si può copiare, incollare ed eseguire in un colpo solo dallo strumento "SQL Script Executor" di ACS

-- Creazione della libreria
CL: CRTLIB MDSALARY;

-- Definizione del diagramma attuale
SET CURRENT SCHEMA = MDSALARY;

-- Creazione della tabella STAFF
CREATE TABLE staff (
    name VARCHAR(50),
    job VARCHAR(50),
    salary DECIMAL(7,2)
);

-- Inserimento dei dati di prova
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);

-- Creazione della procedura memorizzata 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️⃣ Verifica su IBM i

Verificare che la libreria MDSALARY esista, che contenga la tabella staff con i dati inseriti e che contenga la stored procedure SQL denominata MEDIAN_RESULT_SET (tipo *PGM).

Passo 2 - Chiamare la stored procedure IBM i da .NET

Ora che il nostro ambiente IBM i è pronto, chiamiamo la procedura dalla nostra applicazione .NET. Creiamo un progetto BlazorWebApp in .NET 8 e installiamo i seguenti pacchetti da NuGet:

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

1️⃣ Creare un servizio di connessione

Per semplificare la gestione delle connessioni, abbiamo creato un semplice servizio 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;
    }
}

Quindi salvare questo servizio nel file Programma.cs.

builder.Services.AddSingleton();

2️⃣ Richiamo della stored procedure

Lo faremo:

• Definire un'entità che rappresenti i nostri dipendenti
• Chiamare la procedura memorizzata per recuperare i risultati
• Sfruttare questi risultati con due approcci: DataReader e Dapper
• Visualizzare i risultati in una tabella Blazor

3️⃣ Creazione dell'entità Dipendente

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

4️⃣ Metodo 1 - Approccio classico (DataReader)

In questo primo approccio classico, creiamo esplicitamente una connessione al server IBM i tramite il nostro servizio dedicato DB2Service, quindi configuriamo un comando NTi per chiamare la nostra stored procedure MEDIAN_RESULT_SET.

Definiamo chiaramente il parametro di output medianSalary, che recupera il salario mediano calcolato dalla procedura. Dopo aver eseguito il comando, un DataReader ci permette di leggere sequenzialmente ogni risultato restituito dalla procedura, trasformando ogni riga in un oggetto di tipo Employee. Infine, estraiamo direttamente il valore del parametro di output per scoprire l'esatta mediana.

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

    private async Task LoadDatawhithDataReader()
    {
         // Creazione della connessione tramite DB2Service
        using var conn = Db2Service.CreateConnection();
        using var cmd = new NTiCommand("MDSALARY.MEDIAN_RESULT_SET", conn);

        // Creare il comando per richiamare la stored procedure
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        // Definizione del parametro di output "medianSalary".
        var param = new NTiParameter();
        param.ParameterName = "medianSalary";
        param.Direction = System.Data.ParameterDirection.Output;

        // Aggiungere il parametro di uscita al comando
        cmd.Parameters.Add(param);

        // Eseguire il comando e recuperare i risultati tramite DataReader
        using var reader = await cmd.ExecuteReaderAsync();

        // Recuperare il salario mediano (parametro di uscita)

        median = Convert.ToDecimal(param.Value);

        // Leggere i dati di ogni dipendente dal DataReader
        while (await reader.ReadAsync())
        {
            employees.Add(new Employee
                {
                    Name = reader.GetString(0),
                    Job = reader.GetString(1),
                    Salary = reader.GetDecimal(2)
                });
        }
    }

5️⃣ Metodo 2 - Approccio semplificato (Dapper)

Il secondo approccio utilizza Dapper, un ORM leggero, per semplificare il codice necessario a richiamare la nostra stored procedure. È sufficiente definire il parametro di output usando DynamicParameters e Dapper si occupa automaticamente di aprire la connessione, eseguire il comando e recuperare direttamente i risultati sotto forma di un elenco di oggetti di tipo Employee. Infine, possiamo facilmente recuperare il valore del parametro di output corrispondente al salario mediano. Questo approccio semplifica notevolmente il codice, pur mantenendo prestazioni e leggibilità.

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

private async Task LoadDataWithDapper()
{
    // Creazione della connessione tramite DB2Service
    using var conn = Db2Service.CreateConnection();

     // Definire il parametro di uscita 'medianSalary' con DynamicParameters
    var parameters = new DynamicParameters();
    parameters.Add("medianSalary", dbType: DbType.Decimal, direction: ParameterDirection.Output);

    // Dapper gestisce automaticamente l'apertura e la chiusura della connessione e la lettura dei risultati.
    employees = (await conn.QueryAsync(
        "MDSALARY.MEDIAN_RESULT_SET",
        parameters,
        commandType: CommandType.StoredProcedure)).ToList();

     // Recupero diretto del salario mediano (parametro di uscita)
    median = parameters.Get("medianSalary");
}

6️⃣ Visualizzazione dei risultati in un componente Blazor

Una volta recuperati i dati, tutto ciò che dobbiamo fare è visualizzarli in modo ordinato nella nostra interfaccia Blazor, utilizzando una tabella.

Conclusione

Avete visto come richiamare una procedura memorizzata sul vostro IBM i da un'applicazione .NET con NTi: controllo dettagliato tramite un DataReader o implementazione rapida con Dapper. In entrambi i casi, NTi consente di integrare le stored procedure SQL in modo semplice ed efficiente nelle applicazioni .NET. Con poche righe di codice, si ottiene un risultato immediatamente utilizzabile e adeguato alle moderne pratiche di sviluppo .NET.

Quentin Destrade