/// <summary>
/// GroupBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public virtual SqlPack GroupBy(T expression, SqlPack sqlPack) => throw new NotImplementedException("未实现" + typeof(T).Name + "ISqlBuilder.GroupBy方法");
/// <summary>
/// OrderBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="orders">排序方式</param>
/// <returns>SqlBuilderCore</returns>
public SqlBuilderCore <T> OrderBy(Expression <Func <T, object> > expression, params OrderType[] orders)
{
this._sqlPack += " ORDER BY ";
SqlBuilderProvider.OrderBy(expression.Body, this._sqlPack, orders);
return(this);
}
/// <summary>
/// Not In
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
private static void NotIn(MethodCallExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.Where(expression.Arguments[0], sqlPack);
sqlPack += " NOT IN ";
SqlBuilderProvider.In(expression.Arguments[1], sqlPack);
}
/// <summary>
/// Insert
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Insert(UnaryExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.Insert(expression.Operand, sqlPack);
return(sqlPack);
}
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Where(UnaryExpression expression, SqlPack sqlPack)
{
var startIndex = sqlPack.Length;
SqlBuilderProvider.Where(expression.Operand, sqlPack);
if (expression.NodeType == ExpressionType.Not)
{
var subString = sqlPack.ToString().Substring(startIndex, sqlPack.ToString().Length - startIndex).ToUpper();
//IS NOT、IS
if (subString.Contains("IS NOT"))
{
var index = sqlPack.ToString().LastIndexOf("IS NOT");
if (index != -1)
{
sqlPack.Sql.Replace("IS NOT", "IS", index, 6);
}
}
if (subString.Contains("IS") && subString.LastIndexOf("IS") != subString.LastIndexOf("IS NOT"))
{
var index = sqlPack.ToString().LastIndexOf("IS");
if (index != -1)
{
sqlPack.Sql.Replace("IS", "IS NOT", index, 2);
}
}
//NOT LIKE、LIKE
if (subString.Contains("NOT LIKE"))
{
var index = sqlPack.ToString().LastIndexOf("NOT LIKE");
if (index != -1)
{
sqlPack.Sql.Replace("NOT LIKE", "LIKE", index, 8);
}
}
if (subString.Contains("LIKE") && subString.LastIndexOf("LIKE") != (subString.LastIndexOf("NOT LIKE") + 4))
{
var index = sqlPack.ToString().LastIndexOf("LIKE");
if (index != -1)
{
sqlPack.Sql.Replace("LIKE", "NOT LIKE", index, 4);
}
}
//NOT IN、IN
if (subString.Contains("NOT IN"))
{
var index = sqlPack.ToString().LastIndexOf("NOT IN");
if (index != -1)
{
sqlPack.Sql.Replace("NOT IN", "IN", index, 6);
}
}
if (subString.Contains("IN") && subString.LastIndexOf("IN") != (subString.LastIndexOf("NOT IN") + 4))
{
var index = sqlPack.ToString().LastIndexOf("IN");
if (index != -1)
{
sqlPack.Sql.Replace("IN", "NOT IN", index, 2);
}
}
//AND、OR
if (subString.Contains("AND"))
{
var index = sqlPack.ToString().LastIndexOf("AND");
if (index != -1)
{
sqlPack.Sql.Replace("AND", "OR", index, 3);
}
}
if (subString.Contains("OR"))
{
var index = sqlPack.ToString().LastIndexOf("OR");
if (index != -1)
{
sqlPack.Sql.Replace("OR", "AND", index, 2);
}
}
//=、<>
if (subString.Contains(" = "))
{
var index = sqlPack.ToString().LastIndexOf(" = ");
if (index != -1)
{
sqlPack.Sql.Replace(" = ", " <> ", index, 3);
}
}
if (subString.Contains("<>"))
{
var index = sqlPack.ToString().LastIndexOf("<>");
if (index != -1)
{
sqlPack.Sql.Replace("<>", "=", index, 2);
}
}
//>、<
if (subString.Contains(" > "))
{
var index = sqlPack.ToString().LastIndexOf(" > ");
if (index != -1)
{
sqlPack.Sql.Replace(" > ", " <= ", index, 3);
}
}
if (subString.Contains(" < "))
{
var index = sqlPack.ToString().LastIndexOf(" < ");
if (index != -1)
{
sqlPack.Sql.Replace(" < ", " >= ", index, 3);
}
}
//>=、<=
if (subString.Contains(" >= "))
{
var index = sqlPack.ToString().LastIndexOf(" >= ");
if (index != -1)
{
sqlPack.Sql.Replace(" >= ", " < ", index, 4);
}
}
if (subString.Contains(" <= "))
{
var index = sqlPack.ToString().LastIndexOf(" <= ");
if (index != -1)
{
sqlPack.Sql.Replace(" <= ", " > ", index, 4);
}
}
}
return(sqlPack);
}
/// <summary>
/// OrderBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <param name="orders">排序方式</param>
/// <returns>SqlPack</returns>
public override SqlPack OrderBy(MemberExpression expression, SqlPack sqlPack, params OrderType[] orders)
{
string tableName = string.Empty;
if (expression.Expression.NodeType == ExpressionType.Parameter)
{
var type = expression.Expression.Type != expression.Member.DeclaringType ?
expression.Expression.Type :
expression.Member.DeclaringType;
tableName = sqlPack.GetTableName(type);
}
if (expression.Expression.NodeType == ExpressionType.Constant)
{
tableName = sqlPack.GetTableName(sqlPack.DefaultType);
}
sqlPack.SetTableAlias(tableName);
var tableAlias = sqlPack.GetTableAlias(tableName);
if (!string.IsNullOrEmpty(tableAlias))
{
tableAlias += ".";
}
if (expression.Expression.NodeType == ExpressionType.Parameter)
{
sqlPack += tableAlias + sqlPack.GetColumnInfo(expression.Member.DeclaringType, expression.Member).columnName;
if (orders?.Length > 0)
{
sqlPack += $" { (orders[0] == OrderType.Descending ? "DESC" : "ASC")}";
}
}
if (expression.Expression.NodeType == ExpressionType.Constant)
{
var obj = expression.ToObject();
if (obj != null)
{
var type = obj.GetType().Name;
if (type == "String[]" && obj is string[] array)
{
for (var i = 0; i < array.Length; i++)
{
SqlBuilderProvider.OrderBy(Expression.Constant(array[i], array[i].GetType()), sqlPack);
if (i <= orders.Length - 1)
{
sqlPack += $" { (orders[i] == OrderType.Descending ? "DESC" : "ASC")},";
}
else if (!array[i].ToUpper().Contains("ASC") && !array[i].ToUpper().Contains("DESC"))
{
sqlPack += " ASC,";
}
else
{
sqlPack += ",";
}
}
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
if (type == "List`1" && obj is List <string> list)
{
for (var i = 0; i < list.Count; i++)
{
SqlBuilderProvider.OrderBy(Expression.Constant(list[i], list[i].GetType()), sqlPack);
if (i <= orders.Length - 1)
{
sqlPack += $" { (orders[i] == OrderType.Descending ? "DESC" : "ASC")},";
}
else if (!list[i].ToUpper().Contains("ASC") && !list[i].ToUpper().Contains("DESC"))
{
sqlPack += " ASC,";
}
else
{
sqlPack += ",";
}
}
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
if (type == "String" && obj is string str)
{
SqlBuilderProvider.OrderBy(Expression.Constant(str, str.GetType()), sqlPack);
str = str.ToUpper();
if (!str.Contains("ASC") && !str.Contains("DESC"))
{
if (orders.Length >= 1)
{
sqlPack += $" { (orders[0] == OrderType.Descending ? "DESC" : "ASC")},";
}
else
{
sqlPack += " ASC,";
}
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
}
}
}
return(sqlPack);
}
/// <summary>
/// GroupBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack GroupBy(UnaryExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.GroupBy(expression.Operand, sqlPack);
return(sqlPack);
}
/// <summary> /// In /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack In(Expression expression, SqlPack sqlPack) => In((T)expression, sqlPack);
/// <summary> /// GroupBy /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack GroupBy(Expression expression, SqlPack sqlPack) => GroupBy((T)expression, sqlPack);
/// <summary> /// Join /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Join(Expression expression, SqlPack sqlPack) => Join((T)expression, sqlPack);
/// <summary> /// Where /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Where(Expression expression, SqlPack sqlPack) => Where((T)expression, sqlPack);
/// <summary> /// Select /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Select(Expression expression, SqlPack sqlPack) => Select((T)expression, sqlPack);
/// <summary> /// Update /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Update(Expression expression, SqlPack sqlPack) => Update((T)expression, sqlPack);
/// <summary>
/// In
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack In(ConstantExpression expression, SqlPack sqlPack)
{
sqlPack.AddDbParameter(expression.Value);
return(sqlPack);
}
/// <summary>
/// OrderBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <param name="orders">排序方式</param>
/// <returns>SqlPack</returns>
public virtual SqlPack OrderBy(T expression, SqlPack sqlPack, params OrderType[] orders) => throw new NotImplementedException("未实现" + typeof(T).Name + "ISqlBuilder.OrderBy方法");
/// <summary> /// OrderBy /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <param name="orders">排序方式</param> /// <returns>SqlPack</returns> public SqlPack OrderBy(Expression expression, SqlPack sqlPack, params OrderType[] orders) => OrderBy((T)expression, sqlPack, orders);
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Where(InvocationExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.Where(expression.Expression, sqlPack);
return(sqlPack);
}
/// <summary> /// Max /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Max(Expression expression, SqlPack sqlPack) => Max((T)expression, sqlPack);
/// <summary>
/// Insert
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Insert(MemberExpression expression, SqlPack sqlPack)
{
var objectArray = new List <object>();
var fields = new List <string>();
var obj = expression.ToObject();
if (obj.GetType().IsArray)
{
objectArray.AddRange(obj as object[]);
}
else if (obj.GetType().Name == "List`1")
{
objectArray.AddRange(obj as IEnumerable <object>);
}
else
{
objectArray.Add(obj);
}
for (var i = 0; i < objectArray.Count; i++)
{
if (sqlPack.DatabaseType != DatabaseType.Oracle)
{
sqlPack.Sql.Append("(");
}
if (i > 0 && sqlPack.DatabaseType == DatabaseType.Oracle)
{
sqlPack.Sql.Append(" UNION ALL SELECT ");
}
var properties = objectArray[i]?.GetType().GetProperties();
foreach (var p in properties)
{
var type = p.DeclaringType.ToString().Contains("AnonymousType") ? sqlPack.DefaultType : p.DeclaringType;
(string columnName, bool isInsert, bool isUpdate) = sqlPack.GetColumnInfo(type, p);
if (isInsert)
{
var value = p.GetValue(objectArray[i], null);
if (value != null || (sqlPack.IsEnableNullValue && value == null))
{
sqlPack.AddDbParameter(value);
if (!fields.Contains(columnName))
{
fields.Add(columnName);
}
sqlPack += ",";
}
}
}
if (sqlPack[sqlPack.Length - 1] == ',')
{
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
if (sqlPack.DatabaseType != DatabaseType.Oracle)
{
sqlPack.Sql.Append("),");
}
else
{
sqlPack.Sql.Append(" FROM DUAL");
}
}
}
if (sqlPack[sqlPack.Length - 1] == ',')
{
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
sqlPack.Sql = new StringBuilder(string.Format(sqlPack.ToString(), string.Join(",", fields).TrimEnd(',')));
return(sqlPack);
}
/// <summary> /// Min /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Min(Expression expression, SqlPack sqlPack) => Min((T)expression, sqlPack);
/// <summary>
/// OrderBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <param name="orders">排序方式</param>
/// <returns>SqlPack</returns>
public override SqlPack OrderBy(UnaryExpression expression, SqlPack sqlPack, params OrderType[] orders)
{
SqlBuilderProvider.OrderBy(expression.Operand, sqlPack, orders);
return(sqlPack);
}
/// <summary> /// Avg /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Avg(Expression expression, SqlPack sqlPack) => Avg((T)expression, sqlPack);
/// <summary>
/// Update
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Update(UnaryExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.Update(expression.Operand, sqlPack);
return(sqlPack);
}
/// <summary> /// Count /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Count(Expression expression, SqlPack sqlPack) => Count((T)expression, sqlPack);
/// <summary>
/// GroupBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <returns>SqlBuilderCore</returns>
public SqlBuilderCore <T> GroupBy(Expression <Func <T, object> > expression)
{
this._sqlPack += " GROUP BY ";
SqlBuilderProvider.GroupBy(expression.Body, this._sqlPack);
return(this);
}
/// <summary> /// Sum /// </summary> /// <param name="expression">表达式树</param> /// <param name="sqlPack">sql打包对象</param> /// <returns>SqlPack</returns> public SqlPack Sum(Expression expression, SqlPack sqlPack) => Sum((T)expression, sqlPack);
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Where(LambdaExpression expression, SqlPack sqlPack)
{
SqlBuilderProvider.Where(expression.Body, sqlPack);
return(sqlPack);
}
/// <summary>
/// In
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public virtual SqlPack In(T expression, SqlPack sqlPack) => throw new NotImplementedException("未实现" + typeof(T).Name + "ToSql.In方法");