SQLQueryCommandBuilder 1.0.0

  public class user
    {
        public user()
        {

        }
        [SQLResolver(PrimaryKeyName = "id")]
        public int id { get; set; }
        public int age { get; set; }
        public string name { get; set; }
        public bool isActive { get; set; }
    }

Primary key must have SQLResolver Attribute with the property name as PrimaryKeyName value

SQL Query Resolver Extension Methods

1. string Query<T>(this T obj, BuilderOptions options = null)

2.string Query<T>(this T obj, List<string> selectColumns, BuilderOptions options = null) 

3. string Query<T>(this T obj, Expression<Func<T, bool>> whereClause, List<string> selectColumns = null, BuilderOptions options = null)
 
4. Query<T>(this T obj, int rows, int offset = 0, Expression<Func<T, bool>> whereClause = null, List<string> selectColumns = null, BuilderOptions options = null)

Sample Codes

Example 1

var sql = new user().Query<user>()

Output

SELECT  *  FROM user 

Example 2

 var sql = new user().Query<user>(
                whereClause: x => ((x.name == "oscar" && x.isActive == true) || x.age > 20),
                options: new SQL.QueryBuilder.Options.BuilderOptions
                {
                    orderBy = SQL.QueryBuilder.Enums.OrderBy.ASC,
                    orderByColumn = "age"
                });

Output

SELECT  *  FROM user Where ((([name] = 'oscar') AND ([isActive] = True)) OR ([age] > 20)) ORDER BY age ASC

Example 3

 var sql = new user().Query<user>(
               whereClause: x => ((x.name == "oscar" && x.isActive == true) || x.age > 20),
               options: new SQL.QueryBuilder.Options.BuilderOptions
               {
                   orderBy = SQL.QueryBuilder.Enums.OrderBy.ASC,
                   orderByColumn = "age"
               },
               selectColumns: new List<string>() { "age", "name" }
               );

Output

SELECT  age,name  FROM user Where ((([name] = 'oscar') AND ([isActive] = True)) OR ([age] > 20)) ORDER BY age ASC

Example 4

 var items = new List<int>() { 3, 6, 3, 2 };
            var test2 = new user().Query<user>(rows: 20, offset: 0,
               selectColumns: new List<string>() { "age", "name" },
               whereClause: x => x.name.Contains("oscar") && items.Contains(x.id)
               );

Output

SELECT  age,name  FROM  user  (nolock)  Where (([name] LIKE '%oscar%') AND ([id] IN (3,6,3,2)))  OFFSET  0  ROWS FETCH NEXT  20  ROWS ONLY 

Supported Lamda Expression Operations

SQL Command Resolver Extension Methods

1. string Insert<T>(this T obj)
 
2. string Update<T>(this T obj, Expression<Func<T, bool>> whereClause, List<string> selectColums = null) 
 
3. string Delete<T>(this T obj, Expression<Func<T, bool>> whereClause)

Sample Codes

Example 1

  var sql = new user().Insert<user>();

Output

INSERT INTO user (age,name,isActive) VALUES (@age,@name,@isActive)

Example 2

  var items = new List<int>() { 3, 6, 3, 2 };
            var sql = new user().Update<user>(
                whereClause: x => x.name.Contains("oscar") && items.Contains(x.id);

Output

UPDATE user SET age=@age,name=@name,isActive=@isActive Where (([name] LIKE '%oscar%') AND ([id] IN (3,6,3,2)))

Example 3

 var items = new List<int>() { 3, 6, 3, 2 };
            var sql = new user().Delete<user>(
            whereClause: x => x.name.Contains("oscar") && items.Contains(x.id));

Output

DELETE FROM user Where (([name] LIKE '%oscar%') AND ([id] IN (3,6,3,2)))