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(); } }
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 DataTable GetGridTable() { DataTable tbl = null; 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.getlearningcategories", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["Employee_Number"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ViewState["Processing_Year"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "learningcategorylist_", "cursor", "out", ""); DataSet mds = new DataSet(); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); try { conn.Open(); da.Fill(mds); tbl = mds.Tables[0]; } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); mds.Dispose(); } return(tbl); }
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); }
private DataTable GetGridTable() { DataTable tbl = null; 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.paid_request_list", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", ViewState["Adjust_Employee"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "paid_list_", "cursor", "out", ""); DataSet mds = new DataSet(); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); try { conn.Open(); da.Fill(mds); tbl = mds.Tables[0]; } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); mds.Dispose(); } return(tbl); }
private DataTable GetExpenseTable() { DataTable tbl = null; 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.GetEmployeeExpenses", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Expenses_List_", "cursor", "out", ""); DataSet mds = new DataSet(); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); try { conn.Open(); da.Fill(mds); tbl = mds.Tables[0]; } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); mds.Dispose(); } return(tbl); }
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 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 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(); }
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 delete(string sqlDelete) { Oracle.DataAccess.Client.OracleCommand cmdDelete = new Oracle.DataAccess.Client.OracleCommand(sqlDelete, conn); cmdDelete.ExecuteNonQuery(); cmdDelete.Dispose(); }
private void FillOneDropDown(DropDownList ddl, string strTableName) { ddl.Items.Clear(); ddl.Items.Add(new ListItem("---- Select ----", "xx")); string strSQL = "select record_id,description from " + strTableName; Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(Training_Source.TrainingClass.ConnectioString); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(strSQL, conn); conn.Open(); Oracle.DataAccess.Client.OracleDataReader reader = null; try { reader = cmd.ExecuteReader(); while (reader.Read()) { ListItem li = new ListItem(reader.GetValue(1).ToString(), reader.GetValue(0).ToString()); ddl.Items.Add(li); } } finally { reader.Dispose(); cmd.Dispose(); 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 DataTable GetGridTable() { DataTable tbl = null; string strProcedureName = "BASDBA.pkg_training.History_List"; if (ViewState["Product_Code"].ToString() == "11001") { strProcedureName = "BASDBA.pkg_training_cdp.History_List"; } 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; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "hstList_", "cursor", "out", ""); DataSet mds = new DataSet(); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); try { conn.Open(); da.Fill(mds); tbl = mds.Tables[0]; } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); mds.Dispose(); } return(tbl); }
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 DataTable GetDataTable() { DataTable tbl = null; 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.ExpenseAdjustmentList", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "expense_record_id_", Request.Params["recid"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "adjustmentList_", "cursor", "out", ""); DataSet mds = new DataSet(); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); try { conn.Open(); da.Fill(mds); tbl = mds.Tables[0]; } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); mds.Dispose(); } return(tbl); }
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(); } }
/// <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 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); }
//有返回值的SQL执行 public object ExecuteScalar(string cmdText) { cmdText = SqlTransfer(cmdText); object retVal = null; 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.ExecuteScalar(); } catch (Exception exp) { LogWriter.Error(exp, cmdText); //retVal = (object)exp.Message; throw exp; } finally { conn.Close(); } } return(retVal); }
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 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 FillReasonCheckBoxList() { System.Data.DataTable dTable; 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.reason_for_admin_partial_list", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "reason_list_", "cursor", "out", ""); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); DataSet mds = new DataSet(); conn.Open(); try { da.Fill(mds, "Purpose"); dTable = mds.Tables["Purpose"]; chklstReasons.Items.Clear(); foreach (DataRow dr in dTable.Rows) { ListItem li = new ListItem(dr["description"].ToString(), dr["record_id"].ToString()); chklstReasons.Items.Add(li); } } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); da.Dispose(); mds.Dispose(); dTable = null; } }
private DataTable GetTable() { DataTable tbl = null; Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_training_2.employeeslist_sup", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "admin_ee_number", "number", "in", ViewState["Employee_Number"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "retresult_", "cursor", "out", ""); DataSet mds = new DataSet(); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); try { conn.Open(); da.Fill(mds); tbl = mds.Tables[0]; } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); mds.Dispose(); } return(tbl); }
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(Training_Source.TrainingClass.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(); } }
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 SetEmployeeNumber() { bool eeFound = true; 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.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 FillExpenseType() { ddlExpenseType.Items.Clear(); DataTable tbl = null; 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.GetExpenseTypes", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "header_record_id_", ViewState["Request_Record_ID"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "Expenses_List_", "cursor", "out", ""); DataSet mds = new DataSet(); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); try { conn.Open(); da.Fill(mds); tbl = mds.Tables[0]; foreach (DataRow dr in tbl.Rows) { ListItem li = new ListItem(dr["description"].ToString(), dr["record_id"].ToString()); ddlExpenseType.Items.Add(li); } } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); mds.Dispose(); tbl.Dispose(); } }
private DataTable GetGridTable() { DataTable tbl = null; 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_trn_budget.budget_detail_list", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "employee_number_", Request.Params["ee"]); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "processing_year_", ddlBudgetYear.SelectedItem.Text); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "budget_list_", "cursor", "out", ""); DataSet mds = new DataSet(); Oracle.DataAccess.Client.OracleDataAdapter da = new Oracle.DataAccess.Client.OracleDataAdapter(cmd); try { conn.Open(); da.Fill(mds); tbl = mds.Tables[0]; } finally { conn.Close(); conn.Dispose(); cmd.Dispose(); mds.Dispose(); } return(tbl); }
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 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(); } }
//带SQL执行类型,无返回值的SQL执行 public string ExecuteNonQuery(string cmdText, CommandType cmdtype) { 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 = cmdtype; try { if (conn.State == ConnectionState.Closed) { conn.Open(); } retVal = cmd.ExecuteNonQuery().ToString(); } catch (Exception exp) { LogWriter.Error(exp, cmdText); throw exp; retVal = exp.Message; } finally { conn.Close(); } } return(retVal); }
public static bool isExist(string table, string where) { string cmdQuery = "select * from " + table + " where " + where; Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn); Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { reader.Close(); cmd.Dispose(); return true; } reader.Close(); cmd.Dispose(); return false; }
/// <summary> /// execute a sql statement /// </summary> public void ExecuteNonQuery(string sql) { using (XSqlCommand cmd = new XSqlCommand(sql, Conn)) { int iResult = cmd.ExecuteNonQuery(); } }
/// <summary> /// execute a sql statement, return an Int64. /// </summary> public long ExecuteScalar(string sql) { using (XSqlCommand cmd = new XSqlCommand(sql, Conn)) { object oResult = cmd.ExecuteScalar(); Assert.IsNotNull("Expecting result, instead got null. (sql=" + sql + ")"); Assert.IsInstanceOfType(typeof(long), oResult, "Expecting 'long' result from query " + sql + ", instead got type " + oResult.GetType()); return (long)oResult; } }
public static string selectStr(string cmdQuery, string nullValue) { Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn); Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader(); string value = nullValue; while (reader.Read()) value = reader.GetString(0); reader.Close(); cmd.Dispose(); return value; }
public static System.Collections.Generic.List<string> selectListStr(string cmdQuery) { System.Collections.Generic.List<string> list = new System.Collections.Generic.List<string>(); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn); Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) list.Add(reader.GetString(0)); reader.Close(); cmd.Dispose(); return list; }
public static long selectLong(string cmdQuery, long nullValue) { Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn); Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader(); long value = nullValue; while (reader.Read()) value = System.Int32.Parse(reader.GetValue(0).ToString()); reader.Close(); cmd.Dispose(); return value; }
/// <summary>手持登录</summary> public AscmUserInfo MobileLogin(string userId, string userPwd, string connString, ref string errorMsg) { AscmUserInfo ascmUserInfo = null; errorMsg = string.Empty; using (Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(connString)) { if (conn.State != System.Data.ConnectionState.Open) conn.Open(); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT userId,userName,password,employeeId,extExpandType,extExpandId FROM ynUser WHERE extExpandId = :extExpandId"; cmd.CommandType = System.Data.CommandType.Text; Oracle.DataAccess.Client.OracleParameter parm = new Oracle.DataAccess.Client.OracleParameter(); parm.ParameterName = ":extExpandId"; parm.OracleDbType = Oracle.DataAccess.Client.OracleDbType.NVarchar2; parm.Size = 20; parm.Value = userId; parm.Direction = System.Data.ParameterDirection.Input; cmd.Parameters.Add(parm); using (Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { cmd.Parameters.Clear(); if (reader.Read()) { ascmUserInfo = new AscmUserInfo(); ascmUserInfo.userId = reader["userId"].ToString(); ascmUserInfo.userName = reader["userName"].ToString(); ascmUserInfo.password = reader["password"].ToString(); int employeeId = 0; int.TryParse(reader["employeeId"].ToString(), out employeeId); ascmUserInfo.employeeId = employeeId; ascmUserInfo.extExpandType = reader["extExpandType"].ToString(); ascmUserInfo.extExpandId = reader["extExpandId"].ToString(); if (ascmUserInfo.extExpandType == "erp") { byte[] result = Encoding.Default.GetBytes(userPwd); System.Security.Cryptography.MD5 md5 = new System.Security.Cryptography.MD5CryptoServiceProvider(); userPwd = BitConverter.ToString(md5.ComputeHash(result)).Replace("-", ""); if (ascmUserInfo.password != userPwd) { errorMsg = "密码不正确"; } else if (!string.IsNullOrEmpty(ascmUserInfo.userName)) { Oracle.DataAccess.Client.OracleCommand cmd2 = new Oracle.DataAccess.Client.OracleCommand(); cmd2.Connection = conn; cmd2.CommandText = "SELECT id,name FROM ascm_supplier WHERE docNumber = :docNumber"; cmd2.CommandType = System.Data.CommandType.Text; cmd2.Parameters.Add(new Oracle.DataAccess.Client.OracleParameter { ParameterName = ":docNumber", OracleDbType = Oracle.DataAccess.Client.OracleDbType.NVarchar2, Size = 20, Value = ascmUserInfo.userName, Direction = System.Data.ParameterDirection.Input }); using (Oracle.DataAccess.Client.OracleDataReader reader2 = cmd2.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { cmd2.Parameters.Clear(); if (reader2.Read()) { int id = 0; if (int.TryParse(reader2["id"].ToString(), out id)) { AscmSupplier ascmSupplier = new AscmSupplier(); ascmSupplier.id = id; ascmSupplier.name = reader2["name"].ToString(); ascmUserInfo.ascmSupplier = ascmSupplier; } } } } } } } } return ascmUserInfo; }
public static System.Collections.Generic.List<int> selectListInt(string cmdQuery) { System.Collections.Generic.List<int> list = new System.Collections.Generic.List<int>(); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(cmdQuery, conn); Oracle.DataAccess.Client.OracleDataReader reader = cmd.ExecuteReader(); while (reader.Read()) list.Add(System.Int32.Parse(reader.GetValue(0).ToString())); reader.Close(); cmd.Dispose(); return list; }