Skip to content

koshovyi/SqlBuilder

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

38 Commits
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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

Insert

  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 <T> + default attributes:
string sql = new Insert<Author>(Format.MsSQL)
	.GetSql();

/* Result:

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

*/

Delete

  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 <T> + 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

  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

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
IgnoreInsertAttribute Ignore property from INSERT statement
IgnoreUpdateAttribute Ignore property from UPDATE statement
InsertDefaultAttribute Default value for INSERT statement
UpdateDefaultAttribute Default value for UPDATE statement

Reflection

SqlBuilder reflection methods:

Method Description Attribute
GetTableName<T> Get table name TableNameAttribute
GetTableAlias<T> Get table alias TableNameAttribute
GetPrimaryKey<T> Get PK from table PrimaryKeyAttribute
GetForeignKeys<T> Get FK[] array from table ForeignKeyAttribute