blowin.SqlQueryBuilder 1.0.0

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