cdroulers/awesome-sql-builder

A small library for building SQL queries in a better way than regular string concatenation.

Awesome.Data.Sql.Builder

Travis CI: Gitter Chat:

A small library for building SQL queries in a better way than regular string concatenation.

Clauses can be added in any order.

Mostly for use directly by ADO.NET or micro-ORMs such as Dapper. It aims to support as much of SQL as possible in a fluent, simple way.

It definitely does not aim to be an ORM, so there's no integration with any actual querying mechanism!

Awesome.Data.Sql.Builder.OData

A small helper project that allows transforming ODataQueryOptions<T> directly into a SelectStatement.

Installation

NuGet Awesome.Data.Sql.Builder

Install-Package Awesome.Data.Sql.Builder

NuGet Awesome.Data.Sql.Builder.OData

Install-Package Awesome.Data.Sql.Builder.OData

Awesome.Data.Sql.Builder Usage

SELECT Statement

Here is a sample unit test from the project.

var statement = SqlStatements.Select(new[] { "u.ID", "u.Name", "u.EmailAddress" })
    .From("Users u")
    .Where("u.IsCool = TRUE")
    .Where("u.Name LIKE @Query")
    .OrderBy("u.Name", false)
    .Limit(3).Offset(6);

var sql = statement.ToSql();

Assert.That(
    sql,
    SqlCompareConstraint.EqualTo(@"SELECT
    u.ID, u.Name, u.EmailAddress
FROM
    Users u
WHERE
    u.IsCool = TRUE AND
    u.Name LIKE @Query
ORDER BY
    u.Name DESC
LIMIT 3 OFFSET 6"));

UPDATE Statement

Sample unit test!

var statement = new UpdateStatement(new[] { "ID", "Name", "EmailAddress" })
    .From("Users")
    .Where("u.IsCool = TRUE")
    .Where("u.Name LIKE @Query");

var sql = statement.ToSql();

Assert.That(
    sql,
    SqlCompareConstraint.EqualTo(@"UPDATE Users
SET
    ID = @ID,
    Name = @Name,
    EmailAddress = @EmailAddress
WHERE
    u.IsCool = TRUE AND
    u.Name LIKE @Query"));

INSERT Statement

Sample unit test!

var statement = new InsertStatement(new[] { "Name", "EmailAddress" })
    .Into("Users");

var sql = statement.ToSql();

Assert.That(
    sql,
    SqlCompareConstraint.EqualTo(@"INSERT INTO Users
    (
        Name,
        EmailAddress
    )
VALUES
    (
        @Name,
        @EmailAddress
    )"));

DELETE Statement

Sample unit test!

var statement = new DeleteStatement(tableToDelete: "u")
    .From("Users u")
    .InnerJoin("Teams t", "u.TeamID = t.ID")
    .Where("t.IsOld = TRUE");

var sql = statement.ToSql();

Assert.That(
    sql,
    SqlCompareConstraint.EqualTo(@"DELETE u
FROM
    Users u
    INNER JOIN Teams t ON u.TeamID = t.ID
WHERE
    t.IsOld = TRUE"));

Different provider

var statement = new SelectStatement(new[] { "u.ID" })
    .From("Users u")
    .Limit(3)
    .Offset(6);

var sql = new SqlServer2012SqlRenderer().RenderSelect(statement);

Assert.That(
    sql,
    SqlCompareConstraint.EqualTo(@"SELECT
    u.ID
FROM
    Users u
OFFSET 6 ROWS
FETCH NEXT 3 ROWS ONLY"));

Awesome.Data.Sql.Builder.OData Usage

var options = ODataQueryOptionsHelper.Build<TestDTO>(
    "$select=Id,Name,Contact/FirstName,Contact/BirthDate,Contact/Address/City&" + 
    "$expand=Contact,Contact/Address&" +
    "$top=10&" +
    "$skip=20");
var result = new ODataQueryOptionsToSqlStatement().ToSelect(options).First();

Assert.That(
    result.ColumnsList,
    Is.EquivalentTo(new[]
    {
        "Id",
        "Name",
        "Contact/FirstName",
        "Contact/BirthDate",
        "Contact/Address/City"
    }));
Assert.That(result.LimitClause, Is.EqualTo("10"));
Assert.That(result.OffsetClause, Is.EqualTo("20"));

Only supports $select, $top, $skip and $inlinecount=allpages for now!

Using with ADO.NET

You can generate the SQL then add the parameters manually.

var select = SqlStatements.Select("Name").From("Users").Where("Name LIKE @Query");

cmd.Text = select.ToString();
cmd.Parameters.AddWithValue("@Query", "%" + userInput + "%");

var dr = cmd.ExecuteDataReader();

Contributing

1. Fork it!
2. Create your feature branch: git checkout -b my-new-feature
3. Commit your changes: git commit -am 'Add some feature'
4. Make sure you add a unit test!
5. Push to the branch: git push origin my-new-feature
6. Submit a pull request :D

Credits

Author: cdroulers

License

LGPL: See LICENSE

Awesome.Data.Sql.Builder History

1.2.0 (2018-05-07)

• Basic support for INSERT, UPDATE and DELETE statements.

1.1.0 (2016-01-18)

• Support for different SQL providers (SQL Server in particular) in a pluggable way.

1.0.0 (2012-11-16)

• First public version. Supports all basic SQL operations for PostgreSQL.

Roadmap

1.2.0

• ???

Awesome.Data.Sql.Builder.OData History

1.0.0 (2016-01-23)

• First public version. Supports $select, $top, $skip and $inlinecount=allpages.

Roadmap

1.1.0

• Support basic $filter operations

awesome-sql-builder's Issues

UPDATE statement

var statement = SqlStatements.Update(new[] { "Name", "Email" })
    .Table("Users");

var sql = statement.ToSql();

Assert.That(
    sql,
    SqlCompareConstraint.EqualTo(@"UPDATE Users
SET
    Name = @Name,
    Email = @Email")

INSERT statement

var statement = SqlStatements.Insert(new[] { "Name", "Email" })
    .Into("Users")
    .FromValues();

var sql = statement.ToSql();

Assert.That(
    sql,
    SqlCompareConstraint.EqualTo(@"INSERT INTO Users
(
    Name,
    Email
)
VALUES
(
    @Name,
    @Email
)")

Probably add something like .FromSelect(SelectStatement select)!

Does it use parameter binding?

The where clause is string with field name, operator and value all in one string. Does It uses parameter binding to protect my application against SQL injection attacks?

Or there is need to clean strings being passed? Ideally it should be
Where("FieldName", Equal.To, "Some value")

Source for Awesome.Data.Sql.Builder.OData?

Hello!

This is not exactly a code issue, more like a project one.

You mentioned Awesome.Data.Sql.Builder.OData, and the nuget package for it links to this repo, but I don't see the source for it anywhere.
Is it not public for some reason, or is it just hard to find?

Escape identifiers automatically

column names with '[columnName]'.

Example:
SELECT [Id], [Name], [Age] FROM [Register]

Discontinued?

Hello,

the last commit is almost 2 years ago, are there any plans to continue this project?

Migrate to .NET Standard so it's cross-platform compatible!

Duh!

postgreSQL support for renderer

DELETE statement

var statement = SqlStatements.Delete()
    .From("Users")
    .Where("IsCool = TRUE")
    .Where("Name LIKE @Query");

var sql = statement.ToSql();

Assert.That(
    sql,
    SqlCompareConstraint.EqualTo(@"DELETE FROM Users
WHERE
    u.IsCool = TRUE AND
    u.Name LIKE @Query")