不使用表达式树,使用强类型生成where子句,好处是性能高,相比表达式树生成的sql,10倍+
目前只支持生成where条件、查询语句,后期会增加生成Update SQL
支持生成Sqlserver、Mysql语句
where子句支持相等、比较大小、Between、Like、In操作
sql语句支持设置select字段、top、order by;不支持联表查询、group by
客户端调用:
public static void Main(string[] args)
{
var sqlBuilder = new SqlBuilder(typeof(Person));
//生成查询SQL
#region 单个条件查询#SELECT * FROM `Person` WHERE `ID`=1 LIMIT 10
var filter = new EqualFieldFilter<int>("ID", 1);
var sql = sqlBuilder
.SetTop(10)
.SetWhere(filter)
.BuildSelectSql();
#endregion
#region 复杂条件查询#SELECT `ID`,`Name` FROM `Person` WHERE ((`ID`=1 AND (`Age`=13 OR `Name`=1 OR `name` LIKE 'fan%'))) LIMIT 10
var filter2 = new AndFieldFilter(new EqualFieldFilter<int>("ID", 1), new OrFieldFilter(new EqualFieldFilter<int>("Age", 13), new EqualFieldFilter<int>("Name", 1), new LikeFieldFilter("name", "fan", LikeMode.Start)));
var sql2 = sqlBuilder
.SetTableName(nameof(Person))
.SetAndWhere(filter2)
.BuildSelectSql();
#endregion
#region 指定SELECT字段查询#SELECT `ID`,`Name` FROM `Person` WHERE LIMIT 10
var sql3 = sqlBuilder
.SetTableName(nameof(Person))
.SetSelect(nameof(Person.ID), nameof(Person.Name))
.BuildSelectSql();
sql3 = sqlBuilder
.SetTableName(nameof(Person))
.SetWhere(null)
.SetSelect(nameof(Person.ID), nameof(Person.Name))
.BuildSelectSql();
#endregion
#region 带排序查询#SELECT `ID`,`Name` FROM `Person` WHERE (`ID`=1) ORDER BY ID ASC,Name DESC LIMIT 10
var sql4 = sqlBuilder
.SetTableName(nameof(Person))
.SetAndWhere(filter)
.SetOrderBy(new Dictionary<string, int> { { "ID", 1 }, { "Name", -1 } })
.BuildSelectSql();
#endregion
//生成WhereSQL
#region 生成AndWhere#(`ID`=1 AND `Age`>13 AND `Name` LIKE 'fan%')
var filter4 = new FieldFilter[] { new EqualFieldFilter<int>(nameof(Person.ID), 1), new CompareFieldFilter<int>(nameof(Person.Age), 13, ">"), new LikeFieldFilter(nameof(Person.Name), "fan", LikeMode.Start) };
var whereSql1 = SqlBuilder.BuildAndWhere(filter4);
//使用Dictionary类型参数
whereSql1 = SqlBuilder.BuildAndWhere(new Dictionary<string, object> { { "ID", 1 }, { "Birthday", DateTime.Now }, { "Name", "fan" } });
#endregion
#region 生成OrWhere#(`ID`=1 OR `Age`>13 OR `Name` LIKE 'fan%')
var whereSql2 = SqlBuilder.BuildOrWhere(filter4);
#endregion
#region 生成复杂Where(`ID` = 1 AND (`Age` = 13 OR `name` LIKE 'fan%'))
var filter5 = new AndFieldFilter(new EqualFieldFilter<int>("ID", 1), new OrFieldFilter(new EqualFieldFilter<int>("Age", 13), new LikeFieldFilter("name", "fan", LikeMode.Start)));
var whereSql3 = SqlBuilder.BuildWhere(filter5);
#endregion
Console.ReadKey();
}
实体类:
public class Person { public int ID { get; set; } public string StudentNumber { get; set; } public int Age { get; set; } public string Name { get; set; } public string ClassName { get; set; } }
SqlBuilder:
public class SqlBuilder
{
public static readonly ESqlDataType SQL_DATA_TYPE = ESqlDataType.MySql;//如果要切换数据库,只需要改此处
public static readonly string START_OPE = "`";
public static readonly string END_OPE = "`";
private static readonly ConcurrentDictionary<Type, PropertyInfo[]> TYPE_CACHE = new ConcurrentDictionary<Type, PropertyInfo[]>();
private static readonly ConcurrentDictionary<Type, string> EXPRESSION_CACHE = new ConcurrentDictionary<Type, string>();
private const string SQLSERVER_FORMAT = "SELECT {top} {select} FROM {table} {where} {orderby}";
private const string MYSQL_FORMAT = "SELECT {select} FROM {table} {where} {orderby} {top}";
private string _selectSql = " * ";
private string _tableName = string.Empty;
private string _topSql = string.Empty;
private string _orderBySql = string.Empty;
private WherePart _wherePart = null;
#region Constructor
static SqlBuilder()
{
if (SQL_DATA_TYPE == ESqlDataType.SqlServer)
{
START_OPE = "[";
END_OPE = "]";
}
}
public SqlBuilder()
{ }
public SqlBuilder(string tableName)
{
this.SetTableName(tableName);
}
public SqlBuilder(Type tableType)
{
this.SetTableName(tableType.Name);
}
#endregion
#region BuildSql
public WherePart BuildSelectSql()
{
string sql = (SQL_DATA_TYPE == ESqlDataType.MySql ? MYSQL_FORMAT : SQLSERVER_FORMAT);
if (string.IsNullOrWhiteSpace(this._tableName))
{
throw new ArgumentException("Invoke SetTableName Method");
}
sql = sql.Replace("{table}", this._tableName);
sql = sql.Replace("{select}", this._selectSql);
sql = sql.Replace("{where}", this._wherePart != null ? this._wherePart.Sql : string.Empty);
sql = sql.Replace("{top}", this._topSql);
sql = sql.Replace("{orderby}", this._orderBySql);
return new WherePart(sql, this._wherePart?.Parameters);
}
#endregion
#region BuildWhereSql
/// <summary>
/// 创建where(AND)
/// </summary>
/// <param name="FieldFilters"></param>
/// <returns></returns>
public static WherePart BuildAndWhere(params FieldFilter[] FieldFilters)
{
string sql = string.Empty;
Dictionary<string, object> parameters = new Dictionary<string, object>();
if (FieldFilters?.Length > 0)
{
sql = new AndFieldFilter(FieldFilters).BuildCriteria(out string where, parameters) ? where : string.Empty;
}
return new WherePart(sql, parameters);
}
/// <summary>
/// 创建where(AND)
/// </summary>
/// <param name="pairs"></param>
/// <returns></returns>
public static WherePart BuildAndWhere(ICollection<KeyValuePair<string, object>> pairs)
{
WherePart wherePart = null;
Dictionary<string, object> parameters = new Dictionary<string, object>();
if (pairs?.Count > 0)
{
wherePart = SqlBuilder.BuildAndWhere(BuildFildFilters(pairs));
}
return wherePart;
}
/// <summary>
/// 创建where(OR)
/// </summary>
/// <param name="FieldFilters"></param>
/// <returns></returns>
public static WherePart BuildOrWhere(params FieldFilter[] FieldFilters)
{
WherePart wherePart = null;
Dictionary<string, object> parameters = new Dictionary<string, object>();
if (FieldFilters?.Length > 0)
{
if (new OrFieldFilter(FieldFilters).BuildCriteria(out string where, parameters))
{
wherePart = new WherePart(where, parameters);
}
}
return wherePart;
}
/// <summary>
/// 创建where(OR)
/// </summary>
/// <param name="pairs"></param>
/// <returns></returns>
public static WherePart BuildOrWhere(ICollection<KeyValuePair<string, object>> pairs)
{
WherePart wherePart = null;
if (pairs?.Count > 0)
{
wherePart = SqlBuilder.BuildOrWhere(BuildFildFilters(pairs));
}
return wherePart;
}
/// <summary>
/// 创建复杂where sql
/// </summary>
/// <param name="complexFieldFilter"></param>
/// <returns></returns>
public static WherePart BuildWhere(FieldFilter complexFieldFilter)
{
WherePart wherePart = null;
if (complexFieldFilter != null)
{
ICollection<KeyValuePair<string, object>> parameters = new Dictionary<string, object>();
if (complexFieldFilter.BuildCriteria(out string where, parameters))
{
wherePart = new WherePart(where, parameters);
}
}
return wherePart;
}
#endregion
#region TableName
public SqlBuilder SetTableName<T>()
{
this.SetTableName(typeof(T).Name);
return this;
}
public SqlBuilder SetTableName(Type tableType)
{
this.SetTableName(tableType.GetType().Name);
return this;
}
public SqlBuilder SetTableName(string tableName)
{
this._tableName = $"{START_OPE}{tableName}{END_OPE}";
return this;
}
#endregion
#region Select
/// <summary>
/// 设置Select子句
/// </summary>
/// <param name="selectFields"></param>
/// <returns></returns>
public SqlBuilder SetSelect(params string[] selectFields)
{
if (selectFields != null && selectFields.Length > 0)
{
var list = new List<string>();
foreach (var field in selectFields)
{
list.Add($"{START_OPE}{field}{END_OPE}");
}
this._selectSql = string.Join(",", list);
}
return this;
}
/// <summary>
/// 设置Select子句(不推荐,性能低)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="propertyExpression"></param>
/// <returns></returns>
public SqlBuilder SetSelect<T>(params Expression<Func<T, object>>[] propertyExpression)
{
string[] includePropertyNames = null;
if (propertyExpression?.Length > 0)
{
includePropertyNames = this.GetPropertyNames<T>(propertyExpression);
}
if (includePropertyNames?.Length > 0)
{
this.SetSelect(includePropertyNames);
}
return this;
}
#endregion
#region Where
/// <summary>
/// 设置条件
/// </summary>
/// <param name="whereField"></param>
/// <returns></returns>
public SqlBuilder SetWhere(FieldFilter whereField)
{
if (whereField != null)
{
this.SetWherePart(SqlBuilder.BuildWhere(whereField));
}
return this;
}
/// <summary>
/// 设置条件(AND)
/// </summary>
/// <param name="fieldFilters"></param>
/// <returns></returns>
public SqlBuilder SetAndWhere(params FieldFilter[] fieldFilters)
{
if (fieldFilters?.Length > 0)
{
this.SetWherePart(SqlBuilder.BuildAndWhere(fieldFilters));
}
return this;
}
/// <summary>
/// 设置条件(AND)
/// </summary>
/// <param name="pairs"></param>
/// <returns></returns>
public SqlBuilder SetAndWhere(ICollection<KeyValuePair<string, object>> pairs)
{
if (pairs?.Count > 0)
{
this.SetWherePart(SqlBuilder.BuildAndWhere(pairs));
}
return this;
}
/// <summary>
/// 设置条件(OR)
/// </summary>
/// <param name="FieldFilters"></param>
/// <returns></returns>
public SqlBuilder SetOrWhere(params FieldFilter[] fieldFilters)
{
if (fieldFilters?.Length > 0)
{
this.SetWherePart(SqlBuilder.BuildOrWhere(fieldFilters));
}
return this;
}
/// <summary>
/// 设置条件(OR)
/// </summary>
/// <param name="pairs"></param>
/// <returns></returns>
public SqlBuilder SetOrWhere(ICollection<KeyValuePair<string, object>> pairs)
{
if (pairs?.Count > 0)
{
this.SetWherePart(SqlBuilder.BuildOrWhere(pairs));
}
return this;
}
public SqlBuilder SetWherePart(WherePart wherePart)
{
if (wherePart != null)
{
wherePart.Sql = !string.IsNullOrWhiteSpace(wherePart.Sql) ? " WHERE " + wherePart.Sql : string.Empty;
this._wherePart = wherePart;
}
return this;
}
#endregion;
#region Top
public SqlBuilder SetTop(int top)
{
if (top > 0)
{
string topSql = top.ToString();
this._topSql = SqlBuilder.SQL_DATA_TYPE == ESqlDataType.MySql ? $"LIMIT {topSql}" : $"TOP {topSql}";
}
return this;
}
#endregion
#region OrderBy
/// <summary>
///
/// </summary>
/// <param name="orderBys"></param>
/// <returns></returns>
public SqlBuilder SetOrderBy(Dictionary<string, int> orderByDict)
{
StringBuilder orderSql = new StringBuilder();
if (orderByDict?.Count > 0)
{
orderSql.Append(" ORDER BY ");
string temp = "ASC";
foreach (var orderField in orderByDict.Keys)
{
temp = orderByDict[orderField] >= 0 ? "ASC" : "DESC";
orderSql.Append($"{orderField} {temp},");
}
this._orderBySql = orderSql.ToString().Trim(',');
}
return this;
}
#endregion
#region Private
private string[] GetPropertyNames<T>(params Expression<Func<T, object>>[] exps)
{
string[] props = new string[exps.Length];
for (int i = 0; i < exps.Length; i++)
{
props[i] = this.GetPropertyName(exps[i]);
}
return props;
}
private string GetPropertyName<T>(Expression<Func<T, object>> expr)
{
return EXPRESSION_CACHE.GetOrAdd(expr.GetType(), t =>
{
string rtn = null;
if (expr.Body is UnaryExpression)
{
rtn = ((MemberExpression)((UnaryExpression)expr.Body).Operand).Member.Name;
}
else if (expr.Body is MemberExpression)
{
rtn = ((MemberExpression)expr.Body).Member.Name;
}
else if (expr.Body is ParameterExpression)
{
rtn = ((ParameterExpression)expr.Body).Type.Name;
}
return rtn;
});
}
private static FieldFilter[] BuildFildFilters(ICollection<KeyValuePair<string, object>> pairs)
{
FieldFilter[] fieldFilters = new FieldFilter[pairs.Count];
int index = 0;
string key = string.Empty;
object value = null;
foreach (var pair in pairs)
{
key = pair.Key;
value = pair.Value;
if (value is System.Int32)
{
int intValue = (Int32)Convert.ChangeType(value, typeof(System.Int32));
fieldFilters[index++] = new EqualFieldFilter<int>(key, intValue);
}
else if (value is System.Int64)
{
long longValue = (Int64)Convert.ChangeType(value, typeof(System.Int64));
fieldFilters[index++] = new EqualFieldFilter<System.Int64>(key, longValue);
}
else// if (value is System.String|| value is System.DateTime)
{
string strValue = (string)Convert.ChangeType(value, typeof(System.String));
fieldFilters[index++] = new EqualFieldFilter<string>(key, strValue);
}
}
return fieldFilters;
}
#endregion
}
public class WherePart
{
/// <summary>
/// 含有参数变量的SQL语句
/// </summary>
public string Sql { get; set; }
/// <summary>
/// SQL语句中的参数变量
/// </summary>
public ICollection<KeyValuePair<string, object>> Parameters { get; set; }
public WherePart(string sql, ICollection<KeyValuePair<string, object>> parameters)
{
this.Parameters = parameters;
this.Sql = sql;
}
}
public enum ESqlDataType
{
MySql = 1,
SqlServer = 2
}
筛选字段类(FieldFilter).
/// <summary>
/// 筛选抽象基类
/// </summary>
[Serializable]
public abstract class FieldFilter
{
private string _fieldName = null;
/// <summary>
/// 字段名称
/// </summary>
public string FieldName
{
get
{
return this._fieldName;// $"{SqlBuilder.START_OPE}{this._fieldName}{SqlBuilder.END_OPE}";
}
set
{
this._fieldName = value;
}
}
/// <summary>
/// 字段排序先后顺序
/// </summary>
internal int FieldOrderByPriority { get; set; }
internal abstract bool isEmpty { get; }
public bool IsEmpty()
{
return this.isEmpty;
}
public abstract bool BuildCriteria(out string where,ICollection<KeyValuePair<string,object>> parameters);
}
#region - 筛选器字段类 -
public class EqualFieldFilter<T> : FieldFilter //where T : struct
{
public EqualFieldFilter()
{
}
public EqualFieldFilter(string fieldName, T value)
{
base.FieldName = fieldName;
this.Value = value;
}
//public EqualFieldFilter(string fieldName, T? value)
//{
// base.FieldName = fieldName;
// this.Value = value;
//}
public T Value { get; set; }
internal override bool isEmpty
{
get { return false; }
}
public override bool BuildCriteria(out string where, ICollection<KeyValuePair<string, object>> parameters)
{
where = string.Empty;
if (this.isEmpty)
{
return false;
}
where = new SimpleExpression(base.FieldName, this.Value, " = ").ToString(parameters);
//parameters = new Dictionary<string, object> { { $"@{base.FieldName}",this.Value} };
return true;
}
}
/// <summary>
/// 比较筛选条件
/// </summary>
/// <typeparam name="T"></typeparam>
[Serializable]
public class CompareFieldFilter<T> : FieldFilter where T : struct
{
public CompareFieldFilter()
{
//this.Operator = CompareOperator.Equal;
}
public CompareFieldFilter(string fieldName, T value) : this(fieldName, value, "=")
{
}
public CompareFieldFilter(string fieldName, T value, string @operator)
{
base.FieldName = fieldName;
this.Value = value;
this.Operator = @operator;
}
public CompareFieldFilter(string fieldName, T? value) : this(fieldName, value, "=")
{
}
public CompareFieldFilter(string fieldName, T? value, string @operator)
{
base.FieldName = fieldName;
this.Value = value;
this.Operator = @operator;
}
public T? Value { get; set; }
/// <summary>
/// 获取或者设置 比较类型<see cref="CompareOperator"/>
/// 默认值:Equal
/// </summary>
public string Operator { get; set; }
internal override bool isEmpty
{
get { return !this.Value.HasValue; }
}
public override bool BuildCriteria(out string where, ICollection<KeyValuePair<string, object>> parameters)
{
where = string.Empty;
if (this.isEmpty)
{
return false;
}
where = new SimpleExpression(base.FieldName, this.Value, this.Operator).ToString(parameters);
return true;
}
public static explicit operator T(CompareFieldFilter<T> value)
{
return value.Value.Value;
}
public static explicit operator T? (CompareFieldFilter<T> value)
{
return value.Value;
}
}
/// <summary>
/// 范围筛选条件
/// </summary>
/// <typeparam name="T"></typeparam>
[Serializable]
public class RangeFieldFilter<T> : FieldFilter where T : struct
{
public RangeFieldFilter()
{
}
public RangeFieldFilter(string fieldName, T startValue, T endValue)
{
base.FieldName = fieldName;
this.StartValue = startValue;
this.EndValue = endValue;
}
public RangeFieldFilter(string fieldName, T? startValue, T? endValue)
{
base.FieldName = fieldName;
this.StartValue = startValue;
this.EndValue = endValue;
}
public T? StartValue { get; set; }
public T? EndValue { get; set; }
internal override bool isEmpty
{
get { return this.StartValue == null && this.EndValue == null; }
}
public override bool BuildCriteria(out string where, ICollection<KeyValuePair<string, object>> parameters)
{
where = string.Empty;
if (this.isEmpty)
{
return false;
}
if (this.StartValue != null && this.EndValue != null)
{
where = new BetweenExpression(base.FieldName, this.StartValue, this.EndValue).ToString(parameters);
}
return true;
}
public static explicit operator T[] (RangeFieldFilter<T> value)
{
return new T[2] { value.StartValue.Value, value.EndValue.Value };
}
public static explicit operator T?[] (RangeFieldFilter<T> value)
{
return new T?[2] { value.StartValue, value.EndValue };
}
}
/// <summary>
/// 多值模式
/// </summary>
[Serializable]
public enum AnyMode : int
{
/// <summary>
/// 包含这些值
/// </summary>
Contain = 0,
/// <summary>
/// 排除这些值
/// </summary>
Exclude = 1
}
/// <summary>
/// 多值筛选条件
/// </summary>
/// <typeparam name="T"></typeparam>
[Serializable]
public class AnyFieldFilter<T> : FieldFilter
{
public AnyFieldFilter()
{
}
/// <summary>
/// 构造
/// </summary>
/// <param name="value">集合值</param>
public AnyFieldFilter(string fieldName, IEnumerable<T> value)
: this(fieldName, value, AnyMode.Contain)
{
}
/// <summary>
/// 构造
/// </summary>
/// <param name="value">集合值</param>
/// <param name="Mode">多值模式</param>
public AnyFieldFilter(string fieldName, IEnumerable<T> value, AnyMode Mode)
{
base.FieldName = fieldName;
this.Value = value;
this.Mode = Mode;
}
public IEnumerable<T> Value { get; set; }
public AnyMode Mode { get; set; }
internal override bool isEmpty
{
get { return this.Value == null || !this.Value.Any(); }
}
public override bool BuildCriteria(out string where, ICollection<KeyValuePair<string, object>> parameters)
{
where = string.Empty;
if (this.isEmpty)
{
return false;
}
if (this.Value.Count() == 1)
{
where = new SimpleExpression(base.FieldName, this.Value.First(), " = ").ToString(parameters);
}
else
{
switch (this.Mode)
{
case AnyMode.Contain:
where = string.Format("{0} IN ({1})", base.FieldName, string.Join(",", this.Value));
break;
case AnyMode.Exclude:
where = string.Format("{0} NOT IN ({1})", base.FieldName, string.Join(",", this.Value));
break;
}
}
return true;
}
}
/// <summary>
/// 模糊筛选模式
/// </summary>
[Serializable]
public enum LikeMode : int
{
/// <summary>
/// 任何位置,相当于 like '%value%'
/// </summary>
AnyWhere = 0,
/// <summary>
/// 开始处,相当于 like 'value%'
/// </summary>
Start = 1,
/// <summary>
/// 结尾处,相当于 like '%value'
/// </summary>
End = 2
}
/// <summary>
/// 模糊筛选条件
/// </summary>
[Serializable]
public class LikeFieldFilter : FieldFilter
{
public LikeFieldFilter(string fieldName, string value)
: this(fieldName, value, LikeMode.AnyWhere)
{
}
public LikeFieldFilter(string fieldName, string value, LikeMode likeMode)
{
base.FieldName = fieldName;
this.Value = value;
this.Mode = likeMode;
}
public string Value { get; set; }
/// <summary>
/// 匹配模式,默认 AnyWhere
/// </summary>
public LikeMode Mode { get; set; }
internal override bool isEmpty
{
get { return string.IsNullOrWhiteSpace(this.Value); }
}
public override bool BuildCriteria(out string where, ICollection<KeyValuePair<string, object>> parameters)
{
where = string.Empty;
if (this.isEmpty)
{
return false;
}
switch (this.Mode)
{
case LikeMode.AnyWhere:
where = new SimpleExpression(base.FieldName, $"%{this.Value}%", " LIKE ").ToString(parameters);
break;
case LikeMode.End:
where = new SimpleExpression(base.FieldName, $"%{this.Value}", " LIKE ").ToString(parameters);
break;
case LikeMode.Start:
where = new SimpleExpression(base.FieldName, $"{this.Value}%", " LIKE ").ToString(parameters);
break;
}
return true;
}
}
/// <summary>
/// 表达式筛选方式
/// </summary>
/// <typeparam name="T"></typeparam>
public class WhereFieldFilter<T> : FieldFilter where T : class
{
internal string Value { get; set; }
public WhereFieldFilter(string value)
{
this.Value = value;
}
internal override bool isEmpty
{
get { return string.IsNullOrWhiteSpace(this.Value); }
}
public override bool BuildCriteria(out string where, ICollection<KeyValuePair<string, object>> parameters)
{
where = string.Empty;
if (this.isEmpty)
{
return false;
}
where = this.Value;
return true;
}
}
#endregion
#region- 组合筛选器字段类 -
public class CombineFieldFilterBase : FieldFilter
{
protected virtual string Operator { get; }
protected FieldFilter[] _fieldFilterList = null;
public CombineFieldFilterBase(params FieldFilter[] fieldFilterList)
{
this._fieldFilterList = fieldFilterList;
}
internal override bool isEmpty => false;
public override bool BuildCriteria(out string where, ICollection<KeyValuePair<string, object>> parameters)//
{
where = string.Empty;
List<string> whereList = new List<string>();
foreach (var fieldFilter in this._fieldFilterList)
{
if (fieldFilter.BuildCriteria(out string fieldWhere, parameters))
{
whereList.Add(fieldWhere);
}
}
if (whereList.Count <= 0)
{
return false;
}
where = "(" + string.Join(Operator, whereList) + ")";
return true;
}
}
public class AndFieldFilter : CombineFieldFilterBase
{
protected override string Operator { get { return " AND "; } }
public AndFieldFilter(params FieldFilter[] fieldFilterList) : base(fieldFilterList)
{
}
}
public class OrFieldFilter : CombineFieldFilterBase
{
protected override string Operator { get { return " OR "; } }
public OrFieldFilter(params FieldFilter[] fieldFilterList) : base(fieldFilterList)
{
}
}
#endregion