Connection

NTiConnection implements the standard DbConnection contract. Opening, CreateCommand, transactions, GetSchema, and events all work the same way as with any other ADO.NET provider. This page does not redocument that shared contract, it covers what is specific to NTi and to IBM i.

Lifecycle: configuration locked at open

All configuration, the connection string, the properties, the MFA providers, has to be set before you call Open or OpenAsync. Once the connection is open, it stops moving. The physical session created at that point, which may later be handed back to the pool for reuse, keeps running with that initial configuration even if you go on changing properties on the NTiConnection object afterward: those later changes simply have no effect. To apply a new setting, close the connection and reopen it with the configuration you want, or create a new one.

The ADO.NET contract does carve out one exception to this rule. The current schema can be changed on the fly, with ChangeDatabase or ChangeDatabaseAsync, the equivalent of a SET CURRENT SCHEMA.

using System;
using Aumerial.Data.Nti;

await using var connection = new NTiConnection(
    "server=MYIBMI;user=MYUSER;password=MYPASSWORD;default schema=MYLIB");
await connection.OpenAsync();
Console.WriteLine(connection.Database);           // MYLIB
await connection.ChangeDatabaseAsync("OTHERLIB"); // SET CURRENT SCHEMA on the fly

The synchronous ChangeDatabase variant exists on every target. ChangeDatabaseAsync is available everywhere except .NET Framework, whose base class does not expose it.

Open and OpenAsync: real cancellation

OpenAsync is asynchronous end to end, not just on the surface. Waiting for a session from the pool, the TCP or TLS connection, signon, and any MFA factor all honor the caller's cancellation token, without ever falling back to sync-over-async behind the scenes. Open remains available for synchronous code, and follows exactly the same protocol path, just without the asynchronous part.

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

using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));
await using var connection = new NTiConnection(
    "server=MYIBMI;user=MYUSER;password=MYPASSWORD;pooling=true");
await connection.OpenAsync(cts.Token);

One detail matters here in particular. Cancelling the token while an operation is in flight breaks the connection, and that is by design. The network frame that was in transit is lost, and NTi refuses to silently resynchronize the session, since that could leave it in an inconsistent state. The call therefore throws an OperationCanceledException carrying the caller's token, and the connection has to be reopened before you can keep working. With connection pooling turned on, that reopening costs almost nothing, since another session already sitting in the pool simply takes over.

Pooling: off by default

Connection pooling is off by default. You turn it on with pooling=true, which is strongly recommended for web workloads. Once it is on, Open and OpenAsync pick up an already validated session from the pool instead of redoing the whole TCP, TLS, and signon handshake. Close and DisposeAsync, in turn, hand the session back to the pool instead of actually closing it.

Each distinct configuration gets its own pool. Client identity is part of that pool's key, through application name, client accounting, client user identifier, and client program identifier. Two distinct identities therefore never share the same pool.

using Aumerial.Data.Nti;

var connectionString =
    "server=MYIBMI;user=MYUSER;password=MYPASSWORD;pooling=true;max pool size=20";

await using (var connection = new NTiConnection(connectionString))
{
    await connection.OpenAsync();  // picks up a pooled session, or opens one
}                                  // DisposeAsync hands the session back to the pool

// Programmatic purge
await using var probe = new NTiConnection(connectionString);
NTiConnection.ClearPool(probe);    // clears the pool matching this configuration
NTiConnection.ClearAllPools();     // clears every pool in the process

Timeouts: unlimited by default

Every timeout is unlimited by default, with 0 meaning infinite. Neither opening the connection nor executing a command is bounded until you ask for it explicitly. Three levers let you set a limit:

connect timeout, in seconds, bounds how long opening the connection can take.
CommandTimeout, also in seconds, is set per command. It's the standard property from the DbCommand contract, and it stays unlimited by default (0).
The cancellation token remains, on the async path, the universal bound.

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

await using var connection = new NTiConnection(
    "server=MYIBMI;user=MYUSER;password=MYPASSWORD;connect timeout=15");
using var cts = new CancellationTokenSource(TimeSpan.FromMinutes(2));
await connection.OpenAsync(cts.Token);

await using var command = connection.CreateCommand();
command.CommandText = "SELECT COUNT(*) FROM MYLIB.ORDERS";
command.CommandTimeout = 60;   // per command, in seconds; 0 = unlimited (default)
Console.WriteLine(await command.ExecuteScalarAsync(cts.Token));

TLS

ssl=true encrypts the connections to all three host servers, and the default ports switch themselves over to their TLS variants (9476, 9471, 9475). The server certificate is validated against the machine's trust store. untrusted=true accepts any certificate, regardless of where it comes from. Reserve that option for test environments, never for production.

using Aumerial.Data.Nti;

await using var connection = new NTiConnection(
    "server=MYIBMI;user=MYUSER;password=MYPASSWORD;ssl=true");
await connection.OpenAsync();

MFA: an additional authentication factor

Three members let you supply the additional factor, depending on where it comes from:

AdditionalFactor supplies a static factor, a TOTP code you already have in hand. It's also reachable through the additional factor, mfa, or 2fa connection string keywords.
AdditionalFactorProvider is a synchronous callback that receives a context (the host, the user). It's invoked once per physical session, only if the server advertises MFA support, and can be called concurrently as the pool grows. If it's set alongside the async variant, it takes precedence.
AdditionalFactorAsyncProvider is the normal way to reach into a secrets vault or drive a prompt. On OpenAsync, it's awaited natively with the caller's token, which makes it cancellable. On synchronous Open, though, it starts outside any SynchronizationContext with an unbounded wait, so it's up to you to bound interactive prompts yourself.

using System.Threading.Tasks;
using Aumerial.Data.Nti;

await using var connection = new NTiConnection(
    "server=MYIBMI;user=MYUSER;password=MYPASSWORD;pooling=true");

// Normal path: an async provider, awaited natively
// with the caller's token on OpenAsync.
connection.AdditionalFactorAsyncProvider = async (context, cancellationToken) =>
{
    await Task.Yield();  // here: call a secrets vault with cancellationToken
    return "123456";
};
await connection.OpenAsync();

The older AdditionalFactorCallback callback, a Func<string> with no context, still compiles but is marked [Obsolete]. It's better to move to AdditionalFactorProvider, which receives the authentication context. Between the two, whichever was assigned last wins.

Two IBM i jobs per connection

An open NTiConnection occupies two distinct server jobs on the IBM i:

SQL runs in the database server's job, QZDASOINIT.
CL commands and program calls run in the command server's job, QZRCSRVS.

Each job has its own QTEMP, its own CURLIB, and its own overrides. An object created in QTEMP by a CL command is therefore invisible from SQL, and vice versa. To run CL inside the SQL job, and so inside its own QTEMP, go through the bridge CALL QSYS2.QCMDEXC('...'), at the cost of a round trip through SQL. The DatabaseJob and CommandJob properties give the qualified name of each job for inspection, through WRKJOB, the logs, or an audit.

using System;
using Aumerial.Data.Nti;

await using var connection = new NTiConnection(
    "server=MYIBMI;user=MYUSER;password=MYPASSWORD");
await connection.OpenAsync();

// CL: command job (QZRCSRVS), and so ITS OWN QTEMP
await connection.ExecuteClCommandAsync(
    "CRTDUPOBJ OBJ(ORDERS) FROMLIB(MYLIB) OBJTYPE(*FILE) TOLIB(QTEMP)");

// SQL: database job (QZDASOINIT), with a DIFFERENT QTEMP:
// SELECT * FROM QTEMP.ORDERS would not see the object created above.

// Bridge: run the CL inside the SQL job (costs an extra SQL round trip)
await using (var command = connection.CreateCommand())
{
    command.CommandText =
        "CALL QSYS2.QCMDEXC('CRTDUPOBJ OBJ(ORDERS) FROMLIB(MYLIB) OBJTYPE(*FILE) TOLIB(QTEMP)')";
    await command.ExecuteNonQueryAsync();
}

Console.WriteLine(connection.DatabaseJob);  // e.g. 123456/QUSER/QZDASOINIT
Console.WriteLine(connection.CommandJob);   // e.g. 123457/QUSER/QZRCSRVS

Persist security info

By default, with persist security info=false, the password is stripped from the ConnectionString property as soon as the connection opens. A log or a debugger reading the string will not see it. persist security info=true keeps it, reserved for the few diagnostic scenarios that genuinely need it.

using System;
using Aumerial.Data.Nti;

await using var connection = new NTiConnection(
    "server=MYIBMI;user=MYUSER;password=MYPASSWORD");
await connection.OpenAsync();
// The password no longer appears in the exposed string
Console.WriteLine(connection.ConnectionString);

The exhaustive list of connection string keywords, with their defaults, is the subject of the next article.

What's next?

Connection string: the full list of keywords and their default values
Overview: the provider's architecture and features
Quickstart guide: first connection and first IBM i calls