SQL Queries on IBM i (AS/400) in C# (.NET) with NTi: SELECT, INSERT, UPDATE, DELETE

Introduction

Reading and writing DB2 for i data from a .NET application follows the exact same pattern as any other database with NTi Data Provider, the native ADO.NET provider for IBM i. No ODBC driver, no system configuration: a single NuGet package is all you need.

NTi is truly asynchronous end to end: this tutorial shows the async path first (OpenAsync, ExecuteReaderAsync, QueryAsync), and every method has its synchronous equivalent. Standard ADO.NET members are not re-documented here: see the ADO.NET overview at Microsoft.

Two approaches depending on your needs:

  • ADO.NET with NTiCommand: full control over query execution
  • Dapper: a lightweight micro ORM that integrates natively with NTi, for more concise code and automatic mapping to your C# objects

The examples are based on a RETAIL schema containing two tables:

ARTICLES

  • REF - VARCHAR(20) - product reference
  • LIBELLE - VARCHAR(100) - product label
  • STOCK - INTEGER - stock quantity
  • PRIX_UNIT - DECIMAL(10,2) - unit price

CLIENTS

  • ID_CLIENT - INTEGER - customer ID
  • NOM - VARCHAR(100) - customer name
  • VILLE - VARCHAR(50) - city
  • PAYS - VARCHAR(50) - country

Step 1: Install NTi and Dapper

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

Step 2: Open the connection

using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

💡 The NTi connection opens just like any other ADO.NET provider and works natively with Dapper, with no additional configuration. For a web application, enable pooling with pooling=true: see Connection string.

ADO.NET approach: NTiCommand

NTiCommand implements DbCommand: ExecuteReaderAsync to read, ExecuteNonQueryAsync to write, ExecuteScalarAsync for a single value, plus their synchronous equivalents ExecuteReader, ExecuteNonQuery and ExecuteScalar.

SELECT

using System;
using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT REF, LIBELLE, STOCK, PRIX_UNIT FROM RETAIL.ARTICLES";

using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine($"{reader.GetString(0)} | {reader.GetString(1)} | qty: {reader.GetInt32(2)} | {reader.GetDecimal(3)}");

💡 Every asynchronous method accepts a CancellationToken. By contract, cancellation breaks the connection: the call throws OperationCanceledException and the connection is no longer usable.

Parameterized queries: positional ? or named @name

Parameterized queries protect against SQL injection and handle types automatically. NTi accepts two marker styles:

  • Positional ?: the native DB2 for i style. Parameters bind in the order they are added to the collection; their name is ignored.
  • Named @name: NTi rewrites the query into positional markers, honoring string literals, quoted identifiers and SQL comments. Each parameter is looked up by name: the order in the collection does not matter, and the same name may appear several times in the query.

Positional ? markers

using System;
using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT REF, LIBELLE, STOCK FROM RETAIL.ARTICLES WHERE STOCK < ? AND PRIX_UNIT < ?";

cmd.Parameters.Add(new NTiParameter { Value = 50 });     // 1st ? marker
cmd.Parameters.Add(new NTiParameter { Value = 100.0m }); // 2nd ? marker

using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine($"{reader.GetString(0)} | {reader.GetString(1)} | qty: {reader.GetInt32(2)}");

Named @name markers

using System;
using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT REF, LIBELLE, STOCK FROM RETAIL.ARTICLES WHERE STOCK < @threshold AND PRIX_UNIT < @maxPrice";

cmd.Parameters.AddWithValue("@threshold", 50);
cmd.Parameters.AddWithValue("@maxPrice", 100.0m);

using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine($"{reader.GetString(0)} | {reader.GetString(1)} | qty: {reader.GetInt32(2)}");

Warning: the two styles never mix within the same query. NTi rejects it with an NTiException carrying the exact message: Cannot mix named (@name) and unnamed (?) parameters.

INSERT, UPDATE, DELETE

using System;
using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

// INSERT
var insert = conn.CreateCommand();
insert.CommandText = "INSERT INTO RETAIL.ARTICLES (REF, LIBELLE, STOCK, PRIX_UNIT) VALUES (@ref, @libelle, @stock, @prix)";
insert.Parameters.AddWithValue("@ref", "REF-031");
insert.Parameters.AddWithValue("@libelle", "128GB USB Drive");
insert.Parameters.AddWithValue("@stock", 200);
insert.Parameters.AddWithValue("@prix", 19.99m);
await insert.ExecuteNonQueryAsync();

// UPDATE
var update = conn.CreateCommand();
update.CommandText = "UPDATE RETAIL.ARTICLES SET STOCK = @stock WHERE REF = @ref";
update.Parameters.AddWithValue("@stock", 999);
update.Parameters.AddWithValue("@ref", "REF-031");
await update.ExecuteNonQueryAsync();

// DELETE
var delete = conn.CreateCommand();
delete.CommandText = "DELETE FROM RETAIL.ARTICLES WHERE REF = @ref";
delete.Parameters.AddWithValue("@ref", "REF-031");
await delete.ExecuteNonQueryAsync();

ExecuteScalar

using System;
using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

var count = conn.CreateCommand();
count.CommandText = "SELECT COUNT(*) FROM RETAIL.ARTICLES";
int total = Convert.ToInt32(await count.ExecuteScalarAsync());
Console.WriteLine($"Total articles: {total}");

var avg = conn.CreateCommand();
avg.CommandText = "SELECT AVG(PRIX_UNIT) FROM RETAIL.ARTICLES";
decimal averagePrice = Convert.ToDecimal(await avg.ExecuteScalarAsync());
Console.WriteLine($"Average price: {averagePrice}");

DataTable and DataSet

NTiDataAdapter loads results entirely into memory in a DataTable or DataSet. Useful for displaying data in a DataGrid or loading several tables at once. The framework's DataAdapter API is synchronous by nature.

DataTable

using System;
using System.Data;
using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
conn.Open();

var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT REF, LIBELLE, STOCK, PRIX_UNIT FROM RETAIL.ARTICLES";

var dataTable = new DataTable();
var adapter = new NTiDataAdapter(cmd);
adapter.Fill(dataTable);

Console.WriteLine($"{dataTable.Rows.Count} rows loaded");
foreach (DataRow row in dataTable.Rows)
    Console.WriteLine($"{row["REF"]} | {row["LIBELLE"]} | qty: {row["STOCK"]}");

DataSet

DataSet extends this approach to several tables in memory at the same time. Each table is named and accessible independently.

using System;
using System.Data;
using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
conn.Open();

var dataSet = new DataSet();

var cmdArticles = conn.CreateCommand();
cmdArticles.CommandText = "SELECT REF, LIBELLE, STOCK, PRIX_UNIT FROM RETAIL.ARTICLES";
new NTiDataAdapter(cmdArticles).Fill(dataSet, "ARTICLES");

var cmdClients = conn.CreateCommand();
cmdClients.CommandText = "SELECT ID_CLIENT, NOM, VILLE, PAYS FROM RETAIL.CLIENTS";
new NTiDataAdapter(cmdClients).Fill(dataSet, "CLIENTS");

Console.WriteLine($"{dataSet.Tables["ARTICLES"].Rows.Count} articles");
Console.WriteLine($"{dataSet.Tables["CLIENTS"].Rows.Count} clients");

Dapper approach

Dapper is a lightweight micro ORM developed by the Stack Overflow team. It plugs directly into the NTi connection with no configuration required and automatically maps SQL results to your C# classes.

Compared to NTiCommand, the code is more concise, more readable, and eliminates all the repetitive column-by-column reading boilerplate. Dapper adds extension methods directly on the connection: QueryAsync<T> for SELECT (or Query<T> synchronously) and ExecuteAsync for INSERT, UPDATE, DELETE. Parameters are passed via an anonymous object, and Dapper always emits named @name markers.

This is the recommended approach for most modern .NET projects accessing DB2 for i with NTi.

C# model and SELECT

QueryAsync<Article> maps each row to an Article instance (column/property matching is case-insensitive), with or without parameters; synchronously, it is Query<Article>:

using System;
using System.Linq;
using Aumerial.Data.Nti;
using Dapper;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

// Plain SELECT
var articles = (await conn.QueryAsync
(
    "SELECT REF, LIBELLE, STOCK, PRIX_UNIT FROM RETAIL.ARTICLES")).ToList();
Console.WriteLine($"{articles.Count} articles");

// Parameterized SELECT: anonymous object
var lowStock = await conn.QueryAsync
(
    "SELECT REF, LIBELLE, STOCK, PRIX_UNIT FROM RETAIL.ARTICLES WHERE STOCK < @threshold",
    new { threshold = 50 });

foreach (var article in lowStock)
    Console.WriteLine($"{article.REF} | {article.LIBELLE} | qty: {article.STOCK}");

public class Article
{
    public string REF { get; set; }
    public string LIBELLE { get; set; }
    public int STOCK { get; set; }
    public decimal PRIX_UNIT { get; set; }
}

INSERT, UPDATE, DELETE

With Dapper, write operations take a single line. Parameter mapping is automatic: the property names of the anonymous object map directly to the @name markers of the SQL query.

using System;
using Aumerial.Data.Nti;
using Dapper;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

// INSERT
await conn.ExecuteAsync(
    "INSERT INTO RETAIL.ARTICLES (REF, LIBELLE, STOCK, PRIX_UNIT) VALUES (@REF, @LIBELLE, @STOCK, @PRIX_UNIT)",
    new { REF = "REF-031", LIBELLE = "128GB USB Drive", STOCK = 200, PRIX_UNIT = 19.99m });

// UPDATE
await conn.ExecuteAsync(
    "UPDATE RETAIL.ARTICLES SET STOCK = @STOCK WHERE REF = @REF",
    new { STOCK = 999, REF = "REF-031" });

// DELETE
await conn.ExecuteAsync(
    "DELETE FROM RETAIL.ARTICLES WHERE REF = @REF",
    new { REF = "REF-031" });

Multiple result sets: NextResult

A DB2 for i stored procedure can return several result sets (DYNAMIC RESULT SETS clause). On the .NET side, you walk through them with NextResultAsync (or NextResult synchronously) on the DataReader.

CREATE OR REPLACE PROCEDURE RETAIL.SALES_REPORT()
  DYNAMIC RESULT SETS 2
  LANGUAGE SQL
BEGIN
  DECLARE articles CURSOR WITH RETURN FOR
    SELECT REF, LIBELLE, STOCK FROM RETAIL.ARTICLES;
  DECLARE clients CURSOR WITH RETURN FOR
    SELECT ID_CLIENT, NOM, VILLE FROM RETAIL.CLIENTS;
  OPEN articles;
  OPEN clients;
END;
using System;
using Aumerial.Data.Nti;

using var conn = new NTiConnection("server=serverName;user=userName;password=password");
await conn.OpenAsync();

var cmd = conn.CreateCommand();
cmd.CommandText = "CALL RETAIL.SALES_REPORT()";

using var reader = await cmd.ExecuteReaderAsync();

// 1st result set: the articles
while (await reader.ReadAsync())
    Console.WriteLine($"Article: {reader.GetString(0)} ({reader.GetInt32(2)} in stock)");

// 2nd result set: the customers
if (await reader.NextResultAsync())
{
    while (await reader.ReadAsync())
        Console.WriteLine($"Customer: {reader.GetString(1)} ({reader.GetString(2)})");
}

💡 For IN/OUT parameters and calling stored procedures with Dapper, see the Stored procedure tutorial.

What's next?

Documentation

Tutorials

Aumerial

© 2026 Aumerial SAS - NTi Data Provider documentation

Reconnecting to the server...

The connection to the server was lost. The page will reload.