/// <summary>
/// 更新大文本字符串保存到数据库
/// 王方圆添加 2017-5-16
/// </summary>
/// <param name="tableName">表明</param>
/// <param name="id">唯一标识ID</param>
/// <param name="longTextFieldName">字段名称</param>
/// <param name="longTextContent">更新文本内容</param>
public static void UpdateLongText(string tableName, long id, string longTextFieldName, string longTextContent)
{
string connectStr = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultDB"].ConnectionString;
using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connectStr))
{
conn.Open();
//OracleTransaction trans = conn.BeginTransaction();
Oracle.DataAccess.Client.OracleCommand cmd = conn.CreateCommand();
//cmd.Transaction = trans;
cmd.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :templob := xx; end;";
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":templob", Oracle.DataAccess.Client.OracleDbType.Clob)).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleClob tmplob = (OracleClob)cmd.Parameters[0].Value;
byte[] buffer = System.Text.Encoding.Unicode.GetBytes(longTextContent);
tmplob.Write(buffer, 0, buffer.Length);
tmplob.Position = 0;
cmd.Parameters.Clear();
string cmdText = "update {0} set {1} = :lob where EMAIL_ID= :id";
cmdText = string.Format(cmdText, tableName, longTextFieldName);
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":lob", Oracle.DataAccess.Client.OracleDbType.Clob)).Value = tmplob;
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter(":id", id));
cmd.ExecuteNonQuery();
}
}
private void FillSupervisorInfo()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_trn_fdic.supervisor_info", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["ee_number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "supervisor_employee_number_", "varchar2", "out", null);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "supervisor_name_", "varchar2", "out", null);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "client_employee_number_", "varchar2", "out", null);
try
{
conn.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["client_employee_number_"].Value != null)
{
try
{
ViewState["sup_number"] = cmd.Parameters["supervisor_employee_number_"].Value.ToString();
txtSupervisor.Text = cmd.Parameters["supervisor_name_"].Value.ToString();
txtSupervisorClientNumber.Text = cmd.Parameters["client_employee_number_"].Value.ToString();
}
catch
{
}
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
public static void CheckBudgetForApproval(string strheader_record_id, string strCurseExpense,
ref string returned_condintion, ref string returned_test)
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Data.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("BASDBA.pkg_trn_budget.checbudgetbeforeapproval_ee", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "pla_header_record_id_", strheader_record_id);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "course_expense_", strCurseExpense);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "return_condition_", "varchar2", "out", null, 10);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "return_condition_text_", "varchar2", "out", null, 4000);
conn.Open();
try
{
cmd.ExecuteNonQuery();
returned_condintion = cmd.Parameters["return_condition_"].Value.ToString();
if (returned_condintion != "1")
{
returned_test = cmd.Parameters["return_condition_text_"].Value.ToString();
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
public static void insert(string sqlInsert)
{
Oracle.DataAccess.Client.OracleCommand cmdInsert = new Oracle.DataAccess.Client.OracleCommand(sqlInsert, conn);
cmdInsert.ExecuteNonQuery();
cmdInsert.Dispose();
}
public static void alter(string sqlAlter)
{
Oracle.DataAccess.Client.OracleCommand cmdAlter = new Oracle.DataAccess.Client.OracleCommand(sqlAlter, conn);
cmdAlter.ExecuteNonQuery();
cmdAlter.Dispose();
}
public static void delete(string sqlDelete)
{
Oracle.DataAccess.Client.OracleCommand cmdDelete = new Oracle.DataAccess.Client.OracleCommand(sqlDelete, conn);
cmdDelete.ExecuteNonQuery();
cmdDelete.Dispose();
}
private void Save2ndApproval(Oracle.DataAccess.Client.OracleConnection conn)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.req_apprvd_by_2nd_sprvsor", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
Oracle.DataAccess.Client.OracleParameter parm = null;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
ViewState["Saved"] = "";
cmd.ExecuteNonQuery();
if ((cmd.Parameters["error_msg_"].Value != null) && (cmd.Parameters["error_msg_"].Value.ToString() != ""))
{
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
ViewState["Saved"] = "F";
}
}
finally
{
if (parm != null)
{
parm = null;
}
cmd.Dispose();
}
}
private bool DoSave_cdp()
{
bool blnSaved = true;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training_cdp.req_apprvd_by_sprvsor", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
ViewState["Saved"] = "";
conn.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["error_msg_"].Value != null)
{
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
ViewState["Saved"] = "F";
}
}
catch
{
blnSaved = false;
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return(blnSaved);
}
public static void SavePDF(string session_id_, string strName, byte[] bValue)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Enrollment_Wizard_LE.save_LE_Doc_upload", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "session_id_", session_id_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_name_", strName);
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, bValue.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, bValue);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
conn.Clone();
conn.Dispose();
}
}
public void insertMessengerRecordIntoDB(string filePath, string startTime, string endTime, int portNumber)
{
try
{
// 오라클 연결
conn = new Oracle.DataAccess.Client.OracleConnection(connectionInfo);
conn.Open();
//명령 실행을 위한 커맨드 객체 생성
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
cmd.Connection = conn;
// SQL문 지정 및 INSERT 실행
cmd.CommandText = "insert into KBJ_MESSENGER(start_time, end_time, file_path, port_number) " +
"values(TO_DATE('" + startTime + "', 'YYYY.MM.DD-HH24:MI:SS')," +
" TO_DATE('" + endTime + "', 'YYYY.MM.DD-HH24:MI:SS'), '" +
filePath + "', " + portNumber + " )";
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception)
{
MessageBox.Show(LanguageResource.language_res.strExceptionMessageDBInsertFailed);
}
finally
{
conn.Close();
}
}
private void ResetCDPStatus()
{
string strEmployee_Number = ViewState["Employee_Number"].ToString();
string strSQL = "select pkg_training_cdp.headerRecordID('" + strEmployee_Number + "') from dual";
string cdp_Header_id = SQLStatic.SQL.ExecScaler(strSQL, Training_Source.TrainingClass.ConnectioString).ToString();
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training_cdp.set_application_status", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", cdp_Header_id);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ViewState["Processing_Year"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", ViewState["User_Name"].ToString());
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch {}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
//带错误输出，无返回值的SQL执行（默认是Text）
public string ExecuteNonQuery(string cmdText)
{
cmdText = SqlTransfer(cmdText);
string retVal = "0";
using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connStr))
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdText, conn);
cmd.CommandType = CommandType.Text;
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
retVal = cmd.ExecuteNonQuery().ToString();
}
catch (Exception exp)
{
LogWriter.Error(exp, cmdText);
//retVal = exp.Message;
throw exp;
}
finally
{
conn.Close();
}
}
return(retVal);
}
public static void SavePDF(string account_number_, string employee_number_, string dependent_sequence_no_, string user_name_,
string doc_name_, byte[] bValue)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_dependents_audit.save_Verify_Doc_upload", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "account_number_", account_number_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", employee_number_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "dependent_sequence_no_", dependent_sequence_no_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", user_name_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_name_", doc_name_);
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, bValue.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, bValue);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
conn.Clone();
conn.Dispose();
}
}
private bool DoSave()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(
"basdba.PKG_Training.Select_New_Supervisor", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "supervisor_employee_number_", "varchar2", "in", ViewState["Supervisor_Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", "varchar2", "in", ViewState["User_Name"].ToString());
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
string strStatus = Training_Source.TrainingClass.ApplicationStatus(ViewState["Request_Record_ID"].ToString());
if (Convert.ToInt16(strStatus) > 0)
{
lblifo.Visible = true;
}
else
{
lblifo.Visible = false;
}
return(true);
}
public string First_step_in_wizard()
{
string FirststepinWiz = null;
string sessID = Request.Cookies["Session_ID"].Value.ToString();
// create the command for the function
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
Oracle.DataAccess.Client.OracleConnection conn =
new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
cmd.Connection = conn;
cmd.CommandText = "pkg_wizard.First_step_in_wizard";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("session_id_", Oracle.DataAccess.Client.OracleDbType.Varchar2, 100, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, sessID));
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("first_step_id_", Oracle.DataAccess.Client.OracleDbType.Double, 20, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
cmd.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("first_step_url_", Oracle.DataAccess.Client.OracleDbType.Varchar2, 255, System.Data.ParameterDirection.Output, false, ((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, null));
// execute the function
conn.Open();
cmd.ExecuteNonQuery();
FirststepinWiz = Convert.ToString(cmd.Parameters[2].Value);
cmd.Dispose();
conn.Close();
conn.Dispose();
return(FirststepinWiz);
}
private void DoSave()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.CancelPaidRequest", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
string strUserID = "";
if (ViewState["User_Name"] != null)
{
strUserID = ViewState["User_Name"].ToString();
}
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtReason.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", strUserID);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", null);
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
private bool DoSave()
{
string strProcedureName = "basdba.PKG_Training.SupervisorCancelRequest";
if (Request.Params["who"] == "payor")
{
strProcedureName = "basdba.PKG_Training.AdministratorCancelRequest";
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
bool blnOk = true;
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtOther.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
cmd.ExecuteNonQuery();
if ((cmd.Parameters["error_msg_"].Value != null) && (cmd.Parameters["error_msg_"].Value.ToString() != ""))
{
blnOk = false;
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
}
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return(blnOk);
}
private bool SetEmployeeNumber()
{
bool eeFound = true;
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training.set_employee_number", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "User_id_", ViewState["User_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Session_id_", Request.Cookies["Session_ID"].Value.ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", "varchar2", "out", null, 20);
try
{
conn.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["employee_number_"].Value == null)
{
eeFound = false;
}
else
{
ViewState["Employee_Number"] = cmd.Parameters["employee_number_"].Value.ToString();
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
return(eeFound);
}
private void DoDelete()
{
if (!Training_Source.TrainingClass.IsReadOnly(Request.Cookies["Session_ID"].Value.ToString()))
{
string strProcedure = "basdba.PKG_Training.deleteExpense";
if (Request.Params["Admin"] == "YES")
{
strProcedure = "basdba.PKG_Training.deleteExpense_Admin";
}
string strIndex = SQLStatic.Sessions.GetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "deleteIndex", Training_Source.TrainingClass.ConnectioString);
SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "deleteIndex", "", Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedure, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", strIndex);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
}
private bool DoSave(Oracle.DataAccess.Client.OracleConnection conn)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.req_Partial_Payment", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
bool blnOk = true;
Oracle.DataAccess.Client.OracleParameter parm = null;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtMemo.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
cmd.ExecuteNonQuery();
if ((cmd.Parameters["error_msg_"].Value != null) && (cmd.Parameters["error_msg_"].Value.ToString() != ""))
{
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
blnOk = false;
}
}
catch
{
}
finally
{
if (parm != null)
{
parm = null;
}
cmd.Dispose();
}
return(blnOk);
}
private void GetInvolvedEmployeesData()
{
txtMemo.Text = "";
lblReplayFrom.Text = Training_Source.TrainingClass.Employee_Name(ViewState["Employee_Number"].ToString());
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.Get_Possible_CommEEs", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Communication_record_id_", "number", "in", ViewState["comm_record_id"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_employee_number_", "number", "in", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "sprvsr_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "sprvsr_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "payor_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "payor_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "applicant_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "applicant_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "from_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "from_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "from_position_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "to_employee_number_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "to_name_", "varchar2", "out", "", 100);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "to_position_", "varchar2", "out", "", 100);
cmd.ExecuteNonQuery();
lblFromPosition.Text = cmd.Parameters["from_position_"].Value.ToString();
lblFromName.Text = cmd.Parameters["from_name_"].Value.ToString();
lblToPosition.Text = cmd.Parameters["to_position_"].Value.ToString();
lblToName.Text = cmd.Parameters["to_name_"].Value.ToString();
chklstEmailTo.Items.Clear();
if (cmd.Parameters["sprvsr_employee_number_"].Value != null)
{
AddItemToSelection("Supervisor", cmd.Parameters["sprvsr_employee_number_"].Value.ToString(), cmd.Parameters["sprvsr_name_"].Value.ToString());
}
if (cmd.Parameters["payor_employee_number_"].Value != null)
{
AddItemToSelection("Administrator", cmd.Parameters["payor_employee_number_"].Value.ToString(), cmd.Parameters["payor_name_"].Value.ToString());
}
if (cmd.Parameters["applicant_employee_number_"].Value != null)
{
AddItemToSelection("Applicant", cmd.Parameters["applicant_employee_number_"].Value.ToString(), cmd.Parameters["applicant_name_"].Value.ToString());
}
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
}
private void SaveTypesofDevelopment(Oracle.DataAccess.Client.OracleConnection conn)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(
"basdba.PKG_Training.add_type_of_development", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
try
{
foreach (ListItem li in chklstTypeofDev.Items)
{
if (li.Selected)
{
cmd.Parameters.Clear();
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "type_of_development_", "number", "in", li.Value);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", "varchar2", "in", ViewState["User_Name"].ToString());
cmd.ExecuteNonQuery();
}
}
}
finally
{
cmd.Dispose();
}
}
public static void Request_info_doc(string employee_number_, string account_number_, string log_user_name_, string r_log_id_,
string reason_, string doc_name_, byte[] bValue)
{
Oracle.DataAccess.Client.OracleConnection conn = SQLStatic.SQL.OracleConnection();
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_Dependent_Audit_Wizard.Request_info_doc", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", employee_number_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "account_number_", account_number_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "log_user_name_", log_user_name_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "r_log_id_", r_log_id_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", reason_);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "doc_name_", doc_name_);
Oracle.DataAccess.Client.OracleParameter parm;
parm = new Oracle.DataAccess.Client.OracleParameter(
"value_", Oracle.DataAccess.Client.OracleDbType.Blob, bValue.Length, System.Data.ParameterDirection.Input, true,
((System.Byte)(0)), ((System.Byte)(0)), "", System.Data.DataRowVersion.Current, bValue);
cmd.Parameters.Add(parm);
try
{
cmd.ExecuteNonQuery();
}
finally
{
SQLStatic.SQL.CloseConnection(conn);
cmd.Dispose();
}
}
private void saveSupervisor()
{
ViewState["Product_Code"] = SQLStatic.Sessions.GetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "Product_Code", PLA_Approval.TrainingClass.ConnectionString);
string strProcedureName = "basdba.PKG_Training.reRouteSupervisor";
if (ViewState["Product_Code"].ToString() == "11001")
{
strProcedureName = "basdba.PKG_Training_cdp.reRouteSupervisor";
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
ViewState["Request_Record_ID"] = SQLStatic.Sessions.GetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "Request_Record_ID", PLA_Approval.TrainingClass.ConnectionString);
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "supervisor_employee_number_", "varchar2", "in", ViewState["Supervisor_Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_name_", "varchar2", "in", ViewState["User_Name"].ToString());
conn.Open();
cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
private bool DoSave_cdp()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training_cdp.req_dclnd_by_sprvsor", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
bool blnOk = true;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtMemo.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
cmd.ExecuteNonQuery();
if (cmd.Parameters["error_msg_"].Value != null)
{
blnOk = false;
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
}
}
finally
{
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return(blnOk);
}
private void ExcecuteNonQuery(Oracle.DataAccess.Client.OracleCommand command, string storedProcName, Oracle.DataAccess.Client.OracleConnection dbConnection)
{
command.Connection = dbConnection;
command.CommandText = storedProcName;
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
private void SaveOne(Oracle.DataAccess.Client.OracleConnection conn, string strEmployeeNumber)
{
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("basdba.PKG_Training.add_Communication_memo", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
Oracle.DataAccess.Client.OracleParameter parm = null;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "rqst_header_record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "from_employee_number_", "number", "in", ViewState["Loged_Employee_Number"]);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "to_employee_number_", "number", "in", strEmployeeNumber);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "subject_", "varchar2", "in", txtSubject.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "memo_body_", "varchar2", "in", txtMemo.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "add_user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "send_email", "number", "in", 1);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "new_record_id_", "number", "out", "");
cmd.ExecuteNonQuery();
ViewState["New_Comm_ID"] = cmd.Parameters["new_record_id_"].Value.ToString();
}
finally
{
if (parm != null)
{
parm = null;
}
cmd.Dispose();
}
}
private void SaveData()
{
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Data.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(
"basdba.PKG_Training.SaveUpdateHeaderRecord", conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Product_Code_", "number", "in", ViewState["Product_Code"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", "number", "in", ViewState["Employee_Number"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", "varchar2", "in", ViewState["Processing_Year"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "course_code_", "varchar2", "in", txtCourseCode.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "course_title_", "varchar2", "in", txtCourseTitle.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_name_", "varchar2", "in", txtVedorName.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_contact_", "varchar2", "in", txtVendorContact.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_address1_", "varchar2", "in", txtAddressLine1.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_address2_", "varchar2", "in", txtAddressLine2.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_city_", "varchar2", "in", txtCity.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_state_", "varchar2", "in", ddlStates.SelectedValue);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_zip_code_", "varchar2", "in", txtZipCode.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_phone_number_", "varchar2", "in", txtPhoneNumber.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_fax_number_", "varchar2", "in", txtFaxNumber.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_website_", "varchar2", "in", txtWebSite.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "vendor_email_", "varchar2", "in", txtEmail.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "desription_of_course_value_", "varchar2", "in", txtDescribtion.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "location_of_training_", "varchar2", "in", "");
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "training_address1_", "varchar2", "in", txtLearningAddress1.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "training_address2_", "varchar2", "in", txtLearningAddress2.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "training_city_", "varchar2", "in", txtLearningCity.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "training_state_", "varchar2", "in", ddlLearningState.SelectedValue);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "training_zip_", "varchar2", "in", txtLearningZipCode.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "country_code_", ddlContries.SelectedValue);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "province_", txtProvince.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "training_province_", txtLearningProvince.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "training_country_code_", ddlTrainingContries.SelectedValue);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "book_request_", ViewState["Book"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "BookCost_", txtBookCost.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Dept_id_", txtDeptID.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "new_record_id_", "number", "out", "");
conn.Open();
cmd.ExecuteNonQuery();
ViewState["Request_Record_ID"] = cmd.Parameters["new_record_id_"].Value;
SQLStatic.Sessions.SetSessionValue(Request.Cookies["Session_ID"].Value.ToString(), "Request_Record_ID", ViewState["Request_Record_ID"].ToString(), Data.ConnectioString);
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
}
private void SADADDataUpload(ulong agreementID, decimal amount, string remarks, string billNumber, string paymentRef)
{
try
{
#region Call stored Procedure
using (var command = new Oracle.DataAccess.Client.OracleCommand())
{
command.Connection = OraConnection.GetConnection();
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "SADAD_manual_data_upload";
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("p_Agreementid", Oracle.DataAccess.Client.OracleDbType.Int64)).Value = agreementID;
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("p_Chqamount", Oracle.DataAccess.Client.OracleDbType.Double)).Value = amount;
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("p_remarks", Oracle.DataAccess.Client.OracleDbType.Varchar2, 100)).Value = remarks;
Oracle.DataAccess.Client.OracleParameter errorcodeparam = new Oracle.DataAccess.Client.OracleParameter("Error_code", Oracle.DataAccess.Client.OracleDbType.Int32);
errorcodeparam.Direction = ParameterDirection.Output;
Oracle.DataAccess.Client.OracleParameter errormsgparam = new Oracle.DataAccess.Client.OracleParameter("Error_msg", Oracle.DataAccess.Client.OracleDbType.Varchar2, 100);
errorcodeparam.Direction = ParameterDirection.Output;
command.Parameters.Add(errorcodeparam);
command.Parameters.Add(errormsgparam);
command.ExecuteNonQuery();
string errorcode = command.Parameters["Error_code"].Value.ToString();
string errorMessage = command.Parameters["Error_msg"].Value.ToString();
if (!string.IsNullOrWhiteSpace(errorcode))
{
if (errorcode.ToLower() != "null")
{
UnprocessedRec(billNumber, paymentRef);
Logger.TrackLogs("Invalid Agreement ID: '" + agreementID + "' with amount : '" + amount + "' , Error Code Message : '" + errorcode + "' , Error Message : '" + errorMessage + "'", "after calling 'SADAD_manual_data_upload' Procedure");
}
}
else
{
Logger.TrackLogs("Agreement ID: '" + agreementID + "' with amount : '" + amount + "' Added Successfully", "after calling 'SADAD_manual_data_upload' Procedure");
}
command.Connection.Close();
}
#endregion
}
catch (Exception ex)
{
UnprocessedRec(billNumber, paymentRef);
Logger.TrackLogs("Invalid Agreement ID: '" + agreementID + "' with amount : '" + amount + "' , Error Code Message : '" + ex.Source + "' , Error Message : '" + ex.Message + "'", "after calling 'SADAD_manual_data_upload' Procedure");
}
}
private bool DoSave(string strMsgList)
{
string strProcedureName;
if (ViewState["From_2nd_supervisor"].ToString() == "T")
{
strProcedureName = "basdba.PKG_Training.req_dclnd_by_2nd_sprvsor";
}
else
{
strProcedureName = "basdba.PKG_Training.req_dclnd_by_sprvsor";
}
int intIs_payor = 0;
if (Request.Params["who"] == "payor")
{
intIs_payor = 1;
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(PLA_Approval.TrainingClass.ConnectionString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
bool blnOk = true;
Oracle.DataAccess.Client.OracleParameter parm = null;
try
{
int intFileLen = txtMemo.Text.Length;
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
if (ViewState["From_2nd_supervisor"].ToString() != "T")
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "is_payor", "number", "in", intIs_payor);
}
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_", "varchar2", "in", txtMemo.Text);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "resonn_sel", "varchar2", "in", strMsgList);
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", "");
cmd.ExecuteNonQuery();
if ((cmd.Parameters["error_msg_"].Value != null) && (cmd.Parameters["error_msg_"].Value.ToString() != ""))
{
blnOk = false;
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
}
}
finally
{
if (parm != null)
{
parm = null;
}
cmd.Dispose();
conn.Close();
conn.Dispose();
}
return(blnOk);
}
private void BankChargeEntry(string transactionType, string channel, decimal amount, string cardType, string customerNo, string customerName, string billNumber, string paymentRef)
{
try
{
#region Call stored Procedure
using (var command = new Oracle.DataAccess.Client.OracleCommand())
{
command.Connection = OraConnection.GetConnection();
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "trx_bank_charge_entry";
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("TRX_TYPE", Oracle.DataAccess.Client.OracleDbType.Varchar2)).Value = transactionType;
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("TRX_CHANEL", Oracle.DataAccess.Client.OracleDbType.Varchar2)).Value = channel;
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("TRX_AMT", Oracle.DataAccess.Client.OracleDbType.Decimal, 100)).Value = amount;
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("CARD_TYPE", Oracle.DataAccess.Client.OracleDbType.Varchar2)).Value = cardType;
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("customer_no", Oracle.DataAccess.Client.OracleDbType.Varchar2)).Value = customerNo;
command.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter("CUSTOMER_NAME", Oracle.DataAccess.Client.OracleDbType.Varchar2)).Value = customerName;
Oracle.DataAccess.Client.OracleParameter errorcodeparam = new Oracle.DataAccess.Client.OracleParameter("VERROR", Oracle.DataAccess.Client.OracleDbType.Varchar2);
errorcodeparam.Direction = ParameterDirection.Output;
command.Parameters.Add(errorcodeparam);
command.ExecuteNonQuery();
string errorMessage = command.Parameters["VERROR"].Value.ToString();
if (!string.IsNullOrWhiteSpace(errorMessage))
{
if (errorMessage.ToLower() != "null")
{
UnprocessedRec(billNumber, paymentRef);
Logger.TrackLogs("Invalid Bank Charge Entry Process for Transaction Type : '" + transactionType + "' , Customer No : '" + customerNo + "' , Customer Name : '" + customerName + "' , Card Type :'" + cardType + "' , Error Message : '" + errorMessage + "'", "after calling 'trx_bank_charge_entry' Procedure");
}
}
else
{
Logger.TrackLogs("Transaction Type : '" + transactionType + "' , Customer No : '" + customerNo + "' , Customer Name : '" + customerName + "' , Card Type :'" + cardType + "' , Added Successfully", "after calling 'trx_bank_charge_entry' Procedure");
}
command.Connection.Close();
}
#endregion
}
catch (Exception ex)
{
UnprocessedRec(billNumber, paymentRef);
Logger.TrackLogs("Invalid Bank Charge Entry Process for Transaction Type : '" + transactionType + "' , Customer No : '" + customerNo + "' , Customer Name : '" + customerName + "' , Card Type :'" + cardType + "' , Error Message : '" + ex.Message + "'", "after calling 'trx_bank_charge_entry' Procedure");
}
}
private bool DoSave()
{
string strProcedureName = "basdba.PKG_Training.submitForApproval";
if (Request.Params["c"] == "y")
{
strProcedureName = "basdba.PKG_Training.CancelRequest";
}
if (Request.Params["c"] == "r")
{
strProcedureName = "basdba.PKG_Training.ReactivateRequest";
}
Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString);
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strProcedureName, conn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 30;
bool blnResult = false;
try
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "record_id_", "number", "in", ViewState["Request_Record_ID"].ToString());
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "user_id_", "varchar2", "in", ViewState["User_Name"].ToString());
if (strProcedureName == "basdba.PKG_Training.submitForApproval")
{
if (opnSubmitedForm.Visible)
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "require_continuation_", "varchar2", "in", "1");
}
else
{
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "require_continuation_", "varchar2", "in", "0");
}
}
SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "error_msg_", "varchar2", "out", null);
conn.Open();
cmd.ExecuteNonQuery();
if (cmd.Parameters["error_msg_"].Value == null)
{
blnResult = true;
}
else
{
ShowError(cmd.Parameters["error_msg_"].Value.ToString());
}
}
finally
{
conn.Close();
conn.Dispose();
cmd.Dispose();
}
return(blnResult);
}
private void button1_Click(object sender, EventArgs e)
{
using (Oracle.DataAccess.Client.OracleConnection cn = new Oracle.DataAccess.Client.OracleConnection(
"Data Source=MBDEVDB;User ID=DHHUSER;Password=DHHUSER123")) {
Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();
cmd.Connection = cn;
cmd.CommandText = "INSERT INTO MBFS_SUBJECT(ID,CODE) VALU0ES(99999,'test transaction')";
cmd.CommandType = CommandType.Text;
cn.Open();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// execute a sql statement
/// </summary>
public void ExecuteNonQuery(string sql)
{
using (XSqlCommand cmd = new XSqlCommand(sql, Conn))
{
int iResult = cmd.ExecuteNonQuery();
}
}
