using
System;
using
System.Collections
.Generic;
using
System.Configuration;
using
System.Data;
using
System.Data.Common;
using
System.Data.SqlClient;
using
System.Linq;
using
Oracle.Managed
DataAccess
.Client;
using MySql
.Data.MySqlClient;
namespace StorehouseServer
.Helper
{
/// <summary>
/// 数据库Helper
/// </summary>
public class
DatabaseHelper : IDisposable
{
private DbConnection _connection;
private readonly
DatabaseType _
databaseType;
private string _connectionString;
private const int MaxRetryCount = 3;
private const int RetryDelayMs = 1000;
public enum
DatabaseType
{
SqlServer,
Oracle,
MySql
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="
databaseType">数据库类型</param>
/// <param name="connectionStringName">配置文件中连接字符串的名称</param>
/// <exception cref="ArgumentNullException">当connectionStringName为null或空时抛出</exception>
/// <exception cref="ConfigurationErrorsException">当连接字符串配置不存在时抛出</exception>
public
DatabaseHelper(
DatabaseType
databaseType, string connectionStringName)
{
if (string
.IsNullOrWhiteSpace(connectionStringName))
throw new ArgumentNullException(nameof(connectionStringName), "Connection string name cannot be null or empty
.");
_
databaseType =
databaseType;
var connectionSettings = ConfigurationManager
.ConnectionStrings[connectionStringName];
if (connectionSettings == null)
throw new ConfigurationErrorsException($"Connection string '{connectionStringName}' not found in configuration
.");
// 从配置文件中获取加密的连接字符串并解密
var encryptedConnString = connectionSettings
.ConnectionString;
if (string
.IsNullOrWhiteSpace(encryptedConnString))
throw new ConfigurationErrorsException($"Connection string '{connectionStringName}' is empty
.");
_connectionString = StringEncryptor
.Decrypt(encryptedConnString);
// 确保连接字符串包含连接池设置
EnsureConnectionPoolSettings();
}
/// <summary>
/// 确保连接字符串包含连接池设置
/// </summary>
private void EnsureConnectionPoolSettings()
{
// 根据不同数据库类型添加连接池优化参数
switch (_
databaseType)
{
case
DatabaseType
.SqlServer:
var sqlBuilder = new SqlConnectionStringBuilder(_connectionString)
{
Pooling = true,
MaxPoolSize = 100,
MinPoolSize = 5,
ConnectTimeout = 15
};
_connectionString = sqlBuilder
.ToString();
break;
case
DatabaseType
.Oracle:
var
oracleBuilder = new
OracleConnectionStringBuilder(_connectionString)
{
Pooling = true,
MaxPoolSize = 100,
MinPoolSize = 5,
ConnectionTimeout = 15
};
_connectionString =
oracleBuilder
.ToString();
break;
case
DatabaseType
.MySql:
var mysqlBuilder = new MySqlConnectionStringBuilder(_connectionString)
{
Pooling = true,
MaximumPoolSize = 100,
MinimumPoolSize = 5,
ConnectionTimeout = 15
};
_connectionString = mysqlBuilder
.ToString();
break;
}
}
/// <summary>
/// 初始化数据库连接
/// </summary>
private DbConnection InitializeConnection()
{
DbConnection connection;
switch (_
databaseType)
{
case
DatabaseType
.SqlServer:
connection = new SqlConnection(_connectionString);
break;
case
DatabaseType
.Oracle:
connection = new
OracleConnection(_connectionString);
break;
case
DatabaseType
.MySql:
connection = new MySqlConnection(_connectionString);
break;
default:
throw new ArgumentException("Unsupported
database type");
}
// 设置连接超时和重试策略
connection
.ConnectionString = _connectionString;
return connection;
}
/// <summary>
/// 获取数据库连接（带重试机制）
/// </summary>
public DbConnection GetConnection()
{
if (_connection == null)
{
_connection = InitializeConnection();
}
// 检查连接状态，必要时重新连接
if (_connection
.State != ConnectionState
.Open)
{
int retryCount = 0;
while (retryCount < MaxRetryCount)
{
try
{
_connection
.Open();
return _connection;
}
catch (DbException)
{
retryCount++;
if (retryCount >= MaxRetryCount)
throw;
System.Threading
.Thread
.Sleep(RetryDelayMs);
// 尝试重新初始化连接
try { _connection
.Dispose(); } catch { }
_connection = InitializeConnection();
}
}
}
return _connection;
}
/// <summary>
/// 创建新的数据库连接（不自动打开）
/// </summary>
public DbConnection CreateConnection()
{
DbConnection connection;
switch (_
databaseType)
{
case
DatabaseType
.SqlServer:
connection = new SqlConnection(_connectionString);
break;
case
DatabaseType
.Oracle:
connection = new
OracleConnection(_connectionString);
break;
case
DatabaseType
.MySql:
connection = new MySqlConnection(_connectionString);
break;
default:
throw new ArgumentException("Unsupported
database type");
}
return connection;
}
/// <summary>
/// 执行非查询SQL语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>受影响的行数</returns>
/// <exception cref="ArgumentNullException">当sql为null或空时抛出</exception>
public int ExecuteNonQuery(string sql, params DbParameter[] parameters)
{
if (string
.IsNullOrWhiteSpace(sql))
throw new ArgumentNullException(nameof(sql), "SQL statement cannot be null or empty
.");
using (var connection = GetConnection())
{
using (
DbCommand command = Create
Command(connection, sql, parameters))
{
return
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行查询并返回
DataTable
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>
DataTable结果</returns>
/// <exception cref="ArgumentNullException">当sql为null或空时抛出</exception>
public
DataTable Execute
DataTable(string sql, params DbParameter[] parameters)
{
if (string
.IsNullOrWhiteSpace(sql))
throw new ArgumentNullException(nameof(sql), "SQL statement cannot be null or empty
.");
using (var connection = GetConnection())
{
using (
DbCommand command = Create
Command(connection, sql, parameters))
{
using (Db
DataAdapter adapter = Create
DataAdapter(
command))
{
DataTable dt = new
DataTable();
adapter
.Fill(dt);
return dt;
}
}
}
}
/// <summary>
/// 执行查询并返回
DataTable（支持事务）
/// </summary>
public
DataTable Execute
DataTable(string sql, DbTransaction transaction, params DbParameter[] parameters)
{
if (string
.IsNullOrWhiteSpace(sql))
throw new ArgumentNullException(nameof(sql));
using (
DbCommand command = Create
Command(transaction
.Connection, sql, parameters))
{
command.Transaction = transaction;
using (Db
DataAdapter adapter = Create
DataAdapter(
command))
{
DataTable dt = new
DataTable();
adapter
.Fill(dt);
return dt;
}
}
}
/// <summary>
/// 执行查询并返回第一行第一列的值
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">参数</param>
/// <returns>结果</returns>
/// <exception cref="ArgumentNullException">当sql为null或空时抛出</exception>
public object ExecuteScalar(string sql, params DbParameter[] parameters)
{
if (string
.IsNullOrWhiteSpace(sql))
throw new ArgumentNullException(nameof(sql), "SQL statement cannot be null or empty
.");
using (var connection = GetConnection())
{
using (
DbCommand command = Create
Command(connection, sql, parameters))
{
return
command.ExecuteScalar();
}
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="procedureName">存储过程名称</param>
/// <param name="parameters">参数</param>
/// <returns>
DataTable结果</returns>
/// <exception cref="ArgumentNullException">当procedureName为null或空时抛出</exception>
public
DataTable ExecuteStoredProcedure(string procedureName, params DbParameter[] parameters)
{
if (string
.IsNullOrWhiteSpace(procedureName))
throw new ArgumentNullException(nameof(procedureName), "Procedure name cannot be null or empty
.");
using (var connection = GetConnection())
{
using (
DbCommand command = connection
.Create
Command())
{
command.CommandText = procedureName;
command.CommandType =
CommandType
.StoredProcedure;
if (parameters != null && parameters
.Length > 0)
{
foreach (var param in parameters)
{
if (param == null) continue;
command.Parameters
.Add(param);
}
}
using (Db
DataAdapter adapter = Create
DataAdapter(
command))
{
DataTable dt = new
DataTable();
adapter
.Fill(dt);
return dt;
}
}
}
}
/// <summary>
/// 创建数据库命令
/// </summary>
private
DbCommand Create
Command(DbConnection connection, string sql, params DbParameter[] parameters)
{
DbCommand command = connection
.Create
Command();
command.CommandText = sql;
command.CommandType =
CommandType
.Text;
if (parameters != null && parameters
.Length > 0)
{
foreach (var param in parameters)
{
if (param == null) continue;
command.Parameters
.Add(param);
}
}
return
command;
}
/// <summary>
/// 创建数据适配器
/// </summary>
private Db
DataAdapter Create
DataAdapter(
DbCommand command)
{
switch (_
databaseType)
{
case
DatabaseType
.SqlServer:
return new Sql
DataAdapter((Sql
Command)
command);
case
DatabaseType
.Oracle:
return new
OracleDataAdapter((
OracleCommand)
command);
case
DatabaseType
.MySql:
return new MySql
DataAdapter((MySql
Command)
command);
default:
throw new ArgumentException("Unsupported
database type");
}
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="name">参数名</param>
/// <param name="value">参数值</param>
/// <param name="dbType">参数类型</param>
/// <param name="direction">参数方向</param>
/// <returns>DbParameter实例</returns>
/// <exception cref="ArgumentNullException">当name为null或空时抛出</exception>
public DbParameter CreateParameter(string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection
.Input)
{
if (string
.IsNullOrWhiteSpace(name))
throw new ArgumentNullException(nameof(name), "Parameter name cannot be null or empty
.");
DbParameter parameter;
switch (_
databaseType)
{
case
DatabaseType
.SqlServer:
parameter = new SqlParameter(name, value ?? DBNull
.Value);
break;
case
DatabaseType
.Oracle:
parameter = new
OracleParameter(name, value ?? DBNull
.Value);
break;
case
DatabaseType
.MySql:
parameter = new MySqlParameter(name, value ?? DBNull
.Value);
break;
default:
throw new ArgumentException("Unsupported
database type");
}
parameter
.DbType = dbType;
parameter
.Direction = direction;
return parameter;
}
/// <summary>
/// 开始事务
/// </summary>
/// <returns>DbTransaction实例</returns>
public DbTransaction BeginTransaction()
{
var connection = GetConnection();
return connection
.BeginTransaction();
}
/// <summary>
/// 执行事务操作
/// </summary>
/// <param name="action">要执行的事务操作</param>
/// <exception cref="ArgumentNullException">当action为null时抛出</exception>
public void ExecuteInTransaction(Action<DbTransaction> action)
{
if (action == null)
throw new ArgumentNullException(nameof(action), "Action cannot be null
.");
using (var connection = GetConnection())
{
using (var transaction = connection
.BeginTransaction())
{
try
{
action(transaction);
transaction
.Commit();
}
catch
{
try
{
transaction
.Rollback();
}
catch (Exception ex)
{
// 记录日志或处理回滚异常
throw new Exception("Transaction rollback failed
.", ex);
}
throw;
}
}
}
}
/// <summary>
/// 释放资源
/// </summary>
public void
Dispose()
{
if (_connection != null)
{
try
{
if (_connection
.State != ConnectionState
.Closed)
{
_connection
.Close();
}
_connection
.Dispose();
}
catch
{
// 忽略释放过程中的异常
}
finally
{
_connection = null;
}
}
}
}
}
本文介绍了一个在线上遇到的Oracle错误ORA-0100:超出打开游标的最大数的问题。通过分析代码发现是在一个大约5000次的循环中未正确处理DbCommand的释放导致的问题，并给出了修改前后的代码对比。
扫一扫
1996
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
