Call IBM i SQL Stored Procedure in C# (.NET) with NTi

Introduction

Stored procedures are a widely adopted practice for centralizing and encapsulating business logic in a secure, optimized environment. They shift part of the application logic to the database server, reducing client-side complexity, improving performance by cutting down network traffic, and strengthening security by restricting direct table access.

This tutorial shows how to call an SQL stored procedure from a C# (.NET) application using NTi.

The procedure used in this example comes from the official IBM i documentation: DB2 for i SQL Reference (page 1141). It calculates the median salary across the workforce and returns the list of employees whose salary exceeds that median.

Two .NET calling approaches are covered:

Step 1 - Prepare the IBM i environment

Before calling the procedure from .NET, you need to understand what it does, what elements it requires (tables, data), and prepare the IBM i environment.

Here is the SQL code for the procedure, taken from the DB2 for i SQL Reference manual (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

This procedure calculates the median salary of employees. It returns this salary through an output parameter medianSalary, and opens a cursor c2 that returns the list of employees whose salary exceeds this median.

Parameter	Type	Direction	Description
medianSalary	Decimal(7,2)	OUT	Median salary calculated by the procedure

💡 There is no input parameter, the procedure performs its calculation directly from the staff table.

For the procedure to work, a staff table must exist in the same schema, with at least the columns salary (DECIMAL), name and job.

Create the library

Create a MDSALARY library to isolate the test elements, from ACS

CRTLIB MDSALARY

Set the current schema

Set the current schema so that all subsequent SQL commands automatically refer to it:

SET CURRENT SCHEMA = MDSALARY;

From now on, every table or procedure we create will automatically be placed in the MDSALARY library.

Create the STAFF table

Create the staff table with the required columns:

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

name - employee name
job - job title
salary - salary

Insert a dataset

Insert some representative data to calculate a meaningful median salary:

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

Complete script ready to run in ACS

Copy and paste this script into the ACS SQL Script Runner and run it all at once:

-- Library creation
CL: CRTLIB MDSALARY;

-- Current schema definition
SET CURRENT SCHEMA = MDSALARY;

-- STAFF table creation
CREATE TABLE staff (
    name VARCHAR(50),
    job VARCHAR(50),
    salary DECIMAL(7,2)
);

-- Test data insertion
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);

-- MEDIAN_RESULT_SET stored procedure creation
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;

Check on IBM i

Verify that the MDSALARY library exists, that it contains the staff table with the inserted data, and the MEDIAN_RESULT_SET procedure (type *PGM).

5250 screen

Step 2 - Call the stored procedure from .NET

Create a Blazor Web App project in .NET 8 and install the following packages:

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

Create a connection service

Create a DB2Service.cs service to centralize connection management:

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

Then register this service in Program.cs:

builder.Services.AddSingleton();

Create the Employee entity

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

Method 1 - Classic approach (DataReader)

Explicitly create a connection via DB2Service, configure an NTi command to call MEDIAN_RESULT_SET, define the medianSalary output parameter, then read the results through a 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)
                });
        }
    }

Method 2 - Simplified approach (Dapper)

With Dapper, define the output parameter via DynamicParameters. Dapper automatically handles execution and maps the results directly into a list of Employee objects:

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

Display the results in a Blazor component

.NET App screen

What's next?

Call a program - RPG program call with input/output parameters
Execute a CL command - run a CL command and handle errors
Connection - connection string, pool, MFA
NTiConnection - complete class reference