コード例 #1
0
        private string ResovleCount()
        {
            var table  = DbMetaInfoCache.GetTable(typeof(T)).TableName;
            var column = "COUNT(1)";

            var where = ResolveWhere();
            var group = ResolveGroup();

            if (group.Length > 0)
            {
                column = group.Remove(0, 10);
            }
            else if (_countExpression != null)
            {
                column = new SelectExpressionResovle(_countExpression).Resovle();
            }
            var sql = $"SELECT {column} FROM {table}{where}{group}";

            if (group.Length > 0)
            {
                sql = $"SELECT COUNT(1) FROM ({sql}) as t";
                return(sql);
            }
            return(sql);
        }
コード例 #2
0
        private string ResolveUpdate()
        {
            var table   = DbMetaInfoCache.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    = DbMetaInfoCache.GetColumns(typeof(T));
                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);
            }
        }
コード例 #3
0
        private string ResovleDelete()
        {
            var table = DbMetaInfoCache.GetTable(typeof(T)).TableName;

            var where = ResolveWhere();
            var sql = $"DELETE FROM {table}{where}";

            return(sql);
        }
コード例 #4
0
        private string ResovleSum()
        {
            var table  = DbMetaInfoCache.GetTable(typeof(T)).TableName;
            var column = $"SUM({ResolveColumns()})";

            var where = ResolveWhere();
            var sql = $"SELECT {column} FROM {table}{where}";

            return(sql);
        }
コード例 #5
0
        private string ResovleExists()
        {
            var table = DbMetaInfoCache.GetTable(typeof(T)).TableName;

            var where = ResolveWhere();
            var group  = ResolveGroup();
            var having = ResolveHaving();
            var sql    = $"SELECT 1 WHERE EXISTS(SELECT 1 FROM {table}{where}{group}{having})";

            return(sql);
        }
コード例 #6
0
 private string ResolveColumns()
 {
     if (_selectExpression == null)
     {
         var filters = new GroupExpressionResovle(_filterExpression)
                       .Resovle().Split(',');
         var columns = DbMetaInfoCache.GetColumns(typeof(T))
                       .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());
     }
 }
コード例 #7
0
        private string ResovleInsert(bool identity)
        {
            var table      = DbMetaInfoCache.GetTable(typeof(T)).TableName;
            var filters    = new GroupExpressionResovle(_filterExpression).Resovle().Split(',');
            var columns    = DbMetaInfoCache.GetColumns(typeof(T));
            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);
        }
コード例 #8
0
        private string ResolveSelect()
        {
            var table  = DbMetaInfoCache.GetTable(typeof(T)).TableName;
            var column = ResolveColumns();

            var where = ResolveWhere();
            var    group  = ResolveGroup();
            var    having = ResolveHaving();
            var    order  = ResolveOrder();
            string sql;

            if (_context.DbContextType == DbContextType.SqlServer)
            {
                if (_lockname != string.Empty)
                {
                    _lockname = $" WITH({_lockname})";
                }
                if (_page.Index == 0)
                {
                    sql = $"SELECT TOP {_page.Count} {column} FROM {table}{_lockname}{where}{group}{having}{order}";
                }
                else if (_page.Index > 0)
                {
                    if (order == string.Empty)
                    {
                        order = " ORDER BY (SELECT 1)";
                    }
                    var limit = $" OFFSET {_page.Index} ROWS FETCH NEXT {_page.Count} ROWS ONLY";
                    sql = $"SELECT {column} FROM {_lockname}{table}{where}{group}{having}{order}{limit}";
                }
                else
                {
                    sql = $"SELECT {column} FROM {_lockname}{table}{where}{group}{having}{order}";
                }
            }
            else
            {
                var limit = _page.Index > 0 || _page.Count > 0 ? $" LIMIT {_page.Index},{_page.Count}" : string.Empty;
                sql = $"SELECT {column} FROM {table}{where}{group}{having}{order}{limit}{_lockname}";
            }
            return(sql);
        }
コード例 #9
0
        private string ResovleBatchInsert(IEnumerable <T> entitys)
        {
            var table   = DbMetaInfoCache.GetTable(typeof(T)).TableName;
            var filters = new GroupExpressionResovle(_filterExpression).Resovle().Split(',');
            var columns = DbMetaInfoCache.GetColumns(typeof(T))
                          .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 = TypeConvert.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 == typeof(JsonElement) || column.CsharpType == typeof(JsonElement?))
                        {
                            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();
        }