DbaClientX - Multi-provider database client for .NET and PowerShell

DbaClientX is a lightweight database client for .NET and PowerShell. It keeps the database-specific work in provider libraries and exposes a small PowerShell surface for pleasant data movement, query execution, transactions, and bulk writes.

NuGet Packages

PowerShell Module

Two Brands In One Repository

DbaClientX remains the database and data-movement product. Its .NET packages and DbaClientX PowerShell module keep their existing version family and DbaX commands.

FabricClientX is an unreleased sibling product for Microsoft Fabric and Power BI automation. It has separate .NET packages, a FabricClientX PowerShell module, FabricX commands, version source, package-build configuration, and artifact folder. The two brands share this repository while their APIs and consumers settle, but they can be installed and released independently.

See Module-FabricClientX for the intended command surface and docs/architecture-decision.md for the ownership boundary.

Project Information

Author and Community

What it's all about

DbaClientX provides provider connections, query execution, transactions, metadata, and bulk-copy commands for scripts and .NET code. It works with normal tabular inputs such as DataTable, DataView, IDataReader, DataRow, hashtables, regular objects, and rows imported from CSV or Excel with PSWriteOffice.

The PowerShell module stays small and operator-friendly; the heavy database logic remains in C#. That keeps user scripts clean without forcing people to pick low-level parser settings just to get good performance.

Use it when you need:

• one codebase for SQL Server, PostgreSQL, MySQL, SQLite, Oracle, and Azure Tables
• sync and async query execution
• consistent caller cancellation across query, scalar, non-query, reader, streaming, stored procedure, and bulk APIs; provider-specific cancellation failures are normalized to OperationCanceledException with the caller's token
• parameterized commands and provider-specific parameter type preservation
• transaction helpers that commit on success and roll back on failure
• provider-native bulk insert paths for staging tables and direct table writes
• owned forward-only readers for bounded consumers such as OfficeIMO.Data.Arrow
• provider-neutral metadata discovery for databases, tables, views, columns, indexes, foreign keys, and routines without SQL Server Management Objects
• PowerShell cmdlets for quick scripts, scheduled jobs, and data movement

Supported Providers

Provider	NuGet package	PowerShell query cmdlets	Bulk write	Transaction helper
SQL Server	DBAClientX.SqlServer	Invoke-DbaXQuery, Invoke-DbaXNonQuery	Write-DbaXTableData -Provider SqlServer	Invoke-DbaXTransaction
PostgreSQL	DBAClientX.PostgreSql	Invoke-DbaXPostgreSql, Invoke-DbaXPostgreSqlNonQuery	Write-DbaXTableData -Provider PostgreSql	Invoke-DbaXPostgreSqlTransaction
MySQL	DBAClientX.MySql	Invoke-DbaXMySql, Invoke-DbaXMySqlNonQuery, Invoke-DbaXMySqlScalar	Write-DbaXTableData -Provider MySql	Invoke-DbaXMySqlTransaction
SQLite	DBAClientX.SQLite	Invoke-DbaXSQLite	Write-DbaXTableData -Provider SQLite	Invoke-DbaXSQLiteTransaction
Oracle	DBAClientX.Oracle	Invoke-DbaXOracle, Invoke-DbaXOracleNonQuery, Invoke-DbaXOracleScalar	Write-DbaXTableData -Provider Oracle	Invoke-DbaXOracleTransaction
Azure Storage Tables / Cosmos DB Table API	DbaClientX.AzureTables	Get-DbaXAzureTableEntity	Write-DbaXAzureTableEntity	Same-partition transactions through the adapter

Data Movement

Start with the job you need to finish:

You need to...	Use this	Notes
Write PowerShell objects, DataTable, DataView, IDataReader, or Excel-imported rows to a table	Write-DbaXTableData	Uses provider-native database writes
Load a SQL Server staging table and let the command create the table, map columns, lock the table, preserve identities/nulls, fire triggers, check constraints, or report progress	Write-DbaXTableData -Provider SqlServer	SQL Server-specific knobs map to SqlServerBulkInsertOptions
Copy one or more tables between database providers	Copy-DbaXTableData	Uses reusable copy definitions plus provider adapters
Stream Azure Table entities between accounts or Table API endpoints	Copy-DbaXAzureTableData	Preserves native continuation tokens and keeps each write transaction inside one partition
Query or upsert Azure Table entities from PowerShell	Get-DbaXAzureTableEntity / Write-DbaXAzureTableEntity	Requires PartitionKey and RowKey; transaction batches are capped at 100 entities and the service payload limit
Export SQL rows to CSV, compressed CSV, or Excel	Invoke-DbaXQuery -AsDataReader or -ReturnType DataTable plus PSWriteOffice Export-OfficeCsv / Export-OfficeExcel	Streams or buffers database rows into the file writer
Import CSV, compressed CSV, or Excel into SQL Server	PSWriteOffice Import-OfficeCsv -AsDataReader / Import-OfficeExcel -AsDataReader plus Write-DbaXTableData	Reads the file as tabular data, then bulk-writes it
Stream a reader into SQL Server bulk copy	Write-DbaXTableData -Provider SqlServer -InputObject (, $reader)	Pass the reader as a single input object with , $reader
Stream database rows into Apache Arrow	Provider QueryReaderAsync plus OfficeIMO.Data.Arrow ReadArrowBatchesAsync	DbaClientX owns the connection/reader lifetime; OfficeIMO owns Arrow conversion and managed/C stream export

CSV and Excel round trips use matching DbaClientX, PSWriteOffice, and OfficeIMO packages. Use -PSWriteOfficeModulePath only when validating a local PSWriteOffice build.

The PowerShell layer is intentionally thin: it maps friendly parameters to provider-owned C# APIs. Put repeatable database behavior in DbaClientX and keep consumer scripts focused on choosing source data, destination names, and credentials.

Install

PowerShell:

Install-Module DbaClientX -Scope CurrentUser

.NET:

dotnet add package DBAClientX.Core
dotnet add package DBAClientX.SqlServer
dotnet add package DBAClientX.PostgreSql
dotnet add package DBAClientX.MySql
dotnet add package DBAClientX.SQLite
dotnet add package DBAClientX.Oracle
dotnet add package DbaClientX.AzureTables

Install only the provider packages you need.

PowerShell Usage

Query SQL Server

Invoke-DbaXQuery `
    -Server 'sql01' `
    -Database 'App' `
    -Query 'SELECT TOP (10) * FROM dbo.Users' `
    -Credential $Credential

Query Other Providers

Invoke-DbaXPostgreSql -Server 'pg01' -Database 'app' -Query 'select * from users limit 10' -Credential $Credential
Invoke-DbaXMySql -Server 'mysql01' -Database 'app' -Query 'select * from users limit 10' -Credential $Credential
Invoke-DbaXOracle -Server 'ora01' -Database 'service' -Query 'select * from users fetch first 10 rows only' -Credential $Credential
Invoke-DbaXSQLite -Database '.\app.db' -Query 'select * from users limit 10'

-ReadOnly opens a SQLite database with Mode=ReadOnly, so statements that modify it fail and a missing file is not created (SQLite may still create -wal/-shm files next to a WAL database). Use it to inspect a database that a running service owns:

Invoke-DbaXSQLite -Database 'C:\ProgramData\App\monitoring.db' -Query 'select count(*) as Probes from ProbeResults' -ReadOnly

-Database accepts a file path or a file-backed connection string with one source key (Data Source, DataSource, Filename, or FullUri). Read-only connection strings retain options such as Password, Cache, and Default Timeout; Mode and Pooling are overridden. Explicit connection options take precedence over file-URI query hints. Conflicting source aliases and in-memory databases are rejected. Validation failures warn and return under -ErrorAction Continue, and terminate under Stop. Connection options are kept out of confirmation targets and error targets. VACUUM INTO can still write a separate output file.

Azure Tables

Azure queries expose the provider continuation tokens through -AsPage; ordinary use streams all returned entities:

$daily = Get-DbaXAzureTableEntity `
    -ConnectionString $sourceConnectionString `
    -TableName Reports `
    -Filter "PartitionKey eq 'daily'"

$daily | Write-DbaXAzureTableEntity `
    -ConnectionString $archiveConnectionString `
    -TableName ReportsArchive `
    -WriteMode UpsertReplace `
    -PassThru

Use Copy-DbaXAzureTableData for a streaming account-to-account copy. Row-count verification is enabled by default and performs additional full-table scans; use -NoVerify when that cost is not appropriate. -ClearDestination is explicit and is rejected when any destination would clear a source used elsewhere in the same copy plan. The shared adapter applies Azure Storage or Cosmos DB Table API table-name casing rules; this safety decision is not duplicated in the PowerShell cmdlet.

Adapter authors must return DbaTableCopyPage from IDbaTableCopySource.ReadPageAsync, carrying the provider's opaque continuation token in the page result. The offset constructor on DbaTableCopyPageRequest remains temporarily available for callers, but provider implementations should no longer invent paging state in consumers.

Verified and resumable database copies

The .NET table-copy engine supports bounded keyset pages, content verification, and atomic destination checkpoints for SQL Server, PostgreSQL, MySQL, Oracle, and SQLite. Define an ascending, unique, non-null key that is preserved in the destination, then enable the required options:

Continuation tokens support provider-neutral scalar keys, including arbitrary-precision decimals, network addresses/prefixes, and calendar intervals. PostgreSQL arrays, ranges, multiranges, geometry values, and other provider-native values without a lossless token representation are rejected when selected as paging keys; use a supported scalar key or disable keyset pagination. Ordinary unquoted Oracle snake-case keys follow Oracle's uppercase identifier folding.

var definition = new DbaTableCopyDefinition("SourceRows", "dbo.ArchiveRows", new[] { "Id" })
{
    UseKeysetPagination = true
};
var options = new DbaTableCopyOptions
{
    VerifyContent = true,
    CheckpointId = migrationId, // Persist this identifier outside the process.
    Resume = resume,
    KeepIdentity = true,
    PageSize = 10_000,
    MaxPageBytes = 32L * 1024 * 1024,
    BulkCopyTimeout = 600
};
var result = await new DbaTableCopyEngine().CopyAsync(sourceAdapter, destinationAdapter,
    new[] { definition }, options, cancellationToken);

Create the destination schema first and keep its writers stopped during migration. Verified copies require empty destination tables unless ClearDestination explicitly discards their contents. The engine validates source contents and destination state before clearing tables. Each page and its checkpoint commit together in the destination database's checkpoint table (dbo.DbaClientX_TableCopyCheckpoints on SQL Server, DbaX_TableCopyCheckpoints on Oracle, and DbaClientX_TableCopyCheckpoints in the current schema/database on PostgreSQL, MySQL, and SQLite). Resume rereads the source and committed destination rows and refuses changed contents or definitions. A preflight interruption with no checkpoint may resume only into empty destination tables. Changing page size or timeouts does not require a new migration identifier.

MySQL atomic checkpoints require both the destination and DbaClientX checkpoint tables to use InnoDB. DbaClientX creates its checkpoint table with InnoDB and rejects nontransactional destination engines before it clears or writes destination rows. Standalone MySQL bulk inserts also require InnoDB and use an owned transaction so conversion warnings roll back instead of silently committing coerced values.

If initial destination clearing is interrupted, resume refuses nonempty tables that have no checkpoint. Confirm that the destination can still be discarded, then restart with a new checkpoint identifier and ClearDestination enabled.

Content verification compares row counts and a SHA-256 multiset checksum over the copied columns. It normalizes numeric widths and booleans, preserves exact strings, nulls, binary values, and datetimeoffset offsets, and tolerates different provider sort orders. It does not verify excluded columns, triggers, permissions, or schema equivalence. Preflight rejects missing copied columns, generated columns in the write projection, and required destination columns without a supplied value or default. SQL Server verified writes preserve nulls and check constraints.

MySQL DECIMAL and Oracle NUMBER/FLOAT values beyond System.Decimal precision are carried losslessly through content hashes and continuation tokens to compatible native destinations. MySQL accepts its full 65-digit range; Oracle accepts MySQL decimals through 38 digits. Wider MySQL decimals are rejected before Oracle writes unless the column is excluded or explicitly converted to String. Copies to other providers reject declarations beyond System.Decimal before writing unless projected portably. PostgreSQL table copy fails before writing when a numeric declaration can exceed System.Decimal; project that column to text or apply an explicit provider-neutral conversion.

Oracle bulk and transactional writes apply the same provider-neutral conversions for GUID/RAW, date/time, interval, unsigned, and arbitrary-precision numeric values. Source compatibility checks resolve local and public synonyms before inspecting the underlying object and reject remote database-link synonyms. BC DATE values and unsupported binary floating-point special values fail before paging unless the affected column is excluded or has a supported portable projection.

PostgreSQL reads preserve native interval month, day, and microsecond components in DbaCalendarInterval; PostgreSQL bulk writes rehydrate them without approximating calendar months as fixed-duration TimeSpan values. Oracle INTERVAL YEAR TO MONTH values continue to use DbaYearMonthInterval and rehydrate as native Oracle values. PostgreSQL destinations accept declarations through eight leading year digits, whose possible month values fit Npgsql's 32-bit representation; wider declarations and other destinations require exclusion or explicit String conversion. Excluded Oracle columns are skipped during page materialization, so an unrepresentable excluded temporal or locator value cannot fail an otherwise valid copy.

Supported PostgreSQL arrays, geometric, range/multirange, text-search, bit-string, and other provider-native values remain lossless when the destination is PostgreSQL. PostgreSQL inet host addresses retain their original prefix lengths rather than being widened to host-only /32 or /128 values. Copies to other providers reject provider-native declarations before writing unless the column is excluded or explicitly converted to String. Excluded non-key PostgreSQL columns are not materialized, so unsupported numeric shapes and special values in an omitted column do not abort an otherwise valid copy.

PostgreSQL enum and enum-array columns require an Npgsql enum mapping that the table-copy adapter cannot accept safely from a caller. DbaClientX therefore rejects those columns during source metadata validation, before reading a page or changing the destination. Cast or omit them in a provider-side source view before copying.

With ClearDestination, SQL Server, PostgreSQL, MySQL, Oracle, and SQLite validate every projected source page against the real destination before committed destination rows are removed. Related definitions share one rollback-only transaction: destinations are cleared in reverse definition order and projected rows are validated in forward order, matching the real dependency-safe copy. This extra preflight pass catches cross-page uniqueness and destination-constraint failures without retaining all projected pages in memory. Because sequence, identity, auto-increment, and trigger side effects are not reliably reversible on every provider, destructive preflight rejects unsafe generator or trigger shapes. SQL Server also follows cascading-delete relationships when checking for rollback-unsafe child-table triggers. MySQL rejects explicit auto-increment values at or above the destination's current next value because even a rolled-back insert can advance that counter. Non-clearing copies perform metadata-only schema preflight so validation does not invoke destination generators or triggers before the real write. Project non-generating values, remove triggers for a destructive migration, or copy without ClearDestination.

Content verification preserves non-zero DateTimeOffset offsets in addition to the UTC instant. A destination that normalizes 2026-09-21 12:00 +02:00 to 2026-09-21 10:00 +00:00 therefore no longer verifies as semantically identical.

For verified or resumable SQL Server copies, set identity preservation through DbaTableCopyOptions.KeepIdentity and column mappings through DbaTableCopyDefinition.ColumnMappings. These settings are part of the checkpoint contract. SQL bulk destination names must match the physical column casing; map source id to destination ID explicitly even when the database uses a case-insensitive collation. Adapter-level mappings and the FireTriggers or AllowEncryptedValueModifications bulk flags are rejected in this mode. Ordinary copies retain adapter mappings and automatic table creation. KeepIdentity retains supplied identity values; it does not copy schema or replace ordinary key mappings.

Set DbaProviderTableCopyAdapterOptions.ReadConsistency to Snapshot to hold one provider transaction across the engine call. SQLite uses a deferred read transaction; WAL mode is recommended when writers must continue during the copy. SQL Server validates ALLOW_SNAPSHOT_ISOLATION in every database named by the source definitions before reading rows or changing destinations; linked-server sources are not supported in snapshot sessions. PostgreSQL and MySQL use their repeatable-read snapshot semantics. Oracle uses a serializable read transaction. Serializable is an alternative where supported and can block writers; SQLite begins an immediate transaction for this mode. CallerManaged, the default, leaves source consistency to the caller. A stable SQLite backup remains a useful offline source, and any resumed source must remain unchanged between calls.

MySQL Snapshot and Serializable source sessions require InnoDB tables. The table-copy engine validates every source definition before reading; callers that open a MySQL read session directly must use the definition-aware overload so the same validation can run.

Source and destination keys must be unique under their own provider's comparison rules. Set DbaTableCopyDefinition.DestinationOrderByColumns when destination verification needs a different key, such as a generated binary hash that distinguishes text values SQL collation considers equal. Generated source keys may be excluded from copied content when an explicit destination verification key is supplied. These key settings are part of the checkpoint contract.

Implicit destination keys follow the actual source column names and the same mapping and exclusion rules as copied pages. Exclusions apply to both source and mapped destination names. Dictionary mappings and HashSet exclusions retain their comparers; other collection implementations use ordinal matching. An exclusion such as ordinal id therefore leaves source column Id intact. If the projected key is excluded, supply an explicit destination verification key.

MaxPageBytes limits estimated payload per keyset page, not total process memory or provider-native buffers. Values are read sequentially and bounded pages fail rather than truncate a row that exceeds the configured limit; raise the limit explicitly for large text or binary payloads. PostgreSQL arrays and other variable-size native objects are rejected before materialization in bounded mode; project them to text or binary, or omit MaxPageBytes. Mixed SQLite storage types and embedded null characters in text are preserved, while malformed UTF-8 text is rejected. Full content scans add I/O before and after copying. Checkpoints provide page-level durability, not an all-or-nothing transaction over the entire migration, so an incomplete destination must remain offline.

Stream database rows into OfficeIMO.Data.Arrow

Each relational provider exposes QueryReaderAsync, which returns an owned, forward-only DbaDataReader. Disposing it closes the provider reader, command, and any connection DbaClientX opened. Provider failures raised later by synchronous or asynchronous row navigation and field access are normalized to the same sanitized exception and caller-cancellation contract used while opening the reader. This is the integration boundary for bounded consumers; DbaClientX provider packages intentionally do not depend on Apache.Arrow.

using DBAClientX;
using OfficeIMO.Data.Arrow;

var client = new PostgreSql();
await using var reader = await client.QueryReaderAsync(
    connectionString,
    "select id, payload from public.events order by id",
    cancellationToken: cancellationToken);

await foreach (var batch in reader.ReadArrowBatchesAsync(
    new ArrowReadOptions { BatchSize = 16_384 },
    cancellationToken))
{
    using (batch)
    {
        // Consume one bounded Apache Arrow RecordBatch.
    }
}

OfficeIMO.Data.Arrow also provides OpenArrowStream and ExportArrowCStream. Keep the returned stream owner alive until its managed or native consumer has finished, and dispose the DbaDataReader last. SQL Server, PostgreSQL, MySQL, Oracle, and SQLite expose the same owned async-reader shape.

Build SQL

New-DbaXQuery -TableName 'dbo.Users' -Limit 50 -Compile

Discover Metadata

Get-DbaXMetadata `
    -Provider SqlServer `
    -Type Table `
    -ConnectionString 'Server=.;Database=master;Encrypt=True;TrustServerCertificate=True;Integrated Security=True'

Get-DbaXMetadata `
    -Provider SQLite `
    -Type Column `
    -ConnectionString '.\app.db' `
    -Table Users

Get-DbaXMetadata `
    -Provider PostgreSql `
    -Type ForeignKey `
    -ConnectionString 'Host=localhost;Database=app;Username=user;Password=secret' `
    -Schema public

Get-DbaXMetadata `
    -Provider Oracle `
    -Type Routine `
    -ConnectionString 'User Id=app;Password=secret;Data Source=localhost/XEPDB1'

Write Table Data

Use Write-DbaXTableData when the data is already in PowerShell and the next step is a database table. The command accepts DataTable, DataView, IDataReader, DataRow, hashtables, and regular objects from the pipeline. SQL Server IDataReader input streams directly into SqlBulkCopy; when passing a reader through -InputObject, wrap it as -InputObject (, $reader) so PowerShell treats it as one object.

$rows = @(
    [pscustomobject]@{ Id = 1; DisplayName = 'Alice' }
    [pscustomobject]@{ Id = 2; DisplayName = 'Bob' }
)

$rows | Write-DbaXTableData `
    -Provider SqlServer `
    -ConnectionString 'Server=.;Database=App;Encrypt=True;TrustServerCertificate=True;Integrated Security=True' `
    -DestinationTable 'dbo.ImportUsers' `
    -AutoCreateTable `
    -BatchSize 5000 `
    -PassThru

Import CSV with PSWriteOffice, then hand the reader to DbaClientX for the SQL Server write:

$reader = $null
try {
    $reader = Import-OfficeCsv .\Users.csv -AsDataReader -InferSchema
    Write-DbaXTableData `
        -Provider SqlServer `
        -ConnectionString 'Server=.;Database=App;Encrypt=True;TrustServerCertificate=True;Integrated Security=True' `
        -DestinationTable 'dbo.ImportUsers' `
        -InputObject (, $reader) `
        -AutoCreateTable `
        -BatchSize 5000
} finally {
    if ($null -ne $reader) {
        $reader.Dispose()
    }
}

For Excel, or for providers that prefer a fully materialized table, use the DataTable output shape.

Export SQL rows to CSV or compressed CSV by keeping the database read and file write in their owning libraries:

$rows = Invoke-DbaXQuery `
    -Server 'sql01' `
    -Database 'App' `
    -Query 'SELECT Id, DisplayName, CreatedUtc FROM dbo.Users' `
    -ReturnType DataTable `
    -TrustServerCertificate

$rows | Export-OfficeCsv -Path .\Users.csv
$rows | Export-OfficeCsv -Path .\Users.csv.gz -CompressionType GZip

For the fastest SQL Server to CSV export path, stream an owned DbDataReader from DbaClientX directly into PSWriteOffice and dispose the reader when the file writer is done:

$reader = $null
try {
    $reader = Invoke-DbaXQuery `
        -Server 'sql01' `
        -Database 'App' `
        -TrustServerCertificate `
        -Query 'SELECT Id, DisplayName, CreatedUtc FROM dbo.Users' `
        -AsDataReader `
        -ErrorAction Stop
    Export-OfficeCsv -InputObject $reader -Path .\Users.csv
} finally {
    if ($null -ne $reader) {
        $reader.Dispose()
    }
}

Load compressed CSV back into SQL Server with the same streaming table-write command:

$reader = $null
try {
    $reader = Import-OfficeCsv .\Users.csv.gz -CompressionType GZip -AsDataReader -InferSchema
    Write-DbaXTableData `
        -Provider SqlServer `
        -ConnectionString 'Server=.;Database=App;Encrypt=True;TrustServerCertificate=True;Integrated Security=True' `
        -DestinationTable 'dbo.ImportUsers' `
        -InputObject (, $reader) `
        -AutoCreateTable `
        -BatchSize 5000
} finally {
    if ($null -ne $reader) {
        $reader.Dispose()
    }
}

The same database write path accepts Excel-imported readers:

$reader = Import-OfficeExcel .\Users.xlsx -AsDataReader
try {
    Write-DbaXTableData `
        -Provider SqlServer `
        -ConnectionString 'Server=.;Database=App;Encrypt=True;TrustServerCertificate=True;Integrated Security=True' `
        -DestinationTable 'dbo.ImportUsers' `
        -InputObject (, $reader) `
        -AutoCreateTable `
        -TableLock
} finally {
    $reader.Dispose()
}

Run the full SQL Server -> file -> SQL Server examples when you want to prove both sides together:

.\Module\Examples\Example.CsvRoundTrip.ps1 -Server localhost -Database tempdb -RowCount 100 -KeepArtifacts
.\Module\Examples\Example.ExcelRoundTrip.ps1 -Server localhost -Database tempdb -RowCount 100 -KeepArtifacts

The examples create SQL Server source rows, export them to a .csv or .xlsx file with PSWriteOffice, import the file back as a streaming reader, write to SQL Server with -AutoCreateTable, and fail if any row count does not match.

When another library already gives you an IDataReader, pass the reader as one object so the SQL Server path stays streaming:

Write-DbaXTableData `
    -Provider SqlServer `
    -ConnectionString 'Server=.;Database=App;Encrypt=True;TrustServerCertificate=True;Integrated Security=True' `
    -DestinationTable 'dbo.ImportUsers' `
    -InputObject (, $reader) `
    -BatchSize 5000 `
    -TableLock

When the destination table has SQL Server-specific requirements, opt into the needed knobs explicitly:

$customerTable | Write-DbaXTableData `
    -Provider SqlServer `
    -ConnectionString 'Server=sql01;Database=warehouse;Encrypt=True;TrustServerCertificate=True;Integrated Security=True' `
    -DestinationTable 'staging.Customers' `
    -AutoCreateTable `
    -ColumnMap @{ CustomerName = 'DisplayName'; CustomerId = 'Id' } `
    -TableLock `
    -KeepIdentity `
    -KeepNulls `
    -NotifyAfter 5000 `
    -PassThru

Keep file-format conversion in the owning library. DbaClientX does small PowerShell input shaping: TimeSpan values stay scalar, scalar pipeline input becomes a Value column, and a single enumerable input expands into rows. For richer Excel, CSV, JSON, or document rules, shape the data first and pass DbaClientX a DataTable, IDataReader, or object stream.

Use Transactions

Invoke-DbaXTransaction -Server 'sql01' -Database 'App' -ScriptBlock {
    param($client)

    $client.ExecuteNonQuery(
        'sql01',
        'App',
        $true,
        'UPDATE dbo.Users SET IsActive = 1 WHERE Id = 1',
        $null,
        $true
    )
}

Transaction helpers honor -WhatIf and -Confirm, commit when the script block succeeds, roll back on failure, and dispose the provider client in finally.

SQL Server Benchmarks

Current workstation timings are below. Commands, measured operations, validation, and artifact details are in SQL Server benchmark notes.

Write Benchmark

Scenario	Variables	Host	Operation	DbaClientX	dbatools	Result
25000 rows / batch 5000 / Class	BatchSize=5000, InputKind=Class, RowCount=25000	Core-7.6.3	Write	1.00x (373ms)	5.70x (2.13s)	DbaClientX fastest
25000 rows / batch 5000 / DataReader	BatchSize=5000, InputKind=DataReader, RowCount=25000	Core-7.6.3	Write	1.00x (70ms)	Skipped	DbaClientX only successful
25000 rows / batch 5000 / DataTable	BatchSize=5000, InputKind=DataTable, RowCount=25000	Core-7.6.3	Write	1.00x (40ms)	1.51x (60ms)	DbaClientX fastest
25000 rows / batch 5000 / PSCustomObject	BatchSize=5000, InputKind=PSCustomObject, RowCount=25000	Core-7.6.3	Write	1.00x (423ms)	5.12x (2.17s)	DbaClientX fastest

Read Benchmark

Scenario	Variables	Host	Operation	DbaClientX	dbatools	Result
25000 rows / DataTableAll	ReadShape=DataTableAll, RowCount=25000	Core-7.6.3	Read	1.00x (32ms)	1.33x (43ms)	DbaClientX fastest
25000 rows / PSObjectAll	ReadShape=PSObjectAll, RowCount=25000	Core-7.6.3	Read	1.00x (151ms)	2.03x (308ms)	DbaClientX fastest

SQL Server CSV Export

Scenario	Variables	Host	Operation	DbaClientXReader	bcp	DbaClientXDataTable	DbaClientXPowerShellStream	dbatools	Result
100000 rows / CSV export	RowCount=100000	Core-7.6.4	Export	1.00x (58ms)	1.92x (112ms)	17.82x (1.04s)	19.32x (1.13s)	1.08x (63ms)	DbaClientXReader fastest

Office File Round Trip

Scenario	Variables	Host	Operation	DbaClientX	dbatools	Result
100000 rows / Csv	ColumnShape=Default, FileKind=Csv, RowCount=100000	Core-7.6.3	RoundTrip	1.00x (639ms)	4.07x (2.60s)	DbaClientX fastest
100000 rows / CsvGZip	ColumnShape=Default, FileKind=CsvGZip, RowCount=100000	Core-7.6.3	RoundTrip	1.00x (658ms)	3.99x (2.63s)	DbaClientX fastest
100000 rows / CsvGZipTyped	ColumnShape=Default, FileKind=CsvGZipTyped, RowCount=100000	Core-7.6.3	RoundTrip	1.00x (561ms)	1.10x (614ms)	DbaClientX fastest
100000 rows / CsvTyped	ColumnShape=Default, FileKind=CsvTyped, RowCount=100000	Core-7.6.3	RoundTrip	1.00x (567ms)	1.18x (671ms)	DbaClientX fastest
100000 rows / CsvTyped / Mapped columns	ColumnShape=Mapped, FileKind=CsvTyped, RowCount=100000	Core-7.6.3	RoundTrip	1.00x (282ms)	1.92x (541ms)	DbaClientX fastest

The affinity-pinned streaming XLSX round-trip comparison is documented in the SQL Server benchmark notes. On this source-linked run, the DbaClientX/PSWriteOffice/OfficeIMO path used 6.2-6.6% of ImportExcel's median time while passing the same SQL-side value and schema validation.

.NET Usage

Query Data

using DBAClientX;
using System.Data;

using var sqlServer = new SqlServer {
    ReturnType = ReturnType.DataTable
};

var result = await sqlServer.QueryAsync(
    "SQL1",
    "master",
    integratedSecurity: true,
    query: "SELECT TOP 1 * FROM sys.databases");

if (result is DataTable table) {
    foreach (DataRow row in table.Rows) {
        Console.WriteLine(row["name"]);
    }
}

Stream Typed Rows

Query/QueryAsync build a whole DataTable in memory. For large results, stream instead and pick the lightest shape that fits:

API	Memory	Use when
Query, QueryAsync (DataTable/DataSet)	Whole result	Small results, PowerShell output, code that needs a DataTable
QueryStreamAsync (DataRow)	One row plus a detached DataRow per row	Existing DataRow code; SQLite may switch to a wider row schema mid-stream
QueryStreamAsync<T>(…, map)	One row	Large results, typed consumers, reports and exports
QueryStreamAsync<T>(…).ChunkAsync(n)	One chunk	Batched writes: bulk inserts, dataset chunks, HTTP responses
QueryReaderAsync (DbaDataReader)	One row	Full reader control, for example SqlBulkCopy from a reader

using DBAClientX;
using DBAClientX.Mapping;

using var sqlite = new DBAClientX.SQLite();

// Map columns to properties by name (case-insensitive), converting SQLite text dates, enums and GUIDs.
await foreach (var evt in sqlite.QueryStreamAsync("monitoring.db", "SELECT Id, Zone, CreatedUtc FROM Events", DbaRecordMapper.For<EventRow>())) {
    Console.WriteLine($"{evt.Id} {evt.Zone}");
}

// Hand-written maps are fastest; chunk them for batch consumers.
await foreach (var chunk in sqlite.QueryStreamAsync("monitoring.db", "SELECT Id, Zone FROM Events", r => (r.GetInt64(0), r.GetString(1))).ChunkAsync(5_000)) {
    await WriteBatchAsync(chunk);
}

DbaRecordMapper.Values() maps each row to an object?[], with DBNull replaced by null, for columnar consumers. Mappers must copy what they need, because the record is reused for the next row. The typed streams are available on .NET Standard 2.1, .NET 8 and later for SQL Server, PostgreSQL, MySQL, Oracle and SQLite; SQLite also has QueryReadOnlyStreamAsync<T> for a read-only stream of a database file and QueryStreamWithConnectionStringAsync<T> for other connection options. Canceling a SQLite stream interrupts the statement that is running.

DbaRecordMapper.Bind<T>(schema) resolves column ordinals once per result. It uses the same conversions and null handling as For<T>(), without checking every column name on every row. Bind before reading values, and bind again after NextResult() or any change to the column layout. The delegate retains column metadata and can be reused with independent readers that have that same layout. Use For<T>() when one delegate must automatically adapt to different layouts.

Func<System.Data.IDataRecord, EventRow>? map = null;
var events = await sqlite.QueryAsListAsync(
    "monitoring.db", "SELECT Id, Zone, CreatedUtc FROM Events",
    row => map!(row),
    initialize: schema => map = DbaRecordMapper.Bind<EventRow>(schema));

Bulk Insert

using System.Data;
using DBAClientX;

using var sqlServer = new SqlServer();
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Rows.Add(1, "Example");

sqlServer.BulkInsert(
    serverOrInstance: "SQL1",
    database: "App",
    integratedSecurity: true,
    table: table,
    destinationTable: "dbo.ImportUsers",
    options: new SqlServerBulkInsertOptions
    {
        AutoCreateTable = true,
        BulkCopyOptions = Microsoft.Data.SqlClient.SqlBulkCopyOptions.TableLock
    },
    batchSize: 1000,
    bulkCopyTimeout: 60);

Build Connection Strings

var sqlServer = DBAClientX.SqlServer.BuildConnectionString("SQL1", "App", integratedSecurity: true, ssl: true);
var postgres = DBAClientX.PostgreSql.BuildConnectionString("localhost", "app", "user", "password", ssl: true);
var mysql = DBAClientX.MySql.BuildConnectionString("localhost", "app", "user", "password", ssl: true);
var sqlite = DBAClientX.SQLite.BuildConnectionString("app.db");

SQLite accepts ordinary filenames and file: URIs. Long Windows filenames use extended paths and the locking-capable win32-longpath VFS unless the caller selects a VFS. File URI authorities must be empty or exactly localhost; pass Windows network shares as ordinary UNC filenames. Explicit connection options take precedence over URI hints, and native options such as immutable=1 are retained. Shared-memory URI targets stay in memory. Table-copy and backup guards resolve file and directory links, including aliases in parent directories, before changing a destination.

Query Builder

using DBAClientX.QueryBuilder;

var query = new Query()
    .Select("*")
    .From("users")
    .Where("name", "Alice")
    .Where("age", ">", 30);

var (sql, parameters) = QueryBuilder.CompileWithParameters(query);

Identifier methods always quote identifiers. Use explicit raw methods for trusted SQL expressions, and never pass user input to them:

var aggregate = new Query()
    .Select("DepartmentId")
    .SelectRaw("COUNT(*)")
    .From("Employees")
    .GroupBy("DepartmentId")
    .HavingRaw("COUNT(*)", ">", 5);

var joined = new Query()
    .Select("u.Id", "o.Total")
    .From("Users", "u")
    .Join("Orders", "o", "u.Id", "=", "o.UserId");

Negative Limit, Offset, and Top values are rejected before compilation.

Compound queries apply root ordering and paging to the combined result. Mixed Union/UnionAll chains stay flat, preserving outer-row references in correlated subqueries. Nested, locally paged and other mixed operands use derived tables to preserve their grouping. MySQL/MariaDB rejects correlations from these derived tables to an enclosing query; the compiler rejects explicit outer qualifiers in such operands with NotSupportedException. Move the correlation outside the grouped operand or keep a flat union chain. Qualify correlated columns explicitly: unqualified columns and omitted source databases require connection/schema metadata and remain subject to the server's name resolution. Raw sources retain their index hints, selected partitions, table groups and MariaDB FOR SYSTEM_TIME clauses; these modifiers do not become aliases. MySQL/MariaDB executable comments can change bindings depending on the server version, so fragments containing them defer correlation binding to the server. The original SQL is preserved.

Paging

KeysetPagination pages by key values (seek paging), so page 1,000 costs the same as page 1 when an index covers the keys. OffsetPagination supports jumping to any page but reads every earlier row. Both return a copy of the source query with dialect-correct TOP/LIMIT/FETCH, fetch one extra row to detect the next page, and hand back an opaque, URL-safe cursor. Compile page queries with CompileWithParameters so cursor values are sent as parameters.

var paging = new KeysetPagination(100, KeysetColumn.Desc("CreatedUtc"), KeysetColumn.Asc("Id"));
var source = new Query().Select("Id", "CreatedUtc", "Message").From("Events").Where("Zone", zone);

// CompileWithNamedParameters returns parameters keyed by placeholder (@p0…, or :p0… for Oracle).
var (sql, parameters) = paging.CreatePageQuery(source, cursor).CompileWithNamedParameters(SqlDialect.SQLite);
using var sqlite = new DBAClientX.SQLite { ReturnType = ReturnType.DataTable };
var table = (DataTable)(await sqlite.QueryAsync(path, sql, parameters))!;
var page = paging.CreatePage(table); // page.Items, page.NextCursor (null on the last page)

To read a whole result page by page with bounded memory, let the pagination drive a typed stream. StreamAsync yields rows across pages; ReadPagesAsync yields pages with their cursors, so a consumer can stop and resume:

var paging = new KeysetPagination(5_000, KeysetColumn.Desc<long>("CreatedUtcMs"), KeysetColumn.Asc<long>("Id"));
await foreach (var evt in paging.StreamAsync(
    source,
    SqlDialect.SQLite,
    (sql, parameters, ct) => sqlite.QueryStreamAsync(path, sql, DbaRecordMapper.For<EventRow>(), parameters, cancellationToken: ct),
    evt => new object?[] { evt.CreatedUtcMs, evt.Id })) {
    // memory stays bounded by the page size
}

Automatic keyset streaming checks that every row advances, including within a page. Numeric, boolean and temporal keys with matching codec-normalized runtime types use their natural ordering with each column's direction. Mixed numeric storage types (for example, SQLite INTEGER and REAL in one key column), text, GUID, binary and provider-specific keys require a database-specific comparator. For these keys, set CompareKeys to compare complete key tuples in the database's ordering; return a positive value when the first tuple comes after the second. This avoids assuming that CLR string or GUID ordering matches the database collation. Floating-point keys containing NaN also require CompareKeys: PostgreSQL, for example, sorts NaN after every other value, whereas CLR comparison puts it first. The default path rejects NaN keys, including a single row or a resume cursor, before yielding a page. Cancellation stops both page queries and rows already buffered in the current page.

QueryParameters.ToDictionary(values, dialect) converts the positional values from CompileWithParameters into the same named shape.

Keyset columns must be unquoted, non-null and unique together (end with the primary key). The source query must not set ORDER BY (keyset), Limit, Offset, Top, or UNION; offset paging requires ORDER BY on a unique column set.

A key can sort under a collation or by an expression, in both the seek condition and ORDER BY: KeysetColumn.Asc("Name").WithCollation("DBX_NOCASE") sorts without case (with SQLiteUnicodeText registered), and KeysetColumn.Expression("+\"Seen\"", "Seen", descending: true) sorts by a column while SQLite's unary plus keeps that column's index from choosing the plan. Expressions are trusted SQL. Both can make different rows equal, so the last key must be a plain unique column: new KeysetPagination(100, new[] { KeysetColumn.Asc("Name").WithCollation("NOCASE") }, KeysetColumn.Asc("Id")).

• Keyset page queries must be compiled with CompileWithParameters; Compile() throws, because literal SQL loses precision such as fractional seconds.
• Cursors are unsigned by default, so treat them as untrusted input. Declare key types (KeysetColumn.Asc<long>("Id"), matching the CLR type the provider returns) to reject cursor values of another type, and set SigningKey (32 random bytes kept on the server and used only for paging) to reject any modified cursor. Signing proves the server issued a cursor; it does not authorize access, so keep applying the caller's filters. For offset paging, also cap the offset you accept.
• SQL Server sends DateTime parameters as datetime (1/300 s precision). For datetime2 keys, set UseDateTime2ForDateTimeParameters = true on the SqlServer client or pass an explicit SqlDbType.DateTime2 parameter type, so page boundaries do not repeat or skip rows.

Qualify a SQL Server backup

The SQL Server provider creates dedicated checksummed full, differential and log backups, preflights pinned single-backup or ordered chain plans, restores to new database names with explicit file relocation, and runs full or physical-only CHECKDB. See the SQL Server recovery example for the separate verification, restore and integrity steps, required permissions and cleanup responsibilities.

Supported .NET Versions

Component	Windows	Linux/macOS
Provider libraries	.NET Framework 4.7.2, .NET 8.0, .NET 10.0	.NET 8.0, .NET 10.0
PowerShell binary modules	.NET Framework 4.7.2, .NET 8.0	.NET 8.0
Examples	.NET 8.0, .NET 10.0	.NET 8.0, .NET 10.0
Benchmarks	Current PowerShell host through PSPublishModule	Current PowerShell host through PSPublishModule

Repository Structure

Path	Purpose
DbaClientX.Core	Shared base client, retry logic, query builder, connection validation, invoker abstractions
DbaClientX.Dbf	Forward-only DBF/xBase table and DBT/FPT memo reader without SQL providers
DbaClientX.SqlServer	SQL Server provider
DbaClientX.PostgreSql	PostgreSQL provider
DbaClientX.MySql	MySQL provider
DbaClientX.SQLite	SQLite provider
DbaClientX.Oracle	Oracle provider
DbaClientX.PowerShell	DbaClientX binary cmdlets and PowerShell-facing helpers
FabricClientX.Core	Fabric REST transport and workspace/item contracts
FabricClientX.PowerBI	Power BI semantic-model and refresh workflows
FabricClientX.OfficeIMO	Optional OfficeIMO CSV to Warehouse/Power BI adapter
FabricClientX.PowerShell	FabricClientX binary cmdlets
Module	DbaClientX module manifest, tests, examples, and build script
Module-FabricClientX	FabricClientX module manifest, tests, documentation, and build script
DbaClientX.Examples	C# usage examples
DbaClientX.Tests	xUnit tests
Build	Project release configuration

Examples

• PowerShell examples: Module/Examples
• C# examples: DbaClientX.Examples
• Benchmarks: Module/Examples/Benchmark.SqlServerDataMovement.ps1, Module/Examples/Benchmark.SqlServerCsvExport.ps1, Module/Examples/Benchmark.OfficeFileRoundTrip.ps1
• Data movement guide: docs/data-movement.md
• SQL Server benchmark notes: docs/sqlserver-benchmark-notes.md

Useful example files:

• Example.QuerySqlServer.ps1
• Example.SqlServerDataMovement.ps1
• Example.CsvRoundTrip.ps1
• Example.ExcelRoundTrip.ps1
• Benchmark.SqlServerDataMovement.ps1
• Benchmark.SqlServerCsvExport.ps1
• Benchmark.OfficeFileRoundTrip.ps1
• Example.QueryPostgreSql.ps1
• Example.QueryMySql.ps1
• Example.QueryOracle.ps1
• Example.QuerySQLite.ps1
• BulkInsertExample.cs
• TransactionExample.cs
• ParameterizedQueryExample.cs

Build and Test

dotnet restore DbaClientX.sln
dotnet build DbaClientX.sln -c Release
dotnet test DbaClientX.sln -c Release --framework net8.0

PowerShell module tests:

.\Module\DbaClientX.Tests.ps1
.\Module-FabricClientX\FabricClientX.Tests.ps1

Module package builds:

.\Module\Build\Build-Module.ps1 -RunMode Build
.\Module-FabricClientX\Build\Build-Module.ps1 -RunMode Build

Regenerate command documentation and external help:

.\Module\Build\Build-Module.ps1 -RunMode Documentation
.\Module-FabricClientX\Build\Build-Module.ps1 -RunMode Documentation

Documentation mode regenerates command Markdown and external help without running the package lanes, signing, publishing, or changing release versions. Build and Publish keep their coordinated package lanes.

Release Packaging

Package publishing is intentionally manual in this repository because releases are signed locally with the USB key certificate.

The DbaClientX PowerShell module and its eight NuGet packages use one coordinated version. FabricClientX remains a separate release train.

Generate a package plan without changing versions:

.\Build\Build-Project.ps1 -Plan $true
.\Build\Build-Project.ps1 -ConfigPath .\Build\fabricclientx.build.json -Plan $true

Build signed release candidates without publishing:

pwsh.exe -NoLogo -NoProfile -File .\Module\Build\Build-Module.ps1 -RunMode Build
pwsh.exe -NoLogo -NoProfile -File .\Module-FabricClientX\Build\Build-Module.ps1 -RunMode Build

Publish the coordinated DbaClientX module and NuGet release:

pwsh.exe -NoLogo -NoProfile -File .\Module\Build\Build-Module.ps1 -RunMode Publish

Publish FabricClientX separately:

pwsh.exe -NoLogo -NoProfile -File .\Module-FabricClientX\Build\Build-Module.ps1 -RunMode Publish

Package build configuration lives in Build/project.build.json and Build/fabricclientx.build.json. Package artifacts are generated under Artefacts/DbaClientX/ProjectBuild and Artefacts/FabricClientX/ProjectBuild; coordinated release assets are staged under each module's Artefacts/UploadReady directory.

Notes

• The solution enables nullable reference types and .NET analyzers via Directory.Build.props.
• SourceLink is enabled for provider projects for easier debugging into packages.
• SQL Server uses Microsoft.Data.SqlClient.
• PowerShell 7 package builds use a module-scoped AssemblyLoadContext so DbaClientX can coexist more safely with other modules that load overlapping assemblies.
• Build/Build-Project.ps1 is for package-only plans and local builds. Version updates and publication must run through the matching module build so package and module versions cannot diverge.