Queries

This is a "basic" datastore agnostic query builder.

Table of contents

• Why?
• No more tightly coupled SQL string
• SQL queries secured by default
• Building statements
  • Columns
    • Statements
      • Select
      • Update
      • Delete
      • Insert
      • Combine
    • Criterias
      • Where
      • Having
    • Functions
  • Rendering statements
    • SQL Server Query Renderer
    • MySQL Query Renderer
    • Sqlite Query Renderer
    • QueryRendererSettings
  • How to install ?
• Contribute
• What's new

Why?

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 / ... .

No more tightly coupled SQL string

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"

SQL queries secured by default

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

Building statements

Columns

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/datetime value.
  Use this class to output a DateTime/DateTimeOffset value.

• 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.

Statements

You can start building various statements after installing the Queries.Core package.

Select

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");

Update

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");

Delete

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%")

Insert

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

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.

Criterias

Queries.Core.Parts.Clauses namespace contains classes to add filters to IQuery instances.

Where

• WhereClause : a criterion that will be applied to only one field of a IQuery
• CompositeWhereClause : combine several IWhereClause instances together.

Having

• HavingClause : a criterion that will be applied to only one field of a IQuery
• CompositeHavingClause : allow to combine several IHavingClause instances together.

Functions

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.

Rendering statements

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 target

SQL Server Query Renderer

Builds 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

*/ 

MySQL Query Renderer

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" 

*/

Sqlite Query Renderer

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.

QueryRendererSettings

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 target

• DateFormatString : defines how DateTimes should be printed (YYYY-MM-DD by default)
• FieldnameCasingStrategy : Defines the column name casing strategy (Default meaning no transformation)
• PrettyPrint
• Parametrization : 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)

How to install ?

1. Run dotnet add package Queries.Core command 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.
2. Download the Queries.Renderers.XXXXX that is specific to the database engine you're targeting. This will add extensions methods ForXXXX to all IQuery instances that produces SQL statements
3. Enjoy !!!

Contribute

Check out the contribution guidelines if you want to contribute to this project.

What's new

Check out the changelog to see what's new