SqlArtisan 0.2.0-alpha.2
Prefix Reserveddotnet add package SqlArtisan --version 0.2.0-alpha.2
NuGet\Install-Package SqlArtisan -Version 0.2.0-alpha.2
<PackageReference Include="SqlArtisan" Version="0.2.0-alpha.2" />
<PackageVersion Include="SqlArtisan" Version="0.2.0-alpha.2" />
<PackageReference Include="SqlArtisan" />
paket add SqlArtisan --version 0.2.0-alpha.2
#r "nuget: SqlArtisan, 0.2.0-alpha.2"
#addin nuget:?package=SqlArtisan&version=0.2.0-alpha.2&prerelease
#tool nuget:?package=SqlArtisan&version=0.2.0-alpha.2&prerelease
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.
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
- Packages
- Key Features
- Getting Started
- Performance
- Usage Examples
- SELECT Query
- SELECT Clause: Column Aliases,
DISTINCT
, Hints - FROM Clause: FROM-less,
DUAL
- WHERE Clause
- JOIN Clause:
INNER JOIN
,LEFT JOIN
,RIGHT JOIN
,FULL JOIN
,CROSS JOIN
- ORDER BY Clause:
ASC
,DESC
,NULLS FIRST/LAST
- GROUP BY and HAVING Clause
- Set Operators:
UNION [ALL]
,EXCEPT [ALL]
,MINUS [ALL]
,INTERSECT [ALL]
- SELECT Clause: Column Aliases,
- DELETE Statement
- UPDATE Statement
- INSERT Statement: Standard, SET-like,
INSERT SELECT
- WITH Clause (Common Table Expressions):
WITH
,WITH RECURSIVE
, CTEs with DML - Expressions
- NULL Literal
- Arithmetic Operators:
+
,-
,*
,/
,%
- Conditions: Logical, Comparison,
NULL
,LIKE
,REGEXP_LIKE
,BETWEEN
,IN
,EXISTS
, Dynamic Conditions - CASE Expressions: Simple CASE, Searched CASE
- Window Functions
- Sequence:
CURRVAL
,NEXTVAL
,NEXT VALUE FOR
- SELECT Query
- Additional Query Details
- Bind Parameter Types
- Functions
- Numeric Functions:
ABS
,MOD
,TRUNC
- Character Functions:
CONCAT
,INSTR
,LPAD
,LTRIM
,LENGTH
,LENGTHB
,LOWER
,RPAD
,RTRIM
,REGEXP_COUNT
,REGEXP_REPLACE
,REGEXP_SUBSTR
,REPLACE
,SUBSTR
,SUBSTRB
,TRIM
,UPPER
- Date and Time Functions:
ADD_MONTHS
,CURRENT_DATE
,CURRENT_TIME
,CURRENT_TIMESTAMP
,EXTRACT
,LAST_DAY
,MONTHS_BETWEEN
,SYSDATE
,SYSTIMESTAMP
,TRUNC
- Conversion Functions:
COALESCE
,DECODE
,NVL
,TO_CHAR
,TO_DATE
,TO_NUMBER
,TO_TIMESTAMP
- Aggregate Functions:
AVG
,COUNT
,MAX
,MIN
,SUM
- Window Functions:
CUME_DIST
,DENSE_RANK
,PERCENT_RANK
,RANK
,ROW_NUMBER
- Numeric Functions:
- Contributing
- License
Changelog
Please see the CHANGELOG.md file for all notable changes.
Packages
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 likeConditionIf
. - 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'sSYSTIMESTAMP
andCurrentTimestamp
for PostgreSQL'sCURRENT_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 yourIDbConnection
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
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; } }
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; }
Build and Execute your Query
Construct your query using SqlArtisan's SQL-like API. For convenient access to entry point methods like
Select()
orInsertInto()
, add a static using forSqlArtisan.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 optionalDbms
argument (defaulting toDbms.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()
forINNER JOIN
LeftJoin()
forLEFT JOIN
RightJoin()
forRIGHT JOIN
FullJoin()
forFULL JOIN
CrossJoin()
forCROSS 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
forUNION
UnionAll
forUNION ALL
Except
forEXCEPT
ExceptAll
forEXCEPT ALL
Minus
forMINUS
MinusAll
forMINUS ALL
Intersect
forINTERSECT
IntersectAll
forINTERSECT 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)
- 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; }
}
- 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
, andDELETE
).
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()
forABS
Mod()
forMOD
Trunc()
forTRUNC
(Numeric Overload)
Character Functions
Concat()
forCONCAT
Instr()
forINSTR
Lpad()
forLPAD
Ltrim()
forLTRIM
Length()
forLENGTH
Lengthb()
forLENGTHB
Lower()
forLOWER
Rpad()
forRPAD
Rtrim()
forRTRIM
RegexpCount()
forREGEXP_COUNT
RegexpReplace()
forREGEXP_REPLACE
RegexpSubstr()
forREGEXP_SUBSTR
Replace()
forREPLACE
Substr()
forSUBSTR
Substrb()
forSUBSTRB
Trim()
forTRIM
Upper()
forUPPER
Date and Time Functions
AddMonths()
forADD_MONTHS
CurrentDate
forCURRENT_DATE
CurrentTime
forCURRENT_TIME
CurrentTimestamp
forCURRENT_TIMESTAMP
Datepart()
forDATEPART
Extract()
forEXTRACT
(Date/Time Overload)LastDay()
forLAST_DAY
MonthsBetween()
forMONTHS_BETWEEN
Sysdate
forSYSDATE
Systimestamp
forSYSTIMESTAMP
Trunc()
forTRUNC
(Date/Time Overload)
Conversion Functions
Coalesce()
forCOALESCE
Decode()
forDECODE
Nvl()
forNVL
ToChar()
forTO_CHAR
ToDate()
forTO_DATE
ToNumber()
forTO_NUMBER
ToTimestamp()
forTO_TIMESTAMP
Aggregate Functions
Avg()
forAVG
Count()
forCOUNT
Max()
forMAX
Min()
forMIN
Sum()
forSUM
Window Functions
CumeDist()
forCUME_DIST()
DenseRank()
forDENSE_RANK()
PercentRank()
forPERCENT_RANK()
Rank()
forRANK()
RowNumber()
forROW_NUMBER()
Contributing
We welcome your feedback, suggestions, and bug reports! Your contributions help make SqlArtisan better for everyone.
- For bug reports or specific feature requests: Please open an issue on our GitHub Issues page.
- For general questions, discussions about ideas, or seeking help: Please start a new topic on our GitHub Discussions page.
Your collaboration is greatly appreciated!
License
This project is licensed under the MIT License. See the LICENSE file for the full license text.
Product | Versions 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. |
-
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 |
Please see https://github.com/h-tacayama/SqlArtisan/blob/main/CHANGELOG.md for details.