blowin.SqlQueryBuilder 1.0.0

Package Description

Install-Package blowin.SqlQueryBuilder -Version 1.0.0
dotnet add package blowin.SqlQueryBuilder --version 1.0.0
<PackageReference Include="blowin.SqlQueryBuilder" Version="1.0.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add blowin.SqlQueryBuilder --version 1.0.0
The NuGet Team does not provide support for this client. Please contact its maintainers for support.

SqlQueryBuilder

Simple query
var builder = Query.From("People")
    .Column("is_blocked")
    .Column("id")
    .Column("brief_name")
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine;
Assert.Equal(res, builder);
Simple query with alias
var fromTable = SqlAliasField.Of("p", "People");
var builder = Query.From(fromTable)
    .Column(fromTable.WithData("is_blocked"), "block")
    .Column(fromTable.WithData("id"))
    .Column(fromTable.WithData("brief_name"))
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[block] = [p].[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id] = [p].[id],"  + Environment.NewLine +
    $"{Tab}[brief_name] = [p].[brief_name]"  + Environment.NewLine +
    "FROM [People] [p]" + Environment.NewLine;
Assert.Equal(res, builder);
Where
var builder = Query.From(SqlAliasField.Of("People"))
    .Column("is_blocked", "id", "brief_name")
    .Where("city_1", ConditionType.Eq, SqlWherePair.Of(SqlWhereValue.Of("LA", SqlFormatType.String), ConditionJoinerType.And))
    .Where("city_2", ConditionType.NotEq, SqlWhereValue.Of("New", SqlFormatType.String))
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine + 
    "WHERE [city_1] = 'LA' AND" + Environment.NewLine +
    $"{Tab}[city_2] != 'New'" + Environment.NewLine;

Assert.Equal(res, builder);
Inner join
var builder = Query.From(SqlAliasField.Of("People"))
    .Column("is_blocked", "id", "brief_name")
    .Join("vwa_C_Abstract_Contractor", JoinType.Inner, "id_entity", ConditionType.Eq, "id")
    .Join("vw_G_Country", JoinType.Inner, "id_country", ConditionType.Eq, "id_country_cross")
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine +
    $"{Tab}INNER JOIN [vwa_C_Abstract_Contractor] ON [id_entity] = [id]" + Environment.NewLine + 
    $"{Tab}INNER JOIN [vw_G_Country] ON [id_country] = [id_country_cross]" + Environment.NewLine;

Assert.Equal(res, builder);
Group by
var builder = Query.From(SqlAliasField.Of("People"))
    .Column("is_blocked", "id", "brief_name")
    .OrderBy("brief_name", "full_name")
    .OrderBy(OrderByDirection.Desc)
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine + 
    "ORDER BY" + Environment.NewLine +
    $"{Tab}[brief_name]," + Environment.NewLine +
    $"{Tab}[full_name] DESC" + Environment.NewLine;

Assert.Equal(res, builder);
Order by
var builder = Query.From(SqlAliasField.Of("People"))
    .Column("is_blocked", "id", "brief_name")
    .Where("is_blocked", ConditionType.Eq, "1")
    .GroupBy("my_field_1", "my_field_2")
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine +
    "WHERE [is_blocked] = 1" + Environment.NewLine +
    "GROUP BY" + Environment.NewLine + 
    $"{Tab}[my_field_1]," + Environment.NewLine +
    $"{Tab}[my_field_2]" + Environment.NewLine;

Assert.Equal(res, builder.ToString());

SqlQueryBuilder

Simple query
var builder = Query.From("People")
    .Column("is_blocked")
    .Column("id")
    .Column("brief_name")
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine;
Assert.Equal(res, builder);
Simple query with alias
var fromTable = SqlAliasField.Of("p", "People");
var builder = Query.From(fromTable)
    .Column(fromTable.WithData("is_blocked"), "block")
    .Column(fromTable.WithData("id"))
    .Column(fromTable.WithData("brief_name"))
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[block] = [p].[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id] = [p].[id],"  + Environment.NewLine +
    $"{Tab}[brief_name] = [p].[brief_name]"  + Environment.NewLine +
    "FROM [People] [p]" + Environment.NewLine;
Assert.Equal(res, builder);
Where
var builder = Query.From(SqlAliasField.Of("People"))
    .Column("is_blocked", "id", "brief_name")
    .Where("city_1", ConditionType.Eq, SqlWherePair.Of(SqlWhereValue.Of("LA", SqlFormatType.String), ConditionJoinerType.And))
    .Where("city_2", ConditionType.NotEq, SqlWhereValue.Of("New", SqlFormatType.String))
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine + 
    "WHERE [city_1] = 'LA' AND" + Environment.NewLine +
    $"{Tab}[city_2] != 'New'" + Environment.NewLine;

Assert.Equal(res, builder);
Inner join
var builder = Query.From(SqlAliasField.Of("People"))
    .Column("is_blocked", "id", "brief_name")
    .Join("vwa_C_Abstract_Contractor", JoinType.Inner, "id_entity", ConditionType.Eq, "id")
    .Join("vw_G_Country", JoinType.Inner, "id_country", ConditionType.Eq, "id_country_cross")
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine +
    $"{Tab}INNER JOIN [vwa_C_Abstract_Contractor] ON [id_entity] = [id]" + Environment.NewLine + 
    $"{Tab}INNER JOIN [vw_G_Country] ON [id_country] = [id_country_cross]" + Environment.NewLine;

Assert.Equal(res, builder);
Group by
var builder = Query.From(SqlAliasField.Of("People"))
    .Column("is_blocked", "id", "brief_name")
    .OrderBy("brief_name", "full_name")
    .OrderBy(OrderByDirection.Desc)
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine + 
    "ORDER BY" + Environment.NewLine +
    $"{Tab}[brief_name]," + Environment.NewLine +
    $"{Tab}[full_name] DESC" + Environment.NewLine;

Assert.Equal(res, builder);
Order by
var builder = Query.From(SqlAliasField.Of("People"))
    .Column("is_blocked", "id", "brief_name")
    .Where("is_blocked", ConditionType.Eq, "1")
    .GroupBy("my_field_1", "my_field_2")
    .ToString();

var res = "SELECT" + Environment.NewLine +
    $"{Tab}[is_blocked],"  + Environment.NewLine +
    $"{Tab}[id],"  + Environment.NewLine +
    $"{Tab}[brief_name]"  + Environment.NewLine +
    "FROM [People]" + Environment.NewLine +
    "WHERE [is_blocked] = 1" + Environment.NewLine +
    "GROUP BY" + Environment.NewLine + 
    $"{Tab}[my_field_1]," + Environment.NewLine +
    $"{Tab}[my_field_2]" + Environment.NewLine;

Assert.Equal(res, builder.ToString());

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version History

Version Downloads Last updated
1.0.0 194 6/13/2019