GraphQL.SQL.Builder 1.0.6

GraphQL.SQL.Builder for .NET

SQL Query Builder Utility

Useful for scenarios where dynamic SQL is required. Supports multiple condition set logic.

Generate Select, Insert, Update and Delete statements with parameters.

Additional features include : Paging, Advanced Condition Sets, Auto Parameter Naming

Currently used in GraphQL.SQL for dynamically generating SQL statements.

Getting Started

Install Package

Install-Package GraphQL.SQL.Builder

Simple Usage

 public DataTable GetUser(int userId)
 {
     var query = new SelectQueryBuilder("Users", "U");
     query.Field("UserId", "Id").
           Field("UserName").
           Field("Password").
           Condition("U.UserId", ColumnOperator.Equals, query.AddParam(userId,"UserId"));
     var sqlCommand = query.ToCommand();

     var table = new DataTable();
     using (var connection = new SqlConnection("connection_string"))
     {
         connection.Open();
         sqlCommand.Connection = connection;

         using (var dataAdapter = new SqlDataAdapter(sqlCommand))
         {
             dataAdapter.Fill(table);
         }
     }

     return table;
 }

Output

    SELECT
          UserId AS Id,
          UserName,
          Password
    FROM Users U
    WHERE U.UserId = @UserId
    
    --Parameters
    @UserId=1

Paging

    var query = new SelectQueryBuilder("Users");
    query.Field("UserId").
          Field("UserName").
          Condition("UserId", ColumnOperator.Equals, query.AddParam(1,"UserId")).
          Page(query.AddParam(1, "_PageNumber"), query.AddParam(10, "_PageSize"), "UserId");

Output

    SELECT
        UserId,
        UserName
    FROM Users
    WHERE UserId = @UserId
    ORDER BY UserId
    OFFSET @_PageSize * (@_PageNumber - 1)
    ROWS FETCH NEXT @_PageSize ROWS ONLY

    --Parameters
    @_PageNumber=1,@_PageSize=10,@UserId=1

Condition Sets Usage

    //Find users who are admins and username is either tim or connor
      var query = new SelectQueryBuilder("Users");
      query.Field("UserId").
            Field("UserName").
            Field("IsAdmin").
            Condition("IsAdmin", ColumnOperator.Equals, query.AddParam(true,"IsAdmin")).
            ConditionSet(1, SetOperator.And, (set) =>
            {
                set.OrCondition("UserName", ColumnOperator.Equals, query.AddParam("tim")).
                OrCondition("UserName", ColumnOperator.Equals, query.AddParam("connor"));
            });

Output

    SELECT
         UserId,
         UserName,
         IsAdmin
     FROM Users
     WHERE (IsAdmin = @IsAdmin) AND (UserName = @p_1 OR UserName = @p_2)

    --Parameters
    @IsAdmin=1,@p_1='tim',@p_2='connor'

Multiple Condition Sets

    //(Find users who are admins and username is either tim or connor) and password='password'
    var query = new SelectQueryBuilder("Users");
    query.Field("UserId").
            Field("UserName").
            Field("IsAdmin").
            Field("Password").
            ConditionSet(1, SetOperator.And, (set) =>
            {
                set.AndCondition("IsAdmin", ColumnOperator.Equals, query.AddParam(true, "IsAdmin")).
                OrCondition("UserName", ColumnOperator.Equals, query.AddParam("tim")).
                OrCondition("UserName", ColumnOperator.Equals, query.AddParam("connor"));
            }).
            ConditionSet(2, SetOperator.And, (set) =>
            {
                set.OrCondition("Password", ColumnOperator.Equals, query.AddParam("password")).
                    OrCondition("Password", ColumnOperator.Equals, query.AddParam("Test123")).
            });

Output

    SELECT
        UserId,
        UserName,
        IsAdmin,
        Password
    FROM Users
    WHERE (((IsAdmin = @IsAdmin) AND (UserName = @p_1 OR UserName = @p_2))) AND (Password = @p_3 or Password=@p_4)
                
    --Parameters
    @IsAdmin=1,@p_1='tim',@p_2='connor',@p_3='password'

Select

    var query = new SelectQueryBuilder("Users", "U");
    query.Field("UserId", "Id").
          Field("UserName").
          Field("Password").
          Condition("U.UserId", ColumnOperator.Equals, "1");

Output

    SELECT
          UserId AS Id,
          UserName,
          Password
    FROM Users U
    WHERE U.UserId = 1

Insert

    var insert = new InsertQueryBuilder("Users");
        insert.Field("UserId", insert.AddParam("1")).
               Field("Password", insert.AddParam("test123"));

Output

    INSERT INTO Users
    (
        UserId,
        Password
    )
    VALUES
    (
        @p_0,
        @p_1
    )

Insert From Select

    var insert = new InsertQueryBuilder("Users");
        insert.Field("UserId").
                Field("Password").
                From("Users_Backup", "UB", (query) =>
                {
                    query.Field("UB.UserId").
                            Field("UB.Password").
                            Condition("UB.UserId", ColumnOperator.Equals, insert.AddParam(1, "UserId"));
                });

Output

    INSERT INTO Users
    (
        UserId,
        Password
    )
    SELECT
        UB.UserId,
        UB.Password
    FROM Users_Backup UB
    WHERE UB.UserId = @UserId

Update

    var update = new UpdateQueryBuilder("Users");
        update.Field("UserId", update.AddParam(10, "NewUserId")).
               Field("Password", update.AddParam("3423", "Password")).
               Condition("UserId", ColumnOperator.Equals, update.AddParam("1", "UserId"));

Output

    UPDATE Users
    SET UserId=@NewUserId,
        Password=@Password
    WHERE UserId = @UserId

Update from Join

     var update = new UpdateQueryBuilder("Users","U");
         update.Join("User_Backup UB", JoinType.Inner, "UB.UserId=U.UserId").
                Field("Password", "UB.Password").
                Condition("U.UserId", ColumnOperator.Equals, update.AddParam("1", "UserId"));

Output

    UPDATE U
        SET Password=UB.Password
    FROM Users U
    INNER JOIN User_Backup UB ON UB.UserId=U.UserId
    WHERE U.UserId = @UserId

Delete

    var delete = new DeleteQueryBuilder("Users");
        delete.Condition("UserId", ColumnOperator.Equals, delete.AddParam("1", "UserId"));

Output

    DELETE FROM Users
    WHERE UserId = @UserId

Delete With Join

    var delete = new DeleteQueryBuilder("Users","U");
        delete.Join("User_Backup UB", JoinType.Inner, "UB.UserId=U.UserId").
               Condition("U.UserId", ColumnOperator.Equals, delete.AddParam("1", "UserId"));

Output

    DELETE U FROM Users U
    INNER JOIN User_Backup UB ON UB.UserId=U.UserId
    WHERE U.UserId = @UserId

Join

    var query = new SelectQueryBuilder("Users", "U");
    query.Field("U.UserId", "Id").
          Field("U.UserName").
          Field("U.Password").
          Join("Preferences P",JoinType.Inner,"P.UserId = U.UserId").
          Field("P.Theme").
          Condition("U.UserId", ColumnOperator.Equals, "1");

Output

    SELECT
          U.UserId AS Id,
          U.UserName,
          U.Password,
          P.Theme
    FROM Users U
    INNER JOIN Preferences P ON P.UserId = U.UserId
    WHERE U.UserId = 1

Count

    var query = new SelectQueryBuilder("Users", "U");
    query.Count("*", "[RecordCount]").
          Condition("U.UserId", ColumnOperator.Equals, "1");

Output

    SELECT
        COUNT(*) AS [RecordCount]
    FROM Users U
    WHERE U.UserId = 1