SQLServerInteraction

SQLServerInteraction is a thin layer over Microsoft.Data.SqlClient for .NET 9. A SQLServerInstance holds a connection string, and each of its methods opens a connection, runs one piece of work (a query, a command, a bulk copy, a backup, a schema lookup) and closes the connection again; BeginTransactionAsync gives an object that runs several of them in one transaction. A separate QueryBuilder assembles SELECT statements from strings.

dotnet add package SQLServerInteraction

Everything is in the SQLServerInteraction namespace.

Read this first: what is and is not parameterized

Values you pass in a parameter dictionary or a SqlParameter array are sent as SQL parameters. Table, column, index and database names are quoted as identifiers, so a name is only ever read as a name. Backup paths are sent as parameters.

Some strings are SQL by design and are run as written: the sql strings you pass to the query and command methods, the conditions of UpdateDataAsync, DeleteDataAsync and BulkCopyAsync, and every string QueryBuilder takes, table names included. Build them in your own code, and put any value that comes from a user, a file or another system in the parameters instead. The section "Methods that run your SQL", further down, lists them.

Connecting

Pass a connection string, or build one with SQLServerConnectionString:

using SQLServerInteraction;

// Windows authentication (Integrated Security)
var trusted = new SQLServerConnectionString("your-server", "YourDatabase");
Console.WriteLine(trusted.GetConnectionString());
// Data Source=your-server;Initial Catalog=YourDatabase;Integrated Security=True;Encrypt=True

// SQL Server authentication, encryption off, one extra keyword
var login = new SQLServerConnectionString("your-server", "YourDatabase", "your-user", "your-password",
    encrypt: false, additionalParameters: "TrustServerCertificate=True;");
Console.WriteLine(login.GetConnectionString());
// Data Source=your-server;Initial Catalog=YourDatabase;User ID=your-user;Password=your-password;Encrypt=False;Trust Server Certificate=True

var db = new SQLServerInstance(trusted);
var db2 = new SQLServerInstance("Server=your-server;Database=YourDatabase;Trusted_Connection=True;Encrypt=True;");

How GetConnectionString() builds the string:

It uses SqlClient's SqlConnectionStringBuilder, so a value containing ;, = or a quote, such as a password, is quoted and reaches the server as written.
Encrypt defaults to true.
A null or empty UserId gives Integrated Security=True, and the password is ignored. Otherwise the string carries User ID and Password.
additionalParameters is parsed as a connection string and merged in. Each of its keywords replaces the one the other arguments set, as it did when 1.x appended it to the end, so "Encrypt=Strict" there gives strict encryption. Text that is not a valid connection string, or an unknown keyword, throws ArgumentException.

Constructing a SQLServerInstance does not connect. Each method call opens its own SqlConnection and disposes it before returning, so connection pooling is whatever the connection string sets (SqlClient pools by default). Commands use SqlClient's default 30-second command timeout; only BulkCopyAsync and BulkMergeAsync take a timeout. Every method is async and takes an optional CancellationToken as its last argument.

Errors are not caught: a server error surfaces as SqlClient's SqlException, and a failed connection as whatever SqlConnection.OpenAsync throws.

Keep server names, user names and passwords out of source code. Read them from configuration or a secret store.

Queries

using System.Data;
using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

// Every row and column, as a DataTable
DataTable orders = await db.ExecuteQueryAsync("SELECT OrderId, Total FROM dbo.Orders");

// The first column of every row, converted to T
List<int> ids = await db.ExecuteQueryAsync<int>("SELECT OrderId FROM dbo.Orders");
List<string> names = await db.ExecuteQueryAsync<string>("SELECT Name FROM dbo.Customers");

// The first column of the first row, converted to T
int count = await db.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM dbo.Orders");
decimal total = await db.ExecuteScalarAsync<decimal>("SELECT SUM(Total) FROM dbo.Orders");

ExecuteQueryAsync<T> reads only the first column and converts each value with Convert.ChangeType. A nullable type such as int? converts to its underlying type, and NULL gives null. Otherwise a NULL becomes "" for string and throws InvalidCastException for a value type.
ExecuteScalarAsync<T> returns default(T) when the query returns no rows or a NULL, and otherwise converts the value with Convert.ChangeType. A nullable type converts to its underlying type, so ExecuteScalarAsync<int?> returns the number, or null for no rows or NULL.
None of these three take parameters. To filter on a value, use ExecuteQueryToObjectListAsync<T> below, which does.

Mapping rows to objects

ExecuteQueryToObjectListAsync<T> creates one T per row and sets each public property from the column of the same name. SQLServerInstance.Column maps a property to a differently named column:

using SQLServerInteraction;

public class Customer
{
    public int CustomerId { get; set; }

    [SQLServerInstance.Column("Customer Name")]
    public string? Name { get; set; }

    public DateTime? LastOrder { get; set; }
}

using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

List<Customer> customers = await db.ExecuteQueryToObjectListAsync<Customer>(
    "SELECT CustomerId, [Customer Name], LastOrder FROM dbo.Customers WHERE Region = @Region",
    new Dictionary<string, object> { ["Region"] = "West" });

List<Customer> all = await db.ExecuteQueryToObjectListAsync<Customer>(
    "SELECT CustomerId, [Customer Name], LastOrder FROM dbo.Customers");

T needs a public parameterless constructor.
Every public property needs a column in the result. A property with no matching column throws IndexOutOfRangeException, and a read-only property throws ArgumentException once its column has a value; leave such properties out of the class or alias a column to them in the SQL.
Column names match without regard to case. A NULL leaves the property at its default value. Other values are converted with Convert.ChangeType to the property type (or the underlying type of a nullable property), so a property whose type the column value cannot convert to, such as an enum, throws InvalidCastException.
Parameter names work with or without the leading @. A null value is sent as SQL NULL.

Commands that return nothing

using Microsoft.Data.SqlClient;
using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

await db.ExecuteSQLAsync("UPDATE dbo.Orders SET Archived = 1 WHERE OrderDate < '2020-01-01'");
await db.ExecuteSQLAsync("EXEC dbo.RefreshTotals");

// Dictionary keys are parameter names, with or without the @
await db.ExecuteNonQueryWithParametersAsync(
    "UPDATE dbo.Orders SET Status = @Status WHERE OrderId = @OrderId",
    new Dictionary<string, object> { ["Status"] = "Shipped", ["OrderId"] = 42 });

// SqlParameter objects, for control over type and size
await db.ExecuteParameterizedQueryAsync(
    "DELETE FROM dbo.Orders WHERE OrderId = @OrderId",
    [new SqlParameter("@OrderId", 42)]);

// A stored procedure by name, with optional parameters
await db.ExecuteStoredProcedureAsync("dbo.CloseOrder", [new SqlParameter("@OrderId", 42)]);
await db.ExecuteStoredProcedureAsync("dbo.RebuildIndexes");

None of these return the number of rows affected or any result set; use ExecuteQueryAsync or ExecuteScalarAsync<T> to read data back. ExecuteParameterizedQueryAsync runs a command, not a query, despite its name.
Every method that takes a parameter dictionary names the parameters the same way: the @ is added only when the key lacks it, so "Status" and "@Status" are both @Status.
A null dictionary value is sent as SQL NULL, as DBNull.Value is.
The SqlParameter methods use the array you pass; a SqlParameter can belong to only one command, so build new ones for each call.

Transactions

using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

await db.ExecuteTransactionAsync([
    "UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountId = 1",
    "UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountId = 2",
]);

ExecuteTransactionAsync runs the commands in order on one connection inside one transaction. If any command throws, the transaction is rolled back and the exception is rethrown; otherwise it is committed. The commands take no parameters.

To run the library's own methods in one transaction, begin one and call them on it:

await using var transaction = await db.BeginTransactionAsync();

await transaction.DeleteDataAsync("dbo.Orders", "CustomerId = @Id", new Dictionary<string, object> { ["Id"] = 7 });
await transaction.InsertDataAsync("dbo.Orders", new Dictionary<string, object> { ["CustomerId"] = 7, ["Total"] = 10m });
await transaction.BulkCopyAsync(orderLines, "dbo.OrderLines");
int pending = await transaction.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM dbo.Orders WHERE Status = 'Pending'");

await transaction.CommitAsync();

// With an isolation level
await using var work = await db.BeginTransactionAsync(IsolationLevel.Serializable);
await work.BulkMergeAsync(rows, "dbo.Orders", ["OrderId"]);
await work.CommitAsync();

BeginTransactionAsync opens one connection, begins a transaction on it, and returns a SQLServerTransaction that owns both. It takes an optional IsolationLevel; without one, SqlClient's default (read committed) applies. The object has the data methods of SQLServerInstance with the same signatures, minus any useTransaction parameter: ExecuteQueryAsync, ExecuteQueryAsync<T>, ExecuteScalarAsync<T>, ExecuteQueryToObjectListAsync<T>, ExecuteParameterizedQueryAsync, ExecuteSQLAsync, ExecuteNonQueryWithParametersAsync, ExecuteStoredProcedureAsync, InsertDataAsync, InsertDataAsync<T>, UpdateDataAsync, DeleteDataAsync, MergeDataAsync, BulkCopyAsync, BulkMergeAsync, IndexCreateAsync and IndexDropAsync. A query on it sees the transaction's own uncommitted work.

CommitAsync commits. RollbackAsync rolls back early. Disposing the object without a commit rolls back, so an await using block is enough: an exception in the middle leaves the database as it was.
After an exception from any method, roll back, or let the await using block do it. Do not catch the exception and carry on to CommitAsync: for some errors, such as a constraint violation, SQL Server fails only that statement and leaves the transaction open, so the commit would keep the work done before the failure.
To undo part of a transaction on purpose, mark a savepoint with SaveAsync(name) and return to it with RollbackToAsync(name); the transaction stays open and can still be committed. A name is one identifier of up to 32 characters, and marking it again moves it. Rolling back to a savepoint works after an error that failed one statement, such as a constraint violation, but not after one that made the transaction uncommittable, such as a deadlock: SQL Server refuses it, and only a full rollback is left.
If CommitAsync itself throws, for example because the connection dropped, the object cannot be used again and says so; dispose it, and whatever the server did not commit is rolled back.
Every method takes an optional CancellationToken as its last argument, on the transaction and on SQLServerInstance alike. A cancelled call throws OperationCanceledException and the transaction can still be rolled back.
After a commit, rollback or dispose, every method throws InvalidOperationException (or ObjectDisposedException), so a transaction cannot be reused by mistake.
The object is not thread-safe. Use it from one thread, or one async flow, at a time, and dispose it promptly: it holds a pooled connection and the transaction's locks until then.
Backup, restore, CSV export, the schema lookups and ExecuteScriptFromFileAsync are not on it. Each of those still opens its own connection.

Running a script file

using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

await db.ExecuteScriptFromFileAsync(@"C:\scripts\create-tables.sql");

The whole file is read and sent as a single batch. GO is a separator understood by SQL Server tools, not by the server, so a script that uses it fails; split such a script and run each part with ExecuteSQLAsync.

Inserting, updating and deleting

using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

// Column name -> value
await db.InsertDataAsync("dbo.Customers", new Dictionary<string, object>
{
    ["CustomerId"] = 7,
    ["Customer Name"] = "Contoso",
    ["LastOrder"] = DBNull.Value,
});

// The condition is SQL; its values go in the last argument
await db.UpdateDataAsync("dbo.Customers",
    new Dictionary<string, object> { ["Customer Name"] = "Contoso Ltd" },
    "CustomerId = @Id",
    new Dictionary<string, object> { ["Id"] = 7 });

await db.DeleteDataAsync("dbo.Customers", "CustomerId = @Id", new Dictionary<string, object> { ["Id"] = 7 });

// One object -> one row
await db.InsertDataAsync(new Customer { CustomerId = 8, Name = "Fabrikam" }, "dbo.Customers");

Table names can have one, two or three parts (Customers, dbo.Customers, MyDb.dbo.Customers), each plain or in brackets ([dbo].[My Customers]). Every part is quoted, so a name with a space or any other character works and cannot change the SQL. A malformed name (an empty part, an unclosed bracket, more than three parts, a part over 128 characters) throws ArgumentException.
Each dictionary key is one column name, plain or bracketed (Customer Name or [Customer Name]), and is quoted the same way. Bracket a column name that contains a dot. The values are sent as parameters named @__value_0, @__value_1 and so on, so do not give condition parameters those names.
A null value is sent as NULL, as DBNull.Value is.
UpdateDataAsync and DeleteDataAsync take the condition as SQL text without the WHERE keyword, run as written. An overload of each takes a dictionary of parameters for the condition as its last argument. Parameter names work with or without the @, and null is sent as NULL.
The condition is required. An empty or whitespace condition throws ArgumentException instead of affecting every row; to update or delete every row, pass "1 = 1".
InsertDataAsync<T> inserts one row with a column for every public instance property of T that has a getter, named by its SQLServerInstance.Column attribute or else by the property, so the example above writes to Customer Name. Static properties and indexers are left out. A null property value is sent as NULL. Include only properties that have columns; an identity column fails unless IDENTITY_INSERT is on.
None of these return the number of rows affected.

Bulk copy

using System.Data;
using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

var table = new DataTable();
table.Columns.Add("OrderId", typeof(int));
table.Columns.Add("Total", typeof(decimal));
table.Rows.Add(1, 19.99m);
table.Rows.Add(2, 5.00m);

// Append the rows
await db.BulkCopyAsync(table, "dbo.Orders");

// Delete the rows matching the condition first, then copy, in one transaction
await db.BulkCopyAsync(table, "dbo.Orders",
    flushTable: true,
    flushWhereClauseCondition: "OrderDate >= @From",
    flushParameters: new Dictionary<string, object> { ["From"] = new DateTime(2026, 1, 1) },
    bulkCopyTimeout: 120,
    batchSize: 5000);

// Copy by name: DataTable column -> destination column. Unmapped columns are not copied.
var source = new DataTable();
source.Columns.Add("Code", typeof(int));
source.Columns.Add("Amount", typeof(decimal));
source.Rows.Add(3, 12.50m);

await db.BulkCopyAsync(source, "dbo.Orders", flushTable: false, flushWhereClauseCondition: null, flushParameters: null,
    columnMappings: new Dictionary<string, string> { ["Code"] = "OrderId", ["Amount"] = "Total" });

BulkCopyAsync writes the rows with SqlBulkCopy. Without columnMappings, columns map by position, not by name, so the DataTable's columns must be in the destination table's order. The table name is quoted as described above, for the DELETE and for SqlBulkCopy.

Parameter	Default	Effect
`flushTable`	`false`	Delete rows from the destination before copying.
`flushWhereClauseCondition`	`null`	With `flushTable`, delete only the rows matching this condition (SQL text without `WHERE`, run as written). Left null, every row is deleted. An empty or whitespace string throws `ArgumentException`.
`flushParameters`	none	Parameters for `flushWhereClauseCondition`, taken by an overload whose first five arguments are `dataTable`, `destinationTableName`, `flushTable`, `flushWhereClauseCondition` and `flushParameters`. Names work with or without the `@`, and null is sent as NULL.
`bulkCopyTimeout`	`30`	Seconds the copy may take before it fails.
`batchSize`	`null`	Rows per batch sent to the server. `null` sends all rows in one batch.
`useTransaction`	`true`	Run the delete and the copy in one transaction, rolled back if either fails, so readers never see the table half-written. With `false`, a failure can leave the table emptied or partly filled.
`columnMappings`	`null`	Taken by the same overload as `flushParameters`, as its last argument. Each key is a DataTable column, matched without regard to case, and its value the destination column it is copied to; only mapped columns are copied, and the others in the destination get their defaults. A destination is a column name, not SQL, matched exactly, case included; brackets around it (`[Sales Region]`) are removed first, and a name with a space needs none. An empty dictionary, a key the DataTable does not have, or an empty destination throws `ArgumentException` before anything is deleted. A destination the table does not have fails when the copy runs, after the flush, which the transaction rolls back. `null` maps by position.

Merging

using System.Data;
using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

// Table to table: update the orders whose OrderId is in Staging, insert the rest
int affected = await db.MergeDataAsync("dbo.Orders_Staging", "dbo.Orders",
    keyColumns: ["OrderId"],
    valueColumns: ["Total", "Status"]);

// ... and delete the orders Staging no longer has
affected = await db.MergeDataAsync("dbo.Orders_Staging", "dbo.Orders", ["OrderId"], ["Total", "Status"],
    deleteUnmatched: true);

// DataTable to table, in one transaction: bulk copy into a temporary table, then merge by OrderId
var rows = new DataTable();
rows.Columns.Add("OrderId", typeof(int));
rows.Columns.Add("Total", typeof(decimal));
rows.Rows.Add(1, 24.99m);
rows.Rows.Add(3, 12.50m);

affected = await db.BulkMergeAsync(rows, "dbo.Orders", keyColumns: ["OrderId"], timeout: 120);

MergeDataAsync runs one MERGE statement from the source table into the target: a target row whose key columns all equal a source row's is updated in the value columns, a source row with no match is inserted with its keys and values, and, with deleteUnmatched, a target row with no match is deleted. BulkMergeAsync does the same for a DataTable: it creates a temporary table shaped like the destination's columns, bulk copies the rows into it, and merges from there, matching each DataTable column to the destination column of the same name, so the columns can be in any order. Every column the DataTable has that is not a key is a value column; a column the DataTable does not have is left alone on an update and takes its default on an insert.

Both return the number of rows inserted, updated and deleted. Every table and column name is quoted as described under "Inserting, updating and deleting"; nothing in these methods is run as SQL you wrote.

Parameter	Default	Effect
`keyColumns`	required	The columns that identify a row. A NULL key never matches, so such a row is inserted on every merge. Two source rows with the same key fail the merge, and so does an empty list, before anything runs.
`valueColumns`	required (`MergeDataAsync` only)	The columns to set on a matched row and to insert with the keys on a new one. Empty inserts the missing keys and leaves matched rows alone. A column in both lists throws `ArgumentException`.
`deleteUnmatched`	`false`	Also delete every target row that matches no source row.
`timeout`	`30` (`BulkMergeAsync` only)	Seconds the copy may take, and then seconds the merge may take.
`batchSize`	`null` (`BulkMergeAsync` only)	Rows per batch sent to the temporary table.
`useTransaction`	`true`	`BulkMergeAsync`: run the copy and the merge in one transaction, rolled back if either fails. `MergeDataAsync`: hold the merge's locks until the transaction commits. A `MERGE` statement is atomic on its own, so with `false` a failure still leaves the target as it was.

Leave an identity column out of the value columns and the DataTable: the merge inserts every column it carries, and SQL Server refuses to insert into an identity column even when no row is new. Key on another column instead.

Exporting to CSV

using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

await db.ExportDataToCSVAsync(@"C:\exports\orders.csv", "SELECT OrderId, Total FROM dbo.Orders");

The file is created or overwritten, in UTF-8 without a byte order mark. The first line holds the column names, joined with commas and not quoted. Each data row has every value in double quotes, with embedded quotes doubled; a NULL is written as "". Values are formatted with the current culture, so dates and decimals follow the machine's regional settings.

Backup and restore

using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

await db.BackupDatabaseAsync(@"D:\Backups\YourDatabase.bak");
DateTime? last = await db.GetLastBackupDateTimeAsync();
await db.RestoreDatabaseAsync(@"D:\Backups\YourDatabase.bak");

Both act on the database named in the connection string. The path is a path on the SQL Server machine, not on the machine running your code, and the server's service account needs access to it.
BackupDatabaseAsync runs BACKUP DATABASE @database TO DISK = @path with no options, the name and path sent as parameters, so a second backup to the same file is appended to it.
RestoreDatabaseAsync switches its connection to master, then runs RESTORE DATABASE @database FROM DISK = @path with no options (no REPLACE, no MOVE). It fails if any other connection is using the database, and idle pooled connections from this process count: call SqlConnection.ClearAllPools() first.
GetLastBackupDateTimeAsync returns the latest backup_finish_date in msdb.dbo.backupset for the current database, or null if there is none. It needs read access to msdb.

Schema and server information

Method	Returns
`DoesDatabaseExistAsync(databaseName)`	`true` if a database of that name exists on the server, ignoring case.
`DoesTableExistAsync(tableName)`	`true` if a table or view with that name exists (`INFORMATION_SCHEMA.TABLES`).
`GetTableNamesAsync()`	The names of all tables and views in the database, without schema names.
`GetColumnNamesAsync(tableName)`	The column names of every table or view with that name.
`GetTableColumnsAsync(tableName)`	Column name to data type name (`int`, `nvarchar`, ...), from `INFORMATION_SCHEMA.COLUMNS`, in column order.
`GetTableSchemaAsync(tableName)`	An empty `DataTable` with the table's columns, types and key, from `SqlDataAdapter.FillSchema`.
`GetTablePrimaryKeyColumnAsync(tableName)`	The first column of the primary key in key order, or null. For a composite key, only that one.
`GetTablePrimaryKeyColumnsAsync(tableName)`	Every column of the primary key, in key order, or an empty list.
`GetTableIndexesAsync(tableName)`	The names of the table's non-clustered indexes that are not the primary key. Clustered indexes are not listed.
`GetTableRowCountAsync(tableName)`	`SELECT COUNT(*)` for the table, as an `int`.
`GetStoredProceduresAsync()`	The names of all stored procedures and functions (`INFORMATION_SCHEMA.ROUTINES`), without schema names.
`GetStoredProcedureParametersAsync(storedProcedureName)`	The procedure's parameter names, with their `@`, from `SqlCommandBuilder.DeriveParameters`. The return value is left out.
`GetDatabaseInformationAsync()`	A dictionary with `DatabaseName`, `DatabaseId`, `CreationDate` (formatted with the current culture) and `Collation`.
`GetDatabaseSizeInBytesAsync()`	The total size of the database's data and log files in bytes (`SUM(size) * 8192` from `sys.master_files`, where `size` counts 8 KB pages). Before 2.0.0 it returned kilobytes. Needs permission to read `sys.master_files`.
`IndexCreateAsync(tableName, columnName)`	Nothing. Runs `CREATE INDEX [IX_<column>] ON <table> ([<column>])`.
`IndexDropAsync(tableName, indexName)`	Nothing. Runs `DROP INDEX [<index>] ON <table>`.

Every table name takes the forms described under "Inserting, updating and deleting": one to three parts, plain or bracketed. GetTableSchemaAsync, GetTableRowCountAsync, IndexCreateAsync and IndexDropAsync quote it into the SQL, and GetTableIndexesAsync passes the quoted name to OBJECT_ID as a parameter, so a bare name there means the default schema. DoesTableExistAsync, GetColumnNamesAsync, GetTableColumnsAsync, GetTablePrimaryKeyColumnAsync and GetTablePrimaryKeyColumnsAsync compare the parts with the catalog as parameters: a bare name (Orders) matches a table of that name in any schema, and dbo.Orders matches only the one in dbo.

Methods that run your SQL

These methods run SQL text you write. Treat every string listed here as code: build it in your own code, and send anything else as a parameter.

Method	SQL run as written
`ExecuteQueryAsync`, `ExecuteQueryAsync<T>`, `ExecuteScalarAsync<T>`, `ExecuteSQLAsync`, `ExecuteTransactionAsync`, `ExportDataToCSVAsync`, `ExecuteScriptFromFileAsync` (file contents)	the SQL itself
`ExecuteNonQueryWithParametersAsync`, `ExecuteParameterizedQueryAsync`, `ExecuteQueryToObjectListAsync<T>`	the SQL itself; values go as parameters
`UpdateDataAsync`, `DeleteDataAsync`	the condition; its values can go in the parameters dictionary their overloads take
`BulkCopyAsync`	`flushWhereClauseCondition`; its values can go in `flushParameters`
`QueryBuilder` (every method)	every string

ExecuteStoredProcedureAsync and GetStoredProcedureParametersAsync send the procedure name to SqlClient as a procedure name (CommandType.StoredProcedure), not as a batch. Every other name the library takes, of a table, column, index or database, is quoted or sent as a parameter.

QueryBuilder

QueryBuilder assembles a SELECT statement from SQL fragments, and Build() returns the text with the parameters you recorded. Every string it takes is SQL and is placed as written, table names included: build those strings in your own code and pass values with AddParameter. It does not talk to a server.

The SELECT list comes first: the columns given to Select, then any aggregates and CASE expressions, in call order and separated by commas. The other clauses follow in the order you call their methods, each as its keyword, your text and a space.

using SQLServerInteraction;

var db = new SQLServerInstance(new SQLServerConnectionString("your-server", "YourDatabase"));

var queryBuilder = new QueryBuilder();
queryBuilder.Select("CustomerId, [Customer Name], LastOrder");
queryBuilder.From("dbo.Customers");
queryBuilder.Where("Region = @Region");
queryBuilder.StartNestedCondition();
queryBuilder.And("LastOrder >= @Since");
queryBuilder.Or("LastOrder IS NULL");
queryBuilder.EndNestedCondition();
queryBuilder.OrderBy("CustomerId", QuerySortOrder.Descending);
queryBuilder.Paginate(page: 2, pageSize: 50);
queryBuilder.AddParameter("Region", "West");
queryBuilder.AddParameter("Since", new DateTime(2026, 1, 1));

QueryBuildResult result = queryBuilder.Build();
Console.WriteLine(result.SQL);
// SELECT CustomerId, [Customer Name], LastOrder FROM dbo.Customers WHERE Region = @Region AND (LastOrder >= @Since OR LastOrder IS NULL) ORDER BY CustomerId DESC OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY
Console.WriteLine(result.Parameters);
// @Region, @Since

// The values go with the SQL to any method that takes a parameter dictionary
List<Customer> page = await db.ExecuteQueryToObjectListAsync<Customer>(result.SQL!, result.ParameterValues);

using SQLServerInteraction;

var queryBuilder = new QueryBuilder();
queryBuilder.Select("Category");
queryBuilder.Count("*", "Products");
queryBuilder.Avg("Price", "AveragePrice");
queryBuilder.StartCaseStatement("");
queryBuilder.AddCaseWhen("MAX(Price) > 100", "'premium'");
queryBuilder.AddCaseElse("'standard'");
queryBuilder.EndCaseStatement("Tier");
queryBuilder.From("Products");
queryBuilder.Join("Suppliers s", "s.SupplierId = Products.SupplierId", JoinType.Left);
queryBuilder.GroupBy("Category");

Console.WriteLine(queryBuilder.Build().SQL);
// SELECT Category, COUNT(*) AS Products, AVG(Price) AS AveragePrice, CASE WHEN MAX(Price) > 100 THEN 'premium' ELSE 'standard' END AS Tier FROM Products LEFT JOIN Suppliers s ON s.SupplierId = Products.SupplierId GROUP BY Category

using SQLServerInteraction;

var queryBuilder = new QueryBuilder();
queryBuilder.Select("OrderId, Total");
queryBuilder.From("dbo.Orders");
queryBuilder.Where("CustomerId IN");
QueryBuilder customers = queryBuilder.CreateSubquery();
customers.Select("CustomerId");
customers.From("dbo.Customers");
customers.Where("Region = @Region");
customers.AddParameter("Region", "West");

Console.WriteLine(queryBuilder.Build().SQL);
// SELECT OrderId, Total FROM dbo.Orders WHERE CustomerId IN (SELECT CustomerId FROM dbo.Customers WHERE Region = @Region)

Select() with no argument selects *, unless an aggregate or CASE adds to the list.
Count, Sum, Avg, Min and Max add FUNC(column) AS alias to the SELECT list. Count("*", alias) counts rows.
StartCaseStatement("") starts a searched CASE, whose AddCaseWhen conditions are predicates. StartCaseStatement(expression) starts a simple CASE that compares the expression with each AddCaseWhen value. EndCaseStatement(alias) adds the CASE to the SELECT list.
StartNestedCondition opens a parenthesis after the keyword of the next Where, And or Or, and EndNestedCondition closes it after the last condition. Nested conditions can nest. Build() closes any left open; ending one that holds no condition, or that was never started, throws InvalidOperationException.
CreateSubquery returns a builder whose SQL Build() places, in parentheses, where CreateSubquery was called. Its parameters come back with the outer query's.
OrderBy writes ASC or DESC after the last column. QuerySortOrder was called SortOrder before 2.0.0, which clashed with Microsoft.Data.SqlClient.SortOrder.
Paginate(page, pageSize) appends OFFSET ... ROWS FETCH NEXT ... ROWS ONLY at the end. It needs an OrderBy; without one, Build() throws InvalidOperationException. A page or page size below 1 throws ArgumentOutOfRangeException.
Join takes JoinType.Inner (the default), Left, Right or Full.
AddParameter names work with or without the @. QueryBuildResult.Parameters lists the names, such as "@Region, @Since", and QueryBuildResult.ParameterValues holds the values by name, with null as DBNull.Value.
Build() reads the finished SQL and throws InvalidOperationException unless it starts with SELECT and has a FROM outside parentheses, quotes and comments. It does not change the builder, so calling it again returns the same result.

Union, Intersect and Except

Union(), Intersect() and Except() are obsolete. They put their keyword in front of the builder's own SQL, so the result is only half of a query, and a single builder cannot hold the other half. Build each query and join the two:

using SQLServerInteraction;

var current = new QueryBuilder();
current.Select("CustomerId");
current.From("dbo.Orders");

var archived = new QueryBuilder();
archived.Select("CustomerId");
archived.From("dbo.ArchivedOrders");

string sql = current.Build().SQL + "UNION " + archived.Build().SQL;
// SELECT CustomerId FROM dbo.Orders UNION SELECT CustomerId FROM dbo.ArchivedOrders

License

MIT. See LICENSE.

The icon, SQLServerInteraction.png, was designed by Iconjam on Freepik.com.

Name		Name	Last commit message	Last commit date
.github		.github
SQLServerInteraction.Tests		SQLServerInteraction.Tests
SQLServerInteraction.V1Tests		SQLServerInteraction.V1Tests
SQLServerInteraction		SQLServerInteraction
scripts		scripts
.gitattributes		.gitattributes
.gitignore		.gitignore
AGENTS.md		AGENTS.md
AGENT_PRIMER.md		AGENT_PRIMER.md
CHANGELOG.md		CHANGELOG.md
CLAUDE.md		CLAUDE.md
LICENSE		LICENSE
README.md		README.md
SECURITY.md		SECURITY.md
SQLServerInteraction.sln		SQLServerInteraction.sln
global.json		global.json

Navigation Menu

Repository navigation

About

Repository files navigation

SQLServerInteraction

Read this first: what is and is not parameterized

Connecting

Queries

Mapping rows to objects

Commands that return nothing

Transactions

Running a script file

Inserting, updating and deleting

Bulk copy

Merging

Exporting to CSV

Backup and restore

Schema and server information

Methods that run your SQL

QueryBuilder

Union, Intersect and Except

License

About

Releases

Packages

Used by

Contributors

Languages

Latest commit

History

Folders and files

Repository files navigation

SQLServerInteraction

Read this first: what is and is not parameterized

Connecting

Queries

Mapping rows to objects

Commands that return nothing

Transactions

Running a script file

Inserting, updating and deleting

Bulk copy

Merging

Exporting to CSV

Backup and restore

Schema and server information

Methods that run your SQL

QueryBuilder

Union, Intersect and Except

License

About

Topics

Resources

Security policy

Stars

Watchers

Forks

Releases

Packages

Used by

Contributors

Languages