using System;
using System.Collections.Generic;
namespace DBUtility
{
public class QueryWrapper
{
private string select = "SELECT ";
private string field = "";
private string from = " FROM ";
private string TName = "";
private string Where = "";
private string OrderBy = "";
private string GroupBy = "";
private string Having = "";
private string Limit = "";
private string SQL = " SELECT * FROM " ;
private string CompleteSQL = "";
#region 参数构造器
public QueryWrapper()
{
}
public QueryWrapper(string TName)
{
this.TName = TName;
}
public QueryWrapper(string TName,string field)
{
this.TName = TName;
this.field = field;
}
#endregion
#region SQL构造
public string build()
{
if (string.IsNullOrEmpty(TName))
{
throw new Exception("When querying must be set tableName");
}
if (string.IsNullOrEmpty(field))
{
CompleteSQL += SQL + TName;
}
else
{
CompleteSQL += select + field + from + TName;
}
if (!string.IsNullOrEmpty(Where))
{
CompleteSQL += " WHERE";
Where = Where.Remove(0, 3);
CompleteSQL += Where;
}
if (!string.IsNullOrEmpty(GroupBy))
{
CompleteSQL += GroupBy;
if (!string.IsNullOrEmpty(Having))
{
CompleteSQL += Having;
}
}
if (!string.IsNullOrEmpty(OrderBy))
{
CompleteSQL += OrderBy;
}
if (!string.IsNullOrEmpty(Limit))
{
CompleteSQL += Limit;
}
CompleteSQL = CompleteSQL.TrimEnd();
return CompleteSQL;
}
#endregion
#region 查询内容指向
public QueryWrapper setField(params string[] f)
{
foreach (var item in f)
{
field += item + ",";
}
field = field.Remove(field.Length - 1, 1);
return this;
}
public QueryWrapper setTableName(string tName)
{
if (!string.IsNullOrEmpty(tName))
{
TName = tName;
}
return this;
}
public QueryWrapper setTableName<T>() where T : class, new()
{
T t = new T();
TName = t.GetType().Name;
return this;
}
#endregion
#region WHERE条件构造
public QueryWrapper eq(string Field, object value)
{
if (ObjNumTest(value))
{
Where += "and " + Field + "=" + value + " ";
}
else if (ObjStrTest(value))
{
Where += "and " + Field + "='" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper eq(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value))
{
Where += "and " + Field + "=" + value + " ";
}
else if (ObjStrTest(value))
{
Where += "and " + Field + "='" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper ne(string Field, object value)
{
if (ObjNumTest(value))
{
Where += "and " + Field + "<>" + value + " ";
}
else if (ObjStrTest(value))
{
Where += "and " + Field + "<>'" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper ne(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value))
{
Where += "and " + Field + "<>" + value + " ";
}
else if (ObjStrTest(value))
{
Where += "and " + Field + "<>'" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper gt(string Field, object value)
{
if (ObjNumTest(value))
{
Where += "and " + Field + ">" + value + " ";
}
else if (ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + ">'" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper gt(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value))
{
Where += "and " + Field + ">" + value + " ";
}
else if (ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + ">'" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper ge(string Field, object value)
{
if (ObjNumTest(value))
{
Where += "and " + Field + ">=" + value + " ";
}
else if (ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + ">='" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper ge(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value))
{
Where += "and " + Field + ">=" + value + " ";
}
else if (ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + ">='" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper lt(string Field, object value)
{
if (ObjNumTest(value))
{
Where += "and " + Field + "<" + value + " ";
}
else if (ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + "<'" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper lt(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value))
{
Where += "and " + Field + "<" + value + " ";
}
else if (ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + "<'" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper le(string Field, object value)
{
if (ObjNumTest(value))
{
Where += "and " + Field + "<=" + value + " ";
}
else if (ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + "<='" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper le(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value))
{
Where += "and " + Field + "<=" + value + " ";
}
else if (ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + "<='" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper like(string Field, object value)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " LIKE '%" + value + "%' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper like(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " LIKE '%" + value + "%' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper notLike(string Field, object value)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " NOT LIKE '%" + value + "%' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper notLike(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " NOT LIKE '%" + value + "%' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper likeLeft(string Field, object value)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " LIKE '%" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper likeLeft(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " LIKE '%" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper likeRight(string Field, object value)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " LIKE '" + value + "%' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper likeRight(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " LIKE '" + value + "%' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper notLikeLeft(string Field, object value)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " NOT LIKE '%" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper notLikeLeft(Boolean b,string Field, object value)
{
if (b)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " NOT LIKE '%" + value + "' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper notLikeRight(string Field, object value)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " NOT LIKE '" + value + "%' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
return this;
}
public QueryWrapper notLikeRight(Boolean b, string Field, object value)
{
if (b)
{
if (ObjNumTest(value) || ObjStrTest(value) || ObjDateTest(value))
{
Where += "and " + Field + " NOT LIKE '" + value + "%' ";
}
else
{
throw new Exception("SQL值不受支持:" + Field + "=");
}
}
return this;
}
public QueryWrapper isNull(string Field)
{
if (string.IsNullOrEmpty(Field))
{
return this;
}
Where += "and " + Field + " IS NULL ";
return this;
}
public QueryWrapper isNotNull(string Field)
{
if (string.IsNullOrEmpty(Field))
{
return this;
}
Where += "and " + Field + " IS NOT NULL ";
return this;
}
public QueryWrapper In(string Field,object obj)
{
if (string.IsNullOrEmpty(Field))
{
return this;
}
else if (obj.GetType().Equals(typeof(List<string>)))
{
List<string> list = (List<string>)obj;
if (list.Count != 0)
{
Where += "and " + Field + " IN (";
foreach (string item in list)
{
Where += "'" + item + "',";
}
Where = Where.TrimEnd(',');
Where += ") ";
}
return this;
}
else if (obj.GetType().Equals(typeof(List<int>)))
{
List<int> list = (List<int>)obj;
if (list.Count != 0)
{
Where += "and " + Field + " IN (";
foreach (int item in list)
{
Where += item + ",";
}
Where = Where.TrimEnd(',');
Where += ") ";
}
return this;
}
else
{
throw new Exception("SQL语句中的值不受支持:" + Field);
}
}
public QueryWrapper notIn(string Field, object obj)
{
if (string.IsNullOrEmpty(Field))
{
return this;
}
else if (obj.GetType().Equals(typeof(List<string>)))
{
List<string> list = (List<string>)obj;
if (list.Count != 0)
{
Where += "and " + Field + " NOT IN (";
foreach (string item in list)
{
Where += "'" + item + "',";
}
Where = Where.TrimEnd(',');
Where += ") ";
}
return this;
}
else if (obj.GetType().Equals(typeof(List<int>)))
{
List<int> list = (List<int>)obj;
if (list.Count != 0)
{
Where += "and " + Field + " NOT IN (";
foreach (int item in list)
{
Where += item + ",";
}
Where = Where.TrimEnd(',');
Where += ") ";
}
return this;
}
else
{
throw new Exception("SQL语句中的值不受支持:" + Field);
}
}
public QueryWrapper inSql(string Field, string sql)
{
if (string.IsNullOrEmpty(Field) || string.IsNullOrEmpty(sql))
{
return this;
}
Where += "and " + Field + " IN (" + sql + ") ";
return this;
}
public QueryWrapper notInSql(string Field, string sql)
{
if (string.IsNullOrEmpty(Field) || string.IsNullOrEmpty(sql))
{
return this;
}
Where += "and " + Field + " NOT IN (" + sql + ") ";
return this;
}
public QueryWrapper groupBy(string Field)
{
if (string.IsNullOrEmpty(Field))
{
return this;
}
GroupBy = "GROUP BY " + Field + " ";
return this;
}
public QueryWrapper having(string hav)
{
if (string.IsNullOrEmpty(hav))
{
return this;
}
Having = "HAVING " + hav + " ";
return this;
}
public QueryWrapper apply(string apply)
{
if (string.IsNullOrEmpty(apply))
{
return this;
}
Where += "and " + apply + " ";
return this;
}
public QueryWrapper or(string Field, params Object[] value)
{
if (value.Length.Equals(0))
{
return this;
}
string or = "(";
foreach (var item in value)
{
if (ObjNumTest(item))
{
or += "or " + Field + "=" + item + " ";
}
else if (ObjStrTest(item))
{
or += "or " + Field + "='" + item + "' ";
}
else
{
throw new Exception("SQL语句中的某个值类型不受支持");
}
}
or = or.Remove(1, 3);
or = or.TrimEnd();
or += ")";
Where += "and " + or + " ";
return this;
}
public QueryWrapper between(string Field, object value, object value1)
{
if ( ObjNumTest(value) && ObjNumTest(value1))
{
if (Convert.ToDouble(value) > Convert.ToDouble(value1))
{
throw new Exception("SQL语句中的between用法应为:第一个值<=第二个值");
}
Where += "and " + Field + " BETWEEN " + value + " and " + value1 + " ";
return this;
}
else if (ObjDateTest(value) && ObjDateTest(value1))
{
if (Convert.ToDateTime(value) > Convert.ToDateTime(value1))
{
throw new Exception("SQL语句中的between用法应为:第一个值<=第二个值");
}
Where += "and " + Field + " BETWEEN '" + value + "' and '" + value1 + "' ";
return this;
}
else
{
throw new Exception("SQL语句between之间的值不受支持");
}
}
public QueryWrapper between(Boolean b,string Field, object value, object value1)
{
if (b)
{
if (ObjNumTest(value) && ObjNumTest(value1))
{
if (Convert.ToDouble(value) > Convert.ToDouble(value1))
{
throw new Exception("SQL语句中的between用法应为:第一个值<=第二个值");
}
Where += "and " + Field + " BETWEEN " + value + " and " + value1 + " ";
return this;
}
else if (ObjDateTest(value) && ObjDateTest(value1))
{
if (Convert.ToDateTime(value) > Convert.ToDateTime(value1))
{
throw new Exception("SQL语句中的between用法应为:第一个值<=第二个值");
}
Where += "and " + Field + " BETWEEN '" + value + "' and '" + value1 + "' ";
return this;
}
else
{
throw new Exception("SQL语句between之间的值不受支持");
}
}
return this;
}
public QueryWrapper notBetween(string Field, object value, object value1)
{
if (ObjNumTest(value) && ObjNumTest(value1))
{
if (Convert.ToDouble(value) > Convert.ToDouble(value1))
{
throw new Exception("SQL语句中的notBetween用法应为:第一个值<=第二个值");
}
Where += "and " + Field + " NOT BETWEEN " + value + " and " + value1 + " ";
return this;
}
else if (ObjDateTest(value) && ObjDateTest(value1))
{
if (Convert.ToDateTime(value) > Convert.ToDateTime(value1))
{
throw new Exception("SQL语句中的notBetween用法应为:第一个值<=第二个值");
}
Where += "and " + Field + " NOT BETWEEN '" + value + "' and '" + value1 + "' ";
return this;
}
else
{
throw new Exception("SQL语句notBetween之间的值不受支持");
}
}
public QueryWrapper orderBy(string Field)
{
if (string.IsNullOrEmpty(Field))
{
return this;
}
OrderBy = "ORDER BY "+ Field + " ";
return this;
}
public QueryWrapper orderByDesc(string Field)
{
if (string.IsNullOrEmpty(Field))
{
return this;
}
OrderBy = "ORDER BY " + Field + " DESC ";
return this;
}
public QueryWrapper limit(int max)
{
if (max >= 0)
{
Limit = "LIMIT "+ max;
}
else
{
throw new Exception("SQL语句limit值必须大于等于0");
}
return this;
}
public QueryWrapper limit(int pageIndex,int pageSize)
{
if (pageIndex >= 0 && pageSize >= 0)
{
Limit = " LIMIT " + (pageIndex - 1) * pageSize + "," + pageSize;
}
else
{
throw new Exception("SQL语句limit值必须大于等于0");
}
return this;
}
#endregion
#region 参数值检测
private Boolean ObjNumTest(object value)
{
Type type = value.GetType();
if (type.Equals(typeof(int)) || type.Equals(typeof(decimal)) || type.Equals(typeof(double)) || type.Equals(typeof(float)))
{
return true;
}
return false;
}
private Boolean ObjStrTest(object value)
{
if (value.GetType().Equals(typeof(string)))
{
return true;
}
return false;
}
private Boolean ObjDateTest(object value)
{
if (value.GetType().Equals(typeof(DateTime)))
{
return true;
}
return false;
}
#endregion
}
}