/// <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); }