Koshovyi.SqlBuilder 0.2.0

Simple and powerful SQL builder

Install-Package Koshovyi.SqlBuilder -Version 0.2.0
dotnet add package Koshovyi.SqlBuilder --version 0.2.0
<PackageReference Include="Koshovyi.SqlBuilder" Version="0.2.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Koshovyi.SqlBuilder --version 0.2.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

SqlBuilder [Beta]

SqlBuilder - simple and tiny SQL builder. Most easy way to create sql queries from code for .NET Core :)

Nuget

Install

nuget install Koshovyi.SqlBuilder

Features

  • Supports special database attributes and reflection;
  • Supports RAW sql string (columns, subqueries, aggregation functions etc.);
  • Supports all standard SQL DML queries: SELECT, DELETE, INSERT and UPDATE;
  • Supports only paramterized queries for safe value escaping;
  • Supports query templates;
  • Supports LINQ extensions (using SqlBuilder.Linq;);
  • And many more features;

Usage - Quick Guide

string sql = new Select<Author>(Format.MsSQL)
	.Columns(c =>
	{
		c.Append("s1", "s2", "s3");
		c.FuncMin("date");
	})
	.Where(w =>
	{
		w.Equal("s1", "s2");
		w.IsNotNULL("created_at");
		w.IsNULL("activated");
	})
	.GroupBy(g =>
	{
		g.Append(false, "country", "city");
		g.FuncCount("all", "countOfAll");
	})
	.OrderBy("age")
	.GetSql();

/* Result:

SELECT [s1], [s2], [s3], MIN([date]), COUNT([all]) as 'countOfAll' FROM [tab_authors] WHERE [s1]=@s1 AND [s2]=@s2 AND [created_at] IS NOT NULL AND [activated] IS NULL GROUP BY [country], [city], [all] ORDER BY [age] ASC;

*/

Simple examples (DML)

Select <a id="sql_select"></a>


Insert <a id="sql_insert"></a>

  1. Insert columns:
string sql = new Insert(Format.MsSQL, "table")
	.AppendParameters("a", "b", "c")
	.GetSql();

/* Result:

INSERT INTO [table]([a], [b], [c]) VALUES(@a, @b, @c);

*/
  1. Insert custom columns and custom values:
string sql = new Insert(Format.MsSQL, "table")
	.AppendParameters("firstName", "lastName")
	.Columns("createdAt")
	.Values("'NOW()'")
	.GetSql();

/* Result:

INSERT INTO [table]([firstName], [lastName], [createdAt]) VALUES(@firstName, @lastName, 'NOW()');

*/
  1. Insert new row for &lt;T&gt; + default attributes:
string sql = new Insert<Author>(Format.MsSQL)
	.GetSql();

/* Result:

INSERT INTO [author]([firstName], [lastName], [createdAt]) VALUES(@firstName, @lastName, 'NOW()');

*/

Delete <a id="sql_delete"></a>

  1. Delete all rows:
string sql = new Delete(Format.MsSQL, "table")
	.GetSql();

/* Result:

DELETE FROM [table];

*/

  1. Delete all rows (table with alias):
string sql = new Delete(Format.MsSQL, "table", "t")
	.GetSql();

/* Result:

DELETE FROM [table] as [t];

*/

  1. Delete row where id=@id (Parameter):
string sql = new Delete(Format.MsSQL, "table")
	.Where("id")
	.GetSql();

/* Result:

DELETE FROM [table] WHERE [id]=@id;

*/

  1. Delete row where id=123 (Value):
string sql = new Delete(Format.MsSQL, "table")
	.Where(w => w.EqualValue("id", "123"))
	.GetSql();

/* Result:

DELETE FROM [table] WHERE [id]=123;

*/

  1. Delete row &lt;T&gt; + where:
string sql = new Delete<Author>(Format.MsSQL, "td")
	.Where(w => w.Equal("p1").Less("p2").IsNULL("p3"));
	.GetSql();

/* Result:

DELETE FROM [tab_authors] as [td] WHERE [td].[p1]=@p1 AND [td].[p2]<@p2 AND [td].[p3] IS NULL;

*/

Update <a id="sql_update"></a>

  1. Update all rows:
string sql = new Update<Author>(Format.MsSQL)
	.GetSql();

/* Result:

UPDATE [authors] SET [firstname]=@firstname, [lastname]=@lastname;

*/

  1. Update rows where id=@id (Parameter):
string sql = new Update<Author>(Format.MsSQL)
	.Where("id")
	.GetSql();

/* Result:

UPDATE [authors] SET [firstname]=@firstname, [lastname]=@lastname WHERE [id]=@id;

*/

  1. Update rows where id=123 (Value):
string sql = new Update<Author>(Format.MsSQL)
	.Where(w => w.EqualValue("id", "123"))
	.GetSql();

/* Result:

UPDATE [authors] SET [firstname]=@firstname, [lastname]=@lastname WHERE [id]=123;

*/

  1. Update rows where id=123 (Value):
string sql = new Update<Author>(Format.MsSQL)
	.Where(w => w.EqualValue("id", "123"))
	.GetSql();

/* Result:

UPDATE [authors] SET [firstname]=@firstname, [lastname]=@lastname WHERE [id]=123;

*/

Database attributes <a name="db_attributes"></a>

SqlBuilder attributes:

| Attribute | Description |
|--------|-------------|-------------|
|TableNameAttribute|Set custom table name (and optionaly alias) |
|ColumnAttribute|Set custom column name|
|PrimaryKeyAttribute|Attribute for PK|
|ForeignKeyAttribute|Attribute for FK|
|IgnoreInsert|Ignore property from INSERT statement|
|IgnoreUpdate|Ignore property from UPDATE statement|
|InsertDefault|Default value for INSERT statement|
|UpdateDefault|Default value for UPDATE statement|

Reflection <a name="reflection"></a>

SqlBuilder reflection methods:

| Method | Description | Attribute |
|--------|-------------|-------------|
|GetTableName&lt;T&gt;|Get table name|TableNameAttribute|
|GetTableAlias&lt;T&gt;|Get table alias|TableNameAttribute|
|GetPrimaryKey&lt;T&gt;|Get PK from table|PrimaryKeyAttribute|
|GetForeignKeys&lt;T&gt;|Get FK[] array from table|ForeignKeyAttribute|

SqlBuilder [Beta]

SqlBuilder - simple and tiny SQL builder. Most easy way to create sql queries from code for .NET Core :)

Nuget

Install

nuget install Koshovyi.SqlBuilder

Features

  • Supports special database attributes and reflection;
  • Supports RAW sql string (columns, subqueries, aggregation functions etc.);
  • Supports all standard SQL DML queries: SELECT, DELETE, INSERT and UPDATE;
  • Supports only paramterized queries for safe value escaping;
  • Supports query templates;
  • Supports LINQ extensions (using SqlBuilder.Linq;);
  • And many more features;

Usage - Quick Guide

string sql = new Select<Author>(Format.MsSQL)
	.Columns(c =>
	{
		c.Append("s1", "s2", "s3");
		c.FuncMin("date");
	})
	.Where(w =>
	{
		w.Equal("s1", "s2");
		w.IsNotNULL("created_at");
		w.IsNULL("activated");
	})
	.GroupBy(g =>
	{
		g.Append(false, "country", "city");
		g.FuncCount("all", "countOfAll");
	})
	.OrderBy("age")
	.GetSql();

/* Result:

SELECT [s1], [s2], [s3], MIN([date]), COUNT([all]) as 'countOfAll' FROM [tab_authors] WHERE [s1]=@s1 AND [s2]=@s2 AND [created_at] IS NOT NULL AND [activated] IS NULL GROUP BY [country], [city], [all] ORDER BY [age] ASC;

*/

Simple examples (DML)

Select <a id="sql_select"></a>


Insert <a id="sql_insert"></a>

  1. Insert columns:
string sql = new Insert(Format.MsSQL, "table")
	.AppendParameters("a", "b", "c")
	.GetSql();

/* Result:

INSERT INTO [table]([a], [b], [c]) VALUES(@a, @b, @c);

*/
  1. Insert custom columns and custom values:
string sql = new Insert(Format.MsSQL, "table")
	.AppendParameters("firstName", "lastName")
	.Columns("createdAt")
	.Values("'NOW()'")
	.GetSql();

/* Result:

INSERT INTO [table]([firstName], [lastName], [createdAt]) VALUES(@firstName, @lastName, 'NOW()');

*/
  1. Insert new row for &lt;T&gt; + default attributes:
string sql = new Insert<Author>(Format.MsSQL)
	.GetSql();

/* Result:

INSERT INTO [author]([firstName], [lastName], [createdAt]) VALUES(@firstName, @lastName, 'NOW()');

*/

Delete <a id="sql_delete"></a>

  1. Delete all rows:
string sql = new Delete(Format.MsSQL, "table")
	.GetSql();

/* Result:

DELETE FROM [table];

*/

  1. Delete all rows (table with alias):
string sql = new Delete(Format.MsSQL, "table", "t")
	.GetSql();

/* Result:

DELETE FROM [table] as [t];

*/

  1. Delete row where id=@id (Parameter):
string sql = new Delete(Format.MsSQL, "table")
	.Where("id")
	.GetSql();

/* Result:

DELETE FROM [table] WHERE [id]=@id;

*/

  1. Delete row where id=123 (Value):
string sql = new Delete(Format.MsSQL, "table")
	.Where(w => w.EqualValue("id", "123"))
	.GetSql();

/* Result:

DELETE FROM [table] WHERE [id]=123;

*/

  1. Delete row &lt;T&gt; + where:
string sql = new Delete<Author>(Format.MsSQL, "td")
	.Where(w => w.Equal("p1").Less("p2").IsNULL("p3"));
	.GetSql();

/* Result:

DELETE FROM [tab_authors] as [td] WHERE [td].[p1]=@p1 AND [td].[p2]<@p2 AND [td].[p3] IS NULL;

*/

Update <a id="sql_update"></a>

  1. Update all rows:
string sql = new Update<Author>(Format.MsSQL)
	.GetSql();

/* Result:

UPDATE [authors] SET [firstname]=@firstname, [lastname]=@lastname;

*/

  1. Update rows where id=@id (Parameter):
string sql = new Update<Author>(Format.MsSQL)
	.Where("id")
	.GetSql();

/* Result:

UPDATE [authors] SET [firstname]=@firstname, [lastname]=@lastname WHERE [id]=@id;

*/

  1. Update rows where id=123 (Value):
string sql = new Update<Author>(Format.MsSQL)
	.Where(w => w.EqualValue("id", "123"))
	.GetSql();

/* Result:

UPDATE [authors] SET [firstname]=@firstname, [lastname]=@lastname WHERE [id]=123;

*/

  1. Update rows where id=123 (Value):
string sql = new Update<Author>(Format.MsSQL)
	.Where(w => w.EqualValue("id", "123"))
	.GetSql();

/* Result:

UPDATE [authors] SET [firstname]=@firstname, [lastname]=@lastname WHERE [id]=123;

*/

Database attributes <a name="db_attributes"></a>

SqlBuilder attributes:

| Attribute | Description |
|--------|-------------|-------------|
|TableNameAttribute|Set custom table name (and optionaly alias) |
|ColumnAttribute|Set custom column name|
|PrimaryKeyAttribute|Attribute for PK|
|ForeignKeyAttribute|Attribute for FK|
|IgnoreInsert|Ignore property from INSERT statement|
|IgnoreUpdate|Ignore property from UPDATE statement|
|InsertDefault|Default value for INSERT statement|
|UpdateDefault|Default value for UPDATE statement|

Reflection <a name="reflection"></a>

SqlBuilder reflection methods:

| Method | Description | Attribute |
|--------|-------------|-------------|
|GetTableName&lt;T&gt;|Get table name|TableNameAttribute|
|GetTableAlias&lt;T&gt;|Get table alias|TableNameAttribute|
|GetPrimaryKey&lt;T&gt;|Get PK from table|PrimaryKeyAttribute|
|GetForeignKeys&lt;T&gt;|Get FK[] array from table|ForeignKeyAttribute|

  • .NETStandard 2.0

    • No dependencies.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version History

Version Downloads Last updated
0.2.0 146 7/10/2020
0.1.6 475 8/8/2018
0.1.5 302 7/24/2018
0.1.4 362 7/5/2018
0.1.3 388 7/2/2018