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