SqlArtisan 0.2.0-alpha.2

Prefix Reserved
This is a prerelease version of SqlArtisan.
dotnet add package SqlArtisan --version 0.2.0-alpha.2
                    
NuGet\Install-Package SqlArtisan -Version 0.2.0-alpha.2
                    
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="SqlArtisan" Version="0.2.0-alpha.2" />
                    
For projects that support PackageReference, copy this XML node into the project file to reference the package.
<PackageVersion Include="SqlArtisan" Version="0.2.0-alpha.2" />
                    
Directory.Packages.props
<PackageReference Include="SqlArtisan" />
                    
Project file
For projects that support Central Package Management (CPM), copy this XML node into the solution Directory.Packages.props file to version the package.
paket add SqlArtisan --version 0.2.0-alpha.2
                    
#r "nuget: SqlArtisan, 0.2.0-alpha.2"
                    
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
#addin nuget:?package=SqlArtisan&version=0.2.0-alpha.2&prerelease
                    
Install as a Cake Addin
#tool nuget:?package=SqlArtisan&version=0.2.0-alpha.2&prerelease
                    
Install as a Cake Tool

SqlArtisan

⚠️ Warning: Work In Progress (WIP) & Unstable ⚠️

This project is currently under active development. It should be considered unstable, and breaking changes may occur without notice as the API evolves. Use in production environments is strongly discouraged at this stage.

Lifecycle License: MIT DeepWiki


SqlArtisan: Write SQL, in C#. A SQL query builder that provides a SQL-like experience, designed for developers who value the clarity and control of direct SQL syntax.

Why SqlArtisan?

Does this sound familiar?

  • You write raw SQL strings by hand when your ORM can't handle a query.
  • You build dynamic queries by stitching strings together, cluttering your core application logic.
  • You write boilerplate code for DbParameter objects, a process prone to subtle bugs and type errors.

SqlArtisan is designed to resolve these frustrations. It lets you write that same SQL directly in C# with a SQL-like API, a clean way to build dynamic queries, and automatic parameters—like this:

bool onlyActive = true;

UsersTable u = new();

ISqlBuilder sql =
    Select(
        u.Id,
        u.Name,
        u.CreatedAt)
    .From(u)
    .Where(
        u.Id > 0
        & u.Name.Like("A%")
        & ConditionIf(onlyActive, u.StatusId == 1))
    .OrderBy(u.Id);

So you can focus on the query logic, not the boilerplate. That’s why SqlArtisan.

Table of Contents


Changelog

Please see the CHANGELOG.md file for all notable changes.


Packages

Package Description NuGet Downloads
SqlArtisan The core query builder library for writing SQL in C# with a SQL-like fluent experience. NuGet Nuget
SqlArtisan.Dapper Provides extension methods to seamlessly execute queries built by SqlArtisan using Dapper. NuGet Nuget
SqlArtisan.TableClassGen A .NET tool that generates C# table schema classes from your database, enabling IntelliSense and type-safety with SqlArtisan. NuGet Nuget

Key Features

  • SQL-like API: Write queries naturally, mirroring SQL syntax and structure.
  • Schema IntelliSense: Provides code completion for table/column names via table schema classes, improving development speed and accuracy.
  • Automatic Parameterization: Converts literals to bind variables, boosting security (SQLi prevention) and query performance.
  • Dynamic Query Conditions: Dynamically include or exclude WHERE conditions (and other query parts) using simple helpers like ConditionIf.
  • Low-Allocation Design: Minimizes heap allocations and GC load for superior performance.
  • Seamless Dapper Integration: The optional SqlArtisan.Dapper library provides Dapper extensions that enable effortless SQL execution.

Getting Started

Prerequisites

  • .NET Version: .NET 8.0 or later.
  • Dialect-Specific API Usage: SqlArtisan provides dialect-specific C# APIs that map to DBMS features. For example, use Systimestamp for Oracle's SYSTIMESTAMP and CurrentTimestamp for PostgreSQL's CURRENT_TIMESTAMP. Developers should select the C# API appropriate for their target database.
  • Bind Parameter Handling: SqlArtisan adjusts bind parameter prefixes (e.g., : or @) to suit the target DBMS. Currently, this behavior is verified for MySQL, Oracle, PostgreSQL, SQLite, and SQL Server.
  • (Optional) Dapper Integration: Install SqlArtisan.Dapper for seamless Dapper execution. It auto-detects the dialect from your IDbConnection to apply correct settings (like bind parameter prefixes) and provides helpful execution methods.

Installation

You can install SqlArtisan and its optional Dapper integration library via NuGet Package Manager.

(Note: These packages are currently in their pre-release phase, so use the --prerelease flag when installing.)

For the core query building functionality:

dotnet add package SqlArtisan --prerelease

For seamless execution with Dapper (recommended):

dotnet add package SqlArtisan.Dapper --prerelease

Quick Start

  1. Define your Table Schema Class

    Create C# classes for your database tables to enable IntelliSense and prevent typos in names. You can write these manually (see example below) or generate them from an existing database with the SqlArtisan.TableClassGen tool.

    using SqlArtisan;
    // ...
    
    internal sealed class UsersTable : DbTableBase
    {
        public UsersTable(string tableAlias = "") : base("users", tableAlias)
        {
            Id = new DbColumn(tableAlias, "id");
            Name = new DbColumn(tableAlias, "name");
            CreatedAt = new DbColumn(tableAlias, "created_at");
        }
    
        public DbColumn Id { get; }
        public DbColumn Name { get; }
        public DbColumn CreatedAt { get; }
    }
    
  2. Define your DTO Class

    Create a Data Transfer Object (DTO) class. This class will be used to map the results of your SQL query.

    internal sealed class UserDto(int id, string name, DateTime createdAt)
    {
        public int Id => id;
        public string Name => name;
        public DateTime CreatedAt => createdAt;
    }
    
  3. Build and Execute your Query

    Construct your query using SqlArtisan's SQL-like API. For convenient access to entry point methods like Select() or InsertInto(), add a static using for SqlArtisan.Sql, which provides these static helper methods.

    Once built, execute the query. This example uses Dapper with SqlArtisan.Dapper.

    using SqlArtisan;
    using SqlArtisan.Dapper;
    using static SqlArtisan.Sql;
    // ...
    
    UsersTable u = new();
    
    ISqlBuilder sql =
        Select(u.Id, u.Name, u.CreatedAt)
        .From(u)
        .Where(u.Id > 0 & u.Name.Like("A%"))
        .OrderBy(u.Id);
    
    // Dapper: Set true to map snake_case columns to PascalCase/camelCase C# members.
    Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
    
    // 'connection' is your IDbConnection. SqlArtisan auto-detects the DBMS
    // (MySQL, Oracle, PostgreSQL, SQLite, SQL Server) & applies
    // the correct bind parameter prefix (e.g., ':' or '@').
    IEnumerable<UserDto> users = await connection.QueryAsync<UserDto>(sql);
    

    Alternative: Manual Execution (Accessing SQL and Parameters)

    Alternatively, access the SQL string and parameters directly for use with raw ADO.NET, other micro-ORMs, or for debugging, instead of using SqlArtisan.Dapper.

    ISqlBuilder.Build() accepts an optional Dbms argument (defaulting to Dbms.PostgreSql) to specify the SQL dialect. This affects features like the bind parameter prefix (e.g., : for PostgreSQL, @ for SQL Server).

    Example (Default - PostgreSQL):

    UsersTable u = new();
    
    // No args; defaults to Dbms.PostgreSql, uses ':' prefix
    SqlStatement sql =
        Select(u.Id, u.Name)
        .From(u)
        .Where(u.Id == 10 & u.Name == "Alice")
        .Build();
    
    // sql.Text is
    // SELECT id, name
    // FROM users
    // WHERE (id = :0) AND (name = :1)
    //
    // sql.Parameters.Get<int>(":0") is 10
    // sql.Parameters.Get<string>(":1") is "Alice"
    

    Example (Specifying SQL Server):

    UsersTable u = new();
    
    // With Dbms.SqlServer; uses '@' prefix
    SqlStatement sql =
        Select(u.Id, u.Name)
        .From(u)
        .Where(u.Id == 20 & u.Name == "Bob")
        .Build(Dbms.SqlServer);
    
    // sql.Text is
    // SELECT id, name
    // FROM users
    // WHERE (id = @0) AND (name = @1)
    //
    // sql.Parameters.Get<int>("@0") is 20
    // sql.Parameters.Get<string>("@1") is "Bob"
    

Performance

SqlArtisan is engineered for efficient performance, primarily by minimizing heap allocations. Our core strategy is efficient buffer management using ArrayPool<T>: internal buffers, particularly for string construction, are recycled from a shared pool. This approach leads to fewer garbage collection (GC) pauses and improved application throughput.

To illustrate this, we benchmarked our ArrayPool<T>-based internal string building against common approaches using BenchmarkDotNet.

Benchmark Details

  • Environment:
    • .NET Version: .NET 8
    • CPU: Intel Core i5-1135G7 @ 2.40GHz
    • RAM: 16 GB
    • OS: Windows 11 Pro 24H2
  • Source Code: Benchmark code is available at Benchmark Source Code. We encourage review and custom testing.

Benchmark Result

Method Mean Allocated
StringBuilder_DapperDynamicParams 206.5 ns 1.38 KB
DapperQbNet_NoParams 2,700.3 ns 7.47 KB
DapperSqlBuilder_DapperDynamicParams 1,333.3 ns 5.12 KB
InterpolatedSql_SpecificParams 1,568.4 ns 5.17 KB
SqExpress_NoParams 2,091.0 ns 4.56 KB
Sqlify_SpecificParams 1,001.6 ns 3.13 KB
SqlKata_SpecificParams 29,072.0 ns 40.54 KB
SqlArtisan_SpecificParams 1,433.0 ns 2.66 KB
SqlArtisan_DapperDynamicParams 1,568.5 ns 3.23 KB

Disclaimer

This benchmark highlights the memory efficiency of a specific internal operation within SqlArtisan by comparing it to fundamental string handling techniques. It is not intended as a direct, comprehensive performance benchmark against other SQL builder libraries, as each library has different design goals, features, and may perform optimally under different conditions or workloads.


Usage Examples

SqlArtisan allows you to construct a wide variety of SQL queries in a type-safe and intuitive manner. Below are examples demonstrating common SQL operations and how to achieve them with SqlArtisan.

SELECT Query

SELECT Clause
Column Aliases
UsersTable u = new();
SqlStatement sql =
    Select(
        u.Id.As("user_id"),
        u.Name.As("user_name"))
    .From(u)
    .Build();

// SELECT id "user_id",
// name "user_name"
// FROM users
DISTINCT
UsersTable u = new();
SqlStatement sql =
    Select(Distinct, u.Id)
    .From(u)
    .Build();

// SELECT DISTINCT id
// FROM users
Hints
// The hint below refers to this alias "u".
UsersTable u = new("u");
SqlStatement sql =
    Select(Hints("/*+ INDEX(u users_ix) */"), u.Id)
    .From(u)
    .Build();

// SELECT /*+ INDEX(u users_ix) */ "u".id
// FROM users "u"

FROM Clause
FROM-less Queries
SqlStatement sql =
    Select(CurrentTimestamp)
    .Build();

// SELECT CURRENT_TIMESTAMP
Using DUAL (Oracle)
SqlStatement sql =
    Select(Sysdate)
    .From(Dual)
    .Build();

// SELECT SYSDATE FROM DUAL

WHERE Clause
Example
UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(u.Id == 1)
    .Build();

// SELECT name
// FROM users
// WHERE id = :0

For a detailed guide on constructing various types of conditions (like Logical, Comparison, NULL, Pattern Matching, BETWEEN, IN, EXISTS), including how to use Dynamic Conditions (ConditionIf), check out the Expressions: Conditions section.


JOIN Clause
Example using INNER JOIN
UsersTable u = new("u");
OrdersTable o = new("o");
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .InnerJoin(o)
    .On(u.Id == o.UserId)
    .Build();

// SELECT "u".name
// FROM users "u"
// INNER JOIN orders "o"
// ON "u".id = "o".user_id
Supported JOIN APIs
  • InnerJoin() for INNER JOIN
  • LeftJoin() for LEFT JOIN
  • RightJoin() for RIGHT JOIN
  • FullJoin() for FULL JOIN
  • CrossJoin() for CROSS JOIN

ORDER BY Clause
UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .OrderBy(
        1,
        u.Id,
        u.Id.NullsFirst,
        u.Id.NullsLast,
        u.Id.Asc,
        u.Id.Asc.NullsFirst,
        u.Id.Asc.NullsLast,
        u.Id.Desc,
        u.Id.Desc.NullsFirst,
        u.Id.Desc.NullsLast)
    .Build();

// SELECT name
// FROM users
// ORDER BY
// 1,
// id,
// id NULLS FIRST,
// id NULLS LAST,
// id ASC,
// id ASC NULLS FIRST,
// id ASC NULLS LAST,
// id DESC,
// id DESC NULLS FIRST,
// id DESC NULLS LAST

GROUP BY and HAVING Clause
UsersTable u = new();
SqlStatement sql =
    Select(
        u.Id,
        u.Name,
        Count(u.Id))
    .From(u)
    .GroupBy(u.Id, u.Name)
    .Having(Count(u.Id) > 1)
    .Build();

// SELECT id, name, COUNT(id)
// FROM users
// GROUP BY id, name
// HAVING COUNT(id) > :0

Set Operators
Example using UNION
UsersTable u = new();
ArchivedUsersTable a = new();

SqlStatement sql =
    Select(u.Id, u.Name)
    .From(u)
    .Union
    .Select(a.Id, a.Name)
    .From(a)
    .Build();

// SELECT id, name
// FROM users
// UNION
// SELECT id, name
// FROM archived_users
Example using UNION ALL
UsersTable u = new();
ArchivedUsersTable a = new();

SqlStatement sql =
    Select(u.Id, u.Name)
    .From(u)
    .UnionAll
    .Select(a.Id, a.Name)
    .From(a)
    .Build();

// SELECT id, name
// FROM users
// UNION ALL
// SELECT id, name
// FROM archived_users
Supported Set Operators APIs
  • Union for UNION
  • UnionAll for UNION ALL
  • Except for EXCEPT
  • ExceptAll for EXCEPT ALL
  • Minus for MINUS
  • MinusAll for MINUS ALL
  • Intersect for INTERSECT
  • IntersectAll for INTERSECT ALL

DELETE Statement

UsersTable u = new();
SqlStatement sql =
    DeleteFrom(u)
    .Where(u.Id == 1)
    .Build();

// DELETE FROM users
// WHERE id = :0

UPDATE Statement

UsersTable u = new();
SqlStatement sql =
    Update(u)
    .Set(
        u.Name == "newName",
        u.CreatedAt == Sysdate)
    .Where(u.Id == 1)
    .Build();

// UPDATE users
// SET name = :0,
// created_at = SYSDATE
// WHERE id = :1

Note: SqlArtisan's Set() method uses Column == Value for SQL-like assignment, unlike standard C# == (comparison). In Where() clauses, == is used for comparison as expected.


INSERT Statement

Standard Syntax
UsersTable u = new();
SqlStatement sql =
    InsertInto(u, u.Id, u.Name, u.CreatedAt)
    .Values(1, "newName", Sysdate)
    .Build();

// INSERT INTO users
// (id, name, created_at)
// VALUES
// (:0, :1, SYSDATE)

Alternative Syntax (SET-like)

SqlArtisan also offers an alternative INSERT syntax, similar to UPDATE's Set() method, for clearer column-value pairing.

UsersTable u = new();
SqlStatement sql =
    InsertInto(u)
    .Set(
        u.Id == 1,
        u.Name == "newName",
        u.CreatedAt == Sysdate)
    .Build();

// INSERT INTO users
// (id, name, created_at)
// VALUES
// (:0, :1, SYSDATE)

Note: Generates standard INSERT INTO ... (columns) VALUES (values) SQL, not MySQL's INSERT ... SET ..., for broad database compatibility.


INSERT SELECT Syntax
UsersTable u = new();
ArchivedUsersTable a = new();

SqlStatement sql =
    InsertInto(a, a.Id, a.Name, a.CreatedAt)
    .Select(u.Id, u.Name, u.CreatedAt)
    .From(u)
    .Build();

// INSERT INTO archived_users
// (id, name, created_at)
// SELECT id, name, created_at
// FROM users
WITH Clause (Common Table Expressions)
  1. Define your CTE Schema Class
internal sealed class SeniorUsersCte : CteSchemaBase
{
    public SeniorUsersCte(string name) : base(name)
    {
        SeniorId = new DbColumn(name, "senior_id");
        SeniorName = new DbColumn(name, "senior_name");
        SeniorAge = new DbColumn(name, "senior_age");
    }

    public DbColumn SeniorId { get; }
    public DbColumn SeniorName { get; }
    public DbColumn SeniorAge { get; }
}
  1. Build the Query
UsersTable users = new("users");
SeniorUsersCte seniors = new("seniors");
OrdersTable orders = new("orders");

SqlStatement sql =
    With(
        seniors.As(
            Select(
                users.Id.As(seniors.SeniorId),
                users.Name.As(seniors.SeniorName),
                users.Age.As(seniors.SeniorAge))
            .From(users)
            .Where(users.Age > 40)))
    .Select(
        orders.Id,
        orders.OrderDate,
        seniors.SeniorId,
        seniors.SeniorName,
        seniors.SeniorAge
    )
    .From(orders)
    .InnerJoin(seniors)
    .On(orders.UserId == seniors.SeniorId)
    .Build();

// WITH seniors AS
// (SELECT "users".id "senior_id",
// "users".name "senior_name",
// "users".age "senior_age"
// FROM users "users" WHERE "users".age > :0)
// SELECT "orders".id,
// "orders".order_date,
// "seniors".senior_id,
// "seniors".senior_name,
// "seniors".senior_age
// FROM orders "orders"
// INNER JOIN seniors
// ON "orders".user_id = "seniors".senior_id

SqlArtisan also supports more advanced WITH clause scenarios, including:

  • Recursive CTEs using the WithRecursive() method.
  • CTEs with DML statements (INSERT, UPDATE, and DELETE).

Expressions

NULL Literal
SqlStatement sql =
    Select(
        Null,
        Null.As("NoValue"))
    .Build();

// SELECT
// NULL,
// NULL "NoValue"

Arithmetic Operators
UsersTable u = new();
SqlStatement sql =
    Select(
        u.Age + 1,
        u.Age - 2,
        u.Age * 3,
        u.Age / 4,
        u.Age % 5)
    .From(u)
    .Build();

// SELECT
// (age + :0),
// (age - :1),
// (age * :2),
// (age / :3),
// (age % :4)
// FROM users

Conditions
Logical Condition
UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(
        (u.Id == 1 & u.Id == 2)
        | (u.Id == 3 & Not(u.Id == 4)))
    .Build();

// SELECT name
// FROM users
// WHERE ((id = :0) AND (id = :1))
// OR ((id = :2) AND (NOT (id = :3)))

Note: SqlArtisan's logical conditions use & for SQL AND and | for SQL OR, unlike their standard C# meanings (bitwise or non-short-circuiting logic).

Comparison Condition
UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(
        u.Id == 1
        & u.Id != 2
        & u.Id > 3
        & u.Id >= 4
        & u.Id < 5
        & u.Id <= 6)
    .Build();

// SELECT name
// FROM users
// WHERE (id = :0)
// AND (id <> :1)
// AND (id > :2)
// AND (id >= :3)
// AND (id < :4)
// AND (id <= :5)
NULL Condition
UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(u.Id.IsNull
        | u.Id.IsNotNull)
    .Build();

// SELECT name
// FROM users
// WHERE (id IS NULL)
// OR (id IS NOT NULL)
Pattern Matching Condition
UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(
        u.Name.Like("%a")
        | u.Name.NotLike("%b")
        | RegexpLike(u.Name, "^.*c$"))
    .Build();

// SELECT name
// FROM users
// WHERE (name LIKE :0)
// OR (name NOT LIKE :1)
// OR (REGEXP_LIKE(name, :2))
BETWEEN Condition
UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(
        u.Id.Between(1, 2)
        | u.Id.NotBetween(3, 4))
    .Build();

// SELECT name
// FROM users
// WHERE (id BETWEEN :0 AND :1)
// OR (id NOT BETWEEN :2 AND :3)
IN Condition
UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(
        u.Id.In(1, 2, 3)
        | u.Id.NotIn(4, 5, 6))
    .Build();

// SELECT name
// FROM users
// WHERE (id IN (:0, :1, :2))
// OR (id NOT IN (:3, :4, :5))
EXISTS Condition
UsersTable a = new("a");
UsersTable b = new("b");
UsersTable c = new("c");
SqlStatement sql =
    Select(a.Name)
    .From(a)
    .Where(
        Exists(Select(b.Id).From(b))
        & NotExists(Select(c.Id).From(c)))
    .Build();

// SELECT "a".name
// FROM users "a"
// WHERE (EXISTS (SELECT "b".id FROM users "b"))
// AND (NOT EXISTS (SELECT "c".id FROM users "c"))
Dynamic Condition

SqlArtisan allows you to dynamically include or exclude conditions using a helper like ConditionIf. This is useful when parts of your WHERE clause depend on runtime logic.

Case 1: Condition is Included
bool filterUnderTen = true;

UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(
        u.Id > 0
        & ConditionIf(filterUnderTen, u.Id < 10))
    .Build();

// SELECT name
// FROM users
// WHERE (id > :0)
// AND (id < :1)
Case 2: Condition is Excluded
bool filterUnderTen = false;

UsersTable u = new();
SqlStatement sql =
    Select(u.Name)
    .From(u)
    .Where(
        u.Id > 0
        & ConditionIf(filterUnderTen, u.Id < 10))
    .Build();

// SELECT name
// FROM users
// WHERE (id > :0)

CASE Expressions
Simple CASE Expression
UsersTable u = new();
SqlStatement sql =
    Select(
        u.Id,
        u.Name,
        Case(
            u.StatusId,
            When(1).Then("Active"),
            When(2).Then("Inactive"),
            When(3).Then("Pending"),
            Else("Unknown"))
        .As("StatusDescription"))
    .From(u)
    .Build();

// SELECT id, name,
// CASE status_id
// WHEN :0 THEN :1
// WHEN :2 THEN :3
// WHEN :4 THEN :5
// ELSE :6
// END "StatusDescription"
// FROM users
Searched CASE Expression
UsersTable u = new();
SqlStatement sql =
    Select(
        u.Id,
        u.Name,
        Case(
            When(u.Age < 18).Then("Minor"),
            When(u.Age >= 18 & u.Age < 65).Then("Adult"),
            Else("Senior"))
        .As("AgeGroup"))
    .From(u)
    .Build();

// SELECT id, name,
// CASE
// WHEN (age < :0) THEN :1
// WHEN ((age >= :2) AND (age < :3)) THEN :4
// ELSE :5
// END "AgeGroup"
// FROM users

Window Functions
Example using ROW_NUMBER
UsersTable u = new();
SqlStatement sql =
    Select(
        u.Id,
        u.Name,
        u.DepartmentId,
        RowNumber().Over(
            PartitionBy(u.DepartmentId)
            .OrderBy(u.Salary.Desc)))
    .From(u)
    .Build();

// SELECT id, name, department_id,
// ROW_NUMBER() OVER (
// PARTITION BY department_id
// ORDER BY salary DESC)
// FROM users

For a comprehensive list of all available window functions, please refer to the Additional Query Details: Window Functions section.


Sequence
Oracle Example
SqlStatement sql =
    Select(
        Sequence("users_id_seq").Currval,
        Sequence("users_id_seq").Nextval)
    .Build();

// SELECT
// users_id_seq.CURRVAL,
// users_id_seq.NEXTVAL
PostgreSQL Example
SqlStatement sql =
    Select(
        Currval("users_id_seq"),
        Nextval("users_id_seq"))
    .Build();

// SELECT
// CURRVAL('users_id_seq'),
// NEXTVAL('users_id_seq')
SQL Server Example
SqlStatement sql =
    Select(
        NextValueFor("users_id_seq"))
    .Build();

// SELECT
// NEXT VALUE FOR users_id_seq

Additional Query Details

This section provides supplementary information on specific elements and features within SqlArtisan that offer fine-grained control and enhance query expressiveness. These details build upon the fundamental query structures covered in the Usage Examples section.

Bind Parameter Types

SqlArtisan automatically converts C# literal values into bind parameters. Supported types are as follows:

  • Boolean: bool
  • Character/String: char, string
  • Date/Time: DateTime, DateOnly, TimeOnly
  • Numeric: sbyte, byte, short, ushort, int, uint, nint, nuint, long, ulong, float, double, decimal, Complex
  • Enum: Any enum type

Functions

SqlArtisan provides C# APIs that map to various SQL functions, enabling you to use them seamlessly within your queries. Here's a list of supported functions by category:

Numeric Functions
  • Abs() for ABS
  • Mod() for MOD
  • Trunc() for TRUNC (Numeric Overload)

Character Functions
  • Concat() for CONCAT
  • Instr() for INSTR
  • Lpad() for LPAD
  • Ltrim() for LTRIM
  • Length() for LENGTH
  • Lengthb() for LENGTHB
  • Lower() for LOWER
  • Rpad() for RPAD
  • Rtrim() for RTRIM
  • RegexpCount() for REGEXP_COUNT
  • RegexpReplace() for REGEXP_REPLACE
  • RegexpSubstr() for REGEXP_SUBSTR
  • Replace() for REPLACE
  • Substr() for SUBSTR
  • Substrb() for SUBSTRB
  • Trim() for TRIM
  • Upper() for UPPER

Date and Time Functions
  • AddMonths() for ADD_MONTHS
  • CurrentDate for CURRENT_DATE
  • CurrentTime for CURRENT_TIME
  • CurrentTimestamp for CURRENT_TIMESTAMP
  • Datepart() for DATEPART
  • Extract() for EXTRACT (Date/Time Overload)
  • LastDay() for LAST_DAY
  • MonthsBetween() for MONTHS_BETWEEN
  • Sysdate for SYSDATE
  • Systimestamp for SYSTIMESTAMP
  • Trunc() for TRUNC (Date/Time Overload)

Conversion Functions
  • Coalesce() for COALESCE
  • Decode() for DECODE
  • Nvl() for NVL
  • ToChar() for TO_CHAR
  • ToDate() for TO_DATE
  • ToNumber() for TO_NUMBER
  • ToTimestamp() for TO_TIMESTAMP

Aggregate Functions
  • Avg() for AVG
  • Count() for COUNT
  • Max() for MAX
  • Min() for MIN
  • Sum() for SUM

Window Functions
  • CumeDist() for CUME_DIST()
  • DenseRank() for DENSE_RANK()
  • PercentRank() for PERCENT_RANK()
  • Rank() for RANK()
  • RowNumber() for ROW_NUMBER()

Contributing

We welcome your feedback, suggestions, and bug reports! Your contributions help make SqlArtisan better for everyone.

Your collaboration is greatly appreciated!


License

This project is licensed under the MIT License. See the LICENSE file for the full license text.

Product Compatible and additional computed target framework versions.
.NET net8.0 is compatible.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed.  net9.0 was computed.  net9.0-android was computed.  net9.0-browser was computed.  net9.0-ios was computed.  net9.0-maccatalyst was computed.  net9.0-macos was computed.  net9.0-tvos was computed.  net9.0-windows was computed.  net10.0 was computed.  net10.0-android was computed.  net10.0-browser was computed.  net10.0-ios was computed.  net10.0-maccatalyst was computed.  net10.0-macos was computed.  net10.0-tvos was computed.  net10.0-windows was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.
  • net8.0

    • No dependencies.

NuGet packages (1)

Showing the top 1 NuGet packages that depend on SqlArtisan:

Package Downloads
SqlArtisan.Dapper

Dapper integration for SqlArtisan. Enables effortless execution of your SqlArtisan queries.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last Updated
0.2.0-alpha.2 109 7/1/2025
0.2.0-alpha.1 112 6/19/2025
0.1.0-alpha.17 116 6/16/2025
0.1.0-alpha.16 274 6/11/2025