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!
A small helper project that allows transforming ODataQueryOptions<T>
directly into
a SelectStatement
.
Install-Package Dybdc.Data.Sql.Builder
NuGet Dybdc.Data.Sql.Builder.OData
Install-Package Dybdc.Data.Sql.Builder.OData
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"));
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"));
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
)"));
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"));
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"));
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!
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();
- Fork it!
- Create your feature branch:
git checkout -b my-new-feature
- Commit your changes:
git commit -am 'Add some feature'
- Make sure you add a unit test!
- Push to the branch:
git push origin my-new-feature
- Submit a pull request :D
Author: cdroulers
LGPL: See LICENSE
- Basic support for INSERT, UPDATE and DELETE statements.
- Support for different SQL providers (SQL Server in particular) in a pluggable way.
- First public version. Supports all basic SQL operations for PostgreSQL.
- ???
- First public version. Supports
$select
,$top
,$skip
and$inlinecount=allpages
.
- Support basic
$filter
operations