Skip to content

Quick Start

This page walks through the most common operations in Siqqle. Every query starts with the Sql static class, which provides factory methods for all statement types.

Setup

Make sure you have the Siqqle package installed (see Installation) and add the core namespaces:

using Siqqle;
using Siqqle.Expressions;

Your first query

var sql = Sql
    .Select("Id", "UserName", "Email", "Age")
    .From("Users")
    .Where(SqlExpression.GreaterThanOrEqual("Age", 30))
    .OrderBy("UserName")
    .ToSql();

SELECT [Id], [UserName], [Email], [Age] FROM [Users] WHERE [Age] >= 30 ORDER BY [UserName] ASC

Column names passed as strings are implicitly converted to SqlColumn instances, and literal values like 30 are implicitly converted to SqlConstant. See Selecting Data for more options.

Joining tables

Use SqlTable with aliases and chain .InnerJoin() or .LeftJoin() calls:

SqlTable u = new("Users", "u");
SqlTable o = new("Orders", "o");

var sql = Sql
    .Select(u + "UserName", o + "OrderDate", o + "Total")
    .From(u)
    .InnerJoin(o).On(SqlExpression.Equal(u + "Id", o + "UserId"))
    .Where(SqlExpression.GreaterThan(o + "Total", 100))
    .OrderBy(o + "OrderDate", SqlSortOrder.Descending)
    .ToSql();

SELECT [u].[UserName], [o].[OrderDate], [o].[Total]
FROM [Users] [u]
INNER JOIN [Orders] [o] ON [u].[Id] = [o].[UserId]
WHERE [o].[Total] > 100
ORDER BY [o].[OrderDate] DESC

See Joins for all join types.

Aggregating data

var sql = Sql
    .Select(
        "Department",
        SqlAggregate.Count("Id", "Headcount"),
        SqlAggregate.Average("Salary", "AvgSalary")
    )
    .From("Employees")
    .GroupBy("Department")
    .Having(SqlExpression.GreaterThan(SqlAggregate.Count("Id"), 5))
    .ToSql();

SELECT [Department], COUNT([Id]) AS [Headcount], AVG([Salary]) AS [AvgSalary]
FROM [Employees]
GROUP BY ([Department]) HAVING COUNT([Id]) > 5

See Grouping & Aggregates for the full set of aggregate functions.

Inserting data

var sql = Sql
    .Insert()
    .Into("Users", "UserName", "Email")
    .Values("jdoe", "jane.doe@example.com")
    .ToSql();

INSERT INTO [Users] ([UserName], [Email]) VALUES ('jdoe', 'jane.doe@example.com')

See Inserting Data for multi-row inserts and parameterised values.

Updating data

var sql = Sql
    .Update("Users")
    .Set("Email", "jane.new@example.com")
    .Where(SqlExpression.Equal("Id", 42))
    .ToSql();

UPDATE [Users] SET [Email] = 'jane.new@example.com' WHERE [Id] = 42

See Updating Data for more options.

Deleting data

var sql = Sql
    .Delete()
    .From("Users")
    .Where(SqlExpression.Equal("Id", 42))
    .ToSql();

DELETE FROM [Users] WHERE [Id] = 42

See Deleting Data for compound conditions and parameterised deletes.

Using parameters

Use named parameters instead of literal values for production queries. Create a parameter by combining a parameter name with a SqlConstant:

var sql = Sql
    .Select("Id", "UserName")
    .From("Users")
    .Where(SqlExpression.Equal("Id", "UserId" + (SqlConstant)42))
    .ToSql();

SELECT [Id], [UserName] FROM [Users] WHERE [Id] = @UserId

The parameter value (42) is extracted via a callback or automatically when using Dapper integration. See Parameters for full details.

Choosing a SQL dialect

By default Siqqle produces SQL with [square bracket] identifier quoting. Pass a dialect to ToSql() to target a specific database:

var query = Sql.Select("Id", "Name").From("Users");


query.ToSql();

SELECT [Id], [Name] FROM [Users]


query.ToSql(new PostgreSqlDialect());

SELECT "Id", "Name" FROM "Users"


query.ToSql(new MySqlDialect());

SELECT `Id`, `Name` FROM `Users`

See SQL Dialects for details on all available dialects.

Using with Dapper

With the Siqqle.Dapper package, you can pass Siqqle statements directly to IDbConnection extension methods. Parameters are extracted automatically:

using Siqqle.Dapper;

var users = connection.Query<User>(
    Sql.Select("Id", "Name", "Email")
       .From("Users")
       .Where(SqlExpression.Equal("IsActive", 1))
       .OrderBy("Name")
);

See Dapper Integration for the full API.

Next steps

Topic What you'll learn
Selecting Data Columns, aliases, functions, DISTINCT, subquery sources
Filtering WHERE, comparisons, AND/OR, IN, BETWEEN, LIKE, IS NULL
Joins INNER, LEFT, RIGHT joins, multi-table queries
Grouping & Aggregates GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX
Ordering & Paging ORDER BY, LIMIT/OFFSET, dialect-specific paging
Expressions CASE/WHEN, CAST, arithmetic, concatenation, stored procedures
Parameters Named parameters, DbType, ADO.NET integration
SQL Dialects Dialect differences, custom dialects, DI integration
Dapper Integration Query, Execute, transactions, multi-map, async