kronic / awesome-sql-builder

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

Awesome.Data.Sql.Builder

Travis CI:

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.

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

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

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!

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