/// <summary>
/// 执行 Sql 返回 DataTable
/// </summary>
/// <param name="databaseFacade">ADO.NET 数据库对象</param>
/// <param name="sql">sql 语句</param>
/// <param name="commandType">命令类型</param>
/// <param name="model">命令模型</param>
/// <param name="behavior">行为</param>
/// <returns>(DataTable dataTable, DbParameter[] dbParameters)</returns>
public static (DataTable dataTable, DbParameter[] dbParameters) ExecuteReader(this DatabaseFacade databaseFacade, string sql, object model, CommandType commandType = CommandType.Text, CommandBehavior behavior = CommandBehavior.Default)
{
// 获取真实运行 Sql
sql = DbHelpers.ResolveSqlConfiguration(sql);
// 初始化数据库连接对象和数据库命令对象
var(dbConnection, dbCommand, dbParameters) = databaseFacade.PrepareDbCommand(sql, model, commandType);
// 读取数据
using var dbDataReader = dbCommand.ExecuteReader(behavior);
// 填充到 DataTable
using var dataTable = new DataTable();
dataTable.Load(dbDataReader);
// 关闭连接
dbDataReader.Close();
dbConnection.Close();
// 清空命令参数
dbCommand.Parameters.Clear();
return(dataTable, dbParameters);
}
/// <summary>
/// 执行 Sql 操作
/// </summary>
/// <param name="sqlProxyMethod">代理方法</param>
/// <returns></returns>
private static object ExecuteSql(SqlProxyMethod sqlProxyMethod)
{
// 获取 ADO.NET 数据库操作对象
var database = sqlProxyMethod.Context.Database;
// 定义多次使用变量
var returnType = sqlProxyMethod.ReturnType;
var sql = sqlProxyMethod.FinalSql;
var parameterModel = sqlProxyMethod.ParameterModel;
var commandType = sqlProxyMethod.CommandType;
// 处理 DataSet 返回值
if (returnType == typeof(DataSet))
{
var(dataSet, _) = database.DataAdapterFill(sql, parameterModel, commandType);
return(dataSet);
}
// 处理 无返回值
else if (returnType == typeof(void))
{
var(rowEffects, _) = database.ExecuteNonQuery(sql, parameterModel, commandType);
return(rowEffects);
}
// 处理 元组类型 返回值
else if (returnType.IsValueTuple())
{
var(dataSet, _) = database.DataAdapterFill(sql, parameterModel, commandType);
var result = dataSet.ToValueTuple(returnType);
return(result);
}
// 处理 基元类型 返回值
else if (returnType.IsRichPrimitive())
{
var(result, _) = database.ExecuteScalar(sql, parameterModel, commandType);
return(result.ChangeType(returnType));
}
// 处理 存储过程带输出类型 返回值
else if (returnType == typeof(ProcedureOutputResult) || (returnType.IsGenericType && typeof(ProcedureOutputResult <>).IsAssignableFrom(returnType.GetGenericTypeDefinition())))
{
var(dataSet, dbParameters) = database.DataAdapterFill(sql, parameterModel, commandType);
// 处理返回值
var result = !returnType.IsGenericType
? DbHelpers.WrapperProcedureOutput(database.ProviderName, dbParameters, dataSet)
: DbHelpers.WrapperProcedureOutput(database.ProviderName, dbParameters, dataSet, returnType.GenericTypeArguments.First());
return(result);
}
else
{
var(dataTable, _) = database.ExecuteReader(sql, parameterModel, commandType);
// 处理 DataTable 返回值
if (returnType == typeof(DataTable))
{
return(dataTable);
}
else
{
var list = dataTable.ToList(returnType);
return(list);
}
}
}
/// <summary>
/// 获取代理方法信息
/// </summary>
/// <param name="method">方法</param>
/// <param name="args">参数列表</param>
/// <returns>SqlProxyMethod</returns>
private SqlProxyMethod GetProxyMethod(MethodInfo method, object[] args)
{
// 判断方法是否贴了注解
if (!method.IsDefined(typeof(SqlProxyAttribute), true))
{
throw new InvalidOperationException("The method is missing the [SqlProxy] annotation.");
}
// 获取 Sql 代理特性
var sqlProxyAttribute = method.GetCustomAttribute <SqlProxyAttribute>(true);
// 获取方法真实返回值类型
var returnType = method.GetMethodRealReturnType();
// 获取数据库上下文
var dbContext = GetDbContext(sqlProxyAttribute.DbContextLocator);
// 转换方法参数
var parameters = CombineDbParameter(method, args);
// 定义最终 Sql 语句
string finalSql;
var commandType = CommandType.Text;
// 如果是存储过程类型
if (sqlProxyAttribute is SqlProcedureAttribute sqlProduceAttribute)
{
finalSql = sqlProduceAttribute.Name;
commandType = CommandType.StoredProcedure;
}
// 如果是函数类型
else if (sqlProxyAttribute is SqlFunctionAttribute sqlFunctionAttribute)
{
finalSql = DbHelpers.GenerateFunctionSql(dbContext.Database.ProviderName,
returnType.IsRichPrimitive() ? DbFunctionType.Scalar : DbFunctionType.Table,
sqlFunctionAttribute.Name,
parameters);
}
// 如果是纯Sql类型
else if (sqlProxyAttribute is SqlExecuteAttribute sqlExecuteAttribute)
{
finalSql = sqlExecuteAttribute.Sql;
commandType = sqlExecuteAttribute.CommandType;
}
else
{
throw new NotSupportedException($"{sqlProxyAttribute.GetType().FullName} is an invalid annotation.");
}
// 解析方法参数及参数值并渲染模板
var methodParameterInfos = method.GetParameters().Select((u, i) => new MethodParameterInfo
{
Parameter = u,
Name = u.Name,
Value = args[i]
});
// 渲染模板
finalSql = finalSql.Render(methodParameterInfos.ToDictionary(u => u.Name, u => u.Value));
// 返回
return(new SqlProxyMethod
{
ParameterModel = parameters,
Context = dbContext,
ReturnType = returnType,
IsAsync = method.IsAsync(),
CommandType = commandType,
FinalSql = finalSql
});
}
/// <summary>
/// 根据多个键查询一条记录
/// </summary>
/// <param name="keyValues">多个键</param>
/// <param name="cancellationToken">取消异步令牌</param>
/// <returns>数据库中的实体</returns>
public virtual async Task <TEntity> FindAsync(object[] keyValues, CancellationToken cancellationToken = default)
{
var entity = await FindOrDefaultAsync(keyValues, cancellationToken);
return(entity ?? throw DbHelpers.DataNotFoundException());
}
/// <summary>
/// 根据多个键查询一条记录
/// </summary>
/// <param name="keyValues">多个键</param>
/// <returns>数据库中的实体</returns>
public virtual async Task <TEntity> FindAsync(params object[] keyValues)
{
var entity = await FindOrDefaultAsync(keyValues);
return(entity ?? throw DbHelpers.DataNotFoundException());
}
/// <summary>
/// 根据多个键查询一条记录
/// </summary>
/// <param name="keyValues">多个键</param>
/// <returns>数据库中的实体</returns>
public virtual TEntity Find(params object[] keyValues)
{
var entity = FindOrDefault(keyValues) ?? throw DbHelpers.DataNotFoundException();
return(entity);
}
/// <summary>
/// 根据键查询一条记录
/// </summary>
/// <param name="key">键</param>
/// <returns>数据库中的实体</returns>
public virtual TEntity Find(object key)
{
var entity = FindOrDefault(key) ?? throw DbHelpers.DataNotFoundException();
return(entity);
}
/// <summary>
/// 执行 Sql 操作
/// </summary>
/// <param name="sqlProxyMethod">代理方法</param>
/// <returns></returns>
private static async Task <T> ExecuteSqlOfTAsync <T>(SqlProxyMethod sqlProxyMethod)
{
// 获取 ADO.NET 数据库操作对象
var database = sqlProxyMethod.Context.Database;
// 定义多次使用变量
var returnType = sqlProxyMethod.ReturnType;
var sql = sqlProxyMethod.FinalSql;
var parameterModel = sqlProxyMethod.ParameterModel;
var commandType = sqlProxyMethod.CommandType;
// 处理 DataSet 返回值
if (returnType == typeof(DataSet))
{
var(dataSet, _) = await database.DataAdapterFillAsync(sql, parameterModel, commandType);
return((T)(dataSet as object));
}
// 处理 元组类型 返回值
else if (returnType.IsValueTuple())
{
var(dataSet, _) = await database.DataAdapterFillAsync(sql, parameterModel, commandType);
var result = dataSet.ToValueTuple(returnType);
return((T)result);
}
// 处理 基元类型 返回值
else if (returnType.IsRichPrimitive())
{
var(result, _) = await database.ExecuteScalarAsync(sql, parameterModel, commandType);
return((T)result);
}
// 处理 存储过程带输出类型 返回值
else if (returnType == typeof(ProcedureOutputResult) || (returnType.IsGenericType && typeof(ProcedureOutputResult <>).IsAssignableFrom(returnType.GetGenericTypeDefinition())))
{
var(dataSet, dbParameters) = await database.DataAdapterFillAsync(sql, parameterModel, commandType);
// 处理返回值
var result = !returnType.IsGenericType
? DbHelpers.WrapperProcedureOutput(dbParameters, dataSet)
: DbHelpers.WrapperProcedureOutput(dbParameters, dataSet, returnType.GenericTypeArguments.First());
return((T)result);
}
else
{
var(dataTable, _) = await database.ExecuteReaderAsync(sql, parameterModel, commandType);
// 处理 DataTable 返回值
if (returnType == typeof(DataTable))
{
return((T)(dataTable as object));
}
else
{
var list = await dataTable.ToListAsync(returnType);
return((T)list);
}
}
}
/// <summary>
/// 设置数据库命令对象参数
/// </summary>
/// <param name="providerName"></param>
/// <param name="dbCommand">数据库命令对象</param>
/// <param name="model">参数模型</param>
/// <param name="dbParameters">命令参数</param>
private static void SetDbParameters(string providerName, ref DbCommand dbCommand, object model, out DbParameter[] dbParameters)
{
dbParameters = DbHelpers.ConvertToDbParameters(model, dbCommand);
SetDbParameters(providerName, ref dbCommand, dbParameters);
}
/// <summary>
/// 根据主键删除一条记录
/// </summary>
/// <param name="key">主键</param>
public virtual void DeleteExists(object key)
{
var entity = FindOrDefault(key) ?? throw DbHelpers.DataNotFoundException();
Delete(entity);
}