SQL Query Builder Utility for .NET
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
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