using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Dynamic;
|
using System.Threading.Tasks;
|
using Microsoft.Extensions.Configuration;
|
using Oracle.ManagedDataAccess.Client;
|
|
namespace gdbg.Services
|
{
|
public class OracleDbService
|
{
|
private readonly string _connectionString;
|
|
public OracleDbService(IConfiguration configuration)
|
{
|
_connectionString = configuration.GetConnectionString("OracleDB");
|
}
|
|
// 通用的获取数据方法（执行 SELECT 语句），不带参数
|
public async Task<List<T>> GetAsync<T>(string query, Func<IDataReader, T> mapFunction)
|
{
|
return await GetAsync(query, mapFunction, null); // 调用带参数的重载方法
|
}
|
|
// 通用的获取数据方法（执行 SELECT 语句），支持带参数
|
public async Task<List<T>> GetAsync<T>(string query, Func<IDataReader, T> mapFunction, List<OracleParameter> parameters)
|
{
|
var results = new List<T>();
|
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
await connection.OpenAsync();
|
|
using (var command = new OracleCommand(query, connection))
|
{
|
// 添加参数到命令
|
if (parameters != null)
|
{
|
command.Parameters.AddRange(parameters.ToArray());
|
}
|
|
using (var reader = await command.ExecuteReaderAsync())
|
{
|
while (await reader.ReadAsync())
|
{
|
results.Add(mapFunction(reader));
|
}
|
}
|
}
|
}
|
|
return results;
|
}
|
// 执行存储过程并返回结果
|
public async Task<(List<ExpandoObject>[] results, Dictionary<string, object> outputParams)> ExecuteStoredProcedureAsync(
|
string procedureName,
|
OracleParameter[] parameters,
|
params string[] cursorParamNames)
|
{
|
var results = new List<ExpandoObject>[cursorParamNames.Length];
|
var outputParams = new Dictionary<string, object>();
|
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
await connection.OpenAsync();
|
|
using (var command = new OracleCommand(procedureName, connection))
|
{
|
command.CommandType = CommandType.StoredProcedure;
|
|
// 添加所有输入参数
|
if (parameters != null)
|
{
|
command.Parameters.AddRange(parameters);
|
}
|
|
// 添加游标输出参数
|
for (int i = 0; i < cursorParamNames.Length; i++)
|
{
|
var cursorParam = new OracleParameter(cursorParamNames[i], OracleDbType.RefCursor)
|
{
|
Direction = ParameterDirection.Output
|
};
|
command.Parameters.Add(cursorParam);
|
}
|
|
// 执行存储过程
|
await command.ExecuteNonQueryAsync();
|
|
// 读取每个游标输出
|
for (int i = 0; i < cursorParamNames.Length; i++)
|
{
|
using (var reader = command.Parameters[cursorParamNames[i]].Value as OracleDataReader)
|
{
|
var cursorResults = new List<ExpandoObject>();
|
while (await reader.ReadAsync())
|
{
|
dynamic expando = new ExpandoObject();
|
var expandoDict = (IDictionary<string, object>)expando;
|
|
for (int j = 0; j < reader.FieldCount; j++)
|
{
|
string columnName = reader.GetName(j);
|
object columnValue = reader.IsDBNull(j) ? null : reader.GetValue(j);
|
expandoDict[columnName] = columnValue;
|
}
|
|
cursorResults.Add(expando);
|
}
|
results[i] = cursorResults;
|
}
|
}
|
|
// 获取输出参数的值
|
foreach (var param in parameters)
|
{
|
if (param.Direction == ParameterDirection.Output)
|
{
|
outputParams[param.ParameterName] = param.Value;
|
}
|
}
|
}
|
}
|
|
return (results, outputParams);
|
}
|
|
|
|
// 执行存储过程的方法
|
public async Task ExecuteProcedureAsync(string procedureCall, List<OracleParameter> parameters)
|
{
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
await connection.OpenAsync();
|
|
using (var command = new OracleCommand(procedureCall, connection))
|
{
|
command.CommandType = CommandType.Text; // 使用文本命令来调用存储过程
|
if (parameters != null)
|
{
|
command.Parameters.AddRange(parameters.ToArray());
|
}
|
|
await command.ExecuteNonQueryAsync(); // 执行存储过程
|
}
|
}
|
}
|
// 通用的插入数据方法（执行 INSERT 语句）
|
public async Task<int> InsertAsync(string query, List<OracleParameter> parameters)
|
{
|
return await ExecuteNonQueryAsync(query, parameters);
|
}
|
|
// 通用的更新数据方法（执行 UPDATE 语句）
|
public async Task<int> UpdateAsync(string query, List<OracleParameter> parameters)
|
{
|
return await ExecuteNonQueryAsync(query, parameters);
|
}
|
|
// 通用的删除数据方法（执行 DELETE 语句）
|
public async Task<int> DeleteAsync(string query, List<OracleParameter> parameters)
|
{
|
return await ExecuteNonQueryAsync(query, parameters);
|
}
|
|
// 执行非查询语句的方法（INSERT, UPDATE, DELETE）
|
private async Task<int> ExecuteNonQueryAsync(string query, List<OracleParameter> parameters)
|
{
|
using (var connection = new OracleConnection(_connectionString))
|
{
|
await connection.OpenAsync();
|
|
using (var command = new OracleCommand(query, connection))
|
{
|
if (parameters != null)
|
{
|
command.Parameters.AddRange(parameters.ToArray());
|
}
|
|
return await command.ExecuteNonQueryAsync();
|
}
|
}
|
}
|
}
|
}
|
