csharp/1448376744/Dapper.Linq/src/Dapper.Common/DbContexts/DbContext.cs

DbContext.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Dapper
{
    /// 
    /// 数据库上下文
    /// 
    public interface IDbContext : IDisposable
    {
        /// 
        /// 日志处理
        /// 
        event Logging Logging;
        /// 
        /// 数据库连接
        /// 
        IDbConnection Connection { get; }
        /// 
        /// 数据库上下文类型
        /// 
        DbContextType DbContextType { get; }
        /// 
        /// 获取一个xml执行器
        /// 
        /// 参数类型
        /// 命令id
        /// 参数
        /// 
        IXmlQuery From(string id, T parameter) where T : clast;
        /// 
        /// 获取一个xml执行器
        /// 
        /// 命令id
        /// 
        IXmlQuery From(string id);
        /// 
        /// 获取一个linq执行器
        /// 
        /// 
        /// 
        IDbQuery From();
        /// 
        /// 开启事务会话
        /// 
        void BeginTransaction();
        /// 
        /// 异步开启事务会话
        /// 
        /// 
        Task BeginTransactionAsync();
        /// 
        /// 开启事务会话
        /// 
        /// 事务隔离级别
        void BeginTransaction(IsolationLevel level);
        /// 
        /// 异步开启事务会话
        /// 
        /// 
        /// 
        Task BeginTransactionAsync(IsolationLevel level);
        /// 
        /// 关闭连接和事务
        /// 
        void Close();
        /// 
        /// 提交当前事务会话
        /// 
        void CommitTransaction();
        /// 
        /// 执行多结果集查询,返回IMultiResult
        /// 
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        IDbMultipleResult QueryMultiple(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 执行单结果集查询,并返回dynamic类型的结果集
        /// 
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        IEnumerable Query(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 异步执行单结果集查询,并返回dynamic类型的结果集
        /// 
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        Task QueryAsync(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 执行单结果集查询,并返回T类型的结果集
        /// 
        /// 返回类型
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        IEnumerable Query(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 异步执行单结果集查询,并返回T类型的结果集
        /// 
        /// 返回类型
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        Task QueryAsync(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 执行无结果集查询,并返回受影响的行数
        /// 
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        int Execute(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 异步执行无结果集查询,并返回受影响的行数
        /// 
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        Task ExecuteAsync(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 执行无结果集查询,并返回指定类型的数据
        /// 
        /// 返回类型
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        T ExecuteScalar(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 异步执行无结果集查询,并返回指定类型的数据
        /// 
        /// 返回类型
        /// sql命令
        /// 参数
        /// 超时时间
        /// 命令类型
        /// 
        Task ExecuteScalarAsync(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null);
        /// 
        /// 打开数据库连接
        /// 
        void Open();
        /// 
        /// 异步打开数据库连接
        /// 
        /// 
        Task OpenAsync();
        /// 
        /// 回滚当前事务会话
        /// 
        void RollbackTransaction();
    }

    /// 
    /// 数据库上下文
    /// 
    public clast DbContext : IDbContext
    {
        public DbContextState DbContextState = DbContextState.Closed;
        private IDbTransaction _transaction = null;
        public IDbConnection Connection { get; } = null;
        public DbContextType DbContextType { get; } = DbContextType.Mysql;
        public event Logging Logging;
        public DbContext(DbContextBuilder builder)
        {
            Connection = builder.Connection;
            DbContextType = builder.DbContextType;
        }
        public IXmlQuery From(string id, T parameter) where T : clast
        {
            var sql = GlobalSettings.XmlCommandsProvider.Build(id, parameter);
            var deserializer = GlobalSettings.EnsatyMapperProvider.GetDeserializer(typeof(T));
            var values = deserializer(parameter);
            return new XmlQuery(this, sql, values);
        }
        public IXmlQuery From(string id)
        {
            var sql = GlobalSettings.XmlCommandsProvider.Build(id);
            return new XmlQuery(this, sql);
        }
        public IDbQuery From()
        {
            return new DbQuery(this);
        }
        public IEnumerable Query(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var cmd = Connection.CreateCommand())
            {
                Initialize(cmd, sql, parameter, commandTimeout, commandType);
                var list = new List();
                using (var reader = cmd.ExecuteReader())
                {
                    var handler = GlobalSettings.EnsatyMapperProvider.GetSerializer();
                    while (reader.Read())
                    {
                        list.Add(handler(reader));
                    }
                    return list;
                }
            }
        }
        public async Task QueryAsync(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var cmd = (Connection as DbConnection).CreateCommand())
            {
                Initialize(cmd, sql, parameter, commandTimeout, commandType);
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    var list = new List();
                    var handler = GlobalSettings.EnsatyMapperProvider.GetSerializer();
                    while (reader.Read())
                    {
                        list.Add(handler(reader));
                    }
                    return list;
                }
            }
        }
        public IDbMultipleResult QueryMultiple(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var cmd = Connection.CreateCommand();
            Initialize(cmd, sql, parameter, commandTimeout, commandType);
            return new DbMultipleResult(cmd);
        }
        public IEnumerable Query(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var cmd = Connection.CreateCommand())
            {
                var list = new List();
                Initialize(cmd, sql, parameter, commandTimeout, commandType);
                using (var reader = cmd.ExecuteReader())
                {
                    var handler = GlobalSettings.EnsatyMapperProvider.GetSerializer(reader);
                    while (reader.Read())
                    {
                        list.Add(handler(reader));
                    }
                    return list;
                }
            }
        }
        public async Task QueryAsync(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var cmd = (Connection as DbConnection).CreateCommand())
            {
                Initialize(cmd, sql, parameter, commandTimeout, commandType);
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    var list = new List();
                    var handler = GlobalSettings.EnsatyMapperProvider.GetSerializer(reader);
                    while (await reader.ReadAsync())
                    {
                        list.Add(handler(reader));
                    }
                    return list;
                }
            }
        }
        public int Execute(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var cmd = Connection.CreateCommand())
            {
                Initialize(cmd, sql, parameter, commandTimeout, commandType);
                return cmd.ExecuteNonQuery();
            }
        }
        public async Task ExecuteAsync(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var cmd = (Connection as DbConnection).CreateCommand())
            {
                Initialize(cmd, sql, parameter, commandTimeout, commandType);
                return await cmd.ExecuteNonQueryAsync();
            }
        }
        public T ExecuteScalar(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var cmd = Connection.CreateCommand())
            {
                Initialize(cmd, sql, parameter, commandTimeout, commandType);
                var result = cmd.ExecuteScalar();
                if (result is DBNull || result == null)
                {
                    return default;
                }
                return (T)Convert.ChangeType(result, typeof(T));
            }
        }
        public async Task ExecuteScalarAsync(string sql, object parameter = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var cmd = (Connection as DbConnection).CreateCommand())
            {
                Initialize(cmd, sql, parameter, commandTimeout, commandType);
                var result = await cmd.ExecuteScalarAsync();
                if (result is DBNull || result == null)
                {
                    return default;
                }
                return (T)Convert.ChangeType(result, typeof(T));
            }
        }
        public async Task BeginTransactionAsync()
        {
            await Task.Run(() =>
            {
                _transaction = Connection.BeginTransaction();
                Logging?.Invoke("Begin Transaction");
            });
        }
        public async Task BeginTransactionAsync(IsolationLevel level)
        {
            await Task.Run(() =>
            {
                _transaction = Connection.BeginTransaction(level);
                Logging?.Invoke("Begin Transaction IsolationLevel = " + level);
            });
        }
        public void BeginTransaction()
        {
            _transaction = Connection.BeginTransaction();
            Logging?.Invoke("Begin Transaction");
        }
        public void BeginTransaction(IsolationLevel level)
        {
            _transaction = Connection.BeginTransaction(level);
            Logging?.Invoke("Begin Transaction IsolationLevel = " + level);
        }
        public void Close()
        {
            _transaction?.Dispose();
            Connection?.Close();
            DbContextState = DbContextState.Closed;
            Logging?.Invoke("Colsed Connection");
        }
        public void CommitTransaction()
        {
            _transaction?.Commit();
            DbContextState = DbContextState.Commit;
            Logging?.Invoke("Commit Transaction");
        }
        public void Open()
        {
            Connection?.Open();
            DbContextState = DbContextState.Open;
            Logging?.Invoke("Open Connection");
        }
        public async Task OpenAsync()
        {
            await (Connection as DbConnection).OpenAsync();
            DbContextState = DbContextState.Open;
            Logging?.Invoke("Open Connection");
        }
        public void RollbackTransaction()
        {
            _transaction?.Rollback();
            DbContextState = DbContextState.Rollback;
            Logging?.Invoke("Rollback");
        }
        private void Initialize(IDbCommand cmd, string sql, object parameter, int? commandTimeout = null, CommandType? commandType = null)
        {
            var dbParameters = new List();
            cmd.Transaction = _transaction;
            cmd.CommandText = sql;
            if (commandTimeout.HasValue)
            {
                cmd.CommandTimeout = commandTimeout.Value;
            }
            if (commandType.HasValue)
            {
                cmd.CommandType = commandType.Value;
            }
            if (parameter is IDbDataParameter)
            {
                dbParameters.Add(parameter as IDbDataParameter);
            }
            else if (parameter is IEnumerable parameters)
            {
                dbParameters.AddRange(parameters);
            }
            else if (parameter is Dictionary keyValues)
            {
                foreach (var item in keyValues)
                {
                    var param = CreateParameter(cmd, item.Key, item.Value);
                    dbParameters.Add(param);
                }
            }
            else if (parameter != null)
            {
                var handler = GlobalSettings.EnsatyMapperProvider.GetDeserializer(parameter.GetType());
                var values = handler(parameter);
                foreach (var item in values)
                {
                    var param = CreateParameter(cmd, item.Key, item.Value);
                    dbParameters.Add(param);
                }
            }
            if (dbParameters.Count > 0)
            {
                foreach (IDataParameter item in dbParameters)
                {
                    if (item.Value == null)
                    {
                        item.Value = DBNull.Value;
                    }
                    var pattern = $@"in\s+([\@,\:,\?]?{item.ParameterName})";
                    var options = RegexOptions.IgnoreCase | RegexOptions.Singleline | RegexOptions.Multiline;
                    if (cmd.CommandText.IndexOf("in", StringComparison.OrdinalIgnoreCase) != -1 && Regex.IsMatch(cmd.CommandText, pattern, options))
                    {
                        var name = Regex.Match(cmd.CommandText, pattern, options).Groups[1].Value;
                        var list = new List();
                        if (item.Value is IEnumerable || item.Value is Array)
                        {
                            list = (item.Value as IEnumerable).Cast().Where(a => a != null && a != DBNull.Value).ToList();
                        }
                        else
                        {
                            list.Add(item.Value);
                        }
                        if (list.Count() > 0)
                        {
                            cmd.CommandText = Regex.Replace(cmd.CommandText, name, $"({string.Join(",", list.Select(s => $"{name}{list.IndexOf(s)}"))})");
                            foreach (var iitem in list)
                            {
                                var key = $"{item.ParameterName}{list.IndexOf(iitem)}";
                                var param = CreateParameter(cmd, key, iitem);
                                cmd.Parameters.Add(param);
                            }
                        }
                        else
                        {
                            cmd.CommandText = Regex.Replace(cmd.CommandText, name, $"(SELECT 1 WHERE 1 = 0)");
                        }
                    }
                    else
                    {
                        cmd.Parameters.Add(item);
                    }
                }
            }
            if (Logging != null)
            {
                var parameters = new Dictionary();
                foreach (IDbDataParameter item in cmd.Parameters)
                {
                    parameters.Add(item.ParameterName, item.Value);
                }
                Logging.Invoke(cmd.CommandText, parameters, commandTimeout, commandType);
            }
        }
        private IDbDataParameter CreateParameter(IDbCommand command, string name, object value)
        {
            var parameter = command.CreateParameter();
            parameter.ParameterName = name;
            parameter.Value = value;
            return parameter;
        }
        public void Dispose()
        {
            _transaction?.Dispose();
            Connection?.Dispose();
        }
    }

    /// 
    /// sql执行日志
    /// 
    /// 
    /// 
    /// 
    /// 
    public delegate void Logging(string message, Dictionary parameters = null, int? commandTimeout = null, CommandType? commandType = null);
}