Call an IBM i SQL stored procedure from .NET with NTi

Quentin DESTRADE

Monday 24 March 2025

  • Tutorials

We'll look at how to call a SQL stored procedure hosted on an IBM i system from a .NET application, using NTi.

Main image of the article “Call an IBM i SQL stored procedure from .NET with NTi”

Introduction

The use of stored procedures is a widespread practice for centralizing and encapsulating business logic in a secure, optimized environment. They enable part of the application logic to be moved to the database server, reducing the complexity of client-side code, improving performance by reducing network traffic, and enhancing security by limiting direct access to tables.

We'll look at how to call a SQL stored procedure hosted on an IBM i system from a .NET application, using NTi. We will use a stored procedure taken directly from the official documentation IBM i: DB2 for i SQL Reference - (page 1109).

This procedure calculates the median of staff salaries and returns the list of employees whose salary is higher than this median.

We'll look at two approaches to call from a .NET application:

  • The first involves the direct use of a DataReader.
  • The second is by taking advantage of Dapper, a lightweight micro-ORM that further simplifies implementation and exploitation of results.

Step 1 - Preparing the IBM i environment

Before calling a stored procedure from .NET, we need to understand exactly what the procedure does, what elements are required to run it (tables, data), and prepare our environment.

The stored procedure we are going to use comes directly from the official manual IBM i: DB2 for i SQL Reference - (page 1109). Here is the SQL code provided:

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 the company's employees. It returns this salary as an out parameter named medianSalary, and opens a c2 cursor that returns a detailed list of employees earning a salary above this calculated median.

💡 There are no input parameters here, and the procedure calculates directly from a specific table: the staff table.

For the procedure to work, a table named staff must exist in the same schema where the procedure will be created. It must contain at least one DECIMAL salary column and the other name and job columns required for the results cursor.

Now that we know what the procedure does and what it needs, we can prepare our IBM i environment step by step.

1️⃣ Library creation

We create an MDSALARY library to isolate our test items neatly. Run the following command from your IBM i, either via ACS, or the 5250 screen

CRTLIB MDSALARY

2️⃣ Define the current schema

We then define the current schema as the default, 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 LIB.

3️⃣ Create the STAFF table

As we saw earlier, the stored procedure expects a specific table named staff. Let's create it according to the columns required by our example:

CREATE TABLE staff (
    name VARCHAR(50),
    job VARCHAR(50),
    salary DECIMAL(7,2)
);
  • A name column for the employee's name.
  • A job column for the job title.
  • A salary column for the employee's salary.

4️⃣ Insertion of an example dataset

To calculate a relevant median salary, we need to insert some representative data into this 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️⃣ Complete script ready to run in ACS

Here's a complete script that you can copy, paste and run in one go from ACS's "SQL Script Executor" tool

-- Library creation
CL: CRTLIB MDSALARY;

-- Current schematic definition
SET CURRENT SCHEMA = MDSALARY;

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

-- Inserting test data
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);

-- Creation of stored procedure 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️⃣ Verification on IBM i

Check on your IBM i that the MDSALARY library exists, that it contains the staff table with the inserted data, and that it contains the stored procedure in the form of a SQL program named MEDIAN_RESULT_SET (Type *PGM).

article 14

Step 2 - Call the IBM i stored procedure from .NET

Now that our IBM i environment is ready, let's call the procedure from our .NET application. We create a BlazorWebApp project in .NET 8, and install the following packages from NuGet:

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

1️⃣ Create a Connection service

To simplify connection management, we create a simple service 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;
    }
}

Then save this service in your Program.cs.

builder.Services.AddSingleton<DB2Service>();

2️⃣ Call the stored procedure

We will:

  • Define an entity representing our employees
  • Call the stored procedure to retrieve the results
  • Exploit these results with two approaches: DataReader and Dapper
  • Display results in a Blazor table

3️⃣ Employee entity creation

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

4️⃣ Method 1 - Classic approach (DataReader)

In this first, classic approach, we explicitly create a connection to the IBM i server via our dedicated DB2Service service, then configure an NTi command to call our MEDIAN_RESULT_SET stored procedure.

We clearly define the medianSalary output parameter, which will retrieve the median salary calculated by the procedure. After executing the command, a DataReader allows us to sequentially read each result returned by the procedure, transforming each line into an object of type Employee. Finally, we directly extract the value of the output parameter to find the exact median.

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

    private async Task LoadDatawhithDataReader()
    {
         // Create connection via DB2Service
        using var conn = Db2Service.CreateConnection();
        using var cmd = new NTiCommand("MDSALARY.MEDIAN_RESULT_SET", conn);

        // Create the command to call the stored procedure
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        // Definition of 'medianSalary' output parameter
        var param = new NTiParameter();
        param.ParameterName = "medianSalary";
        param.Direction = System.Data.ParameterDirection.Output;

        // We add the output parameter to the command
        cmd.Parameters.Add(param);

        // Execute command and retrieve results via DataReader
        using var reader = await cmd.ExecuteReaderAsync();

        // Median wage retrieval (output parameter)
        median = Convert.ToDecimal(param.Value);

        // Reading employee data from the DataReader
        while (await reader.ReadAsync())
        {
            employees.Add(new Employee
                {
                    Name = reader.GetString(0),
                    Job = reader.GetString(1),
                    Salary = reader.GetDecimal(2)
                });
        }
    }

5️⃣ Method 2 - Simplified approach (Dapper)

The second approach uses Dapper, a lightweight ORM, to simplify the code needed to call our stored procedure. Simply define the output parameter with DynamicParameters, and Dapper will automatically handle connection opening, command execution and direct retrieval of results in the form of a list of Employee objects. Finally, it's easy to retrieve the value of the output parameter corresponding to the median wage. This approach greatly simplifies the code, while maintaining performance and readability.

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

private async Task LoadDataWithDapper()
{
    // Create connection via DB2Service
    using var conn = Db2Service.CreateConnection();

     // Set the 'medianSalary' output parameter with DynamicParameters
    var parameters = new DynamicParameters();
    parameters.Add("medianSalary", dbType: DbType.Decimal, direction: ParameterDirection.Output);

    // Dapper automatically opens and closes the connection, and reads the results
    employees = (await conn.QueryAsync<Employee>(
        "MDSALARY.MEDIAN_RESULT_SET",
        parameters,
        commandType: CommandType.StoredProcedure)).ToList();

     // Direct retrieval of median salary (output parameter)
    median = parameters.Get<decimal>("medianSalary");
}

6️⃣ Displaying results in a Blazor component

Once the data has been retrieved, all we need to do is display it cleanly in our Blazor interface via a table.

article 14

Conclusion

You've seen how to call a procedure stored on your IBM i from a .NET application with NTi: detailed control via a DataReader, or rapid implementation with Dapper. In both cases, NTi lets you integrate SQL stored procedures simply and efficiently into your .NET applications. In just a few lines of code, you'll get a result that's immediately usable and adapted to modern .NET development practices.

Back