JustyBase.NetezzaDriver 1.6.0

JustyBase.NetezzaDriver

A pure C# ADO.NET driver for IBM Netezza Performance Server (NPS) — no native dependencies, no ODBC bridge. The code is based on nzpy (Python) and npgsql (PostgreSQL).

Features at a glance

Installation

dotnet add package JustyBase.NetezzaDriver

The package targets .NET 8, .NET 9, and .NET 10.

Quick start

Synchronous

using JustyBase.NetezzaDriver;

using var conn = new NzConnection("username", "password", "host", "database");
conn.Open();

using var cmd = conn.CreateCommand("SELECT 'Hello from Netezza' AS msg");
using var reader = cmd.ExecuteReader();
while (reader.Read())
    Console.WriteLine(reader.GetString(0));

Asynchronous

using JustyBase.NetezzaDriver;

await using var conn = new NzConnection("username", "password", "host", "database");
await conn.OpenAsync();

await using var cmd = conn.CreateCommand("SELECT 'Hello async' AS msg");
await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine(reader.GetString(0));

Connection string

// Constructors
new NzConnection("user", "password", "host", "database");
new NzConnection("user", "password", "host", "database", port: 5480);
new NzConnection("user", "password", "host", "database", port: 5480,
    securityLevel: SecurityLevelCode.OnlySecuredSession,
    sslCerFilePath: @"C:\certs\netezza.pem");

// Or via NzConnectionStringBuilder
var builder = new NzConnectionStringBuilder
{
    Host = "host", Database = "db", UserName = "user", Password = "pass",
    Port = 5480, Pooling = true, MinPoolSize = 1, MaxPoolSize = 10
};
using var conn = new NzConnection(builder.ConnectionString);
conn.Open();

Parameterized queries

Both named (:name, @name) and positional (?) parameters are supported. Parameters are rendered inline as SQL literals.

// Named parameters
await using var cmd = conn.CreateCommand(
    "SELECT id, name FROM users WHERE age > :minAge AND city = :city");
cmd.Parameters.AddWithValue(":minAge", 18);
cmd.Parameters.AddWithValue(":city", "New York");
await using var reader = await cmd.ExecuteReaderAsync();

// Positional parameters
await using var cmd2 = conn.CreateCommand(
    "SELECT count(*) FROM users WHERE status = ?");
cmd2.Parameters.Add(new NzParameter { Value = "active", IsPositional = true });
var count = await cmd2.ExecuteScalarAsync();

C# to SQL type mapping:

See docs/parameters.md for details.

Connection pooling

NzConnectionPool provides a thread-safe, semaphore-based pool.

var pool = new NzConnectionPool(
    host: "host", database: "db", user: "user", password: "pass",
    port: 5480, minPoolSize: 1, maxPoolSize: 10,
    connectionIdleTimeoutSeconds: 30);

// Rent a connection (auto-return via await using)
await using var pooled = await pool.RentAsync();
await using var cmd = pooled.Connection.CreateCommand("SELECT 1");
var result = await cmd.ExecuteScalarAsync();

The pool validates connections with SELECT 1, automatically rolls back open transactions on return, and runs a background maintenance timer every 30 seconds.

See docs/pooling.md for details.

Column metadata

NzDataReader exposes extended column metadata beyond the standard ADO.NET schema:

// Standard ADO.NET
var schema = reader.GetColumnSchema(); // ReadOnlyCollection<DbColumn>

// Extended methods
int oid = reader.GetProviderType(0);         // OID (e.g., 23 for INTEGER)
string declared = reader.GetDeclaredTypeName(0); // "VARCHAR(32)", "NUMERIC(10,2)"

Each NzDbColumn in the schema includes ProviderType, TypeModifier, and DeclaredTypeName.

Catalog introspection

NzConnection.Meta provides async access to Netezza system catalog views:

var meta = conn.Meta;

var schemas   = await meta.GetSchemasAsync();
var tables    = await meta.GetTablesAsync("ADMIN");
var columns   = await meta.GetColumnsAsync("DIMDATE", "ADMIN");
var views     = await meta.GetViewsAsync();
var procs     = await meta.GetProceduresAsync();
var distKey   = await meta.GetDistributionKeyAsync("DIMDATE", "ADMIN");
var sizes     = await meta.GetTableSizesAsync();
var sessions  = await meta.GetSessionsAsync();
var search    = await meta.SearchObjectsAsync("DIM%");

See docs/metadata_api.md for full API reference.

Timeout and cancel

Per-command and per-connection timeout:

// Per-connection default
connection.DefaultCommandTimeout = TimeSpan.FromSeconds(30);

// Per-command override
cmd.CommandTimeout = 10; // seconds

// CancellationToken
using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(5));
await using var reader = await cmd.ExecuteReaderAsync(cts.Token);

// Connection-level cancel
connection.CancelQuery();

See docs/timeout_and_cancel.md for details.

ADO.NET support

Behavioral Changes (since v1.4.0)

Starting from version 1.4.0, retrieving a NULL column value as a non-nullable type (e.g., reader.GetString(0) when the value is DBNull) throws InvalidCastException instead of returning a default value. Always check reader.IsDBNull(i) before calling typed getters, or use nullable types.

Security (SSL/TLS)

TLS certificate validation is strict by default — certificates with TLS policy errors are rejected.

var conn = new NzConnection("user", "password", "host", "database",
    securityLevel: SecurityLevelCode.OnlySecuredSession,
    sslCerFilePath: @"C:\path\to\certificate.pem");
conn.Open();

Benchmark (sync vs async DataReader)

The benchmark project at scr/JustyBase.NetezzaDriver.Benchmarks compares sync (ExecuteReader) vs async (ExecuteReaderAsync) for large data reads.

dotnet run -c Release -f net10.0 --project .\scr\JustyBase.NetezzaDriver.Benchmarks -- --filter *AsyncReaderBench*

Sample results (net10.0, BenchmarkDotNet, Windows 11):

Async overhead is negligible (~1–3% time, 0–5% allocations).

Testing

# Unit tests only
dotnet test .\scr\JustyBase.NetezzaDriver.Tests\JustyBase.NetezzaDriver.Tests.csproj --filter "Category=Unit"

# Integration tests (requires live Netezza)
dotnet test .\scr\JustyBase.NetezzaDriver.Tests\JustyBase.NetezzaDriver.Tests.csproj --filter "Category=Integration"

Integration tests read connection settings from environment variables:

• NZ_DEV_HOST, NZ_DEV_PORT (default 5480), NZ_DEV_DB, NZ_DEV_USER, NZ_DEV_PASSWORD

License

Copyright 2025–2026 Krzysztof Duśko
Copyright 2019–2020 IBM, Inc.

Licensed under the Apache License, Version 2.0.

Contact

For questions, bug reports, or feature requests, open an issue on GitHub.

Documentation

• Parameters — Named and positional parameter reference
• Pooling — Connection pool configuration and lifecycle
• Metadata API — Catalog introspection methods
• Timeout & Cancel — Command timeout, CancellationToken, CancelQuery
• Examples project — Runnable C# examples