/// <summary>
/// 释放连接
/// </summary>
/// <param name="disposing">是否释放</param>
protected virtual void Dispose(bool disposing)
{
lock (this)
{
if (disposing && !m_disposed)
{
if (ST != null)
{
try
{
ST.Rollback();
ST.Dispose();
ST = null;
}
catch
{ }
}
if (STConn != null)
{
try
{
if (STConn.State != ConnectionState.Closed)
{
STConn.Close();
}
STConn = null;
}
catch
{ }
}
m_disposed = true;
}
}
}
/// <summary>
/// 解放処理
/// </summary>
public void Dispose()
{
//まだDisposeが行われていないなら処理
if (!this.disposed)
{
//以降、解放処理
DisConnect();
if (ORA_Trans != null)
{
ORA_Trans.Dispose();
}
if (ORA_Command != null)
{
ORA_Command.Dispose();
}
if (ORA_Session != null)
{
ORA_Session.Dispose();
}
}
disposed = true;
GC.SuppressFinalize(this);
}
public void Commit()
{
tran.Commit();
if (tran != null)
{
tran.Dispose();
tran = null;
}
}
public void Commit()
{
if (_t2 != null)
{
_t2.Commit();
_t2.Dispose();
_t2 = null;
}
}
// Metodo para cerrar los objetos de la bdd.
public void cerrarConeccion()
{
if (transaccion != null)
{
transaccion.Dispose();
transaccion = null;
}
if (coneccion != null)
{
coneccion.Dispose();
coneccion = null;
}
}
/// <summary>
/// 递交事务
/// </summary>
public void CommitTransaction()
{
try
{
_oleTrans.Commit();
_oleTrans.Dispose();
_oleTrans = null;
}
catch (System.Exception ex)
{
throw new TechException(ex.Message, _connection, ex);
}
}
/// <summary>
/// 事物提交
/// </summary>
public void CommitTrans()
{
if (_oracleTran == null)
{
throw new NullReferenceException("没有事务可进行提交!");
}
else
{
_oracleTran.Commit();
_oracleTran.Dispose();
_oracleTran = null;
CloseConn();
}
}
public bool UpdateStatus(string p1, string p2)
{
bool flag = false;
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
string sql = "update zc_order_transit set orderstatus = :status, updateTime = :updateTime where id = :id";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(":status", p2);
cmd.Parameters.Add(":updateTime", DateTime.Now);
cmd.Parameters.Add(":id", p1);
cmd.ExecuteNonQuery();
tran.Commit();
flag = true;
}
catch (Exception ex)
{
tran.Rollback();
flag = false;
log.Error("服务器订单号删除订单失败", ex);
}
finally
{
tran.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
return(flag);
}
/// <summary>
/// 执行数据库事务查询操作,返回受影响的行数
/// </summary>
/// <param name="transaction">数据库事务对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前事务查询操作影响的数据行数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand command = new OracleCommand();
OracleConnection connection = transaction.Connection;
int result = 0;
try
{
PrepareCommand(command, connection, transaction, cmdType, cmdText, commandParameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
transaction.Dispose();
command.Dispose();
connection.Close();
connection.Dispose();
}
return result;
}
/// <summary>
/// 关闭事务
/// </summary>
public void CloseTrans()
{
if (pTrans != null)
{
pTrans.Dispose();
}
}
/// <summary>
/// 批量修改（常用：update、delete、insert）
/// </summary>
/// <param name="list_sql"></param>
/// <returns></returns>
public static int UpdateDatabase(List <string> list_sql)
{
using (OracleConnection conn = new OracleConnection(connStr))
{
using (OracleCommand cmd = new OracleCommand())
{
int num = 1;
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = tran;
try
{
for (int i = 0; i < list_sql.Count; i++)
{
cmd.CommandText = list_sql[i];
cmd.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
num = 0;
}
tran.Dispose();
//conn.Close();
return(num);
}
}
}
/// <summary>
/// Execute an OracleCommand (that returns no resultset) against an existing database transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new OracleParameter(":prodid", 24));
/// </remarks>
/// <param name="trans">an existing database transaction</param>
/// <param name="cmdType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="cmdText">the stored procedure name or PL/SQL command</param>
/// <param name="cmdParms">an array of OracleParamters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
{
int val = 0;
OracleCommand cmd = new OracleCommand();
try
{
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (Exception e)
{
throw e;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (trans != null)
{
trans.Dispose();
}
}
return(val);
}
public static int ExecutaSql(string Sql)
{
OracleTransaction Transacao = default(OracleTransaction);
OracleConnection Conexao = AbreConexao();
Transacao = Conexao.BeginTransaction();
int N = -1;
try
{
OracleCommand Comando = new OracleCommand();
Comando.Connection = Conexao;
Comando.CommandType = CommandType.Text;
Comando.CommandText = Sql;
Comando.Transaction = Transacao;
N = Comando.ExecuteNonQuery();
Transacao.Commit();
}
catch (Exception ex)
{
Transacao.Rollback();
throw new Exception("ConnectionOracleDAL.ExecutaSql(Sql): " + ex.Message);
}
finally
{
FechaConexao(Conexao);
Transacao.Dispose();
}
return(N);
}
/************************************************************
* Function name : SetQnAHitCnt
* Purpose : 질문&답변 읽은수 Update
* Input : string[] rParams
* Output : string
*************************************************************/
public string SetQnAHitCnt(string rParams)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection(); //base.CreateConnection("LMS");
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTransLMS;
try
{
OracleParameter[] oOraParams = new OracleParameter[2];
oOraParams[0] = base.AddParam("p_not_no", OracleType.Number, Convert.ToInt32(rParams));
oOraParams[1] = base.AddParam("p_in_table", OracleType.VarChar, "T_BOARD");
int j = base.Execute(db, CommandType.StoredProcedure, "pkg_lms_hitcount.lms_hitcount", oOraParams, xTransLMS);
xTransLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
xTransLMS.Rollback(); // Exception 발생시 롤백처리...
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTransLMS != null)
{
xTransLMS.Dispose();
}
}
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, "Propagate Policy");
if (rethrow)
{
throw;
}
}
return(xRtn);
}
public void UpdateCoordinates(SortedList list)
{
OracleTransaction dbTran = GetTransaction();;
long numRowsAffected = 0;
try
{
foreach (DBMapZone.Coordinate coord in list.Values)
{
numRowsAffected = ExecuteCommand(coord.GetSQL(), dbTran);
}
dbTran.Commit();
}
catch (Exception)
{
dbTran.Rollback();
}
finally
{
if (dbTran != null)
{
dbTran.Dispose();
dbTran = null;
}
}
}
/// <summary>
/// 执行数据库事务查询操作,返回结果集中位于第一行第一列的Object类型的值
/// </summary>
/// <param name="transaction">一个已存在的数据库事务对象</param>
/// <param name="commandType">命令类型</param>
/// <param name="commandText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前事务查询操作返回的结果集中位于第一行第一列的Object类型的值</returns>
public object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (transaction == null)
{
throw new ArgumentNullException("当前数据库事务不存在");
}
OracleConnection connection = transaction.Connection;
if (_connection == null)
{
throw new ArgumentException("当前事务所在的数据库连接不存在");
}
OracleCommand command = new OracleCommand();
object result = null;
try
{
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
result = command.ExecuteScalar();
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
transaction.Dispose();
command.Dispose();
}
return(result);
}
public bool SaveTransactionChange()
{
bool result = false;
try
{
if (BeginTransaction && _OracleTranscation != null)
{
_OracleTranscation.Commit();
result = true;
}
}
catch (Exception)
{
_OracleTranscation.Rollback();
result = false;
}
finally
{
if (_OracleTranscation != null)
{
_OracleTranscation.Dispose();
_OracleTranscation = null;
}
}
return(result);
}
public void setIdCodeToBranch(string code, ZcBranchTotal zcBranchTotal, string id)
{
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
string sql = "insert into zc_branchId_info (id,createTime, updateTime,branch_name_id, branch_selfcode) values (:id, :createTime, :updataTime,:branch_name, :branch_selfcode)";
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(":id", Guid.NewGuid().ToString());
cmd.Parameters.Add(":createTime", DateTime.Now);
cmd.Parameters.Add(":updateTime", DateTime.Now);
cmd.Parameters.Add(":branch_name", zcBranchTotal.Id);
cmd.Parameters.Add(":branch_selfcode", code);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
log.Error("上传系统编码信息发生异常", ex);
}
finally
{
tran.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTransaction()
{
try
{
if (_transation != null)
{
_transation.Commit();
_transation.Dispose();
this.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行增删改的sql
/// </summary>
/// <param name="zsgSql">update/insert/delete语句</param>
/// <param name="cmdParms">sql语句中的变量</param>
/// <param name="conn">数据库连接</param>
/// <param name="transaction">事务（可选：
/// 传入非空则事务由传入方控制提交;
/// 为空则自建事务直接提交</param>
/// <param name="commit">事务是否提交（可选：默认执行后就提交</param>
/// <returns></returns>
public static int ExecuteSql(string zsgSql, OracleParameter[] cmdParams, OracleConnection conn, OracleTransaction transaction = null, bool commit = true)
{
OracleTransaction trans = transaction ?? conn.BeginTransaction();
using (OracleCommand cmd = new OracleCommand())
{
try
{
PrepareCommand(zsgSql, cmdParams, cmd, CommandType.Text, conn, trans);
int affectedCounts = cmd.ExecuteNonQuery();
if (transaction == null || commit)
{
trans.Commit();
}
cmd.Parameters.Clear();
return(affectedCounts);
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
cmd.Dispose();
if (transaction == null)
{
trans.Dispose();
}
}
}
}
public void DisposeTransaction(OracleTransaction trans)
{
try
{
if (OracleDBConnection.State == ConnectionState.Open)
{
trans.Dispose();
OracleDBConnection.Close();
}
}
catch (ApplicationException ex)
{
MessageBox.Show(ex.Message);
}
catch (FormatException ex)
{
MessageBox.Show(ex.Message);
}
catch (OracleException ex)
{
MessageBox.Show(ex.Message);
}
catch (InvalidOperationException ex)
{
MessageBox.Show(ex.Message);
}
}
public bool DeleteById(string id)
{
bool flag = false;
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
string sql = "delete from zc_order_transit where id = :id";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(":id", id);
cmd.ExecuteNonQuery();
tran.Commit();
flag = true;
}
catch (Exception ex)
{
tran.Rollback();
flag = false;
log.Error("服务器订单号删除订单失败", ex);
}
finally
{
tran.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
return(flag);
}
/// <summary>
/// 上传交班信息
/// </summary>
/// <param name="obj"></param>
public void AddDeliverAmountInfo(DeliverAmountInfo obj)
{
string sql = "insert into zc_deliver_amountinfo (id, createTime, updateTime, amountcashier, branch_id, exchangedate, salesman) values (:id, :createTime, :updateTime, :amount, :branchId, :exdate, :salesman)";
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.Parameters.Add(":id", obj.Id);
cmd.Parameters.Add(":createTime", obj.CreateTime);
cmd.Parameters.Add(":updateTime", obj.UpdateTime);
cmd.Parameters.Add(":amount", obj.AmountCashier);
cmd.Parameters.Add(":branchId", obj.BranchId);
cmd.Parameters.Add(":exdate", obj.Date);
cmd.Parameters.Add(":salesman", obj.Salesman);
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
log.Error("上传交班信息到服务器失败", ex);
}
finally
{
tran.Dispose();
cmd.Dispose();
OracleUtil.CloseConn(conn);
}
}
//更新
public void UpdateStatus(object id)
{
string sql = "update ZC_SORTE set AUDITS_TATUS = '4' where id = '" + id + "'";
OracleConnection conn = null;
OracleCommand cmd = new OracleCommand();
OracleTransaction tran = null;
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
//cmd.Parameters.Add(":status", obj.OrderStatus);
//cmd.Parameters.Add(":id", obj.Id);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
tran.Commit();
}
catch (Exception ex)
{
tran.Rollback();
}
finally
{
cmd.Dispose();
tran.Dispose();
OracleUtil.CloseConn(conn);
}
}
public bool UpdateOrderStatusByIdsI(string id, string status)
{
bool flag = false;
string sql = "update zc_order_history set orderstatus = '" + status + "' where id =" + id + "";
OracleConnection conn = null;
OracleTransaction tran = null;
OracleCommand cmd = new OracleCommand();
try
{
conn = OracleUtil.OpenConn();
tran = conn.BeginTransaction();
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
tran.Commit();
flag = true;
}
catch (Exception ex)
{
tran.Rollback();
flag = false;
log.Error("上传zcOrderHistory发生异常", ex);
}
finally
{
cmd.Dispose();
tran.Dispose();
OracleUtil.CloseConn(conn);
}
return(flag);
}
/// <summary>
/// Rolls back the transaction if it hasn't been completed
/// </summary>
public void Dispose()
{
// It's better not to call Dispose() twice, but it doesn't crash
// Transaction automatically rollbacks if it wasn't committed before calling Dispose
_transaction?.Dispose();
_connection.Dispose();
}
public override int ExecuteProcedureTran(string storedProcName, params DbParameter[] parameters)
{
using (OracleConnection conn = new OracleConnection())
{
conn.ConnectionString = ConnectionString;
conn.Open();
OracleTransaction tran = conn.BeginTransaction();
OracleCommand cmd = new OracleCommand();
try
{
PrepareCommand(cmd, conn, storedProcName, tran, CommandType.StoredProcedure, parameters);
int i = cmd.ExecuteNonQuery();
tran.Commit();
return(i);
}
catch (Exception ex)
{
tran.Rollback();
if (Debugger.IsAttached)
{
throw new Exception(ex.Message);
}
else
{
LogHelper.Error(ex, "OracleHelper.ExecuteProcedureTran");
}
return(-1);
}
finally
{
tran.Dispose();
cmd.Dispose();
}
}
}
/// <summary>
/// 执行数据库事务非查询操作,返回受影响的行数
/// </summary>
/// <param name="transaction">数据库事务对象</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前事务查询操作影响的数据行数</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
OracleCommand command = new OracleCommand();
OracleConnection connection = transaction.Connection;
int result = 0;
try
{
PrepareCommand(command, connection, transaction, cmdType, cmdText, commandParameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
transaction.Dispose();
command.Dispose();
connection.Close();
connection.Dispose();
}
return(result);
}
/// <summary>
/// 执行指定连接字符串,类型的OracleCommand, 使用了OracleTransaction
/// </summary>
/// <param name="spNameLs">多条存储过程名</param>
/// <param name="commandParametersLs">多组参数</param>
/// <returns>布尔值(每条语句都执行成功返回True)</returns>
public static bool spExecuteNonQuery(List <string> spNameLs, List <OracleParameter[]> commandParametersLs)
{
bool result = false;
using (OracleConnection connection = new OracleConnection(getOracleConnectionString()))
{
connection.Open();
OracleTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
for (int i = 0; i < spNameLs.Count; i++)
{
OracleHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spNameLs[i], commandParametersLs[i]);
}
transaction.Commit();
result = true;
}
catch
{
transaction.Rollback();
result = false;
}
finally
{
transaction.Dispose();
}
}
return(result);
}
public void DisposeTransaction(string source, OracleTransaction trans)
{
try
{
if (myOracleDBConnection.State == ConnectionState.Open)
{
trans.Dispose();
myOracleDBConnection.Close();
}
}
catch (ApplicationException ex)
{
SetSQLError(source, ex.Message);
}
catch (FormatException ex)
{
SetSQLError(source, ex.Message);
}
catch (OracleException ex)
{
SetSQLError(source, ex.Message);
}
catch (InvalidOperationException ex)
{
SetSQLError(source, ex.Message);
}
catch (Exception ex)
{
SetSQLError(source, ex.Message);
}
}
public override void Dispose()
{
_oracleCommand.Dispose();
_oracleConnection.Dispose();
_oracleDataAdapter.Dispose();
_oracleTransaction.Dispose();
}
/// <summary>
/// 执行数据库事务查询操作,返回结果集中位于第一行第一列的Object类型的值
/// </summary>
/// <param name="transaction">一个已存在的数据库事务对象</param>
/// <param name="commandType">命令类型</param>
/// <param name="commandText">Oracle存储过程名称或PL/SQL命令</param>
/// <param name="commandParameters">命令参数集合</param>
/// <returns>当前事务查询操作返回的结果集中位于第一行第一列的Object类型的值</returns>
public static object ExecuteScalar(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
if (transaction == null) throw new ArgumentNullException("当前数据库事务不存在");
OracleConnection connection = transaction.Connection;
if (connection == null) throw new ArgumentException("当前事务所在的数据库连接不存在");
OracleCommand command = new OracleCommand();
object result = null;
try
{
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters);
result = command.ExecuteScalar();
command.Parameters.Clear();
}
catch
{
throw;
}
finally
{
transaction.Dispose();
command.Dispose();
connection.Close();
connection.Dispose();
}
return result;
}