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 void GetNote() { string strSQL = "select t.note,t.expense_type from trn_ee_rqst_expns t where t.record_id=" + Request.Params["rid"]; 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(); reader.Read(); txtDescribtion.Text = reader.GetValue(0).ToString(); lblExpenseType.Text = reader.GetValue(1).ToString(); } finally { if (reader != null) { reader.Dispose(); } cmd.Dispose(); conn.Dispose(); } txtDescribtion.Text = SQLStatic.SQL.ExecScaler(strSQL, Training_Source.TrainingClass.ConnectioString).ToString(); txtRemaining.Text = (4000 - txtDescribtion.Text.Length).ToString(); }
private void SetEmployeeInfo() { if (ViewState["Employee_Number"].ToString() != "0") { Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_employee_3.employee_profile", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "emplouee_number", "number", "in", ViewState["Employee_Number"].ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "result_list_", "cursor", "out", null); Oracle.DataAccess.Client.OracleDataReader reader = null; try { conn.Open(); reader = cmd.ExecuteReader(); while (reader.Read()) { lblEEName.Text = reader.GetValue(0).ToString(); lblWorkPh.Text = reader.GetValue(1).ToString(); lblHomePh.Text = reader.GetValue(2).ToString(); lblMobilePh.Text = reader.GetValue(3).ToString(); lblHomeEmail.Text = reader.GetValue(4).ToString(); lblWorkEmail.Text = reader.GetValue(5).ToString(); lblEmployer.Text = reader.GetValue(6).ToString(); lblEmployeNo.Text = reader.GetValue(7).ToString(); lblEENumber.Text = reader.GetValue(8).ToString(); lblBenefitClass.Text = reader.GetValue(9).ToString(); lblPaySchedule.Text = reader.GetValue(10).ToString(); lblProcessingYear.Text = reader.GetValue(11).ToString(); } } finally { if (reader != null) { reader.Dispose(); } if (cmd != null) { cmd.Dispose(); } if (conn != null) { conn.Dispose(); } } } }
public void InsertCLOB(string SQLStatement, string str) { if (SQLStatement.Length > 0) { if (connOracle.State.ToString().Equals("Open")) { byte[] newvalue = System.Text.Encoding.Unicode.GetBytes(str); sqlCommandOracle = new Oracle.DataAccess.Client.OracleCommand(SQLStatement, connOracle); rstOracle = sqlCommandOracle.ExecuteReader(); rstOracle.Read(); txn = connOracle.BeginTransaction(); clob = rstOracle.GetOracleClob(0); clob.Write(newvalue, 0, newvalue.Length); txn.Commit(); } } }
private static IList <MO> ReadDb() //从数据库得到上行短信信息 { IList <MO> moList = new List <MO>(); string str = "select SMS_MOID,SMS_MOBILE,SMS_CONTENT from SMS_MO where SMS_MODATE = to_date('" + today + "','yyyy-mm-dd')"; using (Oracle.DataAccess.Client.OracleDataReader dr = DataBase.getSysSqlSdr(str)) { while (dr.Read()) { MO result = new MO(); result.Id = dr[0].ToString(); result.Mobile = dr[1].ToString(); result.Content = dr[2].ToString(); moList.Add(result); } return(moList); } }
// private void SetupRollingPanel() // { // upnl.PanelSourceXml = GetRollXML(); // upnl.DataBind(); // } // private void SetupRightPanel() // { // string strXML = GetRightXML(); // if (GetRightXML() != "") // { // upRTab.PanelSourceXml = strXML; // upRTab.DataBind(); // } // } private void SetupMessages() { return; Oracle.DataAccess.Client.OracleConnection conn = new Oracle.DataAccess.Client.OracleConnection(ConnectionString); Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand("pkg_web.get_home_page_bas_msg", conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandTimeout = 30; SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "ps_session_id", Request.Cookies["Session_ID"].Value.ToString()); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "ps_page_url", Request.Path); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "pn_detail_id", "number", "in", 1); SQLStatic.ProcedureParameters.SetStoredProcedureParameter(cmd, "cr", "cursor", "out", null); Oracle.DataAccess.Client.OracleDataReader reader = null; try { conn.Open(); reader = cmd.ExecuteReader(); reader.Read(); lblMessageFromBas.Text = reader.GetValue(0).ToString(); } catch {} finally { if (reader != null) { reader.Dispose(); } if (cmd != null) { cmd.Dispose(); } if (conn != null) { conn.Dispose(); } } // lblMessageFromBas.Text = SQLStatic.SQL.ExecScaler( // "select ('"+Request.Cookies["Session_ID"].Value.ToString()+"','"+Request.Path+"',1) from dual",ConnectionString).ToString(); }
private void LoadDataGrid() { DateTime FrmDt = Convert.ToDateTime(TxtFrm.Text); DateTime ToDt = Convert.ToDateTime(TxtTo.Text); string sql = "SELECT LEAVE_DATE \"LEAVE DATE\",TO_CHAR(LEAVE_DATE, 'DAY') \"DAY\", DESCRIPTION, LEAVE_TYPE \"LEAVE TYPE\", DECODE(STATUS,'C','CANCELLED','APPROVED') STATUS FROM VEW_HRM_LEAVE_HISTORY WHERE EMP_ID=" + EmpID() + " AND LEAVE_DATE>='" + FrmDt.ToString("dd-MMM-yyyy") + "' AND LEAVE_DATE<='" + ToDt.ToString("dd-MMM-yyyy") + "' ORDER BY LEAVE_DATE"; if (db.Con.State == ConnectionState.Closed) { db.Con.Open(); } Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(sql, db.Con); Oracle.DataAccess.Client.OracleDataReader dr = cmd.ExecuteReader(); if (!dr.HasRows) { ClientScript.RegisterStartupScript(GetType(), "alert", "alert('No Leaves in this period');", true); GridView1.DataSource = null; GridView1.DataBind(); dr.Close(); db.Con.Close(); return; } dr.Close(); db.Con.Close(); DataSet ds = db.GetData(sql); GridView1.DataSource = ds; GridView1.DataBind(); if (ds.Tables[0].Rows.Count <= 0) { ClientScript.RegisterStartupScript(GetType(), "alert", "alert('No Leaves in this period');", true); return; } }
protected void btnTest_Click(object sender, EventArgs e) { log4net.ILog Log = log4net.LogManager.GetLogger(typeof(PublicController)); string connstring = AppEnv.LuckyFoneOracleGpcViettel; #region Process using (var conn = new OracleConnection(connstring)) { conn.Open(); string year = DateTime.Now.Year.ToString(); string month = DateTime.Now.Month.ToString(); string day = (DateTime.Now.Day).ToString(); string hour = DateTime.Now.Hour.ToString(); string minute = (DateTime.Now.AddMinutes(-10).Minute).ToString(); string second = DateTime.Now.Second.ToString(); string oldParameterTime = month + "-" + day + "-" + year + " " + hour + ":" + minute + ":" + second; if (month.Length == 1) { month = "0" + month; } if (day.Length == 1) { day = "0" + day; } string oldParameter = year + month + day; string tableMoLog = " SMSDT.SMS_RECEIVE_LOG PARTITION (P_" + oldParameter.Substring(0, 4) + "_" + oldParameter.Substring(4, 2); int intCurrentDate = ConvertUtility.ToInt32(oldParameter.Substring(6, 2)); if (intCurrentDate > 0 && intCurrentDate < 6) { tableMoLog = tableMoLog + "_1)"; } else if (intCurrentDate > 5 && intCurrentDate < 11) { tableMoLog = tableMoLog + "_2)"; } else if (intCurrentDate > 10 && intCurrentDate < 16) { tableMoLog = tableMoLog + "_3)"; } else if (intCurrentDate > 15 && intCurrentDate < 21) { tableMoLog = tableMoLog + "_4)"; } else if (intCurrentDate > 20 && intCurrentDate < 26) { tableMoLog = tableMoLog + "_5)"; } else if (intCurrentDate > 25 && intCurrentDate < 32) { tableMoLog = tableMoLog + "_6)"; } Log.Debug("SQL : " + oldParameterTime); Log.Debug(" "); string sql = "Select USER_ID, " + "SERVICE_ID, " + "MOBILE_OPERATOR, " + "COMMAND_CODE, " + "INFO, " + "TIMESTAMP, " + "RESPONDED, " + "REQUEST_ID " + " From " + tableMoLog + " Where To_Char(TIMESTAMP,'YYYYMMDD')='" + oldParameter + "' " + "and TIMESTAMP > to_date('" + oldParameterTime + "', 'mm-dd-yyyy hh24:mi:ss' ) AND MOBILE_OPERATOR = 'GPC' " ; Log.Debug("********** LUCKFONE LOG SQL VINA-VIETTEL **********"); Log.Debug("SQL : " + sql); Log.Debug(" "); Log.Debug(" "); using (var comm = new OracleCommand(sql, conn)) { using (OracleDataReader rdr = comm.ExecuteReader()) { int count = 0; //SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString); //dbConn.Open(); while (rdr.Read()) { count = count + 1; Log.Debug("VALUE : " + rdr["User_ID"] + "|" + rdr["MOBILE_OPERATOR"]); //var item = new MoEntity997(); //item.CommandCode = rdr["COMMAND_CODE"].ToString(); ////item.Info = rdr["INFO"].ToString(); //item.Info = string.Empty; //item.MobileOperator = rdr["MOBILE_OPERATOR"].ToString(); //item.RequestID = rdr["REQUEST_ID"].ToString(); //item.Responded = ConvertUtility.ToInt32(rdr["RESPONDED"].ToString()); //item.ServiceID = rdr["SERVICE_ID"].ToString(); //item.Timestamp = ConvertUtility.ToDateTime(rdr["TIMESTAMP"].ToString()); //item.UserID = rdr["USER_ID"].ToString(); //PublicController.LuckyfoneMoInsert(item); } Log.Debug("Count : " + count); } } } #endregion }
public OracleDataReaderWrapper(OracleDataAccess.Client.OracleDataReader reader) { this.innerReader = reader; }
/// <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 CustomerPersonalInfo GetCustomerDetailsByCustomerID(int CustomerID, AspxCommonInfo aspxCommonObj) { try { List <KeyValuePair <string, object> > parameter = CommonParmBuilder.GetParamSPUC(aspxCommonObj); parameter.Add(new KeyValuePair <string, object>("customerID", CustomerID)); OracleHandler sqlH = new OracleHandler(); Oracle.DataAccess.Client.OracleDataReader drCustDetail = sqlH.ExecuteAsDataReader("usp_Aspx_GetCustomerDetailsByCustomerID", parameter); CustomerPersonalInfo customerPersonalInfo = new CustomerPersonalInfo(); int count = 0; while (drCustDetail.Read()) { if (count == 0) { DateTime tempDateTime; int tempInt; //customerPersonalInfo.AccountCreatedIn = DateTime.TryParse(drCustDetail["CreateDate"].ToString(), out tempDateTime) ? tempDateTime : DateTime.MinValue; customerPersonalInfo.AccountCreatedOn = DateTime.TryParse(drCustDetail["CreateDate"].ToString(), out tempDateTime) ? tempDateTime.ToString("HH:mm dd/MM/yyyy") : "Error retrieving data"; //customerPersonalInfo.ConfirmedEmail = drCustDetail["Email"].ToString(); customerPersonalInfo.CustomerID = Int32.TryParse(drCustDetail["CustomerID"].ToString(), out tempInt) ? tempInt : 0; customerPersonalInfo.LastLoggedIn = DateTime.TryParse(drCustDetail["LastLoginDate"].ToString(), out tempDateTime) ? tempDateTime.ToString("HH:mm dd/MM/yyyy") : "Never"; customerPersonalInfo.PortalID = Int32.TryParse(drCustDetail["PortalID"].ToString(), out tempInt) ? tempInt : 0; customerPersonalInfo.UserName = drCustDetail["UserName"].ToString(); string strLifetimeSales = FixDouble(drCustDetail["LifetimeSales"].ToString()); customerPersonalInfo.LifetimeSales = strLifetimeSales; string strAverageSales = FixDouble(drCustDetail["AverageSales"].ToString()); customerPersonalInfo.AverageSales = strAverageSales; if (drCustDetail["DefaultBilling"].ToString() == "True" && drCustDetail["DefaultShipping"].ToString() == "True") { //Billing address customerPersonalInfo.BillingAddress = new AddressInfo(); customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString(); customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString(); customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString(); customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString(); customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString(); customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString(); customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString(); customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString(); customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString(); //Shipping address customerPersonalInfo.ShippingAddress = new AddressInfo(); customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString(); customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString(); customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString(); customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString(); customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString(); customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString(); customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString(); customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString(); customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString(); } else if (drCustDetail["DefaultBilling"].ToString() == "True") { //Billing address customerPersonalInfo.BillingAddress = new AddressInfo(); customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString(); customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString(); customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString(); customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString(); customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString(); customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString(); customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString(); customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString(); customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString(); } else if (drCustDetail["DefaultShipping"].ToString() == "True") { //Shipping address customerPersonalInfo.ShippingAddress = new AddressInfo(); customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString(); customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString(); customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString(); customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString(); customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString(); customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString(); customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString(); customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString(); customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString(); } } else { if (drCustDetail["DefaultBilling"].ToString() == "True") { //Billing address customerPersonalInfo.BillingAddress = new AddressInfo(); customerPersonalInfo.BillingAddress.Address1 = drCustDetail["saAddress1"].ToString(); customerPersonalInfo.BillingAddress.Address2 = drCustDetail["saAddress2"].ToString(); customerPersonalInfo.BillingAddress.City = drCustDetail["saCity"].ToString(); customerPersonalInfo.BillingAddress.Company = drCustDetail["saCompany"].ToString(); customerPersonalInfo.BillingAddress.Country = drCustDetail["saCountry"].ToString(); customerPersonalInfo.BillingAddress.FirstName = drCustDetail["saFirstName"].ToString(); customerPersonalInfo.BillingAddress.LastName = drCustDetail["saLastName"].ToString(); customerPersonalInfo.BillingAddress.Zip = drCustDetail["saZip"].ToString(); customerPersonalInfo.BillingAddress.State = drCustDetail["saState"].ToString(); } else if (drCustDetail["DefaultShipping"].ToString() == "True") { //Shipping address customerPersonalInfo.ShippingAddress = new AddressInfo(); customerPersonalInfo.ShippingAddress.Address1 = drCustDetail["saAddress1"].ToString(); customerPersonalInfo.ShippingAddress.Address2 = drCustDetail["saAddress2"].ToString(); customerPersonalInfo.ShippingAddress.City = drCustDetail["saCity"].ToString(); customerPersonalInfo.ShippingAddress.Company = drCustDetail["saCompany"].ToString(); customerPersonalInfo.ShippingAddress.Country = drCustDetail["saCountry"].ToString(); customerPersonalInfo.ShippingAddress.FirstName = drCustDetail["saFirstName"].ToString(); customerPersonalInfo.ShippingAddress.LastName = drCustDetail["saLastName"].ToString(); customerPersonalInfo.ShippingAddress.Zip = drCustDetail["saZip"].ToString(); customerPersonalInfo.ShippingAddress.State = drCustDetail["saState"].ToString(); } } count++; } return(customerPersonalInfo); } catch (Exception e) { throw e; } }