namespace TJ.HIS.INSUR.AnHui.DBHelper
{
/// <summary>
/// Devart联接DB客户端
/// </summary>
public class DBHelperDevart
{
private static string _connectionString;
/// <summary>
/// 连接字符串
/// </summary>
public string ConnectionString
{
get
{
if (string.IsNullOrEmpty(_connectionString))
{
InitConnectionString();
}
return _connectionString;
}
set
{
_connectionString = value;
}
}
private OracleConnection _connection;
/// <summary>
/// 连接对象，打开状态
/// </summary>
public OracleConnection Connection
{
get
{
if (_connection == null)
{
_connection = GetConnection();
}
return _connection;
}
}
private OracleTransaction _trans = null;
/// <summary>
/// 事务对象, 跨连接的事务，第二个连接不是用BeginTransaction开始，而是对连接对象赋值。
/// </summary>
public OracleTransaction Transaction
{
get
{
return _trans;
}
set
{
if (value == null)
{
throw new Exception("Set Transaction Property： 参数错误。");
}
if (_isTransaction || _trans != null)
{
throw new Exception("Set Transaction Property：已经开始事务！");
}
_isTransaction = true;
_trans = value;
}
}
private bool _isTransaction = false;
/// <summary>
/// 连接是否启用事务
/// </summary>
public bool IsTransaction { get { return _isTransaction; } }
private int _commandTimeout = 180;
/// <summary>
/// Command对象执行超时时间，默认180秒
/// </summary>
public int CommandTimeout
{
get
{
return _commandTimeout;
}
set
{
_commandTimeout = value;
}
}
public DateTime DbServerTime
{
get
{
DateTime dtmServerTime;
try
{
var obj = ExecuteScalar("SELECT SYSDATE FROM DUAL");
dtmServerTime = Convert.ToDateTime(obj);
}
catch (Exception)
{
dtmServerTime = DateTime.Now;
}
return dtmServerTime;
}
}
/// <summary>
/// 构造函数
/// </summary>
public DBHelperDevart(Boolean bForceInit = false)
{
InitConnectionString(bForceInit);
}
/// <summary>
/// 构造函数
/// </summary>
public DBHelperDevart(string connectString)
{
if (!string.IsNullOrEmpty(connectString))
{
_connectionString = connectString;
}
else
{
InitConnectionString();
}
}
/// <summary>
/// 析构函数
/// </summary>
~DBHelperDevart()
{
try
{
if (_isTransaction)
{
RollBack();
}
//if (_dataAdapter != null)
//{
// _dataAdapter?.Dispose();
// _dataAdapter = null;
//}
//if (_command != null)
//{
// _command.Dispose();
// _command = null;
//}
if (_connection != null)
{
if (_connection.State != ConnectionState.Closed)
{
_connection.Close();
}
_connection.Dispose();
_connection = null;
}
}
catch (Exception)
{
//throw;
}
GC.Collect();
}
/// <summary>
/// 初始化连接字符串
/// </summary>
private void InitConnectionString(Boolean bForceInit = false)
{
if (string.IsNullOrEmpty(_connectionString) || bForceInit)
{
//Devart联接时，检查DBServerName(通过oracle客户端设置的数据库联接别名)
//if (string.IsNullOrEmpty(His00GlobalVars.DBAliasName))
// throw new Exception("InitConnectionString：DBAliasName没有定义！");
生成联接串
//if (InsurGlobalVar.DbCharsetUS7ASCII)
//{
// _connectionString = $"Data Source={InsurGlobalVar.DBAliasName};Persist Security Info=True;User ID={InsurGlobalVar.strDBUserID};Password={InsurGlobalVar.strDBPassword};Max Pool Size=10; Min Pool Size=1;Unicode=false;oci session pool min size=1";
//}
//else
//{
// _connectionString = $"Data Source={InsurGlobalVar.DBAliasName};Persist Security Info=True;User ID={InsurGlobalVar.strDBUserID};Password={InsurGlobalVar.strDBPassword};Max Pool Size=10; Min Pool Size=1;Unicode=true;oci session pool min size=1";
//}
OracleConnectionStringBuilder ocsb = new OracleConnectionStringBuilder();
ocsb.Add("Data Source", GlobalInsurVariables.Instance.DBAliasName);
ocsb.Add("Persist Security Info", "True");
ocsb.UserId = GlobalInsurVariables.Instance.strDBUserID;
ocsb.Password = GlobalInsurVariables.Instance.strDBPassword;
ocsb.MinPoolSize = 2;
ocsb.MaxPoolSize = 5;
ocsb.OciSessionPoolMinSize = 2;
ocsb.OciSessionPoolMaxSize = 5;
ocsb.Unicode = !GlobalInsurVariables.Instance.DbCharsetUS7ASCII;
ocsb.Pooling = false;
_connectionString = ocsb.ConnectionString;
}
}
/// <summary>
/// 创建新的OracleCommand对象
/// </summary>
/// <returns></returns>
public OracleCommand CreateCommand()
{
OracleCommand cmd = Connection.CreateCommand();
cmd.CommandTimeout = _commandTimeout;
if (_trans != null)
{
cmd.Transaction = _trans;
}
OpenConn();
return cmd;
}
/// <summary>
/// 获取一个独立的连接，不是DBConn对象的连接
/// </summary>
/// <param name="connString"></param>
/// <returns></returns>
public OracleConnection GetConnection(string connString = "")
{
OracleConnection conn = new OracleConnection();
if (string.IsNullOrEmpty(connString))
{
if (string.IsNullOrEmpty(ConnectionString))
{
throw (new Exception("没有设置连接字符串！"));
}
connString = _connectionString;
}
conn.ConnectionString = _connectionString;
return conn;
}
/// <summary>
/// 打开连接
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
public bool OpenConn(OracleConnection conn = null)
{
bool isok = false;
if (conn == null)
{
conn = Connection;
if (conn == null)
{
throw new Exception("OpenConn：参数错误！");
}
}
if (conn.State == ConnectionState.Open)
{
return true;
}
try
{
conn.Open();
if (conn.State == ConnectionState.Open)
{
isok = true;
}
}
catch (Exception ex)
{
throw ex;
}
return isok;
}
/// <summary>
/// 关闭连接
/// </summary>
/// <returns></returns>
public bool CloseConn()
{
if (_connection == null)
{
throw new Exception("没有建立连接！");
}
if (_connection.State == ConnectionState.Open)
{
if (!_isTransaction)
{
_connection.Close();
}
else
{
throw new Exception("请先提交或者回滚事务！");
}
}
else
{
_isTransaction = false;
}
return true;
}
/// <summary>
/// 开始事务
/// </summary>
/// <returns>true成功， false失败</returns>
public OracleTransaction BeginTransaction()
{
if (_isTransaction)
{
throw new Exception("BeginTransaction：已经开始事务！");
}
if (_connection == null)
{
_connection = null;
_connection = GetConnection();
}
OpenConn();
_trans = _connection.BeginTransaction();
_isTransaction = true;
return _trans;
}
/// <summary>
/// 提交事务
/// </summary>
/// <returns>true成功， false失败</returns>
public bool Commit()
{
try
{
_trans.Commit();
_connection.Close();
}
catch (Exception)
{
}
_trans = null;
_isTransaction = false;
return true;
}
/// <summary>
/// 回滚事务
/// </summary>
/// <returns>true成功， false失败</returns>
public bool RollBack()
{
try
{
_trans.Rollback();
_connection.Close();
}
catch (Exception)
{
}
_trans = null;
_isTransaction = false;
return true;
}
/// <summary>
/// 执行SQL语句，返回影响行数
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <returns>int 影响行数</returns>
///
public int Execute(string strSQL)
{
try
{
OpenConn();
OracleCommand cmd = CreateCommand();
if (cmd.Parameters.Count > 0) cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
int iCount = cmd.ExecuteNonQuery();
if (!_isTransaction) CloseConn();
return iCount;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行SQL：" + strSQL;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
/// <summary>
/// 执行带参数的SQL语句，返回影响行数
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="parameters">参数表</param>
/// <returns></returns>
public int Execute(string strSQL, OracleParameter[] parameters)
{
try
{
OpenConn();
OracleCommand cmd = CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
if (cmd.Parameters.Count > 0) cmd.Parameters.Clear();
foreach (OracleParameter item in parameters)
{
if (item.Value == null)
item.Value = DBNull.Value;
}
cmd.Parameters.AddRange(parameters);
int count = cmd.ExecuteNonQuery();
if (!_isTransaction) CloseConn();
return count;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行SQL：" + strSQL;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
/// <summary>
/// 执行只有一个结果的SQL语句
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <returns></returns>
public object ExecuteScalar(string strSQL)
{
try
{
OpenConn();
OracleCommand cmd = CreateCommand();
if (cmd.Parameters.Count > 0) cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
var o = cmd.ExecuteScalar();
if (!_isTransaction) CloseConn();
return o;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行SQL：" + strSQL;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
/// <summary>
/// 执行SQL List，成功提交，失败回滚
/// </summary>
/// <param name="listSQL">SQL语句List</param>
/// <returns></returns>
public int ExecuteWithTrans(List<string> listSQL)
{
if (listSQL == null || listSQL.Count < 1) return 0;
BeginTransaction();
int iRowCount = 0;
try
{
foreach (string strSQL in listSQL)
{
if (string.IsNullOrEmpty(strSQL)) continue;
iRowCount += Execute(strSQL);
}
Commit();
}
catch (Exception ex)
{
RollBack();
throw ex;
}
return iRowCount;
}
/// <summary>
/// 通过SQL语句获取DataTable,带参数
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="parameters">参数表</param>
/// <param name="bCaseSensitive">大小写敏感</param>
/// <returns></returns>
public DataTable GetDataTable(string strSQL, OracleParameter[] parameters, Boolean bCaseSensitive = false)
{
try
{
DataTable dtResult = null;
OpenConn();
OracleCommand cmd = CreateCommand();
if (cmd.Parameters.Count > 0) cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
cmd.Parameters.AddRange(parameters);
using (OracleDataReader dataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
dtResult = GenDataTableSchema(dataReader);
dtResult.CaseSensitive = bCaseSensitive;
dataReader.Close();
}
OracleDataAdapter adapter = new OracleDataAdapter("", Connection);
adapter.AcceptChangesDuringFill = true;
adapter.SelectCommand = cmd;
adapter.Fill(dtResult);
if (!_isTransaction) CloseConn();
return dtResult;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行SQL：" + strSQL;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
/// <summary>
/// 通过SQL语句获取DataTable
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="bCaseSensitive">大小写敏感</param>
/// <returns></returns>
public DataTable GetDataTable(string strSQL, Boolean bCaseSensitive = false)
{
try
{
DataTable dtResult = null;
OpenConn();
OracleCommand cmd = CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
using (OracleDataReader dataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
dtResult = GenDataTableSchema(dataReader);
dtResult.CaseSensitive = bCaseSensitive;
dataReader.Close();
}
OracleDataAdapter adapter = new OracleDataAdapter("", Connection);
adapter.AcceptChangesDuringFill = true;
adapter.SelectCommand = cmd;
adapter.Fill(dtResult);
if (!_isTransaction) CloseConn();
return dtResult;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行SQL：" + strSQL;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
/// <summary>
/// 通过SQL语句获取带主键的DataTable
/// </summary>
/// <param name="strSQL">SQL语句</param>
/// <param name="bCaseSensitive">大小写敏感</param>
/// <returns></returns>
public DataTable GetDataTableWithKey(string strSQL, Boolean bCaseSensitive = false)
{
try
{
DataTable dtResult = null;
OpenConn();
OracleCommand cmd = CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
using (OracleDataReader dataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
dtResult = GenDataTableSchema(dataReader, true);
dtResult.CaseSensitive = bCaseSensitive;
dataReader.Close();
}
//为什么要新一个？因为DataAdapter的属性变了
OracleDataAdapter dataAdapter = new OracleDataAdapter("", Connection);
dataAdapter.AcceptChangesDuringFill = true;
dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
dataAdapter.SelectCommand = cmd;
dataAdapter.Fill(dtResult);
if (!_isTransaction) CloseConn();
return dtResult;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行SQL：" + strSQL;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
/// <summary>
/// 查询数据
/// </summary>
/// <param name="sqlSel">查询语句</param>
/// <param name="tableName">表名</param>
/// <param name="ds">用于存储返回结果的数据集</param>
/// <param name="blnWithKey">是否返回主键</param>
/// <returns>空</returns>
public void SelectData(string sqlSel, string tableName, ref DataSet ds, bool blnWithKey)
{
// 清除原来的数据
if (ds.Tables.IndexOf(tableName) >= 0)
{
ds.Tables.Remove(tableName);
}
// 查询数据
bool blnInTrans = (Transaction != null); // 如果有外部事务
try
{
if (blnInTrans == false) OpenConn();
OracleCommand cmd = Connection.CreateCommand();
cmd.CommandText = sqlSel;
cmd.Connection = Connection;
cmd.Transaction = Transaction;
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
if (blnWithKey)
{
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
}
if (string.IsNullOrEmpty(tableName))
{
adapter.Fill(ds);
}
else
{
adapter.Fill(ds, tableName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (blnInTrans == false) CloseConn();
}
}
/// <summary>
/// 保存数据（护理）
/// </summary>
/// <param name="dsData">数据源DataSet</param>
/// <param name="strTableName">要更新的表名</param>
/// <param name="strSQL">获取数据源的Sql语句</param>
/// <returns>受影响的行数</returns>
public int SaveData(DataSet dsData, string strTableName, string strSQL)
{
if (strSQL.Length == 0)
{
strSQL = "SELECT * FROM " + strTableName + "where 1=2";
}
bool blnInTrans = false;
try
{
blnInTrans = (Transaction != null);
// 如果没有事务
if (blnInTrans == false) BeginTransaction();
try
{
OracleCommand cmd = Connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL;
cmd.Connection = Connection;
cmd.Transaction = Transaction;
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
//adapter.UpdateCommand = (DBCommand)cmd;
//adapter.ContinueUpdateOnError = true;
adapter.AcceptChangesDuringUpdate = false;
OracleCommandBuilder cmdBuilder = new OracleCommandBuilder(adapter);
cmdBuilder.ConflictOption = ConflictOption.OverwriteChanges;
int iResult = 0;
if (strTableName.Length == 0)
{
iResult = adapter.Update(dsData);
}
else
{
iResult = adapter.Update(dsData.Tables[strTableName]);
}
if (blnInTrans == false) Commit();
if (strTableName.Length == 0)
{
dsData.AcceptChanges();
}
else
{
dsData.Tables[strTableName].AcceptChanges();
}
return iResult;
}
catch (Exception ex)
{
if (blnInTrans == false) RollBack();
throw ex;
}
}
finally
{
//if (blnInTrans == false) CloseConn();
}
}
/// <summary>
/// 保存dataTable数据到数据库，selectCommandText选择字段中要求包含表的主键
/// </summary>
/// <param name="dtTableSource">dtTableSave</param>
/// <param name="strUpdateRuleSQL">strUpdateRuleSQL选择字段中要求包含表的主键或者唯一索引字段</param>
/// <param name="bWithKey">是否只比较主键或者唯一索引</param>
/// <returns></returns>
public int DataTableSave(DataTable dtTableSource, string strUpdateRuleSQL, bool bWithKey = false)
{
try
{
OpenConn();
//成功更新的行数
int iUpdateCount = 0;
OracleCommand cmd = CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = strUpdateRuleSQL;
cmd.Transaction = Transaction;
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
OracleCommandBuilder builder = new OracleCommandBuilder(adapter);
//只更新改变了的字段（SET字段）
builder.SetAllValues = false;
//全部比较(生成WHERE条件)
if (bWithKey)
builder.ConflictOption = ConflictOption.OverwriteChanges;
else
builder.ConflictOption = ConflictOption.CompareAllSearchableValues;
builder.DataAdapter = adapter;
//先删除
DataTable dtChanges = dtTableSource.GetChanges(DataRowState.Deleted);
if (dtChanges != null && dtChanges.Rows.Count > 0)
iUpdateCount += adapter.Update(dtChanges);
//再更新
dtChanges = dtTableSource.GetChanges(DataRowState.Modified);
if (dtChanges != null && dtChanges.Rows.Count > 0)
iUpdateCount += adapter.Update(dtChanges);
//最后插入
dtChanges = dtTableSource.GetChanges(DataRowState.Added);
if (dtChanges != null && dtChanges.Rows.Count > 0)
iUpdateCount += adapter.Update(dtChanges);
return iUpdateCount;
}
catch (Exception ex)
{
throw new Exception("表的更新出错了！\r\n错误信息：" + ex.Message);
}
}
/// <summary>
/// 读数据库中的BLOB，返回内容(Devart联接)
/// </summary>
/// <param name="strFieldName">BLOB字段</param>
/// <param name="strSelectSQL">查询SQL</param>
/// <param name="filebytes">BLOB内容byte[]</param>
/// <returns>成功与否</returns>
public Boolean ReadBolbToByte(string strFieldName, string strSelectSQL, ref byte[] filebytes)
{
OpenConn();
try
{
OracleCommand cmd = CreateCommand();
cmd.CommandText = strSelectSQL;
cmd.CommandType = CommandType.Text;
OracleDataReader dataReader = cmd.ExecuteReader();
if (!dataReader.HasRows)
{
CloseConn();
return false;
}
if (dataReader.Read())
{
if (dataReader[strFieldName] != DBNull.Value)
filebytes = (byte[])dataReader[strFieldName];
else
{
CloseConn();
return false;
}
}
else
return false;
}
catch (Exception ex)
{
Cs02MessageBox.ShowError("读数据出错了！\r\n错误信息：" + ex.Message);
return false;
}
finally
{
if (Connection != null && Connection.State == ConnectionState.Open)
CloseConn();
}
return true;
}
/// <summary>
/// 将BLOB，保存到DB的一个字段(Devart联接)
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strFieldName">字段名</param>
/// <param name="strWhere">SQL条件</param>
/// <param name="filebytes">Blob内容</param>
/// <returns></returns>
public Boolean SaveBolbToDB(string strTableName, string strFieldName, string strWhere, byte[] filebytes)
{
if (!IsTransaction) throw new Exception("请先开始事务，再进行保存！");
if (filebytes.Length < 1)
{
RollBack();
Cs02MessageBox.ShowError("要保存的内容为空，不能继续！");
return false;
}
OracleCommand cmd = CreateCommand();
try
{
//检查一下，有没有符合条件的记录
string strSelectSQL = " SELECT COUNT(*) FROM " + strTableName + " WHERE " + strWhere;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSelectSQL;
cmd.Parameters.Clear();
int iCount = Cs01Functions.CInt(cmd.ExecuteScalar());
if (iCount < 1)
{
RollBack();
Cs02MessageBox.ShowError("没有符合条件的记录，不能继续！\r\n" + strSelectSQL);
return false;
}
}
catch (Exception ex)
{
RollBack();
Cs02MessageBox.ShowError("数据库联接出错了！\r\n错误信息：" + ex.Message);
return false;
}
//生成更新SQL，准备进行更新
string strUpdateSQL = " UPDATE " + strTableName + " SET " + strFieldName
+ " = :blobContent WHERE " + strWhere;
OracleParameter param1 = new OracleParameter("blobContent", filebytes);
cmd.Parameters.Clear();
cmd.Parameters.Add(param1);
cmd.CommandType = CommandType.Text;
cmd.CommandText = strUpdateSQL;
try
{
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
RollBack();
Cs02MessageBox.ShowError("执行SQL出错了！\r\n错误信息：" + ex.Message);
return false;
}
}
/// <summary>
/// 生成SQL对应表的字段
/// </summary>
/// <param name="dataReader">数据读取对象</param>
/// <param name="bCheck">检查主键</param>
/// <returns></returns>
private DataTable GenDataTableSchema(OracleDataReader dataReader, bool bCheck = false)
{
List<DataColumn> keyColumns = new List<DataColumn>();
DataTable tartgetDataTable = new DataTable();
DataTable schemaTable = dataReader.GetSchemaTable();
foreach (DataRow row in schemaTable.Rows)
{
DataColumn dc = new DataColumn(row["ColumnName"].ToString());
Type type = Type.GetType(row["DataType"].ToString());
if (type.IsNumericType())
{
switch (type.ToString())
{
case "System.Int16":
type = typeof(Int32);
break;
case "System.Single":
type = typeof(decimal);
break;
case "System.Double":
type = typeof(decimal);
break;
default:
break;
}
}
dc.DataType = type;
if (bCheck)
{
dc.Unique = row["IsUnique"] != DBNull.Value && (bool)row["IsUnique"];
dc.AllowDBNull = row["AllowDBNull"] != DBNull.Value && (bool)row["AllowDBNull"];
if (row["IsKey"] != DBNull.Value && (bool)row["IsKey"])
{
keyColumns.Add(dc);
}
}
tartgetDataTable.Columns.Add(dc);
}
if (bCheck)
{
tartgetDataTable.PrimaryKey = keyColumns.ToArray();
}
return tartgetDataTable;
}
/// <summary>
/// 执行存储过程返回int
/// </summary>
/// <param name="strSPName">存储过程名</param>
/// <param name="parameters">参数数组</param>
/// <returns>返回bool</returns>
public int ExecuteSP(string strSPName, OracleParameter[] parameters)
{
try
{
OpenConn();
OracleCommand cmd = CreateCommand();
cmd.CommandText = strSPName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parameters);
int iResult = cmd.ExecuteNonQuery();
if (!_isTransaction) CloseConn();
return iResult;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行存储过程：" + strSPName;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
/// <summary>
/// 执行带OUT参数的存储过程，返回int
/// </summary>
/// <param name="strSPName">存储过程名</param>
/// <param name="parameters">参数数组</param>
/// <returns>返回int</returns>
public int ExecuteSPWithOut(string strSPName, ref OracleParameter[] parameters)
{
try
{
OpenConn();
OracleCommand cmd = CreateCommand();
cmd.CommandText = strSPName;
cmd.CommandType = CommandType.StoredProcedure;
//设置参数
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parameters);
//执行储存过程
int iResult = cmd.ExecuteNonQuery();
//取Out参数返回值
for (int i = 0; i < parameters.Length; i++)
{
if (parameters[i].Direction == ParameterDirection.InputOutput || parameters[i].Direction == ParameterDirection.Output)
{
parameters[i].Value = cmd.Parameters[i].Value;
}
}
if (!_isTransaction) CloseConn();
return iResult;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行存储过程：" + strSPName;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
/// <summary>
/// 执行存储过程返回DataTable
/// </summary>
/// <param name="strSPName">存储过程名</param>
/// <param name="parameters">参数数组</param>
/// <returns>返回DataTable</returns>
public DataTable ExecuteSPToDataTable(string strSPName, OracleParameter[] parameters)
{
try
{
OpenConn();
OracleCommand cmd = CreateCommand();
DataTable dtResult = new DataTable();
cmd.CommandText = strSPName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
cmd.Parameters.AddRange(parameters);
OracleDataAdapter adapter = new OracleDataAdapter(cmd);
adapter.Fill(dtResult);
if (!_isTransaction) CloseConn();
return dtResult;
}
catch (Exception ex)
{
string strErrmsg = ex.Message + "\r\n执行存储过程：" + strSPName;
Exception exception = new Exception(strErrmsg);
throw (exception);
}
}
}
}
11-23
489
489
05-31
403
403
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
