Izayoi.Data.Query
1.0.0
dotnet add package Izayoi.Data.Query --version 1.0.0
NuGet\Install-Package Izayoi.Data.Query -Version 1.0.0
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="Izayoi.Data.Query" Version="1.0.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Izayoi.Data.Query --version 1.0.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
#r "nuget: Izayoi.Data.Query, 1.0.0"
#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.
// Install Izayoi.Data.Query as a Cake Addin #addin nuget:?package=Izayoi.Data.Query&version=1.0.0 // Install Izayoi.Data.Query as a Cake Tool #tool nuget:?package=Izayoi.Data.Query&version=1.0.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.
Izayoi.Data.Query
This is a library that supports building query (SQL) to manipulate a database.
Wiki
Examples
Query Option
using Izayoi.Data.Query;
static void Main()
{
QueryOption queryOption;
queryOption = new QueryOption(RdbKind.Sqlite, 3);
queryOption = new QueryOption(RdbKind.SqlServer, 2022)
{
QuotationMarks = new QuotationMarkSet('[', ']')
};
queryOption = new QueryOption(RdbKind.Mysql, 8)
{
QuotationMarks = new QuotationMarkSet('`', '`')
};
queryOption = new QueryOption(RdbKind.Pgsql, 16)
{
QuotationMarks = new QuotationMarkSet('"', '"')
};
queryOption = new QueryOption()
{
EnableFormat = true,
IndentSpace = 4,
BeforeComma = true,
}
}
Basic
using Izayoi.Data.Query;
static void Main()
{
var queryOption = new QueryOption();
var queryBuilder = new QueryBuilder(queryOption);
var select = new Select()
.SetFrom("users")
.AddField("*");
queryBuilder.Build(select);
string query = queryBuilder.GetQuery();
var parameters = queryBuilder.GetParameters();
// query:
// SELECT *
// FROM users
// parameters:
// (Empty)
}
Select
// SELECT
{
var select1 = new Select()
.SetFrom("users")
.AddField("id")
.AddField("name")
.AddField("age");
// query:
// SELECT id, name, age
// FROM users
}
// SELECT DISTINCT
{
var select2 = new Select()
.SetType(SType.DISTINCT)
.SetFrom("users")
.AddField("*");
// query:
// SELECT DISTINCT *
// FROM users
}
// WHERE: AND
{
var select3 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", ">=", 13)
.AddWhere("age", "<=", 19);
// query:
// SELECT *
// FROM users
// WHERE age >= @w_0
// AND age <= @w_1
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 13
// [1]:
// ParameterName: @w_1
// DbType: DbType.Int32
// Value: 19
}
// WHERE: BETWEEN
{
var select4 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", OpType.BETWEEN, new int[] { 13, 19 });
// query:
// SELECT *
// FROM users
// WHERE BETWEEN age @w_0_0 AND @w_0_1
// parameters:
// [0]:
// ParameterName: @w_0_0
// DbType: DbType.Int32
// Value: 13
// [1]:
// ParameterName: @w_0_1
// DbType: DbType.Int32
// Value: 19
}
// WHERE: IN
{
var select5 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", OpType.IN, new int[] { 20, 30, 40 });
// query:
// SELECT *
// FROM users
// WHERE age IN (@w_0_0, @w_0_1, @w_0_2)
// parameters:
// [0]:
// ParameterName: @w_0_0
// DbType: DbType.Int32
// Value: 20
// [1]:
// ParameterName: @w_0_1
// DbType: DbType.Int32
// Value: 30
// [2]:
// ParameterName: @w_0_2
// DbType: DbType.Int32
// Value: 40
}
// WHERE: IN
{
var select6 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", OpType.IN, new List<int> { 20, 30, 40 });
// query:
// SELECT *
// FROM users
// WHERE age IN (@w_0_0, @w_0_1, @w_0_2)
// parameters:
// [0]:
// ParameterName: @w_0_0
// DbType: DbType.Int32
// Value: 20
// [1]:
// ParameterName: @w_0_1
// DbType: DbType.Int32
// Value: 30
// [2]:
// ParameterName: @w_0_2
// DbType: DbType.Int32
// Value: 40
}
// WHERE: OR
{
var select7 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere('(', "age", "=", 20)
.AddWhere(CType.OR, "age", "=", 30)
.AddWhere(CType.OR, "age", "=", 40)
.AddWhere(CType.OR, "age", "=", 50, ')');
// query:
// SELECT *
// FROM users
// WHERE (age = @w_0
// OR age = @w_1
// OR age = @w_2
// OR age = @w_3)
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 20
// [1]:
// ParameterName: @w_1
// DbType: DbType.Int32
// Value: 30
// [2]:
// ParameterName: @w_2
// DbType: DbType.Int32
// Value: 40
// [3]:
// ParameterName: @w_3
// DbType: DbType.Int32
// Value: 50
}
// WHERE: IS NULL / IS NOT NULL
{
var select8 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("name", OpType.IS_NULL);
// query:
// SELECT *
// FROM users
// WHERE name IS NULL
// parameters:
// (Empty)
}
// WHERE: IS NULL
{
var select9 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("name", "is", null);
// query:
// SELECT *
// FROM users
// WHERE name IS NULL
// parameters:
// (Empty)
}
// WHERE: IS NOT NULL
{
var select10 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("name", "is not", null);
// query:
// SELECT *
// FROM users
// WHERE name IS NOT NULL
// parameters:
// (Empty)
}
// WHERE: LIKE / NOT LIKE
{
var select11 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("name", OpType.LIKE, "J%");
// query:
// SELECT *
// FROM users
// WHERE name LIKE @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.String
// Value: "J%"
}
// WHERE
{
var select12 = new Select()
.SetFrom("users")
.AddField("*")
.AddWhere("age", ">=", 20)
.AddWhere(Type.AND, "name", OpType.LIKE, "J%")
.AddWhere(Type.AND, "enabled", "=", true);
// query:
// SELECT *
// FROM users
// WHERE age >= @w_0
// AND name LIKE @w_1
// AND enabled = @w_2
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 20
// [1]:
// ParameterName: @w_1
// DbType: DbType.String
// Value: "J%"
// [2]:
// ParameterName: @w_2
// DbType: DbType.Boolean
// Value: true
}
// JOIN
{
var select13 = new Select()
.SetFrom("posts")
.AddJoin(JType.LEFT_JOIN, "users", "users.id = posts.user_id")
.AddField("posts.id")
.AddField("posts.comment")
.AddField("posts.user_id")
.AddField("users.name", "user_name")
.AddWhere("users.age", "<", 18);
// query:
// SELECT posts.id, posts.comment, posts.user_id, users.name AS user_name
// FROM posts
// LEFT JOIN users ON (users.id = posts.user_id)
// WHERE users.age < @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 18
}
// JOIN: Table Alias
{
var select14 = new Select()
.SetFrom("posts", "p")
.AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
.AddField("p.id")
.AddField("p.comment")
.AddField("p.user_id")
.AddField("u.name", "user_name")
.AddWhere("u.age", "<", 18);
// query:
// SELECT p.id, p.comment, p.user_id, u.name AS user_name
// FROM posts AS p
// LEFT JOIN users AS u ON (u.id = p.user_id)
// WHERE u.age < @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 18
}
// JOIN: Schema
{
var select15 = new Select()
.SetFrom("dbo", "posts", "p")
.AddJoin(JType.LEFT_JOIN, "dbo", "users", "u", "u.id = p.user_id")
.AddField("p.id")
.AddField("p.comment")
.AddField("p.user_id")
.AddField("u.name", "user_name")
.AddWhere("u.age", "<", 18);
// query:
// SELECT p.id, p.comment, p.user_id, u.name AS user_name
// FROM dbo.posts AS p
// LEFT JOIN dbo.users AS u ON (u.id = p.user_id)
// WHERE u.age < @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 18
}
// GROUP BY
{
var select16 = new Select()
.SetFrom("posts")
.AddJoin(JType.LEFT_JOIN, "users", "users.id = posts.user_id")
.AddField("user_id")
.AddField("users.name", "user_name")
.AddField("COUNT(comment)", "post_count")
.AddGroup("user_id")
.AddGroup("user_name");
// query:
// SELECT user_id, users.name AS user_name, COUNT(comment) AS post_count
// FROM posts
// LEFT JOIN users ON (users.id = posts.user_id)
// GROUP BY user_id, user_name
}
// HAVING
{
var select17 = new Select()
.SetFrom("posts")
.AddField("user_id")
.AddField("COUNT(comment)", "post_count")
.AddGroup("user_id")
.AddHaving("post_count", ">=", 2)
.AddHaving("post_count", "<=", 4);
// query:
// SELECT user_id, COUNT(comment) AS post_count
// FROM posts
// LEFT JOIN users ON (users.id = posts.user_id)
// GROUP BY user_id
// HAVING post_count >= @h_0 AND post_count <= @h_1
// parameters:
// [0]:
// ParameterName: @h_0
// DbType: DbType.Int32
// Value: 2
// [1]:
// ParameterName: @h_1
// DbType: DbType.Int32
// Value: 4
}
// LIMIT and OFFET
{
var select18 = new Select()
.SetFrom("users")
.AddField("*")
.AddOrder("id", OType.ASC)
.SetLimit(5)
.SetOffset(10);
// query:
// SELECT *
// FROM users
// ORDER BY id ASC
// LIMIT 5
// OFFSET 10
// queryOption
// RdbKind.SqlServer
// query:
// SELECT *
// FROM users
// ORDER BY id ASC
// OFFSET 10 ROWS
// FETCH NEXT 5 ROWS ONLY
}
Insert
{
var insert1 = new Insert();
insert1.SetInto("users")
.Values
.Add("id", 1)
.Add("name", "name1")
.Add("age", 20)
.Add("created_at", DateTimeOffset.UtcNow)
.Add("updated_at", DateTimeOffset.UtcNow);
// query:
// INSERT INTO users
// (id, name, age, created_at, updated_at)
// VALUES
// (@v_0, @v_1, @v_2, @v_3, @v_4)
// parameters:
// [0]:
// ParameterName: @v_0
// DbType: DbType.Int32
// Value: 1
// [1]:
// ParameterName: @v_1
// DbType: DbType.String
// Value: "name1"
// [2]:
// ParameterName: @v_2
// DbType: DbType.Int32
// Value: 20
// [3]:
// ParameterName: @v_3
// DbType: DbType.DateTime
// Value: (2024-08-01 00:00:00)
// [4]:
// ParameterName: @v_4
// DbType: DbType.DateTime
// Value: (2024-08-01 00:00:00)
}
{
var insert2 = new Insert();
insert2.SetInto("users")
.Select ??= new Select()
.SetFrom("users2")
.AddField("id")
.AddField("name")
.AddField("age")
.AddField("created_at")
.AddField("updated_at");
// query:
// INSERT INTO users
// SELECT id, name, age, created_at, updated_at
// FROM users2
}
Update
{
var update1 = new Update()
.SetTable("users")
.AddSet("age", 21)
.AddSet("updated_at", DateTime.UtcNow)
.AddWhere("id", "=", 1);
// query:
// UPDATE users
// SET age = @s_0, updated_at = @s_1
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @s_0
// DbType: DbType.Int32
// Value: 21
// [1]:
// ParameterName: @s_1
// DbType: DbType.DateTime
// Value: (2024-08-01 00:00:00)
// [2]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
// JOIN for MySQL
{
var update2 = new Update()
.SetTable("posts", "p")
.AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
.AddSet("comment", "comment1")
.AddWhere("p.id", "=", 1);
// query:
// UPDATE posts AS p
// LEFT JOIN users AS u ON (u.id = p.user_id)
// SET comment = @s_0
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @s_0
// DbType: DbType.Int32
// Value: "comment1"
// [1]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
// JOIN for PostgreSQL, SQLite and SQL Server
{
var update3 = new Update()
.SetTable("posts")
.AddSet("comment", "comment1")
.SetFrom("posts", "p")
.AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
.AddWhere("p.id", "=", 1);
// query:
// UPDATE posts
// SET comment = @s_0
// FROM posts AS p
// LEFT JOIN users AS u ON (u.id = p.user_id)
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @s_0
// DbType: DbType.String
// Value: "comment1"
// [1]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
// JOIN for SQL Server
{
var update4 = new Update()
.SetTable("", "p")
.AddSet("comment", "comment1")
.SetFrom("posts", "p")
.AddJoin(JType.LEFT_JOIN, "users", "u", "u.id = p.user_id")
.AddWhere("p.id", "=", 1);
// query:
// UPDATE p
// SET comment = @s_0
// FROM posts AS p
// LEFT JOIN users AS u ON (u.id = p.user_id)
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @s_0
// DbType: DbType.String
// Value: "comment1"
// [1]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
Delete
{
var delete1 = new Delete()
.SetFrom("users")
.AddWhere("id", "=", 1);
// query:
// DELETE
// FROM users
// WHERE id = @w_0
// parameters:
// [0]:
// ParameterName: @w_0
// DbType: DbType.Int32
// Value: 1
}
Last updated: 16 August, 2024
Editor: Izayoi Jiichan
Copyright (C) 2024 Izayoi Jiichan. All Rights Reserved.
| 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. |
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 Izayoi.Data.Query:
| Package | Downloads |
|---|---|
|
Izayoi.Data.DbCommandAdapter
This is a database operation support library that includes a fast micro O/R mapper (ORM). |
GitHub repositories
This package is not used by any popular GitHub repositories.
| Version | Downloads | Last updated |
|---|---|---|
| 1.0.0 | 173 | 8/16/2024 |