GennadyGS.SqlQueryBuilder 1.5.0

SqlQueryBuilder

A powerful, type-safe SQL query builder for .NET that leverages C# string interpolation to create parameterized SQL queries with automatic SQL injection protection.

Features

• SQL Injection Protection: Automatically parameterizes interpolated values
• Type-Safe: Full IntelliSense support with compile-time checking
• Simple API: Natural C# string interpolation syntax
• Composable: Easily combine and nest query builders
• Auto-Generated Parameters: Parameter names are automatically generated and collision-safe, even in complex query composition scenarios
• Flexible Formatting: Support for literal strings and custom parameter names
• Metadata Support: Attach additional information to queries
• High Performance: Minimal overhead with efficient string building
• Zero Dependencies: Lightweight library with no external dependencies

Quick Start

Installation

Install via NuGet Package Manager:

Install-Package GennadyGS.SqlQueryBuilder

Or via .NET CLI:

dotnet add package GennadyGS.SqlQueryBuilder

Basic Usage

using SqlQueryBuilders;

// Simple parameterized query
SqlQueryBuilder query = $"SELECT * FROM Users WHERE Id = {userId} AND Status = {status}";

string sql = query.GetQuery();
// Result: "SELECT * FROM Users WHERE Id = @p1 AND Status = @p2"

var parameters = query.GetParameters();
// Result: { ["p1"] = userId, ["p2"] = status }

Examples

Basic Query Building

var userId = 123;
var status = "Active";

SqlQueryBuilder queryBuilder = $"SELECT * FROM Orders WHERE UserId = {userId} AND Status = {status}";

var (query, parameters) = queryBuilder.GetQueryAndParameters();
// query: "SELECT * FROM Orders WHERE UserId = @p1 AND Status = @p2"
// parameters: { ["p1"] = 123, ["p2"] = "Active" }

Query Composition

Easily compose complex queries by combining multiple SqlQueryBuilder instances. Parameter names are automatically managed to avoid collisions:

SqlQueryBuilder innerQuery = $"SELECT OrderId FROM Orders WHERE UserId = {userId}";
SqlQueryBuilder outerQuery = $"SELECT * FROM OrderDetails WHERE OrderId IN ({innerQuery})";

var (sql, parameters) = outerQuery.GetQueryAndParameters();
// sql: "SELECT * FROM OrderDetails WHERE OrderId IN (SELECT OrderId FROM Orders WHERE UserId = @p1)"
// parameters: { ["p1"] = userId }

// Even with multiple nested queries, parameter names remain unique
SqlQueryBuilder innermost = $"SELECT CustomerId FROM Customers WHERE Region = {region}";
SqlQueryBuilder middle = $"SELECT OrderId FROM Orders WHERE CustomerId IN ({innermost}) AND Status = {status}";
SqlQueryBuilder outermost = $"SELECT * FROM OrderDetails WHERE OrderId IN ({middle}) AND Quantity > {minQuantity}";

var (complexSql, complexParams) = outermost.GetQueryAndParameters();
// Parameters: { ["p1"] = region, ["p2"] = status, ["p3"] = minQuantity }
// All parameter names are automatically managed and collision-safe

Literal String Formatting

Use the :l format specifier to include values as literal strings (use with caution):

var tableName = "Orders";
var userId = 123;

SqlQueryBuilder query = $"SELECT * FROM {tableName:l} WHERE UserId = {userId}";

var (sql, parameters) = query.GetQueryAndParameters();
// sql: "SELECT * FROM Orders WHERE UserId = @p1"
// parameters: { ["p1"] = 123 }

Custom Parameter Prefixes

Customize parameter names for better integration with your ORM:

SqlQueryBuilder query = $"SELECT * FROM Users WHERE Id = {userId}";

var (sql, parameters) = query.GetQueryAndParameters("param");
// sql: "SELECT * FROM Users WHERE Id = @param1"
// parameters: { ["param1"] = userId }

Query Concatenation

Combine multiple query parts using standard string concatenation. Parameter names remain unique across all parts:

SqlQueryBuilder baseQuery = $"SELECT * FROM Orders WHERE UserId = {userId}";
SqlQueryBuilder extendedQuery = baseQuery + $" AND CreatedDate > {startDate}";

var (sql, parameters) = extendedQuery.GetQueryAndParameters();
// sql: "SELECT * FROM Orders WHERE UserId = @p1 AND CreatedDate > @p2"
// parameters: { ["p1"] = userId, ["p2"] = startDate }

// Multiple concatenations maintain parameter uniqueness
SqlQueryBuilder furtherExtended = extendedQuery + $" AND Amount > {minAmount}";
// Parameters: { ["p1"] = userId, ["p2"] = startDate, ["p3"] = minAmount }

Metadata Support

Attach additional metadata to your queries for logging, caching, or other purposes:

var queryBuilder = ((SqlQueryBuilder)$"SELECT * FROM Orders WHERE Id = {orderId}")
    .AddMetadata("Operation", "GetOrder")
    .AddMetadata("CacheKey", $"order_{orderId}");

var metadata = queryBuilder.Metadata;
// metadata: { ["Operation"] = "GetOrder", ["CacheKey"] = "order_123" }

Working with ORMs

Entity Framework Core

var minAmount = 1000;
var status = "Completed";

SqlQueryBuilder queryBuilder = $"""
    SELECT o.*, c.Name as CustomerName
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerId = c.Id
    WHERE o.Amount >= {minAmount} AND o.Status = {status}
    """;

var (query, parameters) = queryBuilder.GetQueryAndParameters();
var orders = context.Orders
    .FromSqlRaw(query, parameters.Values.ToArray())
    .ToList();

Dapper

var queryBuilder = $"SELECT * FROM Products WHERE CategoryId = {categoryId} AND Price > {minPrice}";

var (query, parameters) = queryBuilder.GetQueryAndParameters();
var products = connection.Query<Product>(
    query,
    parameters
);

Security

SqlQueryBuilder automatically protects against SQL injection by parameterizing all interpolated values. Values are never directly concatenated into the SQL string, ensuring your queries are safe by default.

var userInput = "'; DROP TABLE Users; --";
SqlQueryBuilder query = $"SELECT * FROM Users WHERE Name = {userInput}";

// Safe! Results in: "SELECT * FROM Users WHERE Name = @p1"
// Parameter: { ["p1"] = "'; DROP TABLE Users; --" }

Supported Frameworks

• NET 8.0 and later

API Reference

Core Methods

Parameter Name Generation

SqlQueryBuilder automatically generates parameter names using a sequential numbering system (@p1, @p2, etc.) that ensures:

• Collision Safety: No duplicate parameter names, even in complex compositions
• Predictable Ordering: Parameters are numbered in the order they appear
• Composition Support: Nested and concatenated queries maintain unique parameter names
• Custom Prefixes: Use GetQueryAndParameters(prefix) to customize the parameter name prefix

Format Specifiers

License

This project is licensed under the MIT License - see the LICENSE.md file for details.

Acknowledgments

• Inspired by the need for type-safe SQL query building in .NET
• Built with modern C# interpolated string handlers
• Designed for developer productivity and application security