private string ResolveUpdate() { var table = GetTableMetaInfo().TableName; var builder = new StringBuilder(); if (_setExpressions.Count > 0) { var where = ResolveWhere(); foreach (var item in _setExpressions) { var column = new BooleanExpressionResovle(item.Column).Resovle(); var expression = new BooleanExpressionResovle(item.Expression, _parameters).Resovle(); builder.Append($"{column} = {expression},"); } var sql = $"UPDATE {table} SET {builder.ToString().Trim(',')}{where}"; return(sql); } else { var filters = new GroupExpressionResovle(_filterExpression).Resovle().Split(','); var where = ResolveWhere(); var columns = GetColumnMetaInfos(); var updcolumns = columns .Where(a => !filters.Contains(a.ColumnName)) .Where(a => !a.IsComplexType) .Where(a => !a.IsIdentity && !a.IsPrimaryKey && !a.IsNotMapped) .Where(a => !a.IsConcurrencyCheck) .Select(s => $"{s.ColumnName} = @{s.CsharpName}"); if (string.IsNullOrEmpty(where)) { var primaryKey = columns.Where(a => a.IsPrimaryKey).FirstOrDefault() ?? columns.First(); where = $" WHERE {primaryKey.ColumnName} = @{primaryKey.CsharpName}"; if (columns.Exists(a => a.IsConcurrencyCheck)) { var checkColumn = columns.Where(a => a.IsConcurrencyCheck).FirstOrDefault(); where += $" AND {checkColumn.ColumnName} = @{checkColumn.CsharpName}"; } } var sql = $"UPDATE {table} SET {string.Join(",", updcolumns)}"; if (columns.Exists(a => a.IsConcurrencyCheck)) { var checkColumn = columns.Where(a => a.IsConcurrencyCheck).FirstOrDefault(); sql += $",{checkColumn.ColumnName} = @New{checkColumn.CsharpName}"; if (checkColumn.CsharpType.IsValueType) { var version = Convert.ToInt32((DateTime.UtcNow - new DateTime(1970, 1, 1, 0, 0, 0, 0)).TotalSeconds); _parameters.Add($"New{checkColumn.CsharpName}", version); } else { var version = Guid.NewGuid().ToString("N"); _parameters.Add($"New{checkColumn.CsharpName}", version); } } sql += where; return(sql); } }
/// <summary> /// 构建更新命令 /// </summary> /// <returns></returns> private string BuildUpdateCommand() { 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 = BuildIgnoreExpression(); var where = BuildWhereExpression(); var columns = _columns .Where(a => !filters.Contains(a.ColumnName)) .Where(a => !a.IsComplexType) .Where(a => !a.IsPrimaryKey) .Where(a => !a.IsNotMapped) .Where(a => !a.IsIdentity) .Where(a => !a.IsConcurrencyCheck) .ToList(); var updatecolumns = IgnoreAllNullColumns(columns); if (string.IsNullOrEmpty(where)) { var primaryKey = _columns.Where(a => a.IsPrimaryKey).FirstOrDefault(); if (primaryKey == null) { throw new MissingPrimaryKeyException("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(); if (!_parameters.ContainsKey(checkColumn.CsharpName) || _parameters[checkColumn.CsharpName] == null) { throw new ArgumentNullException(checkColumn.CsharpName); } 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); } }
private string ResolveColumns() { if (_selectExpression == null) { var filters = new GroupExpressionResovle(_filterExpression).Resovle().Split(','); var columns = TableInfoCache.GetColumns(typeof(T)) .Where(a => !filters.Contains(a.ColumnName)) .Select(s => s.ColumnName != s.PropertyName ? $"{s.ColumnName} AS {s.PropertyName}" : s.PropertyName); return(string.Join(",", columns)); } else { return(new SelectExpressionResovle(_selectExpression).Resovle()); } }
private string ResovleColumns() { if (_selectExpression == null) { var filters = new GroupExpressionResovle(_filterExpression) .Resovle().Split(','); var columns = GetColumnMetaInfos() .Where(a => !filters.Contains(a.ColumnName) && !a.IsNotMapped) .Select(s => s.ColumnName != s.CsharpName ? $"{s.ColumnName} AS {s.CsharpName}" : s.CsharpName); return(string.Join(",", columns)); } else { return(new SelectExpressionResovle(_selectExpression).Resovle()); } }
private string ResovleInsert(bool identity) { var table = DbMetaCache.GetTable(typeof(T)).TableName; var filters = new GroupExpressionResovle(_filterExpression).Resovle().Split(','); var columns = DbMetaCache.GetColumns(typeof(T)); var intcolumns = columns .Where(a => !filters.Contains(a.ColumnName) && !a.IsNotMapped && !a.IsIdentity); var columnNames = string.Join(",", intcolumns.Select(s => s.ColumnName)); var parameterNames = string.Join(",", intcolumns.Select(s => $"@{s.CsharpName}")); var sql = $"INSERT INTO {table}({columnNames}) VALUES ({parameterNames})"; if (identity) { sql = $"{sql};SELECT @@IDENTITY"; } return(sql); }
private string ResolveGroup() { var buffer = new StringBuilder(); foreach (var item in _groupExpressions) { var result = new GroupExpressionResovle(item).Resovle(); buffer.Append($"{result},"); } var sql = string.Empty; if (buffer.Length > 0) { buffer.Remove(buffer.Length - 1, 1); sql = $" GROUP BY {buffer}"; } return(sql); }
protected string BuildGroupExpression() { var buffer = new StringBuilder(); var expressions = _expressions.GetGroupExpressions(); foreach (var item in expressions) { var result = new GroupExpressionResovle(_isSingleTable, item.Expression).Resovle(); buffer.Append($"{result},"); } var sql = string.Empty; if (buffer.Length > 0) { buffer.Remove(buffer.Length - 1, 1); sql = $" GROUP BY {buffer}"; } return(sql); }
private string ResovleInsert(bool identity) { var table = GetTableMetaInfo().TableName; var filters = new GroupExpressionResovle(_filterExpression).Resovle().Split(','); var columns = GetColumnMetaInfos(); var intcolumns = columns .Where(a => !filters.Contains(a.ColumnName) && !a.IsNotMapped && !a.IsIdentity) .Where(a => !a.IsComplexType) .Where(a => !a.IsDefault || (_parameters.ContainsKey(a.CsharpName) && _parameters[a.CsharpName] != null));//如果是默认字段 var columnNames = string.Join(",", intcolumns.Select(s => s.ColumnName)); var parameterNames = string.Join(",", intcolumns.Select(s => $"@{s.CsharpName}")); var sql = $"INSERT INTO {table}({columnNames}) VALUES ({parameterNames})"; if (identity) { sql = $"{sql};SELECT @@IDENTITY"; } return(sql); }
private string ResolveUpdate() { var table = DbMetaCache.GetTable(typeof(T)).TableName; var builder = new StringBuilder(); if (_setExpressions.Count > 0) { var where = ResolveWhere(); foreach (var item in _setExpressions) { var column = new BooleanExpressionResovle(item.Column).Resovle(); var expression = new BooleanExpressionResovle(item.Expression, _parameters).Resovle(); builder.Append($"{column} = {expression},"); } var sql = $"UPDATE {table} SET {builder.ToString().Trim(',')}{where}"; return(sql); } else { var filters = new GroupExpressionResovle(_filterExpression).Resovle().Split(','); var where = ResolveWhere(); var columns = DbMetaCache.GetColumns(typeof(T)); var updcolumns = columns .Where(a => !filters.Contains(a.ColumnName)) .Where(a => !a.IsIdentity && !a.IsPrimaryKey && !a.IsNotMapped) .Select(s => $"{s.ColumnName} = @{s.CsharpName}"); if (string.IsNullOrEmpty(where)) { var primaryKey = columns.Where(a => a.IsPrimaryKey).FirstOrDefault() ?? columns.First(); where = $" WHERE {primaryKey.ColumnName} = @{primaryKey.CsharpName}"; } var sql = $"UPDATE {table} SET {string.Join(",", updcolumns)}{where}"; return(sql); } }
private string ResovleBatchInsert(IEnumerable <T> entitys) { var table = GetTableMetaInfo().TableName; var filters = new GroupExpressionResovle(_filterExpression).Resovle().Split(','); var columns = GetColumnMetaInfos() .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 = GlobalSettings.EntityMapperProvider.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(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 = SqlEncoding(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(); }
/// <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); } }