/// <summary> /// 默认查询字段列表表达式 /// </summary> /// <returns></returns> protected Expression DefaultSelectColumnsExpression() { var filters = new IgnoreExpressionResovle(_filterExpression).Resovles(); var columns = _columns .Where(a => !filters.Contains(a.ColumnName) && !a.IsNotMapped) .Select(s => s.ColumnName != s.CsharpName ? $"{s.ColumnName} AS {s.CsharpName}" : s.CsharpName); var expression = string.Join(",", columns); return(Expression.Constant(expression)); }
/// <summary> /// 构建新增命令 /// </summary> /// <param name="identity"></param> /// <param name="entityType"></param> /// <returns></returns> private string BuildInsertCommand(bool identity, Type entityType) { var table = GetViewName(); var ignores = new IgnoreExpressionResovle(_filterExpression).Resovles(); var columns = _columns; var insertcolumns = columns .Where(a => !ignores.Contains(a.ColumnName)) //忽略列 .Where(a => !a.IsNotMapped) //非映射列 .Where(a => !a.IsIdentity) //非自增列 .Where(a => !a.IsComplexType) //非计算列 .Where(a => !a.IsDefault || (_parameters.ContainsKey(a.CsharpName) && _parameters[a.CsharpName] != null)) .ToList(); //并发检查列 if (insertcolumns.Any(a => a.IsConcurrencyCheck)) { var checkColumn = insertcolumns.Where(a => a.IsConcurrencyCheck).First(); if (!_parameters.ContainsKey(checkColumn.CsharpName) || _parameters[checkColumn.CsharpName] == null) { if (_parameters.ContainsKey(checkColumn.CsharpName)) { _parameters[checkColumn.CsharpName] = GetConcurrencyColumnValue(checkColumn.CsharpType); } else { _parameters.Add(checkColumn.CsharpName, GetConcurrencyColumnValue(checkColumn.CsharpType)); } } } if (typeof(T) != entityType)//如果插入的实体类型和表实体类型不同,则忽略null值 { var temps = new List <DbColumnMetaInfo>(); foreach (var item in insertcolumns) { if (_parameters.ContainsKey(item.CsharpName)) { temps.Add(item); } } insertcolumns = temps; } else if (_ignoreAllNullColumns) { foreach (var item in _parameters) { if (item.Value == null) { var c = insertcolumns.Where(a => a.CsharpName == item.Key).FirstOrDefault(); if (c != null) { insertcolumns.Remove(c); } _parameters.Remove(item.Key); } } } var columnNames = string.Join(",", insertcolumns.Select(s => s.ColumnName)); var parameters = string.Join(",", insertcolumns.Select(s => $"{_parameterPrefix}{s.CsharpName}")); var sql = $"INSERT INTO {table}({columnNames}) VALUES ({parameters})"; if (identity) { sql = $"{sql};SELECT @@IDENTITY"; } return(sql); }
/// <summary> /// 构建更新命令 /// </summary> /// <returns></returns> private string BuildUpdateCommand(Type entityType = null) { var table = GetViewName(); var builder = new StringBuilder(); var expressions = _expressions.GetSetExpressions(); if (expressions.Any()) { var where = BuildWhereExpression(); foreach (var item in expressions) { var column = new GroupExpressionResovle(_isSingleTable, item.Column).Resovle(); var expression = new BooleanExpressionResovle(_isSingleTable, item.Expression, _parameters).Resovle(); builder.Append($"{column} = {expression},"); } var sql = $"UPDATE {table} SET {builder.ToString().Trim(',')}{where}"; return(sql); } else { var filters = new IgnoreExpressionResovle(_filterExpression).Resovles(); var where = BuildWhereExpression(); var columns = _columns; var updatecolumns = columns .Where(a => !filters.Contains(a.ColumnName)) .Where(a => !a.IsComplexType) .Where(a => !a.IsIdentity && !a.IsPrimaryKey && !a.IsNotMapped) .Where(a => !a.IsConcurrencyCheck) .ToList(); if (typeof(T) != entityType) { var temps = new List <DbColumnMetaInfo>(); foreach (var item in updatecolumns) { if (_parameters.ContainsKey(item.CsharpName)) { temps.Add(item); } } updatecolumns = temps; } else if (_ignoreAllNullColumns) { foreach (var item in _parameters) { if (item.Value == null) { var c = updatecolumns.Where(a => a.CsharpName == item.Key).FirstOrDefault(); if (c != null) { updatecolumns.Remove(c); } _parameters.Remove(item.Key); } } } if (string.IsNullOrEmpty(where)) { var primaryKey = columns.Where(a => a.IsPrimaryKey).FirstOrDefault(); if (primaryKey == null) { throw new Exception("primary key is required"); } where = $" WHERE {primaryKey.ColumnName} = {_parameterPrefix}{primaryKey.CsharpName}"; } var setsql = updatecolumns.Select(s => $"{s.ColumnName} = {_parameterPrefix}{s.CsharpName}"); var sql = $"UPDATE {table} SET {string.Join(",", setsql)}"; //并发检查 if (columns.Any(a => a.IsConcurrencyCheck)) { var checkColumn = columns.Where(a => a.IsConcurrencyCheck).FirstOrDefault(); sql += $",{checkColumn.ColumnName} = {_parameterPrefix}New{checkColumn.CsharpName}"; where += $" AND {checkColumn.ColumnName} = {_parameterPrefix}{checkColumn.CsharpName}"; _parameters.Add($"New{checkColumn.CsharpName}", GetConcurrencyColumnValue(checkColumn.CsharpType)); } sql = $"{sql}{where}"; return(sql); } }
/// <summary> /// 构建批量新增命令 /// </summary> /// <param name="entitys"></param> /// <returns></returns> private string BuildBatchInsertCommand(IEnumerable <T> entitys) { var table = GetViewName(); var filters = new IgnoreExpressionResovle(_filterExpression).Resovles(); var columns = _columns .Where(a => !a.IsComplexType).ToList(); var intcolumns = columns .Where(a => !filters.Contains(a.ColumnName) && !a.IsNotMapped && !a.IsIdentity) .ToList(); var columnNames = string.Join(",", intcolumns.Select(s => s.ColumnName)); if (_context.DbContextType == DbContextType.Mysql) { var buffer = new StringBuilder(); buffer.Append($"INSERT INTO {table}({columnNames}) VALUES "); var serializer = SqlBatisSettings.DbEntityMapperProvider.GetDeserializer(typeof(T)); var list = entitys.ToList(); for (var i = 0; i < list.Count; i++) { var item = list[i]; var values = serializer(item); buffer.Append('('); for (var j = 0; j < intcolumns.Count; j++) { var column = intcolumns[j]; var value = values[column.CsharpName]; if (value == null) { buffer.Append(column.IsDefault ? "DEFAULT" : "NULL"); } else if (column.CsharpType == typeof(bool) || column.CsharpType == typeof(bool?)) { buffer.Append(Convert.ToBoolean(value) == true ? 1 : 0); } else if (column.CsharpType == typeof(Guid) || column.CsharpType == typeof(Guid?)) { buffer.Append($"'{value}'"); } else if (column.CsharpType == typeof(byte[])) { var bytes = value as byte[]; var hexstr = string.Join("", bytes.Select(s => Convert.ToString(s, 16).PadLeft(2, '0'))); buffer.Append($"0x{hexstr}"); } else if (column.CsharpType == typeof(DateTime) || column.CsharpType == typeof(DateTime?)) { buffer.Append($"'{value}'"); } else if (column.CsharpType.IsValueType || (Nullable.GetUnderlyingType(column.CsharpType)?.IsValueType == true)) { buffer.Append(value); } else { var str = CheckSql(value.ToString()); buffer.Append($"'{str}'"); } if (j + 1 < intcolumns.Count) { buffer.Append(','); } } buffer.Append(')'); if (i + 1 < list.Count) { buffer.Append(','); } } return(buffer.Remove(buffer.Length - 1, 0).ToString()); } throw new NotImplementedException(); }