/// <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>
/// 设置数据库命令对象参数
/// </summary>
/// <param name="dbCommand">数据库命令对象</param>
/// <param name="model">参数模型</param>
/// <param name="dbParameters">命令参数</param>
private static void SetDbParameters(ref DbCommand dbCommand, object model, out DbParameter[] dbParameters)
{
dbParameters = DbHelpers.ConvertToDbParameters(model, dbCommand);
SetDbParameters(ref dbCommand, dbParameters);
}
/// <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 Oops.Oh("The method is missing the [SqlProxy] annotation", typeof(InvalidOperationException));
}
// 获取 Sql 代理特性
var sqlProxyAttribute = method.GetCustomAttribute <SqlProxyAttribute>(true);
// 判断是否是异步方法
var isAsyncMethod = method.IsAsync();
// 获取类型返回值并处理 Task 和 Task<T> 类型返回值
var returnType = method.ReturnType;
returnType = isAsyncMethod ? (returnType.GenericTypeArguments.FirstOrDefault() ?? typeof(void)) : returnType;
// 获取数据库上下文
var dbContext = GetDbContext(sqlProxyAttribute.DbContextLocator);
// 转换方法参数
var parameters = method.GetParameters().ToSqlParameters(args);
// 定义最终 Sql 语句
string finalSql;
var commandType = CommandType.Text;
// 如果是存储过程类型
if (sqlProxyAttribute is SqlProduceAttribute sqlProduceAttribute)
{
finalSql = sqlProduceAttribute.Name;
commandType = CommandType.StoredProcedure;
}
// 如果是函数类型
else if (sqlProxyAttribute is SqlFunctionAttribute sqlFunctionAttribute)
{
finalSql = DbHelpers.CombineFunctionSql(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 Oops.Oh($"{sqlProxyAttribute.GetType().FullName} is an invalid annotation", typeof(NotSupportedException));
}
// 返回
return(new SqlProxyMethod
{
SqlParameters = parameters,
DbContext = dbContext,
ReturnType = returnType,
IsAsync = isAsyncMethod,
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>
/// 根据主键删除一条记录
/// </summary>
/// <param name="key">主键</param>
public virtual void DeleteExists(object key)
{
var entity = FindOrDefault(key) ?? throw DbHelpers.DataNotFoundException();
Delete(entity);
}
