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