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.

This tutorial covers all the essential SQL operations from C# with NTi: reads, writes, parameterized queries, and the different approaches available 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 System.Data;
using Dapper;

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

💡The NTi connection opens just like any other ADO.NET provider. It is natively compatible with Dapper with no additional configuration required.

Classic approach - ADO.NET with NTiCommand

NTiCommand is the standard ADO.NET approach for running SQL queries on IBM i with NTi. It gives you full control over execution and covers every use case: reading data with ExecuteReader, writing with ExecuteNonQuery for INSERT, UPDATE and DELETE, and retrieving a single value with ExecuteScalar for aggregates such as COUNT or AVG.

SELECT

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

while (reader.Read())
{
    Console.WriteLine($"{reader.GetString(0)} | {reader.GetString(1)} | qty: {reader.GetInt32(2)} | {reader.GetDecimal(3)}");
}

Parameterized SELECT

Named parameters are added via NTiParameter. This approach protects against SQL injection and handles types automatically.

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

var param = new NTiParameter();
param.ParameterName = "@seuil";
param.Value = 50;
cmd.Parameters.Add(param);

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

INSERT

var cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO RETAIL.ARTICLES (REF, LIBELLE, STOCK, PRIX_UNIT) VALUES (@ref, @libelle, @stock, @prix)";

var p1 = new NTiParameter(); p1.ParameterName = "@ref";     p1.Value = "REF-031";
var p2 = new NTiParameter(); p2.ParameterName = "@libelle"; p2.Value = "128GB USB Drive";
var p3 = new NTiParameter(); p3.ParameterName = "@stock";   p3.Value = 200;
var p4 = new NTiParameter(); p4.ParameterName = "@prix";    p4.Value = 19.99m;

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
cmd.ExecuteNonQuery();

UPDATE

var cmd = conn.CreateCommand();
cmd.CommandText = "UPDATE RETAIL.ARTICLES SET STOCK = @stock WHERE REF = @ref";

var p1 = new NTiParameter(); p1.ParameterName = "@stock"; p1.Value = 999;
var p2 = new NTiParameter(); p2.ParameterName = "@ref";   p2.Value = "REF-031";

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.ExecuteNonQuery();

DELETE

var cmd = conn.CreateCommand();
cmd.CommandText = "DELETE FROM RETAIL.ARTICLES WHERE REF = @ref";

var p1 = new NTiParameter(); p1.ParameterName = "@ref"; p1.Value = "REF-031";
cmd.Parameters.Add(p1);
cmd.ExecuteNonQuery();

ExecuteScalar

var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM RETAIL.ARTICLES";
int total = (int)cmd.ExecuteScalar();
Console.WriteLine($"Total articles: {total}");

var cmdAvg = conn.CreateCommand();
cmdAvg.CommandText = "SELECT AVG(PRIX_UNIT) FROM RETAIL.ARTICLES";
decimal prixMoyen = (decimal)cmdAvg.ExecuteScalar();
Console.WriteLine($"Average price: {prixMoyen} ");

DataTable and DataSet

DataTable and DataSet load results entirely into memory via NTiDataAdapter. Useful for displaying data in a DataGrid, manipulating results on the .NET side, or loading multiple tables at once.

DataTable

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 multiple tables in memory at the same time. Each table is named and accessible independently.

 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.

In practice, Dapper adds extension methods directly on the connection, Query<T> for SELECT and Execute for INSERT, UPDATE, DELETE. Parameters are passed via an anonymous object.

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

Define the C# model

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

SELECT

var articles = conn.Query<Article>(
    "SELECT REF, LIBELLE, STOCK, PRIX_UNIT FROM RETAIL.ARTICLES"
);
Console.WriteLine($"{articles.Count()} articles");

Parameterized SELECT

var lowStock = conn.Query<Article>(
    "SELECT REF, LIBELLE, STOCK FROM RETAIL.ARTICLES WHERE STOCK < @seuil",
    new { seuil = 50 }
);
foreach (var a in lowStock)
    Console.WriteLine($"{a.REF} | {a.LIBELLE} | qty: {a.STOCK}");

INSERT, UPDATE, DELETE

With Dapper, write operations are handled in a single line. Parameter mapping is automatic, and the property names of the anonymous object map directly to the SQL query parameters.

// INSERT
conn.Execute(
    "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
conn.Execute(
    "UPDATE RETAIL.ARTICLES SET STOCK = @STOCK WHERE REF = @REF",
    new { STOCK = 999, REF = "REF-031" }
);

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

What's next?

Documentation

Overview Quick Start Guide Entity Framework Core 8 NTi Toolbox

Tutorials

Run a CL Command Call a Program Stored Procedure Call a System API

Aumerial

Aumerial website Sign in NuGet Privacy Policy Legal Notice
© 2026 Aumerial SAS - NTi Data Provider documentation