Skip to content

jodendaal/GraphQL.SQL.Builder

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

70 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

GraphQL.SQL.Builder for .NET

.NET NuGet Badge license

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

https://www.nuget.org/packages/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

Buy me a coffee

https://www.buymeacoffee.com/timdoestech?new=1