Table of contents
- Why?
- No more tightly coupled SQL string
- SQL queries secured by default
- Building statements
- Contribute
- What's new
The idea of this project came to me when I dealt with Entity Framework 6.x Code First for a project I was working on as Architect.
We used Migrations to make changes to our database and sometimes we needed to write plain SQL statements as part of migratinuons.
For this, the EF 6.x Sql(...) command allows to add additional SQL statements that will be executed alongside the migrations.
But I wasn't happy with that approach as the written SQL was tightly coupled to the database engine those migrations were run against.
I wanted something more dynamic allowing to code SQL once in the migrations and be sure that it will run smoothly if we switch from SQL Server to PostgreSQL / MySQL / ... .
Writing tightly coupled SQL means that you're writing SQL statements that are specific to a database engine.
The following SQL string
SELECT [Firstname] + ' ' + [Lastname] AS [fullname] FROM [members]is tightly coupled to SQL Server engine and won't work if dealing with Postgres whereas
// import the library
import static Queries.Core.Builders.Fluent.QueryBuilder // C# 7 syntax
import _ = Queries.Core.Builders.Fluent.QueryBuilder // Pre C# 6 syntax
//compute a "static" query
IQuery query = Select(Concat("firstname").Field(), " ".Literal(), "lastname".Field()).From("members");will output
//For SQL SERVER
string sqlServerString = query.ForSqlServer();
Console.Writeline(sqlServerString); // SELECT [Firstname] + ' ' + [Lastname] AS [fullname] FROM [members]
//For Postgres
string postgresSqlString = query.ForPostgres();
Console.Writeline(postgresSqlString);// SELECT "Firstname" + ' ' + "Lastname" "fullname" FROM "members"Most developers know about SQL injection and how to protect from it.
But when using SQL string throughout one's codebase, it can quickly become a tedious task to secure each and every SQL query.
Sure there's the ADO.NET library which provide various classes to create parameterized queries but this add more and more boilerplate code :
using (var conn = GetConnectionSomehow() )
{
conn.Open();
DbParameter nicknameParam = new SqlParameter();
nicknameParam.SqlDbType = SqlDbType.String;
nicknameParam.ParameterName = "@nickname";
nicknameParam.Value = "Bat%";
SqlCommand cmd = new SqlCommand()
cmd.Connection = conn;
cmd.CommandText = "SELECT Firstname + ' ' + 'Lastname' FROM SuperHero WHERE Nickname LIKE @nickname";
var result = cmd.ExecuteQuery();
....
conn.Close();
}whereas with Queries :
using (var conn = GetConnectionSomehow() )
{
conn.Open();
IQuery query = Select(Concat("Firstname".Field(), " ".Literal(), "Lastname".Field())
.From("SuperHero")
.Where("Nickname", Like, "Bat%" );
cmd.CommandText = query.ForSqlServer();
/* CommandText now contains
DECLARE @p0 NVARCHAR(max);
SET @p0 = 'Bat%';
SELECT Firstname + ' ' + 'Lastname' FROM SuperHero WHERE Nickname LIKE @p0;
*/
var result = cmd.ExecuteQuery();
....
conn.Close();
}The code is shorter, clearer as the boilerplate code is no longer a distraction
IColumn is the base interface that all column like types implement.
-
FieldColumn contains a column name.
-
LiteralColumn Uses the following classes whenever you want to write a "raw" data in a query
-
BooleanColumn : a column that can contains a boolean value.
Use this class to output a boolean value in the query
IQuery query = Select("Firstname".Field(), "Lastname".Field())
.From("members")
.Where("IsActive", EqualTo, new BooleanColumn(true));can also be written
IQuery query = Select("Firstname".Field(), "Lastname".Field())
.From("members")
.Where("IsActive", EqualTo, true);which will output for
SELECT [Firstname], [Lastname] FROM [members] WHERE [IsActive] = 1-
DateTimeColumn : a IColumn implementation that can contains a
date/time/datetimevalue.
Use this class to output aDateTime/DateTimeOffsetvalue. -
StringColumn : an IColumn implementation that contains "string" values
IQuery query = Select("Firstname".Field(), "Lastname".Field())
.From("members")
.Where("DateOfBirth", EqualTo, 1.April(1990));You can optionally specify a format to use when rendering the variable with the Format(string format) extension method.
IQuery query = Select("Firstname".Field(), "Lastname".Field())
.From("members")
.Where("DateOfBirth", EqualTo, 1.April(1990).Format("dd-MM-yyyy"));💡 Use the column type most suitable to your need to leverage both intellisence and the fluent builder API.
You can start building various statements after installing the Queries.Core package.
Create a SelectQuery instance either by using the builder or the fluent syntax to build (drum rolling ...) a SELECT query
// Using builders ...
IQuery query = new SelectQuery
{
Columns = new IColumn[]
{
new FieldColumn("Firstname"),
new FieldColumn("Lastname")
},
Tables = new ITable[]
{
new Table("members")
}
};
// ... or fluent syntax
IQuery query = Select("Firstname".Field(), "Lastname".Field())
.From("members");Create a UpdateQuery instance either by using the builder or the fluent syntax to build (drum rolling ...) an UPDATE statement
// Using builders ...
IQuery query = new UpdateQuery
{
Table = new Table("members"),
Values = new []
{
new UpdateFieldValue("Nickname".Field(), "Nightwing")
},
WhereCriteria = new WhereClause{ Column = "NickName", Operator = EqualTo, Value = "Robin" }
}
// ... or with fluent syntax
IQuery query = Update("members")
.Set("Nickname".Field().EqualTo("NightWing"))
.Where("Nickname", EqualTo, "Robin");Create a DeleteQuery instance either by using the builder or the fluent syntax to build (drum rolling ...) an DELETE statement
// Using builders ...
IQuery query = new DeleteQuery
{
Table = new Table("members"),
WhereCriteria = new WhereClause{ Column = "Activity", Operator = NotLike, Value = "%Super hero%" }
}
// ... or with fluent syntax
IQuery query = Delete("members")
.Where("Activity".Field(), NotLike, "%Super hero%")Create a InsertIntoQuery instance either by using the builder or the fluent syntax to build (drum rolling ...) an INSERT INTO statement
// Using builders ...
IQuery query = new InsertIntoQuery
// ... or with fluent syntax
IQuery query = InsertInto("members")
.Values(
"Firstname".Field().InsertValue("Bruce".Literal()),
"Lastname".Field().InsertValue("Wayne".Literal())
)or even combine them using a BatchQuery
BatchQuery batch = new BatchQuery(
InsertInto("members").Values("Firstname".Field().EqualTo("Harley"), "Lastname".Field().EqualTo("Quinzel"))
Delete("members_bkp").Where("Nickname".Field(), EqualTo, ""))
);Warning
All xxxxQuery classes are all mutable (unless specified otherwise) meaning that any instance can be modified
AFTER being created.
Use the .Clone() method to duplicate any instance.
Queries.Core.Parts.Clauses namespace contains classes to add filters to IQuery instances.
- WhereClause : a criterion that will be applied to only one field of a IQuery
- CompositeWhereClause : combine several IWhereClause instances together.
- HavingClause : a criterion that will be applied to only one field of a IQuery
- CompositeHavingClause : allow to combine several IHavingClause instances together.
Several functions are supported out of the box. See [IFunction][class-functions] implementations and associated unit tests to see how to use them when building statemeents.
💡 You can always use NativeQuery whenever you need to write a statement that is not yet supported by the libray.
Renderers are special classes that can produce a SQL string given a IQuery instance.
IQuery query = GetQuery();
string sql = query.ForXXX() // where XXX stand for a database engine to targetBuilds SQL string that can be used with SQL Server Database Engine
IQuery query = Select(Concat("Firstname".Field(), " ".Literal(), "Lastname".Field()).As("Fullname"))
.From("members".Table())
.Where("Age".Field().GreaterThanOrEqualTo(18));
string sql = query.ForSqlServer(new QueryRendererSettings { PrettyPrint = true });
Console.WriteLine(sql);
/*
DECLARE @p0 NUMERIC = 18;
SELECT [Firstname] + ' ' + [Lastname] FROM [members] WHERE [Age] >= @p0
*/ Builds SQL string that can be used with MySQL Database Engine
IQuery query = Select(Concat("Firstname".Field(), " ".Literal(), "Lastname".Field()).As("Fullname"))
.From("members".Table())
.Where("Age".Field().GreaterThanOrEqualTo(18));
string sql = query.ForMySql(new QueryRendererSettings { PrettyPrint = true });
Console.WriteLine(sql);
/*
DECLARE @p0 NUMERIC = 18;
SELECT [Firstname] + ' ' + [Lastname] FROM [members] WHERE [Age] >= @p0"
*/Builds SQL string that can be used with Sqlite Database Engine
IQuery query = Select(Concat("firstname".Field(), " ".Literal(), "lastname".Field()).As("Fullname"))
.From("superheroes")
.Where("nickname".Field(), EqualTo, "Batman");
string sql = query.ForSqlite(new SqliteRendererSettings { PrettyPrint = true });
Console.WriteLine(sql);
/*
BEGIN;
PRAGMA temp_store = 2;
CREATE TEMP TABLE "_VARIABLES"(ParameterName TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);
INSERT INTO "_VARIABLES" ("ParameterName") VALUES ('p0');
UPDATE "_VARIABLES" SET "TextValue" = 'Batman' WHERE ("ParameterName" = 'p0')
SELECT "firstname" || ' ' || "lastname" AS "Fullname" "
FROM "superheroes" "
WHERE ("nickname" = (SELECT COALESCE("RealValue", "IntegerValue", "BlobValue", "TextValue") FROM "_VARIABLES" WHERE ("ParameterName" = 'p0') LIMIT 1));
DROP TABLE "_VARIABLES";
END
*/💡 There are several renderers already available on nuget.org.
The "shape" of the string returned by a renderer (date format, parameterized query, ...) can
be customized by providing an implementation of QueryRendererSettings instance.
to the ForXXXX() method.
IQuery query = ...
QueryRendererSettings settings = new MyCustomRendererSettings();
string sql = query.ForXXX(settings) // where XXX stand for a database engine to targetDateFormatString: defines how DateTimes should be printed (YYYY-MM-DDby default)FieldnameCasingStrategy: Defines the column name casing strategy (Defaultmeaning no transformation)PrettyPrintParametrization: a hint for renderers on how to handle all variables a [IQuery] my embbed. This is useful when variables declaration has already been taken care of (see CollectVariableDeclaration)
- Run
dotnet add package Queries.Corecommand to get the latest version of the Queries.Core package and references it in your project.
From this point you can start building queries in your code. - Download the Queries.Renderers.XXXXX that is specific to the database engine you're targeting.
This will add extensions methods
ForXXXXto all IQuery instances that produces SQL statements - Enjoy !!!
Check out the contribution guidelines if you want to contribute to this project.
Check out the changelog to see what's new