/// <summary>
/// Update
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Update(MemberExpression expression, SqlPack sqlPack)
{
var obj = expression.ToObject();
var properties = obj?.GetType().GetProperties();
foreach (var item in properties)
{
var type = item.DeclaringType.ToString().Contains("AnonymousType") ? sqlPack.DefaultType : item.DeclaringType;
(string columnName, bool isInsert, bool isUpdate) = sqlPack.GetColumnInfo(type, item);
if (isUpdate)
{
var value = item.GetValue(obj, null);
if (value != null || (sqlPack.IsEnableNullValue && value == null))
{
sqlPack += columnName + " = ";
sqlPack.AddDbParameter(value);
sqlPack += ",";
}
}
}
if (sqlPack[sqlPack.Length - 1] == ',')
{
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
return(sqlPack);
}
/// <summary>
/// Sum
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Sum(MemberExpression expression, SqlPack sqlPack)
{
var type = expression.Expression.Type != expression.Member.DeclaringType ?
expression.Expression.Type :
expression.Member.DeclaringType;
sqlPack.Sql.Append($"SELECT SUM({sqlPack.GetColumnInfo(expression.Member.DeclaringType, expression.Member).columnName}) FROM {sqlPack.GetTableName(type)}");
return(sqlPack);
}
/// <summary>
/// GroupBy
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack GroupBy(MemberExpression expression, SqlPack sqlPack)
{
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 (!tableAlias.IsNullOrEmpty())
{
tableAlias += ".";
}
if (expression.Expression.NodeType == ExpressionType.Parameter)
{
sqlPack += tableAlias + sqlPack.GetColumnInfo(expression.Member.DeclaringType, expression.Member).columnName;
}
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)
{
foreach (var item in array)
{
SqlBuilderProvider.GroupBy(Expression.Constant(item, item.GetType()), sqlPack);
}
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
if (type == "List`1" && obj is List <string> list)
{
foreach (var item in list)
{
SqlBuilderProvider.GroupBy(Expression.Constant(item, item.GetType()), sqlPack);
}
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
if (type == "String" && obj is string str)
{
SqlBuilderProvider.GroupBy(Expression.Constant(str, str.GetType()), sqlPack);
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
}
}
return(sqlPack);
}
/// <summary>
/// Insert
/// </summary>
/// <param name="expression"></param>
/// <param name="sqlPack"></param>
/// <returns></returns>
public override SqlPack Insert(MethodCallExpression expression, SqlPack sqlPack)
{
var fields = new List <string>();
var array = expression.ToObject() as object[];
for (var i = 0; i < array.Length; i++)
{
if (sqlPack.DatabaseType != DatabaseType.Oracle)
{
sqlPack.Sql.Append("(");
}
if (i > 0 && sqlPack.DatabaseType == DatabaseType.Oracle)
{
sqlPack.Sql.Append(" UNION ALL SELECT ");
}
var properties = array[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(array[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.Sql[sqlPack.Sql.Length - 1] == ',')
{
sqlPack.Sql.Remove(sqlPack.Sql.Length - 1, 1);
}
sqlPack.Sql = new StringBuilder(string.Format(sqlPack.ToString(), string.Join(",", fields).TrimEnd(',')));
return(sqlPack);
}
/// <summary>
/// Join
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Join(MemberExpression expression, SqlPack sqlPack)
{
var type = expression.Expression.Type != expression.Member.DeclaringType ?
expression.Expression.Type :
expression.Member.DeclaringType;
var tableName = sqlPack.GetTableName(type);
sqlPack.SetTableAlias(tableName);
string tableAlias = sqlPack.GetTableAlias(tableName);
if (!tableAlias.IsNullOrEmpty())
{
tableAlias += ".";
}
sqlPack += tableAlias + sqlPack.GetColumnInfo(expression.Member.DeclaringType, expression.Member).columnName;
return(sqlPack);
}
/// <summary>
/// Insert
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Insert(NewExpression expression, SqlPack sqlPack)
{
if (sqlPack.DatabaseType != DatabaseType.Oracle)
{
sqlPack.Sql.Append("(");
}
var fields = new List <string>();
for (int i = 0; i < expression.Members?.Count; i++)
{
var m = expression.Members[i];
var t = m.DeclaringType.ToString().Contains("AnonymousType") ? sqlPack.DefaultType : m.DeclaringType;
(string columnName, bool isInsert, bool isUpdate) = sqlPack.GetColumnInfo(t, m);
if (isInsert)
{
var value = expression.Arguments[i]?.ToObject();
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");
}
}
sqlPack.Sql = new StringBuilder(string.Format(sqlPack.ToString(), string.Join(",", fields).TrimEnd(',')));
return(sqlPack);
}
/// <summary>
/// Update
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Update(MemberInitExpression expression, SqlPack sqlPack)
{
foreach (MemberAssignment m in expression.Bindings)
{
var type = m.Member.DeclaringType.ToString().Contains("AnonymousType") ? sqlPack.DefaultType : m.Member.DeclaringType;
(string columnName, bool isInsert, bool isUpdate) = sqlPack.GetColumnInfo(type, m.Member);
if (isUpdate)
{
var value = m.Expression.ToObject();
if (value != null || (sqlPack.IsEnableNullValue && value == null))
{
sqlPack += columnName + " = ";
sqlPack.AddDbParameter(value);
sqlPack += ",";
}
}
}
if (sqlPack[sqlPack.Length - 1] == ',')
{
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
return(sqlPack);
}
/// <summary>
/// Where
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Where(MemberExpression expression, SqlPack sqlPack)
{
//此处判断expression的Member是否是可空值类型
if (expression.Expression?.NodeType == ExpressionType.MemberAccess &&
expression.Member.DeclaringType.IsNullable())
{
expression = expression.Expression as MemberExpression;
}
if (expression != null)
{
if (expression.Expression?.NodeType == ExpressionType.Parameter)
{
var type = expression.Expression.Type != expression.Member.DeclaringType ?
expression.Expression.Type :
expression.Member.DeclaringType;
var tableName = sqlPack.GetTableName(type);
sqlPack.SetTableAlias(tableName);
var tableAlias = sqlPack.GetTableAlias(tableName);
if (!tableAlias.IsNullOrEmpty())
{
tableAlias += ".";
}
sqlPack += tableAlias + sqlPack.GetColumnInfo(expression.Member.DeclaringType, expression.Member).columnName;
//字段是bool类型
if (expression.NodeType == ExpressionType.MemberAccess &&
expression.Type.GetCoreType() == typeof(bool))
{
sqlPack += " = 1";
}
}
else
{
sqlPack.AddDbParameter(expression.ToObject());
}
}
return(sqlPack);
}
/// <summary>
/// Update
/// </summary>
/// <param name="expression">表达式树</param>
/// <param name="sqlPack">sql打包对象</param>
/// <returns>SqlPack</returns>
public override SqlPack Update(NewExpression expression, SqlPack sqlPack)
{
for (int i = 0; i < expression.Members.Count; i++)
{
var m = expression.Members[i];
var t = m.DeclaringType.ToString().Contains("AnonymousType") ? sqlPack.DefaultType : m.DeclaringType;
(string columnName, bool isInsert, bool isUpdate) = sqlPack.GetColumnInfo(t, m);
if (isUpdate)
{
var value = expression.Arguments[i]?.ToObject();
if (value != null || (sqlPack.IsEnableNullValue && value == null))
{
sqlPack += columnName + " = ";
sqlPack.AddDbParameter(value);
sqlPack += ",";
}
}
}
if (sqlPack[sqlPack.Length - 1] == ',')
{
sqlPack.Sql.Remove(sqlPack.Length - 1, 1);
}
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 (!tableAlias.IsNullOrEmpty())
{
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);
}
