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