A tour of the ArrowTDS ADBC driver in C#

ArrowTDS is a commercial driver for Microsoft SQL Server, built on ADBC (Arrow Database Connectivity), a standard interface for drivers that exchange data as Apache Arrow, the columnar in-memory format. ArrowTDS speaks TDS, SQL Server's native wire protocol, and keeps Arrow in both directions: query results come back as Arrow record batches, and bulk loads go in as Arrow record batches.

This notebook installs the driver, connects to SQL Server from C# through the Apache.Arrow.Adbc client, and walks the client surface: queries, schema and metadata discovery, prepared statements, bulk ingestion, and error handling. It runs under the .NET Interactive C# kernel.

Prerequisites

You will need:

• A reachable SQL Server instance. This notebook uses a local one on localhost:1433 with the TPC-H tpch database and its dbo.nation table.
• An arpe.io licence file (.lic). The binaries download freely but only run once a licence is present.
• Jupyterlab and the .NET Interactive C# kernel:

dotnet tool install --global Microsoft.dotnet-interactive
dotnet interactive jupyter install

This notebook was run on Linux, so the paths shown throughout (install script, driver manifest, search paths) are Linux paths; the Windows equivalents differ.

To run end to end: install the driver (next section), set the ARROWTDS_PASSWORD environment variable (or edit the matching variable in Opening the database), adjust the server / database / username options to your instance, and run all cells in order.

Installing the driver

A single command downloads the driver for your platform, verifies its checksum, writes an ADBC driver manifest (a small file that records where the driver lives), and installs your licence next to the binary. On Linux:

curl -fsSL https://raw.githubusercontent.com/arpe-io/adbc-drivers/main/install.sh \
  | sh -s -- arrowtds --license ./arpeio_adbc.lic

On Windows, from PowerShell:

& ([scriptblock]::Create((irm https://raw.githubusercontent.com/arpe-io/adbc-drivers/main/install.ps1))) `
  arrowtds --license .\arpeio_adbc.lic

The --license ./arpeio_adbc.lic argument points at your licence file, which must be present at that path when you run the command; the installer then copies it next to the driver (as arpeio_adbc.lic) so any client picks it up automatically. Licences are available from arpe.io (contact sales@arpe.io).

The driver is a native library (a compiled file loaded at runtime, one build per OS and CPU). The installer records its path in the manifest (~/.config/adbc/drivers/arrowtds.toml on Linux), so manifest-aware ADBC clients load it by the name arrowtds instead of a file path. That is how this notebook loads it, in Opening the database below; if your manifest lives outside the default search directories, FindLoadDriver's additionalSearchPathList argument points at it.

Package and imports

Apache.Arrow.Adbc (latest version 0.24.0, pinned in the #r directive below) is the managed (C#) ADBC client; it pulls in Apache.Arrow for the record-batch types. Apache.Arrow.Adbc.DriverManager provides AdbcDriverManager.FindLoadDriver, which loads a native driver by name from its installer-written manifest; IArrowArrayStream (a stream of record batches) comes from Apache.Arrow.Ipc.

// #r "nuget:" installs the managed ADBC client from NuGet.org into this
// session (restored under ~/.nuget/packages/)
#r "nuget: Apache.Arrow.Adbc, 0.24.0"

using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading.Tasks;

using Apache.Arrow;
using Apache.Arrow.Types;
using Apache.Arrow.Ipc;
using Apache.Arrow.Adbc;
using Apache.Arrow.Adbc.DriverManager;

Opening the database

Opening the database is two steps: AdbcDriverManager.FindLoadDriver("arrowtds") finds the driver in the manifest, loads the native library, and binds its AdbcDriverInit entry point into an AdbcDriver; driver.Open then turns the connection options into an AdbcDatabase. FindLoadDriver also takes an optional entry point, load flags, and an extra search path; the defaults search the env-var, per-user and system manifest directories, which cover the installer's location here.

ArrowTDS options live under the adbc.arrowtds.* namespace: the server, port, database, username, password, encrypt and trust_server_cert keys used here; a single uri like sqlserver://user:pw@host:1433/?database=tpch&encrypt=true is the alternative. The password comes from an environment variable, to keep it out of the notebook.

Installed with --license, the arpeio_adbc.lic sits next to the library and is picked up automatically (the licence is checked as the database opens), so no licence option appears in the code. Set arpeio.adbc.license_file or the ARPEIO_ADBC_LICENCE_FILE variable only if your .lic lives elsewhere.

// Password from the environment; edit here if you prefer. Do not commit real values.
string password = Environment.GetEnvironmentVariable("ARROWTDS_PASSWORD") ?? "<password>";

// Load by name from the installer's manifest (no library path needed on 0.24.0+);
// entrypoint left null so the manifest's AdbcDriverInit applies. If the manifest
// dir is not on a default search path, pass additionalSearchPathList:
//   AdbcDriverManager.FindLoadDriver("arrowtds", null, AdbcLoadFlags.Default,
//       "/home/francois/.config/adbc/drivers");
AdbcDriver driver = AdbcDriverManager.FindLoadDriver("arrowtds");

var dbOptions = new Dictionary<string, string>
{
    ["adbc.arrowtds.server"]            = "localhost",
    ["adbc.arrowtds.port"]              = "1433",
    ["adbc.arrowtds.database"]          = "tpch",
    ["adbc.arrowtds.username"]          = "francois",
    ["adbc.arrowtds.password"]          = password,
    ["adbc.arrowtds.encrypt"]           = "true",
    ["adbc.arrowtds.trust_server_cert"] = "true",
    // Licence: installing with --license placed arpeio_adbc.lic next to the driver,
    // so it is resolved automatically. Set "arpeio.adbc.license_file" (or the
    // ARPEIO_ADBC_LICENCE_FILE env var) only if your .lic lives elsewhere.
};

AdbcDatabase database = driver.Open(dbOptions);
Console.WriteLine("Database opened; licence validated.");

Database opened; licence validated.

Connecting

database.Connect opens a connection (its per-connection options dictionary is empty here), and CreateStatement produces the statements that run queries. The handles must be disposed inner-first: statements, connection, database, driver. Since the connection stays open across cells, each cell disposes its statements with a using block, and the connection, database and driver are closed together in Cleanup.

AdbcConnection connection = database.Connect(new Dictionary<string, string>());
Console.WriteLine("Connected.");

Connected.

Reading record batches

ExecuteQuery returns a QueryResult whose Stream is an IArrowArrayStream: Arrow RecordBatches plus their shared Schema (column names and types). You pull batches with ReadNextRecordBatchAsync until it returns null. The two helpers below print a result's schema and first rows; FormatCell covers the column types used here and prints the array type name for anything else.

static string FormatCell(IArrowArray array, int index)
{
    switch (array)
    {
        case StringArray s:  return s.GetString(index) ?? "NULL";
        case Int64Array i:   return i.GetValue(index)?.ToString()  ?? "NULL";
        case Int32Array i:   return i.GetValue(index)?.ToString()  ?? "NULL";
        case Int16Array i:   return i.GetValue(index)?.ToString()  ?? "NULL";
        case DoubleArray d:  return d.GetValue(index)?.ToString(CultureInfo.InvariantCulture) ?? "NULL";
        case FloatArray f:   return f.GetValue(index)?.ToString(CultureInfo.InvariantCulture) ?? "NULL";
        case BooleanArray b: return b.GetValue(index)?.ToString()  ?? "NULL";
        default:             return $"<{array.GetType().Name}>";
    }
}

static async Task PreviewAsync(QueryResult result, int maxRows = 10)
{
    using IArrowArrayStream stream = result.Stream!;
    Schema schema = stream.Schema;

    Console.WriteLine("Schema:");
    foreach (Field f in schema.FieldsList)
        Console.WriteLine($"  {f.Name}: {f.DataType.Name}");

    Console.WriteLine();
    Console.WriteLine(string.Join(" | ", schema.FieldsList.Select(f => f.Name)));
    Console.WriteLine(new string('-', 40));

    int printed = 0;
    RecordBatch batch;
    while ((batch = await stream.ReadNextRecordBatchAsync()) != null)
    {
        using (batch)
        {
            for (int row = 0; row < batch.Length && printed < maxRows; row++, printed++)
            {
                var cells = Enumerable.Range(0, batch.ColumnCount)
                                      .Select(col => FormatCell(batch.Column(col), row));
                Console.WriteLine(string.Join(" | ", cells));
            }
        }
        if (printed >= maxRows) break;
    }
}

Running a query

Running a query is three steps: set SqlQuery, call ExecuteQuery, then read the resulting stream with the helper from the previous cell. QueryResult.RowCount is the driver's row estimate; it is -1 when the total is not known before the rows are scanned. A statement can be reused for another query, but executing again invalidates the previous QueryResult's stream, so each example takes a fresh statement (in a using block) and consumes its result before moving on.

using (AdbcStatement statement = connection.CreateStatement())
{
    statement.SqlQuery = "SELECT n_nationkey, n_name, n_regionkey FROM nation ORDER BY n_nationkey";
    QueryResult result = statement.ExecuteQuery();
    Console.WriteLine($"RowCount (driver estimate): {result.RowCount}");
    Console.WriteLine();
    await PreviewAsync(result, maxRows: 10);
}

RowCount (driver estimate): -1

Schema:
  n_nationkey: int32
  n_name: utf8
  n_regionkey: int32

n_nationkey | n_name | n_regionkey
----------------------------------------
0 | ALGERIA | 0
1 | ARGENTINA | 1
2 | BRAZIL | 1
3 | CANADA | 1
4 | EGYPT | 4
5 | ETHIOPIA | 0
6 | FRANCE | 3
7 | GERMANY | 3
8 | INDIA | 2
9 | INDONESIA | 2

Server and driver identity

GetInfo reports who is on each end of the connection: driver name and version, database vendor and version. Its info_value column is an Arrow dense union, so each row's value is read from the union branch its type id points at; the identity codes requested here all land in the string branch. There is no higher-level reader on the ADBC side; you consume every result, data or metadata, by pulling RecordBatches from an IArrowArrayStream until ReadNextRecordBatchAsync returns null, exactly as PreviewAsync did above.

AdbcInfoCode[] codes = { AdbcInfoCode.DriverName, AdbcInfoCode.DriverVersion,
                         AdbcInfoCode.VendorName, AdbcInfoCode.VendorVersion };

using (IArrowArrayStream info = connection.GetInfo(codes))
{
    Console.WriteLine("GetInfo:");
    RecordBatch b;
    while ((b = await info.ReadNextRecordBatchAsync()) != null)
        using (b)
        {
            var names  = (UInt32Array)b.Column(0);       // info_name
            var values = (DenseUnionArray)b.Column(1);   // info_value (dense union)
            for (int i = 0; i < b.Length; i++)
            {
                int branch = values.TypeIds[i];
                int off    = values.ValueOffsets[i];
                string v = values.Fields[branch] is StringArray s
                    ? s.GetString(off)
                    : $"<{values.Fields[branch].GetType().Name}>";
                Console.WriteLine($"  {(AdbcInfoCode)(names.GetValue(i) ?? 0u)}: {v}");
            }
        }
}

GetInfo:
  DriverName: ArrowTDS ADBC Driver
  DriverVersion: 0.5.21
  VendorName: Microsoft SQL Server
  VendorVersion: 16.0.4015

Prepared statements

A prepared statement sends the query once with ? placeholders and supplies the values separately, which is safer against SQL injection and reusable. ArrowTDS runs them server-side: the bound parameters are an Arrow RecordBatch whose columns line up with the placeholders, sent over the TDS RPC layer. Call Prepare, build a one-row batch, Bind it, and execute. The single ? here is the region key, bound to 1 (AMERICA), so the query returns the nations of that region.

using (AdbcStatement statement = connection.CreateStatement())
{
    statement.SqlQuery = "SELECT n_nationkey, n_name FROM nation WHERE n_regionkey = ? ORDER BY n_nationkey";
    statement.Prepare();

    // One parameter column: the region key to filter on.
    var paramSchema = new Schema(
        new List<Field> { new Field("regionkey", Int32Type.Default, nullable: false) },
        metadata: null);

    Int32Array regionKey = new Int32Array.Builder().Append(1).Build();
    var paramBatch = new RecordBatch(paramSchema, new IArrowArray[] { regionKey }, length: 1);

    statement.Bind(paramBatch, paramSchema);
    QueryResult result = statement.ExecuteQuery();
    await PreviewAsync(result, maxRows: 10);
}

Schema:
  n_nationkey: int32
  n_name: utf8

n_nationkey | n_name
----------------------------------------
1 | ARGENTINA
2 | BRAZIL
3 | CANADA
17 | PERU
24 | UNITED STATES

Metadata discovery

Beyond queries, AdbcConnection describes the database through ADBC's catalog methods. GetTableSchema returns one table's column schema without reading rows; GetInfo, GetObjects and GetTableTypes return Arrow streams following the standard ADBC metadata schemas. GetTableSchema runs under SQL Server's FMTONLY mode, so no data is read.

Schema nationSchema = connection.GetTableSchema(catalog: null, dbSchema: "dbo", tableName: "nation");

Console.WriteLine("dbo.nation columns:");
foreach (Field f in nationSchema.FieldsList)
    Console.WriteLine($"  {f.Name}: {f.DataType.Name} (nullable: {f.IsNullable})");

dbo.nation columns:
  n_nationkey: int32 (nullable: True)
  n_name: utf8 (nullable: True)
  n_regionkey: int32 (nullable: True)
  n_comment: utf8 (nullable: True)

GetTableTypes lists the table kinds the catalog exposes.

using (IArrowArrayStream types = connection.GetTableTypes())
{
    Console.WriteLine("Table types:");
    RecordBatch b;
    while ((b = await types.ReadNextRecordBatchAsync()) != null)
        using (b)
        {
            var c = (StringArray)b.Column(0);            // table_type
            for (int i = 0; i < b.Length; i++)
                Console.WriteLine($"  {c.GetString(i)}");
        }
}

Table types:
  TABLE
  VIEW
  SYSTEM TABLE
  GLOBAL TEMPORARY
  LOCAL TEMPORARY

A note on statistics

ADBC also defines GetStatistics and GetStatisticsNames: row counts, distinct counts, min/max per column, returned as Arrow. ArrowTDS does not implement them. They pay off for a downstream consumer planning its own work over Arrow pulled from one or more ADBC sources (a federated engine choosing a join order across them, say), not for the direct-query path here, where SQL Server's own optimizer already plans each query internally, from its own statistics, before ArrowTDS sees a row.

Bulk ingestion

Bulk ingestion loads many rows in one operation instead of a series of INSERTs. AdbcConnection.BulkIngest returns a statement on the bulk-load path; ArrowTDS streams the rows straight to SQL Server, so the data goes in as an Arrow stream via BindStream.

The cell first runs DROP TABLE IF EXISTS so a re-run starts clean, then BulkIngestMode.Create builds the table from the stream's schema (Replace, Append and CreateAppend are the other modes). Autocommit is set first, since an open implicit transaction can deadlock the INSERT BULK path.

// ArrowTDS streams bulk-insert data from a bound stream, so wrap the batch in a
// minimal IArrowArrayStream and use BindStream. (Bind alone feeds query
// parameters and leaves the ingest path with nothing to send.)
class InMemoryArrayStream : IArrowArrayStream
{
    private readonly Queue<RecordBatch> _batches;
    public Schema Schema { get; }

    public InMemoryArrayStream(Schema schema, IEnumerable<RecordBatch> batches)
    {
        Schema = schema;
        _batches = new Queue<RecordBatch>(batches);
    }

    public ValueTask<RecordBatch> ReadNextRecordBatchAsync(
        System.Threading.CancellationToken cancellationToken = default)
        => new ValueTask<RecordBatch>(_batches.Count > 0 ? _batches.Dequeue() : null);

    public void Dispose() { }
}

connection.AutoCommit = true;   // implicit transactions can deadlock the bulk path

// Drop any table left by a previous run so the cell is idempotent.
using (AdbcStatement drop = connection.CreateStatement())
{
    drop.SqlQuery = "DROP TABLE IF EXISTS demo_arrowtds_ingest";
    drop.ExecuteUpdate();
}

var ingestSchema = new Schema(new List<Field>
{
    new Field("id",    Int32Type.Default,  nullable: false),
    new Field("label", StringType.Default, nullable: true),
}, metadata: null);

Int32Array  ids    = new Int32Array.Builder().Append(1).Append(2).Append(3).Build();
StringArray labels = new StringArray.Builder().Append("alpha").Append("beta").Append("gamma").Build();
var rows = new RecordBatch(ingestSchema, new IArrowArray[] { ids, labels }, length: 3);

using (AdbcStatement ingest = connection.BulkIngest("demo_arrowtds_ingest", BulkIngestMode.Create))
{
    ingest.BindStream(new InMemoryArrayStream(ingestSchema, new[] { rows }));
    UpdateResult ingestResult = ingest.ExecuteUpdate();
    Console.WriteLine($"Rows ingested: {ingestResult.AffectedRows}");
}

Rows ingested: 3

Transactions

AutoCommit defaults to true, so each statement commits on its own. Set it false to group several statements and choose the outcome with Commit or Rollback. Below, one row is committed and a second rolled back, so the follow-up count sees only the first. (IsolationLevel and ReadOnly sit alongside AutoCommit for finer control)

// Manual transaction control. AutoCommit defaults to true (each statement commits on
// its own); set it false to group statements and decide the outcome yourself.
connection.AutoCommit = false;

using (AdbcStatement s = connection.CreateStatement())
{
    s.SqlQuery = "INSERT INTO demo_arrowtds_ingest (id, label) VALUES (4, 'delta')";
    s.ExecuteUpdate();
}
connection.Commit();    // 'delta' is now durable

using (AdbcStatement s = connection.CreateStatement())
{
    s.SqlQuery = "INSERT INTO demo_arrowtds_ingest (id, label) VALUES (5, 'epsilon')";
    s.ExecuteUpdate();
}
connection.Rollback();  // 'epsilon' is discarded

connection.AutoCommit = true;   // back to autocommit for later cells

using (AdbcStatement s = connection.CreateStatement())
{
    s.SqlQuery = "SELECT COUNT(*) AS n FROM demo_arrowtds_ingest";
    await PreviewAsync(s.ExecuteQuery());   // expect 4: bulk rows 1-3 + committed 'delta'
}

Schema:
  n: int32

n
----------------------------------------
4

Error handling

Driver and server failures surface as AdbcException, carrying an ADBC Status (a broad category), the vendor SqlState, and NativeError, the SQL Server error number (this binding has no VendorCode; NativeError is the equivalent). The query below references a table that does not exist.

try
{
    using AdbcStatement statement = connection.CreateStatement();
    statement.SqlQuery = "SELECT * FROM table_that_does_not_exist";
    statement.ExecuteQuery();
}
catch (AdbcException ex)
{
    Console.WriteLine($"Status:      {ex.Status}");
    Console.WriteLine($"SqlState:    {ex.SqlState}");
    Console.WriteLine($"NativeError: {ex.NativeError}");
    Console.WriteLine($"Message:     {ex.Message}");
}

Status:      IOError
SqlState:    HY000
NativeError: 208
Message:     native TDS read failed: SQL Server error 208 (state 1, severity 16): Invalid object name 'table_that_does_not_exist'.

Cleanup

Dispose the open handles inner-first: the connection, then the database, then the driver.

connection.Dispose();
database.Dispose();
driver.Dispose();
Console.WriteLine("Disposed.");

Disposed.

Wrap-up

Across the whole surface, reads and writes alike move as Arrow record batches, so the layout the driver hands you on a query is the layout you hand it back on a bulk load, with no transpose to rows and back at the client. The Apache.Arrow.Adbc client surface (Open, Connect, CreateStatement, ExecuteQuery, Prepare/Bind, BulkIngest, and the catalog methods) is the same for every ADBC driver, so the code carries over to other ADBC backends by changing little more than the driver path and connection options.

Driver downloads are at github.com/arpe-io/adbc-drivers. The binaries download freely and run once a licence is present; to obtain one for the ADBC drivers, contact us at sales@arpe.io or see arpe.io.