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();
     }
 }
Example #2
0
        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();
            }
        }
Example #3
0
        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);
        }
Example #7
0
        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);
        }
Example #8
0
    public static void alter(string sqlAlter)
    {
        Oracle.DataAccess.Client.OracleCommand cmdAlter = new Oracle.DataAccess.Client.OracleCommand(sqlAlter, conn);

        cmdAlter.ExecuteNonQuery();
        cmdAlter.Dispose();
    }
Example #9
0
        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();
            }
        }
Example #10
0
 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();
 }
Example #11
0
    public static void insert(string sqlInsert)
    {
        Oracle.DataAccess.Client.OracleCommand cmdInsert = new Oracle.DataAccess.Client.OracleCommand(sqlInsert, conn);

        cmdInsert.ExecuteNonQuery();
        cmdInsert.Dispose();
    }
Example #12
0
    public static void delete(string sqlDelete)
    {
        Oracle.DataAccess.Client.OracleCommand cmdDelete = new Oracle.DataAccess.Client.OracleCommand(sqlDelete, conn);

        cmdDelete.ExecuteNonQuery();
        cmdDelete.Dispose();
    }
Example #13
0
        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);
        }
Example #15
0
        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);
        }
Example #16
0
        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();
            }
        }
Example #17
0
        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();
            }
        }
Example #19
0
        /// <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);
        }
Example #21
0
        //有返回值的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);
        }
Example #22
0
        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();
            }
        }
Example #30
0
        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);
        }
Example #31
0
        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();
            }
        }
Example #32
0
        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();
            }
        }
Example #33
0
        //带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);
        }
Example #34
0
    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;
    }
Example #35
0
 /// <summary>
 /// execute a sql statement
 /// </summary>
 public void ExecuteNonQuery(string sql)
 {
     using (XSqlCommand cmd = new XSqlCommand(sql, Conn))
     {
         int iResult = cmd.ExecuteNonQuery();
     }
 }
Example #36
0
 /// <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;
     }
 }
Example #37
0
    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;
    }
Example #38
0
    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;
    }
Example #39
0
    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;
    }
Example #40
0
        /// <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;
        }
Example #41
0
    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;
    }