/// <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;
}
public static OracleConnection GetConnection()
{
if (conn != null) {
if (conn.State == System.Data.ConnectionState.Open) {
return conn;
}
}
string DBName = "RA00C000";
string DBUser = "******";
string DBPass = "******";
string[] args = Environment.GetCommandLineArgs();
if (args.Length > 3) {
DBName = args[1];
DBUser = args[2];
DBPass = args[3];
}
oradb = "Data Source=" + DBName + ";User Id=" + DBUser + ";Password="******";";
try {
conn = new OracleConnection(oradb);
conn.Open();
transaction = conn.BeginTransaction();
return conn;
} catch (Exception) {
return null;
}
}
public static void Commit()
{
if (conn != null) {
transaction.Commit();
transaction = conn.BeginTransaction();
}
}
/// <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="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">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[] commandParameters)
{
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
public CT2Repository()
{
this.con = ConnectionFactory.GetConnection();
if (this.con.State != ConnectionState.Open)
this.con.Open();
if(usaTransacao)
this._transaction = this.con.BeginTransaction();
}
/// <summary>
/// Connects to the database.
/// </summary>
/// <param name="connDetails"></param>
public SqlRunner(ConnectionDetails connDetails)
{
this.connDetails = connDetails;
string connStr = GetConnectionString();
log.Info("connecting to [" + connDetails.Name + "] with connection string " + connStr);
conn = new OracleConnection(connStr);
conn.Open();
currentTransaction = conn.BeginTransaction();
}
public DataContextTransaction BeginTransaction()
{
if (Connection.State == ConnectionState.Closed)
Connection.Open();
if (Transaction == null)
Transaction = Connection.BeginTransaction();
return this;
}
public OracleCustomCommandBuilder( DataTable dataTable, OracleConnection connection, ArrayList columnOracleTypes, OracleTransaction transaction)
{
if (dataTable.Columns.Count != columnOracleTypes.Count)
{
throw new ArgumentException("Number of OracleTypes does not match number of columns.");
}
this.dataTable = dataTable;
this.connection = connection;
this.columnOracleTypes = columnOracleTypes;
this.transaction = transaction;
}
/// <summary>
/// Create and execute a command to return a single-row DataReader after binding to multiple parameters.
/// </summary>
/// <param name="conn">Connection to execute against. If not open, it will be here.</param>
/// <param name="trans">ADO transaction. If null, will not be attached to the command</param>
/// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
/// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
/// <param name="cmdParms">An array of OracleParameter objects to bind to the query.</param>
public static OracleDataReader ExecuteReaderSingleRow(OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = cmdText;
PrepareCommand(cmd, cmdParms);
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow);
return rdr;
}
/// <summary>
/// Create and execute a command to return DataReader after binding to a single parameter.
/// </summary>
/// <param name="conn">Connection to execute against. If not open, it will be here.</param>
/// <param name="trans">ADO transaction. If null, will not be attached to the command</param>
/// <param name="cmdType">Type of ADO command; such as Text or Procedure</param>
/// <param name="cmdText">The actual SQL or the name of the Stored Procedure depending on command type</param>
/// <param name="singleParm">The single OracleParameter object to bind to the query.</param>
public static OracleDataReader ExecuteReaderSingleParm(OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter singleParm)
{
OracleCommand cmd = new OracleCommand();
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.Parameters.Add(singleParm);
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
return rdr;
}
public static void Rollback()
{
if(transaction != null) {
try {
transaction.Rollback();
transaction = conn.BeginTransaction();
} catch (Exception) {
// Подавляем исключения.
}
}
}
public void Dispose()
{
if (Transaction != null)
{
Transaction.Dispose();
Transaction = null;
}
if (Connection.State != ConnectionState.Closed)
Connection.Close();
}
public static void GrabarMatriculaUpdate(ref MatriculaDto mat, ref OracleConnection con, ref OracleTransaction tran)
{
var cmdPro = new OracleCommand("PRC_MATRICULA_UPDATE");
cmdPro.CommandType = CommandType.StoredProcedure;
cmdPro.Connection = con;
cmdPro.Parameters.Add(CreateParameter("iMTRID", mat.MtrId));//NUMBER
cmdPro.Parameters.Add(CreateParameter("iMTRDESCRIPCION", mat.MtrDescripcion, 45));//NUMBER
cmdPro.Transaction = tran;
cmdPro.ExecuteNonQuery();//EJECUTO CONSULTA
}
/// <summary>
/// 事物提交
/// </summary>
public void CommitTrans()
{
if (_oracleTran == null)
{
throw new NullReferenceException("没有事务可进行提交!");
}
else
{
_oracleTran.Commit();
_oracleTran.Dispose();
_oracleTran = null;
CloseConn();
}
}
public void RunInTransaction(Action<OracleConnection> callback)
{
Boolean closeConnection = connection == null;
if (null == connection)
{
this.connection = new OracleConnection(connectionString);
this.connection.Open();
}
Boolean inDistributedTransaction = null != Transaction.Current;
if (null == this.transaction && !inDistributedTransaction)
this.transaction = connection.BeginTransaction();
try
{
if (connection.State == ConnectionState.Closed)
this.connection.Open();
callback(connection);
if (null != this.transaction && !inDistributedTransaction)
this.transaction.Commit();
}
catch (Exception)
{
if (null != this.transaction)
this.transaction.Rollback();
throw;
}
finally
{
if (null != this.transaction)
{
this.transaction.Dispose();
this.transaction = null;
}
if (null != this.connection)
{
this.connection.Close();
this.connection.Dispose();
this.connection = null;
}
}
}
public OracleDatabaseQuery(
OracleConnection connection,
OracleTransaction transaction)
{
Contract.Requires(connection != null);
this.Connection = connection;
this.ConnectionString = connection.ConnectionString;
this.Transaction = transaction;
Queue =
new Lazy<OracleAQQueue>(() =>
{
var q = new OracleAQQueue("\"-NGS-\".NOTIFY_QUEUE", Connection, OracleAQMessageType.Udt, "-NGS-.NOTIFY_INFO_TYPE");
q.EnqueueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
q.EnqueueOptions.DeliveryMode = OracleAQMessageDeliveryMode.Persistent;
return q;
});
}
public static void GrabarMatriculaInsert(ref MatriculaDto mat, ref OracleConnection con, ref OracleTransaction tran)
{
#region INSERT Matricula del profesional -------------------------------------------------------------------
var cmdMat = new OracleCommand("PRC_MATRICULA_INSERT");
cmdMat.CommandType = CommandType.StoredProcedure;
cmdMat.Connection = con;
cmdMat.Parameters.Add(CreateParameter("iMTRDESCRIPCION", mat.MtrDescripcion, 45));//VARCHAR
cmdMat.Parameters.Add(CreateParameter("iMTR_MTTID", mat.MtrTipo.MttId));//NUMBER
cmdMat.Parameters.Add(CrearParametroSalida("oMTRID", OracleDbType.Int32));//NUMBER
cmdMat.Transaction = tran;
cmdMat.ExecuteNonQuery();
var varMtrId = cmdMat.Parameters["oMTRID"].Value;
mat.MtrId = Convert.ToInt16(varMtrId.ToString());
#endregion
}
public bool Fill()
{
try {
conn = new OracleConnection(GetConnectionString());
conn.Open();
transaction = conn.BeginTransaction();
} catch (Exception e) {
MessageBox.Show("При подключении к базе данных произошла ошибка:\n\n" + e.Message);
conn.Close();
conn.Dispose();
return false;
}
cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TO_CHAR(id), l_name from akriko.cat_executors WHERE id in (SELECT distinct(ispolnitel_cik_id) FROM akriko.appeal) ORDER BY UPPER(l_name)";
try {
dr = cmd.ExecuteReader();
while (dr.Read()) {
if (dr.IsDBNull(0) || dr.IsDBNull(1)) {
MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n");
break;
} else {
UsersGrid.Rows.Add();
UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["executor_id"].Value = dr.GetString(0);
UsersGrid.Rows[UsersGrid.Rows.Count - 1].Cells["akriko"].Value = dr.GetString(1);
}
}
dr.Close();
dr.Dispose();
} catch (Exception e) {
MessageBox.Show("При чтении данных из базы данных произошла ошибка:\n\n" + e.Message);
}
if (UsersGrid.Rows.Count == 0) {
MessageBox.Show("Отсутствуют неотконвертированные исполнители", "Конвертация исполнителей", MessageBoxButtons.OK, MessageBoxIcon.Information);
ConvertButton.Enabled = false;
}
FillDeloDepartmentsColumn();
return true;
}
/// <summary>
/// 为command添加执行内容
/// </summary>
/// <param name="cmd">OracleCommand类型</param>
/// <param name="conn">OracleConnection类型</param>
/// <param name="trans">OracleTransaction要处理的事物</param>
/// <param name="cmdType">执行类型（过程/语句）</param>
/// <param name="cmdText">执行的过程名称/查询语句</param>
/// <param name="cmdParms">ommand执行的OracleParameter[]数组</param>
private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
Open(conn);
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
if (cmdParms.Length > 0)
{
foreach (OracleParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
/************************************************************
* Function name : SetLecturerInfoUpdate
* Purpose : 강사 정보 등록
* Input : string[] rParams
* Output : String Boolean
*************************************************************/
public string SetLecturerInfoUpdate(string[] rParams, byte[] rFileRes, string rFileNameRes, byte[] rFileDoc, string rFileNameDoc)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS; // Command 에 DB 연결
xCmdLMS.Transaction = xTransLMS; // Command 에 트랜잭션 연결
try
{
string xSql = string.Empty;
xSql += " UPDATE t_lecturer SET ";
xSql += string.Format(" lecturer_nm = '{0}', ", rParams[1]); //
xSql += string.Format(" lecturer_nm_eng = '{0}', ", rParams[2]); //
if (rParams[3] == null)
{
xSql += string.Format(" birth_dt = null, ", rParams[3]);
}
else
{
xSql += string.Format(" birth_dt = TO_DATE('{0}', 'yyyy.MM.dd'), ", rParams[3]);
}
xSql += string.Format(" grade = '{0}', ", rParams[4]); //
xSql += string.Format(" user_id = '{0}', ", rParams[5]); //
xSql += string.Format(" job = '{0}', ", rParams[6]); //
xSql += string.Format(" education = '{0}', ", rParams[7]); //
xSql += string.Format(" major = '{0}', ", rParams[8]); //
xSql += string.Format(" org_nm = '{0}', ", rParams[9]); //
xSql += string.Format(" company_nm = '{0}', ", rParams[10]); //
xSql += string.Format(" duty_step = '{0}', ", rParams[11]); //
xSql += string.Format(" zip_code = '{0}', ", rParams[12]); //
xSql += string.Format(" company_addr = '{0}', ", rParams[13]); //
xSql += string.Format(" tel_no = '{0}', ", rParams[14]); //
xSql += string.Format(" mobile_phone = '{0}', ", rParams[15]); //
xSql += string.Format(" email = '{0}', ", rParams[16]); //
xSql += string.Format(" acc_bank = '{0}', ", rParams[17]); //
xSql += string.Format(" account = '{0}', ", rParams[18]); //
//xSql += string.Format(" status = '{0}', ", rParams[19]); //
xSql += string.Format(" upt_id = '{0}', ", rParams[20]); // 수정자 ID
xSql += " upt_dt = SYSDATE "; // 수정일자
xSql += string.Format("WHERE lecturer_id = '{0}' ", rParams[0]); // 강사ID
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
OracleParameter[] oraParams = null;
try
{
xSql = @"
UPDATE t_lecturer
SET RES_FILE_NM = :RES_FILE_NM
, RES_FILE = :RES_FILE
WHERE lecturer_id = :LECTURER_ID
";
oraParams = new OracleParameter[3];
oraParams[0] = base.AddParam("RES_FILE_NM", OracleType.VarChar, rFileNameRes);
oraParams[1] = base.AddParam("RES_FILE", OracleType.Blob, rFileRes.Length, rFileRes);
oraParams[2] = base.AddParam("LECTURER_ID", OracleType.VarChar, rParams[0]);
base.ExecuteScalar("LMS", xSql, oraParams);
}
catch { }
try
{
xSql = @"
UPDATE t_lecturer
SET DOC_FILE_NM1 = :DOC_FILE_NM1
, DOC_FILE1 = :DOC_FILE1
WHERE lecturer_id = :LECTURER_ID
";
oraParams = new OracleParameter[3];
oraParams[0] = base.AddParam("DOC_FILE_NM1", OracleType.VarChar, rFileNameDoc);
oraParams[1] = base.AddParam("DOC_FILE1", OracleType.Blob, rFileDoc.Length, rFileDoc);
oraParams[2] = base.AddParam("LECTURER_ID", OracleType.VarChar, rParams[0]);
base.ExecuteScalar("LMS", xSql, oraParams);
}
catch { }
xRtn = Boolean.TrueString;
xTransLMS.Commit(); // 트랜잭션 커밋
}
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);
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
if (!Page.IsValid)
{
return;
}
string[] curupdateDate = hidNewDt.Value.Split(';');
if (curupdateDate.Length == 0 || hidNewDt.Value == "")
{
return;
}
using (OracleConnection ConnectionStringJXC = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringJXC"].ConnectionString))
{
ConnectionStringJXC.Open(); //打开链接
OracleCommand cmd = new OracleCommand(); //创建执行类
cmd.Connection = ConnectionStringJXC;
OracleTransaction tx = ConnectionStringJXC.BeginTransaction();
cmd.Transaction = tx;//开启链接事务
string strSql = "";
string strPK_TMP_NC_XSJH = "";
///插入销售计划表头信息
try
{
string[] curRowDate = curupdateDate[0].Split(',');
/* strSql = "select PK_TMP_NC_XSJH from tmp_nc_xsjh where dr='0' and DBILLDATE='" + curRowDate[9].ToString() + "' and pk_corp='" + log_corp + "' and FREE1='" + log_user + "' ";
* cmd.CommandText = strSql;
* object objPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar();
* if (objPK_TMP_NC_XSJH == null)
* {*/
cmd.CommandText = "select tmp_nc_xsjh_seq.NextVal from dual ";
strPK_TMP_NC_XSJH = cmd.ExecuteOracleScalar().ToString();
////如果不存在插入
strSql = "insert into tmp_nc_xsjh (PK_TMP_NC_XSJH,DBILLDATE,CCUSTOMERID,PK_CORP,DR,VNOTE,";
strSql += " FREE1,FREE2,FREE3,FREE4,FREE5,TS,FLAG,ERRORMESSAGE,VRECEIPTCODE,CUSTCODE,CUSTNAME,CORPNAME)";
strSql += "values('" + strPK_TMP_NC_XSJH + "','" + TextTHRQ.Text + "','" + curRowDate[0].ToString() + "','" + DplTHGC.Items[DplTHGC.SelectedIndex].Value + "',0,'',";
strSql += "'','','','','',to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'),'0','','" + createNCYDBH(cmd, TextTHRQ.Text) + "',";
strSql += "'" + curRowDate[1].ToString() + "','" + curRowDate[2].ToString() + "','" + DplTHGC.Items[DplTHGC.SelectedIndex].Text + "')";
cmd.CommandText = strSql;
int exIn = cmd.ExecuteNonQuery();
/* }
* else //更新
* {
* strPK_TMP_NC_XSJH = objPK_TMP_NC_XSJH.ToString();
* strSql = "update tmp_nc_xsjh set ts=to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') where dr='0' and DBILLDATE='" + curRowDate[9].ToString() + "' and pk_corp='" + log_corp + "' and FREE1='" + log_user + "' ";
* cmd.CommandText = strSql;
* cmd.ExecuteOracleScalar();
* } */
}
catch (System.Data.OracleClient.OracleException E) {
tx.Rollback();
MessageBox.ShowAndRedirect(this, "操作销售计划表头信息失败，请联系系统管理员！", "SalesPlan.aspx");
return;
}
///插入销售计划表体信息
try {
for (int i = 0; i < curupdateDate.Length; i++)
{
string[] curRowDate_b = curupdateDate[i].Split(',');
string strPK_TMP_NC_XSJH_B = "";//销售计划表体ID
///校验物料是否存在，并创建销售计划表体ID
try
{
strPK_TMP_NC_XSJH_B = createPK_XSJH_B(cmd, curRowDate_b[4], TextTHRQ.Text);
//if (strPK_TMP_NC_XSJH_B.Substring(0, 4).Equals("exist"))
//{
// tx.Rollback();
// MessageBox.ShowAndRedirect(this, "当前物料:"+curRowDate_b[7].ToString()+"已经存在，请返回计划列表直接修改！", "SalesPlan.aspx");
// return ;
//}
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
MessageBox.ShowAndRedirect(this, "创建销售表体ID失败，请联系系统管理员！", "SalesPlan.aspx");
return;
}
///插入销售计划表体ID
strSql = " insert into tmp_nc_xsjh_b (PK_TMP_NC_XSJH_B,PK_TMP_NC_XSJH,BLARGESSFLAG,CINVBASDOCID,CINVENTORYID,FROWNOTE,DR";
strSql += " ,FREE1,FREE2,FREE3,FREE4,FREE5,TS,NUM,INVNAME,UNITWEIGHT)";
strSql += "values('" + strPK_TMP_NC_XSJH_B + "','" + strPK_TMP_NC_XSJH + "','N','" + curRowDate_b[3].ToString() + "','" + curRowDate_b[4].ToString() + "','','0',";
strSql += "'','','','','',to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')," + curRowDate_b[6].ToString() + ",'" + curRowDate_b[5].ToString() + "'," + curRowDate_b[7].ToString() + ")";
cmd.CommandText = strSql;
int exIn = cmd.ExecuteNonQuery();
}
tx.Commit();
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
MessageBox.ShowAndRedirect(this, "插入销售计划表头信息失败，请联系系统管理员！", "SalesPlan.aspx");
return;
}
ConnectionStringJXC.Close();
BindSalesList();
//MessageBox.ShowAndRedirect(this, "保存操作成功！", "SalesPlan.aspx");
}
}
/// <summary>
/// 针对 SQL 列表的命令准备函数
/// </summary>
/// <param name="objCommand">执行命令</param>
/// <param name="objConnection">数据库连接</param>
/// <param name="objTransaction">事务</param>
/// <param name="strCommandType">命令类型</param>
/// <param name="strCommandText">命令体列表</param>
private static void ExecuteListCommand(OracleCommand objCommand, OracleConnection objConnection, OracleTransaction objTransaction, CommandType strCommandType, string[] strCommandTextList)
{
objCommand.CommandType = strCommandType;
objCommand.Connection = objConnection;
if (objTransaction != null)
{
objCommand.Transaction = objTransaction;
}
foreach (string strCommandText in strCommandTextList)
{
//石磊添加 对于null 和 ""的判断规则
if (null == strCommandText || strCommandText.Trim() == "")
{
continue;
}
objCommand.CommandText = strCommandText;
objCommand.ExecuteNonQuery();
}
}
/// <summary>
/// 命令准备
/// </summary>
/// <param name="objCommand">命令</param>
/// <param name="objConnection">连接</param>
/// <param name="objTransaction">事务</param>
/// <param name="strCommandType">命令类型</param>
/// <param name="strCommandText">命令明细</param>
private static void PrepareCommand(OracleCommand objCommand, OracleConnection objConnection, OracleTransaction objTransaction, CommandType strCommandType, string strCommandText)
{
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
objCommand.Connection = objConnection;
objCommand.CommandText = strCommandText;
if (objTransaction != null)
{
objCommand.Transaction = objTransaction;
}
objCommand.CommandType = strCommandType;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static bool ExecuteSqlTran(string conStr, List <CommandInfo> cmdList)
{
using (OracleConnection conn = new OracleConnection(conStr))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
foreach (CommandInfo c in cmdList)
{
if (!String.IsNullOrEmpty(c.CommandText))
{
PrepareCommand(cmd, conn, tx, CommandType.Text, c.CommandText, (OracleParameter[])c.Parameters);
if (c.EffentNextType == EffentNextType.WhenHaveContine || c.EffentNextType == EffentNextType.WhenNoHaveContine)
{
if (c.CommandText.ToLower().IndexOf("count(") == -1)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须符合select count(..的格式");
//return false;
}
object obj = cmd.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull.Value)
{
isHave = false;
}
isHave = Convert.ToInt32(obj) > 0;
if (c.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须大于0");
//return false;
}
if (c.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "返回值必须等于0");
//eturn false;
}
continue;
}
int res = cmd.ExecuteNonQuery();
if (c.EffentNextType == EffentNextType.ExcuteEffectRows && res == 0)
{
tx.Rollback();
throw new Exception("Oracle:违背要求" + c.CommandText + "必须有影像行");
// return false;
}
}
}
tx.Commit();
return(true);
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw E;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
}
/// <summary>
/// Performs CRUD row update. Override to do custom update
/// </summary>
protected internal async override Task <int> DoUpdateAsync(OracleConnection cnn, OracleTransaction transaction, Doc row, IDataStoreKey key = null, FieldFilterFunc filter = null)
{
CheckReadOnly(row.Schema, "update");
return(await CRUDGenerator.CRUDUpdate(this, cnn, transaction, row, key, filter));
}
public static bool AddEditCaseCheckList(List <ATTCaseCheckList> CaseCheckListLST, OracleTransaction Tran, double caseID)
{
string InsertUpdateSQL = "";
List <OracleParameter> paramArray;
try
{
foreach (ATTCaseCheckList obj in CaseCheckListLST)
{
if (obj.Action == "A")
{
InsertUpdateSQL = "SP_ADD_CASE_CHECKLIST";
}
else if (obj.Action == "E")
{
InsertUpdateSQL = "SP_EDIT_CASE_CHECKLIST";
}
paramArray = new List <OracleParameter>();
paramArray.Add(Utilities.GetOraParam("P_CASE_ID", caseID, OracleDbType.Double, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_ORG_ID", obj.OrgID, OracleDbType.Int32, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_CASE_TYPE_ID", obj.CaseTypeID, OracleDbType.Int32, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_REG_TYPE_ID", obj.RegTypeID, OracleDbType.Int32, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_CHECK_LIST_ID", obj.CheckListID, OracleDbType.Int32, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_FULL_FILLED", obj.FulFilled, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_REMARKS", obj.Remarks, OracleDbType.Varchar2, ParameterDirection.Input));
paramArray.Add(Utilities.GetOraParam("P_ENTRY_BY", obj.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input));
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, InsertUpdateSQL, paramArray.ToArray());
}
return(true);
}
catch (Exception ex)
{
Tran.Rollback();
throw ex;
}
}
public int Insert(Konyvek record)
{
OracleConnection oc = GetOracleConnection();
oc.Open();
OracleTransaction ot = oc.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
OracleCommand command = new OracleCommand()
{
CommandType = System.Data.CommandType.StoredProcedure,
CommandText = "spInsert_konyvek"
};
OracleParameter cimParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":p_cim",
Direction = System.Data.ParameterDirection.Input,
Value = record.Cim
};
command.Parameters.Add(cimParameter);
OracleParameter szerzoParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":p_szerzo",
Direction = System.Data.ParameterDirection.Input,
Value = record.Szerzo
};
command.Parameters.Add(szerzoParameter);
OracleParameter kiadoParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":p_kiado",
Direction = System.Data.ParameterDirection.Input,
Value = record.Kiado
};
command.Parameters.Add(kiadoParameter);
OracleParameter kiadas_eveParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":p_kiadas_eve",
Direction = System.Data.ParameterDirection.Input,
Value = record.Kiadas_eve
};
command.Parameters.Add(kiadas_eveParameter);
OracleParameter isbn_azonositoParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":p_isbn_azonosito",
Direction = System.Data.ParameterDirection.Input,
Value = record.Isbn_azonosito
};
command.Parameters.Add(isbn_azonositoParameter);
OracleParameter nyelvParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":p_nyelv",
Direction = System.Data.ParameterDirection.Input,
Value = record.Nyelv
};
command.Parameters.Add(nyelvParameter);
OracleParameter kategoriaParameter = new OracleParameter()
{
DbType = System.Data.DbType.String,
ParameterName = ":p_kategoria",
Direction = System.Data.ParameterDirection.Input,
Value = record.Kategoria
};
command.Parameters.Add(kategoriaParameter);
OracleParameter rowcountParameter = new OracleParameter()
{
DbType = System.Data.DbType.Int32,
ParameterName = "p_out_rowcnt",
Direction = System.Data.ParameterDirection.Output
};
command.Connection = oc;
command.Transaction = ot;
try
{
command.ExecuteNonQuery();
int affectedRows = int.Parse(rowcountParameter.Value.ToString());
ot.Commit();
return(affectedRows);
}
catch (Exception)
{
ot.Rollback();
return(0);
}
}
/// <summary>
/// Apply the given changeset.
/// </summary>
/// <param name="change_set"></param>
public override void ApplyChange(ChangeSet change_set)
{
if (change_set != null && change_set.Changes != null)
{
foreach (Change change in change_set.Changes)
{
switch (change.Type)
{
case ChangeType.Create:
foreach (OsmGeo geo in change.OsmGeo)
{
// start applying the simplechange.
OracleTransaction trans = _connection.BeginTransaction();
try
{
if (geo is Node)
{
this.Create(geo as Node);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"+(n:{0})", geo.Id.Value);
}
else if (geo is Way)
{
this.Create(geo as Way);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"+(w:{0})", geo.Id.Value);
}
else if (geo is Relation)
{
this.Create(geo as Relation);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"+(r:{0})", geo.Id.Value);
}
trans.Commit();
}
catch (OracleException ex)
{
trans.Rollback();
if (!_pragmatic)
{
throw ex;
}
else
{
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"+(E:{0}-{1})", geo.Id.Value, geo.Type.ToString());
}
}
}
break;
case ChangeType.Delete:
foreach (OsmGeo geo in change.OsmGeo)
{
// start applying the simplechange.
OracleTransaction trans = _connection.BeginTransaction();
try
{
if (geo is Node)
{
this.Delete(geo as Node);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"-(n:{0})", geo.Id.Value);
}
else if (geo is Way)
{
this.Delete(geo as Way);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"-(w:{0})", geo.Id.Value);
}
else if (geo is Relation)
{
this.Delete(geo as Relation);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"-(r:{0})", geo.Id.Value);
}
trans.Commit();
}
catch (OracleException ex)
{
trans.Rollback();
if (!_pragmatic)
{
throw ex;
}
else
{
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"-(E:{0}-{1})", geo.Id.Value, geo.Type.ToString());
}
}
}
break;
case ChangeType.Modify:
foreach (OsmGeo geo in change.OsmGeo)
{
// start applying the simplechange.
OracleTransaction trans = _connection.BeginTransaction();
try
{
if (geo is Node)
{
this.Modify(geo as Node);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"/(n:{0})", geo.Id.Value);
}
else if (geo is Way)
{
this.Modify(geo as Way);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"/(w:{0})", geo.Id.Value);
}
else if (geo is Relation)
{
this.Modify(geo as Relation);
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"/(r:{0})", geo.Id.Value);
}
trans.Commit();
}
catch (OracleException ex)
{
trans.Rollback();
if (!_pragmatic)
{
throw ex;
}
else
{
OsmSharp.Logging.Log.TraceEvent("OsmSharp.Data.Oracle.Osm.Streams.OracleOsmChangesetStreamTarget", System.Diagnostics.TraceEventType.Information,
"/(E:{0}-{1})", geo.Id.Value, geo.Type.ToString());
}
}
}
break;
}
}
}
}
public bool ExecSqls(List <string> sqls, List <List <object> > parametreDegerleri, bool transacted)
{
/* Örnek Kullanım:
* Sqllerde parametre isimleri param1, param2 .. şeklinde tanımlanmalıdır
*
* DB db = new DB();
*
* string sql1 = "INSERT INTO TABLO_ADI_1 VALUES(:param1, 'birşey', :param2, 'başkabirşey', :param3)";
* string sql2 = "INSERT INTO TABLO_ADI_2 VALUES(:param1, :param2)";
* string sql3 = "INSERT INTO TABLO_ADI_3 VALUES('bir şey','başka bir şey')";
* string sql4 = "DELETE TABLO_ADI_4 WHERE ALAN_ADI = :param1";
* string sql5 = "UPDATE TABLO_ADI_5 SET ALAN_ADI_1 = :param1 WHERE ALAN_ADI_2 < :param2 ";
*
* List<string> sqls = new List<string>();
* sqls.Add(sql1);
* sqls.Add(sql2);
* sqls.Add(sql3);
* sqls.Add(sql4);
* sqls.Add(sql5);
*
* List<List<object>> parameters = new List<List<object>>();
* parameters.Add(new List<object>() { "DEGER1", "DEGER2", 100 });
* parameters.Add(new List<object>() { "DEGER1", DateTime.Now });
* parameters.Add(null);
* parameters.Add(new List<object>() { "DEGER1" });
* parameters.Add(new List<object>() { "DEGER1", 30 });
*
* bool basari = db.ExecSqls(sqls, parameters, true);
*/
bool basari = true;
if (parametreDegerleri == null)
{
Fonksiyonlar f = new Fonksiyonlar();
OracleConnection conn = new OracleConnection(this.GetConnectionString());
if (transacted)
{
OracleTransaction transaction = null;
try
{
conn.Open();
transaction = conn.BeginTransaction();
foreach (string sql in sqls)
{
OracleCommand cmd = new OracleCommand(sql, conn, transaction);
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (OracleException oex)
{
basari = false;
f.LogYazTxt("ExecSqls()", oex.StackTrace, oex.Message);
transaction.Rollback();
}
catch (Exception ex)
{
basari = false;
f.LogYazTxt("ExecSqls()", ex.StackTrace, ex.Message);
transaction.Rollback();
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
}
else
{
try
{
conn.Open();
foreach (string sql in sqls)
{
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.ExecuteNonQuery();
}
}
catch (OracleException oex)
{
basari = false;
f.LogYazTxt("ExecSqls()", oex.StackTrace, oex.Message);
}
catch (Exception ex)
{
basari = false;
f.LogYazTxt("ExecSqls()", ex.StackTrace, ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
}
}
else
{
List <OracleCommand> cmds = new List <OracleCommand>();
for (int sqlNo = 0; sqlNo < sqls.Count; sqlNo++)
{
cmds.Add(new OracleCommand(sqls[sqlNo]));
if (parametreDegerleri[sqlNo] != null)
{
for (int paramNo = 0; paramNo < parametreDegerleri[sqlNo].Count; paramNo++)
{
cmds[sqlNo].Parameters.AddWithValue("param" + (paramNo + 1).ToString(), parametreDegerleri[sqlNo][paramNo]);
}
}
}
basari = this.ExecCmds(cmds, transacted);
}
return(basari);
}
public static void CerrarConexionesCmd(ref OracleCommand cmd, bool blnCommit = false, bool blnRollback = false)
{
OracleTransaction tra = null;
CerrarConexiones(ref cmd, ref tra, blnCommit, blnRollback);
}
private void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, ref bool mustCloseConnection)
{
if ((command == null))
{
throw new ArgumentNullException("command");
}
if (string.IsNullOrEmpty(commandText))
{
throw new ArgumentNullException("commandText");
}
if (connection.State != ConnectionState.Open)
{
connection.Open();
mustCloseConnection = true;
}
else
{
mustCloseConnection = false;
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
if (transaction.Connection == null)
{
throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
public static bool SaveAttachments(List <ATTPersonAttachments> LstAttachments, OracleTransaction Tran, double empid)
{
//GetConnection conn = new GetConnection();
//OracleConnection dbconn = conn.GetDbConn(Module.PMS);
string InsertSQL = "";
try
{
foreach (ATTPersonAttachments obj in LstAttachments)
{
if (obj.Action == "A")
{
InsertSQL = "SP_ADD_EMP_ATTACHMENTS";
}
else if (obj.Action == "E")
{
InsertSQL = "SP_EDIT_EMP_ATTACHMENTS";
}
if (obj.Action == "A" || obj.Action == "E")
{
OracleParameter[] paramArray = new OracleParameter[7];
paramArray[0] = Utilities.GetOraParam("P_EMP_ID", empid, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam("P_ATT_SEQ", obj.AttSeq, OracleDbType.Int32, ParameterDirection.InputOutput);
paramArray[2] = Utilities.GetOraParam("P_ATT_DATE", obj.AttachmentDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam("P_ATT_SUBJECT", obj.AttachmentTitle, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam("P_ATT_CONTENT", obj.AttachmentDocs, OracleDbType.Blob, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam("P_ATT_REMARKS", obj.AttachmentDesc, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam("P_ENTRY_BY", obj.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, InsertSQL, paramArray);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(true);
}
public TransactedOracleConnection(string connectionString)
{
OracleConnection = new OracleConnection(connectionString);
OracleConnection.Open();
Transaction = OracleConnection.BeginTransaction();
}
private void ReleaseTransaction()
{
oTransaction.Dispose();
oTransaction = null;
}
public bool BeginTransaction(String _sConnectionName)
{
Hashtable hConnectionInfo;
int nDBProvider;
if (_sConnectionName == null)
{
return(false);
}
if (_sConnectionName.Trim().Length == 0)
{
return(false);
}
if (!ConnectionNameExist(_sConnectionName))
{
return(false);
}
if (!IsConnectionOpen(_sConnectionName))
{
OpenConnection(_sConnectionName);
}
hConnectionInfo = GetConnectionInfo(_sConnectionName);
// Don't start another transaction if one has already started....
if (((Object)hConnectionInfo["TransactionObject"]) != null)
{
return(false);
}
nDBProvider = Convert.ToInt32((String)hConnectionInfo["DBProvider"]);
try
{
DataBaseException = "";
if (nDBProvider == DB_MYSQL)
{
MySqlConnection mysqlConnection = (MySqlConnection)hConnectionInfo["ConnectionObject"];
MySqlTransaction mysqlTransaction = mysqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
SetHashProperty(hConnectionInfo, "TransactionObject", mysqlTransaction);
}
if (nDBProvider == DB_ORACLE)
{
OracleConnection oracleConnection = (OracleConnection)hConnectionInfo["ConnectionObject"];
OracleTransaction oracleTransaction = oracleConnection.BeginTransaction(IsolationLevel.ReadCommitted);
SetHashProperty(hConnectionInfo, "TransactionObject", oracleTransaction);
}
if (nDBProvider == DB_SQLSERVER)
{
SqlConnection sqlserverConnection = (SqlConnection)hConnectionInfo["ConnectionObject"];
SqlTransaction sqlserverTransaction = sqlserverConnection.BeginTransaction(IsolationLevel.ReadCommitted);
SetHashProperty(hConnectionInfo, "TransactionObject", sqlserverTransaction);
}
}
catch (Exception eException)
{
ErrorException = eException;
DataBaseException = ErrorException.Message;
return(false);
}
return(true);
}
/// <summary>
/// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//create the DataAdapter & DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the DataSet using default values for DataTable names, etc.
da.Fill(ds);
//return the dataset
return ds;
}
public bool ExecCmds(List <OracleCommand> cmds, bool transacted)
{
bool basari = true;
Fonksiyonlar f = new Fonksiyonlar();
OracleConnection conn = new OracleConnection(this.GetConnectionString());
if (transacted)
{
OracleTransaction transaction = null;
try
{
conn.Open();
transaction = conn.BeginTransaction();
foreach (OracleCommand cmd in cmds)
{
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
transaction.Commit();
}
catch (OracleException oex)
{
basari = false;
f.LogYazTxt("ExecCmds()", oex.StackTrace, oex.Message);
transaction.Rollback();
}
catch (Exception ex)
{
basari = false;
f.LogYazTxt("ExecCmds()", ex.StackTrace, ex.Message);
transaction.Rollback();
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
}
else
{
try
{
conn.Open();
foreach (OracleCommand cmd in cmds)
{
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
}
catch (OracleException oex)
{
basari = false;
f.LogYazTxt("ExecCmds()", oex.StackTrace, oex.Message);
}
catch (Exception ex)
{
basari = false;
f.LogYazTxt("ExecCmds()", ex.StackTrace, ex.Message);
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
conn.Dispose();
}
}
}
return(basari);
}
/// <summary>
/// Create and prepare an OracleCommand, and call ExecuteReader with the appropriate CommandBehavior.
/// </summary>
/// <remarks>
/// If we created and opened the connection, we want the connection to be closed when the DataReader is closed.
///
/// If the caller provided the connection, we want to leave it to them to manage.
/// </remarks>
/// <param name="connection">a valid OracleConnection, on which to execute this command</param>
/// <param name="transaction">a valid OracleTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by OracleHelper</param>
/// <returns>OracleDataReader containing the results of the command</returns>
private static OracleDataReader ExecuteReader(OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters, OracleConnectionOwnership connectionOwnership)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters);
//create a reader
OracleDataReader dr;
// call ExecuteReader with the appropriate CommandBehavior
if (connectionOwnership == OracleConnectionOwnership.External)
{
dr = cmd.ExecuteReader();
}
else
{
dr = cmd.ExecuteReader((CommandBehavior)((int)CommandBehavior.CloseConnection));
}
return (OracleDataReader) dr;
}
public OracleScopeInfoBuilder(DbConnection connection, DbTransaction transaction)
{
this.connection = connection as OracleConnection;
this.transaction = transaction as OracleTransaction;
}
/// <summary>
/// Execute an OracleCommand (that returns a resultset) against the specified OracleTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// OracleDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
/// <returns>an OracleDataReader containing the resultset generated by the command</returns>
public static OracleDataReader ExecuteReader(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//pass through to private overload, indicating that the connection is owned by the caller
return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, OracleConnectionOwnership.External);
}
/**
* 开始事务
* */
public void beginTrans()
{
this.openConn();
this.isTrans = true;
this.trans = this.conn.BeginTransaction();
}
/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the OracleCommand to be prepared</param>
/// <param name="connection">a valid OracleConnection, on which to execute this command</param>
/// <param name="transaction">a valid OracleTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or Oracle statement)
command.CommandText = commandText;
//if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
public OracleBulkCopy(OracleConnection connection, OracleTransaction transation = null)
{
this._connection = connection;
this._externalTransaction = transation;
}
/// <summary>
/// Starts a new ADO.NET transaction using the open connection object of this class.
/// Opens the connection if it isn't already open.
/// No nesting allowed, throws exception if the transaction is already pending.
/// </summary>
/// <param name="isolationLevel">The transaction isolation level.</param>
public void BeginTransaction(IsolationLevel isolationLevel)
{
if (this.isTransactionPending)
throw new InvalidOperationException(Messages.ConnectionProvider_TranAlreadyPending);
if (!IsOpen)
OpenConnection();
this.oraTran = this.oraConn.BeginTransaction(isolationLevel);
this.isTransactionPending = true;
}
/// <summary>
/// Performs CRUD row deletion. Override to do custom deletion
/// </summary>
protected internal async override Task <int> DoDeleteAsync(OracleConnection cnn, OracleTransaction transaction, Doc row, IDataStoreKey key = null)
{
CheckReadOnly(row.Schema, "delete");
return(await CRUDGenerator.CRUDDelete(this, cnn, transaction, row, key));
}
/************************************************************
* Function name : SetLecturerInfo
* Purpose : 강사 정보 등록
* Input : string[] rParams
* Output : String Boolean
*************************************************************/
public string SetLecturerInfo(string[] rParams, byte[] rFileRes, string rFileNameRes, byte[] rFileDoc, string rFileNameDoc)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTransLMS = null;
OracleCommand xCmdLMS = null;
try
{
xTransLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS; // Command 에 DB 연결
xCmdLMS.Transaction = xTransLMS; // Command 에 트랜잭션 연결
try
{
string xSql = string.Empty;
xSql += "INSERT INTO t_lecturer (lecturer_id, "; // 강사ID
xSql += " lecturer_nm, "; // 강사명
xSql += " lecturer_nm_eng, "; // 강사영문명
xSql += " birth_dt, "; // 생년월일
xSql += " grade, "; // 강사등급(A,B,C,D)
xSql += " user_id, "; // 사용자ID
xSql += " job, "; // 직업
xSql += " education, "; // 학력
xSql += " major, "; // 전공
xSql += " org_nm, "; // 소속구분
xSql += " company_nm, "; // 회사명
xSql += " duty_step, "; // 직위
xSql += " zip_code, "; // 우편번호
xSql += " company_addr, "; // 주소
xSql += " tel_no, "; // 전화번호
xSql += " mobile_phone, "; // 휴대폰번호
xSql += " email, "; // 이메일
xSql += " acc_bank, "; // 은행코드
xSql += " account, "; // 계좌번호
xSql += " status, "; // 상태
xSql += " ins_id, "; // 작성자 ID
xSql += " ins_dt, "; // 작성일자
xSql += " upt_id, "; // 수정자 ID
xSql += " upt_dt) "; // 수정일자
xSql += string.Format(" VALUES ( '{0}', ", rParams[0]);
xSql += string.Format("'{0}', ", rParams[1]);
xSql += string.Format("'{0}', ", rParams[2]);
if (rParams[3] == null)
{
xSql += string.Format(" null, ", rParams[3]);
}
else
{
xSql += string.Format(" TO_DATE('{0}', 'yyyy.MM.dd'), ", rParams[3]);
}
xSql += string.Format("'{0}', ", rParams[4]);
xSql += string.Format("'{0}', ", rParams[5]);
xSql += string.Format("'{0}', ", rParams[6]);
xSql += string.Format("'{0}', ", rParams[7]);
xSql += string.Format("'{0}', ", rParams[8]);
xSql += string.Format("'{0}', ", rParams[9]);
xSql += string.Format("'{0}', ", rParams[10]);
xSql += string.Format("'{0}', ", rParams[11]);
xSql += string.Format("'{0}', ", rParams[12]);
xSql += string.Format("'{0}', ", rParams[13]);
xSql += string.Format("'{0}', ", rParams[14]);
xSql += string.Format("'{0}', ", rParams[15]);
xSql += string.Format("'{0}', ", rParams[16]);
xSql += string.Format("'{0}', ", rParams[17]);
xSql += string.Format("'{0}', ", rParams[18]);
xSql += string.Format("'{0}', ", rParams[19]);
xSql += string.Format("'{0}', ", rParams[20]); // 작성자 ID
xSql += " SYSDATE, "; // 작성일자
xSql += string.Format("'{0}', ", rParams[20]); // 수정자 ID
xSql += " SYSDATE) "; // 수정일자
xCmdLMS.CommandText = xSql;
base.Execute(db, xCmdLMS, xTransLMS);
xTransLMS.Commit(); // 트랜잭션 커밋
OracleParameter[] oraParams = null;
try
{
xSql = @"
UPDATE t_lecturer
SET RES_FILE_NM = :RES_FILE_NM
, RES_FILE = :RES_FILE
WHERE lecturer_id = :LECTURER_ID
";
oraParams = new OracleParameter[3];
oraParams[0] = base.AddParam("RES_FILE_NM", OracleType.VarChar, rFileNameRes);
oraParams[1] = base.AddParam("RES_FILE", OracleType.Blob, rFileRes.Length, rFileRes);
oraParams[2] = base.AddParam("LECTURER_ID", OracleType.VarChar, rParams[0]);
base.ExecuteScalar("LMS", xSql, oraParams);
}
catch { }
try
{
xSql = @"
UPDATE t_lecturer
SET DOC_FILE_NM1 = :DOC_FILE_NM1
, DOC_FILE1 = :DOC_FILE1
WHERE lecturer_id = :LECTURER_ID
";
oraParams = new OracleParameter[3];
oraParams[0] = base.AddParam("DOC_FILE_NM1", OracleType.VarChar, rFileNameDoc);
oraParams[1] = base.AddParam("DOC_FILE1", OracleType.Blob, rFileDoc.Length, rFileDoc);
oraParams[2] = base.AddParam("LECTURER_ID", OracleType.VarChar, rParams[0]);
base.ExecuteScalar("LMS", xSql, oraParams);
}
catch { }
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);
}
/// <summary>
/// 开始一个新事务
/// </summary>
/// <returns>事务对象</returns>
public IDbTransaction BeginTransaction()
{
m_Transaction = m_Connection.BeginTransaction();
return(m_Transaction);
}
public static bool SaveEmployeePosting(List <ATTEmployeePosting> lstEmployeePosting, OracleTransaction Tran, double empID)
{
try
{
foreach (ATTEmployeePosting lst in lstEmployeePosting)
{
OracleParameter[] paramArray = new OracleParameter[18];
paramArray[0] = Utilities.GetOraParam(":p_EMP_ID", empID, OracleDbType.Double, ParameterDirection.Input);
paramArray[1] = Utilities.GetOraParam(":p_ORG_ID", lst.OrgID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[2] = Utilities.GetOraParam(":p_DES_ID", lst.DesID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[3] = Utilities.GetOraParam(":p_CREATED_DATE", lst.CreatedDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[4] = Utilities.GetOraParam(":p_POST_ID", lst.PostID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[5] = Utilities.GetOraParam(":p_FROM_DATE", lst.FromDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[6] = Utilities.GetOraParam(":p_TO_DATE", lst.ToDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[7] = Utilities.GetOraParam(":p_JOINING_DATE", lst.JoiningDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[8] = Utilities.GetOraParam(":p_DECISION_DATE", lst.DecisionDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[9] = Utilities.GetOraParam(":p_LEAVE_DATE", lst.LeaveDate, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[10] = Utilities.GetOraParam(":p_POSTING_TYPE_ID", lst.PostingTypeID, OracleDbType.Int64, ParameterDirection.Input);
paramArray[11] = Utilities.GetOraParam(":p_EMP_POSTING_SALARY", lst.EmpSalary, OracleDbType.Int64, ParameterDirection.Input);
paramArray[12] = Utilities.GetOraParam(":p_EMP_POSTING_ALLOWANCE", lst.EmpAllowance, OracleDbType.Int64, ParameterDirection.Input);
paramArray[13] = Utilities.GetOraParam(":p_EMP_POSTING_KITAAB_DARTA_NO", lst.EmpKitaabDartaNo, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[14] = Utilities.GetOraParam(":p_EMP_POSTING_REMARKS", lst.EmpPostingRemarks, OracleDbType.Varchar2, ParameterDirection.Input);
if (lst.PostingAttachmentDocs != null && lst.PostingAttachmentDocs.Length > 0)
{
paramArray[15] = Utilities.GetOraParam(":p_ATTACHMENT", lst.PostingAttachmentDocs, OracleDbType.Blob, ParameterDirection.Input);
}
else
{
paramArray[15] = Utilities.GetOraParam(":p_ATTACHMENT", null, OracleDbType.Blob, ParameterDirection.Input);
}
paramArray[16] = Utilities.GetOraParam(":p_ATTACHMENT_FILE_NAME", lst.PostingAttachmentContent, OracleDbType.Varchar2, ParameterDirection.Input);
paramArray[17] = Utilities.GetOraParam(":p_ENTRY_BY", lst.EntryBy, OracleDbType.Varchar2, ParameterDirection.Input);
if (lst.Action == "A")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_ADD_EMP_POSTINGS", paramArray);
}
else if (lst.Action == "E")
{
SqlHelper.ExecuteNonQuery(Tran, CommandType.StoredProcedure, "SP_EDIT_EMP_POSTINGS", paramArray);
}
}
return(true);
}
catch (OracleException oex)
{
PCS.COREDL.OracleError oe = new PCS.COREDL.OracleError();
throw new ArgumentException(oe.GetOraError(oex.Number, oex.Message));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Execute an OracleCommand (that returns no resultset) against the specified OracleTransaction
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new OracleParameter("@prodid", 24));
/// </remarks>
/// <param name="transaction">a valid OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <param name="commandParameters">an array of OracleParameters used to execute the command</param>
/// <returns>an int representing the number of rows affected by the command</returns>
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//create a command and prepare it for execution
OracleCommand cmd = new OracleCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
//finally, execute the command.
return cmd.ExecuteNonQuery();
}
/// <summary>
/// 根据事务锁定级别创建一个新事务
/// </summary>
/// <param name="isoLevel">事务锁定级别</param>
/// <returns>事务对象</returns>
public IDbTransaction BeginTransaction(System.Data.IsolationLevel isoLevel)
{
m_Transaction = m_Connection.BeginTransaction(isoLevel);
return(m_Transaction);
}
/// <summary>
/// Execute a stored procedure via an OracleCommand (that returns a resultset) against the specified
/// OracleTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid OracleTransaction</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>a dataset containing the resultset generated by the command</returns>
public static DataSet ExecuteDataset(OracleTransaction transaction, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
//pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
//assign the provided values to these parameters based on parameter order
AssignParameterValues(commandParameters, parameterValues);
//call the overload that takes an array of OracleParameters
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
public static int ExecuteNonQuery(OracleTransaction transaction, CommandType commandType, string commandText)
{
return(ExecuteNonQuery(transaction, commandType, commandText, (OracleParameter[])null));
}
/// <summary>
/// Execute an OracleCommand (that returns a resultset and takes no parameters) against the provided OracleTransaction.
/// </summary>
/// <remarks>
/// e.g.:
/// OracleDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders");
/// </remarks>
/// <param name="transaction">a valid OracleTransaction</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or PL/SQL command</param>
/// <returns>an OracleDataReader containing the resultset generated by the command</returns>
public static OracleDataReader ExecuteReader(OracleTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return ExecuteReader(transaction, commandType, commandText, (OracleParameter[])null);
}
public static OracleDataReader ExecuteReader(OracleTransaction transaction, CommandType commandType, string commandText, params OracleParameter[] commandParameters)
{
//pass through to private overload, indicating that the connection is owned by the caller
return(ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, OracleConnectionOwnership.External));
}
/// <summary>
/// Execute a stored procedure via an OracleCommand (that returns a resultset) against the specified
/// OracleTransaction using the provided parameter values. This method will query the database to discover the parameters for the
/// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
/// </summary>
/// <remarks>
/// This method provides no access to output parameters or the stored procedure's return value parameter.
///
/// e.g.:
/// OracleDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36);
/// </remarks>
/// <param name="transaction">a valid OracleTransaction</param>
/// <param name="spName">the name of the stored procedure</param>
/// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
/// <returns>an OracleDataReader containing the resultset generated by the command</returns>
public static OracleDataReader ExecuteReader(OracleTransaction transaction, string spName, params object[] parameterValues)
{
//if we got parameter values, we need to figure out where they go
if ((parameterValues != null) && (parameterValues.Length > 0))
{
OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
//otherwise we can just call the SP without params
else
{
return ExecuteReader(transaction, CommandType.StoredProcedure, spName);
}
}
private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
//associate the connection with the command
command.Connection = connection;
//set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;
//if we were provided a transaction, assign it.
if (transaction != null)
{
//command.Transaction = transaction;
}
//set the command type
command.CommandType = commandType;
//attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
/// <summary>
/// Starts a new ADO.NET transaction using the open connection object of this class.
/// Opens the connection if it isn't already open.
/// No nesting allowed, throws exception if the transaction is already pending.
/// </summary>
/// <remarks>Uses default <see cref="IsolationLevel"/>.</remarks>
public void BeginTransaction()
{
if (this.defaultTransactionIsolationLevel != null)
{
BeginTransaction(this.defaultTransactionIsolationLevel.Value);
}
else
{
if (this.isTransactionPending)
throw new InvalidOperationException(Messages.ConnectionProvider_TranAlreadyPending);
if (!IsOpen)
OpenConnection();
this.oraTran = this.oraConn.BeginTransaction();
this.isTransactionPending = true;
}
}
public static XmlReader ExecuteXmlReader(OracleTransaction transaction, CommandType commandType, string commandText)
{
//pass through the call providing null for the set of OracleParameters
return(ExecuteXmlReader(transaction, commandType, commandText, (OracleParameter[])null));
}
/// <summary>
/// Releases the resources used by this object.
/// </summary>
private void Dispose(bool isDisposing)
{
// Check to see if Dispose has already been called.
if (this.isDisposed == false)
{
if (isDisposing)
{
// Dispose managed resources.
if (this.oraTran != null)
{
this.oraTran.Dispose();
this.oraTran = null;
}
// Closing the connection will abort (rollback) any pending transactions.
if (this.oraConn != null)
{
this.oraConn.Close();
this.oraConn.Dispose();
this.oraConn = null;
}
}
}
this.isDisposed = true;
}
/************************************************************
* Function name : SetLecturerDelete
* Purpose : 강사정보 삭제(변경) 실제로 삭제 하지는 않음
* Input : string[] rParams
* Output : String Bollean Type
*************************************************************/
public string SetLecturerDelete(string[,] rParams)
{
string xRtn = Boolean.FalseString;
Database db = base.GetDataBase("LMS");
OracleConnection xCnnLMS = (OracleConnection)db.CreateConnection();
xCnnLMS.Open();
OracleTransaction xTrnsLMS = null;
OracleCommand xCmdLMS = null;
try
{
xTrnsLMS = xCnnLMS.BeginTransaction(); // 트랜잭션 시작
xCmdLMS = base.GetSqlCommand(db);
xCmdLMS.Connection = xCnnLMS;
xCmdLMS.Transaction = xTrnsLMS;
try
{
//int j = xParams.GetLength(0);
for (int i = 0; i < rParams.GetLength(0); i++)
{
string xsql = " UPDATE t_lecturer SET ";
xsql += string.Format(" status = '{0}', ", rParams[i, 2]);
xsql += string.Format(" del_id = '{0}', ", rParams[i, 1]);
xsql += " del_dt = SYSDATE ";
xsql += string.Format(" WHERE lecturer_id = '{0}'", rParams[i, 0]);
xCmdLMS.CommandText = xsql;
base.Execute(db, xCmdLMS, xTrnsLMS);
}
xTrnsLMS.Commit(); // 트랜잭션 커밋
xRtn = Boolean.TrueString;
}
catch (Exception ex)
{
xTrnsLMS.Rollback(); // Exception 발생시 롤백처리...
throw ex;
}
finally
{
if (xCmdLMS != null)
{
xCmdLMS.Dispose();
}
if (xTrnsLMS != null)
{
xTrnsLMS.Dispose();
}
}
}
catch (Exception ex)
{
bool rethrow = ExceptionPolicy.HandleException(ex, "Propagate Policy");
if (rethrow)
{
throw;
}
}
return(xRtn);
}
