csharp/1448376744/Dapper.Linq/src/Dapper.Common/Queryables/DbQuery.cs

DbQuery.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using Dapper.Expressions;

namespace Dapper
{
    public partial clast DbQuery : IDbQuery
    {
        #region fields

        private readonly Dictionary _parameters
            = new Dictionary();

        private readonly PageData _page = new PageData();

        private string _lockname = string.Empty;

        private readonly IDbContext _context = null;

        private readonly List _whereExpressions = new List();

        private readonly List _setExpressions = new List();

        private readonly List _orderExpressions = new List();

        private readonly List _groupExpressions = new List();

        private readonly List _havingExpressions = new List();

        private Expression _filterExpression = null;

        private Expression _selectExpression = null;

        private Expression _countExpression = null;

        public DbQuery(IDbContext context)
        {
            _context = context;
        }

        #endregion

        #region resovles
        private void ResovleParameter(T ensaty)
        {
            var serializer = GlobalSettings.EnsatyMapperProvider.GetDeserializer(typeof(T));
            var values = serializer(ensaty);
            foreach (var item in values)
            {
                if (_parameters.ContainsKey(item.Key))
                {
                    _parameters[item.Key] = item.Value;
                }
                else
                {
                    _parameters.Add(item.Key, item.Value);
                }
            }
        }

        private string ResovleCount()
        {
            var table = GetTableMetaInfo().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;
        }

        private string ResovleSum()
        {
            var table = GetTableMetaInfo().TableName;
            var column = $"SUM({ResovleColumns()})";
            var where = ResolveWhere();
            var sql = $"SELECT {column} FROM {table}{where}";
            return sql;
        }

        private string ResolveGet()
        {
            var table = GetTableMetaInfo().TableName;
            var columns = GetColumnMetaInfos();
            var column = ResovleColumns();
            var where = $" WHERE {columns.Where(a => a.IsPrimaryKey == true).First().ColumnName}=@id";
            string sql;
            if (_context.DbContextType == DbContextType.SqlServer)
            {
                sql = $"SELECT TOP 1 {column} FROM {table}{where}";
            }
            else
            {
                sql = $"SELECT {column} FROM {table}{where} LIMIT 0,1";
            }
            return sql;
        }

        private string ResolveSelect()
        {
            var table = GetTableMetaInfo().TableName;
            var column = ResovleColumns();
            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;
        }

        private string ResovleInsert(bool idensaty)
        {
            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.IsIdensaty)
                .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 (idensaty)
            {
                sql = $"{sql};SELECT @@IDENsatY";
            }
            return sql;
        }

        private DbTableMetaInfo GetTableMetaInfo()
        {
            return GlobalSettings.DbMetaInfoProvider.GetTable(typeof(T));
        }
        private List GetColumnMetaInfos()
        {
            return GlobalSettings.DbMetaInfoProvider.GetColumns(typeof(T));
        }
        private string ResovleBatchInsert(IEnumerable ensatys)
        {
            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.IsIdensaty)
                .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.EnsatyMapperProvider.GetDeserializer(typeof(T));
                var list = ensatys.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();
        }

        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.IsIdensaty && !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;
            }
        }

        private string ResovleDelete()
        {
            var table = GetTableMetaInfo().TableName;
            var where = ResolveWhere();
            var sql = $"DELETE FROM {table}{where}";
            return sql;
        }

        private string SqlEncoding(string sql)
        {
            var buffer = new StringBuilder();
            for (int i = 0; i < sql.Length; i++)
            {
                var ch = sql[i];
                if (ch == '\'' || ch == '-' || ch == '\\' || ch == '*' || ch == '@')
                {
                    buffer.Append('\\');
                }
                buffer.Append(ch);
            }
            return buffer.ToString();
        }

        private string ResovleExists()
        {
            var table = GetTableMetaInfo().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;
        }

        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 ResolveWhere()
        {
            var builder = new StringBuilder();
            foreach (var expression in _whereExpressions)
            {
                var result = new BooleanExpressionResovle(expression, _parameters).Resovle();
                if (expression == _whereExpressions.First())
                {
                    builder.Append($" WHERE {result}");
                }
                else
                {
                    builder.Append($" AND {result}");
                }
            }
            return builder.ToString();
        }

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

        private string ResolveHaving()
        {
            var buffer = new StringBuilder();
            foreach (var item in _havingExpressions)
            {
                var result = new BooleanExpressionResovle(item, _parameters).Resovle();
                if (item == _havingExpressions.First())
                {
                    buffer.Append($" HAVING {result}");
                }
                else
                {
                    buffer.Append($" AND {result}");
                }
            }
            return buffer.ToString();
        }

        private string ResolveOrder()
        {
            var buffer = new StringBuilder();
            foreach (var item in _orderExpressions)
            {
                if (item == _orderExpressions.First())
                {
                    buffer.Append($" ORDER BY ");
                }
                var result = new OrderExpressionResovle(item.Expression, item.Asc).Resovle();
                buffer.Append(result);
                buffer.Append(",");
            }
            return buffer.ToString().Trim(',');
        }

        clast PageData
        {
            public int Index { get; set; } = -1;
            public int Count { get; set; }
        }

        clast OrderExpression
        {
            public bool Asc { get; set; } = true;
            public Expression Expression { get; set; }
        }

        clast SetExpression
        {
            public Expression Column { get; set; }
            public Expression Expression { get; set; }
        }
        #endregion
    }
}