protected override ExeResEdm ExecuteNonQueryFromSqlContianer(List <SqlContianer> ltSqls) { ExeResEdm dBResEdm = new ExeResEdm(); string curSQL = ""; try { using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connstr)) { conn.Open(); System.Data.OracleClient.OracleTransaction oraOprate = conn.BeginTransaction(); try { System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand(); cmd.Transaction = oraOprate; foreach (SqlContianer objOraSqlCon in ltSqls) { cmd.CommandText = objOraSqlCon.strSqlTxt; curSQL = objOraSqlCon.strSqlTxt; cmd.Parameters.Clear(); cmd.Parameters.AddRange(objOraSqlCon.ltOraParams.ToArray()); int intRes = cmd.ExecuteNonQuery(); dBResEdm.ExeNum += intRes; if (objOraSqlCon.intExpectNums >= 0) { if (intRes != objOraSqlCon.intExpectNums) { throw new Exception("Update records not match the expect nums"); } } else if (objOraSqlCon.intExpectNums != Int16.MinValue) { if (intRes != 0 && intRes != objOraSqlCon.intExpectNums * -1) { throw new Exception("Update records not match the expect nums"); } } } oraOprate.Commit(); } catch (Exception ex) { oraOprate.Rollback(); dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法"; dBResEdm.ExBody = ex; dBResEdm.ErrCode = 1; return(dBResEdm); } } } catch (Exception ex) { dBResEdm.Module = "ExecuteNonQueryFromSqlContianer方法"; dBResEdm.ExBody = ex; dBResEdm.ErrCode = 1; return(dBResEdm); } return(dBResEdm); }
//Value which you want to pass public void btnGet_OTP_Click(object sender, EventArgs e) { try { // to save the Email_id and OTP in database String email = send_otp.Value; Session["Otp_email"] = email; string strNewOtp = GenerateOtp().ToString(); using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST)) { connection.Open(); string queryString = "update VP_OTP SET VALIDITY =:NO WHERE EMAIL = :email AND VALIDITY= :YES "; System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection); command.Parameters.AddWithValue("email", email); command.Parameters.AddWithValue("YES", "YES"); command.Parameters.AddWithValue("NO", "NO"); int rowsUpdated = command.ExecuteNonQuery(); string QueryString = "insert into VP_OTP (EMAIL,OTP, VALIDITY) values (:email,:OTP,:VALIDITY)"; System.Data.OracleClient.OracleCommand Command = new System.Data.OracleClient.OracleCommand(QueryString, connection); Command.Parameters.AddWithValue("email", email); Command.Parameters.AddWithValue("OTP", strNewOtp); Command.Parameters.AddWithValue("VALIDITY", "YES"); int RowsUpdated = Command.ExecuteNonQuery(); HttpContext.Current.Response.Redirect("OTP.aspx", false); } // to send the random OTP in email // MailMessage msg = new MailMessage(); // msg.From = new MailAddress("*****@*****.**"); // msg.To.Add(email); // msg.Body = "Dear " + email + ", <br/><br/> Your OTP is " + strNewOtp + " for Vendor Registration for RCF. <br/> Kindly ignore this email if already submitted. <br/><br/>This is system generated mail.<br/>Please do not reply.<br/><br/>Regards,<br/>RCF Ltd."; //Response.Write(msg.Body); // msg.IsBodyHtml = true; // msg.Subject = "OTP for Verification for Grievance Monitoring System"; /// SmtpClient smt = new SmtpClient("webmail.rcfltd.com"); // smt.Port = 25; // smt.Credentials = new NetworkCredential("*****@*****.**", "asdfgh@123"); //smt.EnableSsl = true; // smt.Send(msg); //string script = "<script>alert('Mail Sent Successfully');self.close();</script>"; //this.ClientScript.RegisterClientScriptBlock(this.GetType(), "sendMail", script); } catch (Exception ex) { } }
/// <summary> /// There are no comments for COMP_BIOSUM_VOLS_BY_UPDATE in the schema. /// </summary> public virtual void COMP_BIOSUM_VOLS_BY_UPDATE() { m_intError = 0; m_strError = ""; bool bCloseConnection = false; if (_OracleADO.ConnectionDisposed || _OracleADO.m_Connection == null || (_OracleADO.m_Connection != null && _OracleADO.m_Connection.State != ConnectionState.Open)) { OracleConnect(); bCloseConnection = true; } if (_OracleADO.m_intError == 0) { try { //using (Oracle.DataAccess.Client.OracleCommand command = new Oracle.DataAccess.Client.OracleCommand()) using (System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand()) { command.Connection = _OracleADO.m_Connection; command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandText = FCSOracle.FCSSchema + ".COMPUTE_BIOSUM_VOLUMES_PKG.COMP_BIOSUM_VOLS_BY_UPDATE"; command.ExecuteNonQuery(); } } catch (Exception e) { m_intError = -1; m_strError = e.Message; if (DisplayErrors) { MessageBox.Show("!!Error!! \n" + "Module - FCSEntities:COMP_BIOSUM_VOLS_BY_UPDATE \n" + "Err Msg - " + e.Message.ToString().Trim(), "BIOSUM", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Exclamation); } } finally { if (bCloseConnection) { //close connection and release resources OracleADO.CloseAndDisposeConnection(_OracleADO.m_Connection, false); } } } }
protected void btnSignUp_Click(object sender, EventArgs e) { try { String username = user_name.Value; String email = user_email.Value; String password = user_password.Value; using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST)) { connection.Open(); string queryString = "SELECT * FROM VP_LOGIN WHERE EMAIL_ID = :useremail"; System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection); command.Parameters.AddWithValue("useremail", email); using (System.Data.OracleClient.OracleDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { reader.Read(); lblError_email.Text = "Email already exist!"; } else { string QueryString = "insert into VP_LOGIN (NAME,EMAIL_ID,PASSWORD) values (:username,:email,:password)"; System.Data.OracleClient.OracleCommand Command = new System.Data.OracleClient.OracleCommand(QueryString, connection); Command.Parameters.AddWithValue("username", username); Command.Parameters.AddWithValue("email", email); Command.Parameters.AddWithValue("password", password); int rowsUpdated = Command.ExecuteNonQuery(); if (rowsUpdated == 0) { Response.Write("Record not inserted"); } else { HttpContext.Current.Response.Redirect("Login.aspx", false); } } } } } catch (Exception ex) { lblError.Text = "Something Went Wrong!"; } }
protected void btnResetPassword_Click(object sender, EventArgs e) { try { String newpassword = New_password.Value; using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST)) { connection.Open(); string queryString = "update VP_LOGIN SET PASSWORD =:New_password WHERE EMAIL_ID = :email"; System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection); command.Parameters.AddWithValue("email", Session["Otp_email"]); command.Parameters.AddWithValue("New_password", newpassword); int rowsUpdated = command.ExecuteNonQuery(); Response.Redirect("Login.aspx"); } } catch (Exception ex) { Response.Write("pm"); } }
//total marks //procedure private void button1_Click(object sender, EventArgs e) { try { String oradb = "Data Source=DESKTOP-K6AG53J;Persist Security Info=True;User ID=system;Password=student;"; System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(oradb); //conn.Open(); System.Data.OracleClient.OracleCommand comm = new System.Data.OracleClient.OracleCommand(); comm.Connection = conn; comm.CommandText = "tot_marks"; comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add("s_id", System.Data.OracleClient.OracleType.Number).Value = s1; comm.Parameters.Add("co_id", System.Data.OracleClient.OracleType.Number).Value = comboBox1.Text; comm.Parameters.Add("cout", System.Data.OracleClient.OracleType.Number).Direction = ParameterDirection.Output; conn.Open(); comm.ExecuteNonQuery(); textBox1.Text = (comm.Parameters["cout"].Value).ToString(); conn.Close(); } catch (IndexOutOfRangeException ex) { MessageBox.Show("Invalid Details!"); } }
protected void btnFormSubmit_Click(object sender, EventArgs e) { try { String selectp = Request.Form.Get("title"); String Fname = fname.Value; String Mname = mname.Value; String Lname = lname.Value; String Username = selectp + " " + Fname + " " + Mname + " " + Lname; String House_no = house_no.Value; String Street = street.Value; String City_code = city_code.Value; String City = city.Value; String State = state.Value; String PIN = pin.Value; String Country = country.Value; String Address = House_no + ", " + Street + ", " + City_code + ", " + City + ", " + State + ", " + PIN + ", " + Country; String Contact_person = contact_person.Value; String Telephone_no = telephone_no.Value; String Mobile_no = mobile_no.Value; String Fax = fax.Value; //String Email = email_id.Value; String Pan = pan_no.Value; String Gst_no = gst_no.Value; String Industry = industry.Value; String Micro = micro.Value; using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection(connectionStringGST)) { connection.Open(); string queryString = "insert into VP_REGISTRATION (NAME, ADDRESS, CONTACT_PERSON,EMAIL,FAX, GST_NO, INDUSTRY, MICRO_OR_SSI_STATUS, MOBILE_NO, PAN_NO, TELEPHONE_NO_EXT) values (:username , :Address, :C_person, :Email, :Fax, :Gst, :Industry, :Micro, :Mob_no, :Pan_no, :Telephone_no)"; System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(queryString, connection); command.Parameters.AddWithValue("username", Username); command.Parameters.AddWithValue("Address", Address); command.Parameters.AddWithValue("C_person", Contact_person); command.Parameters.AddWithValue("Email", Session["Login_email"]); command.Parameters.AddWithValue("Fax", Fax); command.Parameters.AddWithValue("Gst", Gst_no); command.Parameters.AddWithValue("Industry", Industry); command.Parameters.AddWithValue("Micro", Micro); command.Parameters.AddWithValue("Mob_no", Mobile_no); command.Parameters.AddWithValue("Pan_no", Pan); command.Parameters.AddWithValue("Telephone_no", Telephone_no); int rowsUpdated = command.ExecuteNonQuery(); if (rowsUpdated == 0) { Response.Write("Record not inserted"); } else { HttpContext.Current.Response.Redirect("Login.aspx", false); } } } catch (Exception ex) { lblError.Text = "Something Went Wrong!"; } }
public DataResult Forder_delivery(List <Forder_delivery_Input> input) { string sql = ""; string where = "WHERE Fjde_sono=:Fjde_sono AND FsoentryID=:FsoentryID"; int count = 0; DataResult result = new DataResult() { errCode = 0 }; System.Data.OracleClient.OracleCommand cmd = null; try { if (input == null || input.Count == 0) { throw new Exception("传入参数不能为空"); } TB_Dzsrcdel model = null; cmd = DbUtils.GetCommand(model, Operate.Insert); input.ForEach(p => { DataChecker.CheckObj(p); //根据Fjde_sono和FsoentryID获取底表记录FID var manuscript = GetManuscript_By_SonoAnd_EntryID(p.Fjde_sono, p.FsoentryID, cmd); model = GetDzsrcdel(p.Fjde_sono, p.FsoentryID, cmd); Operate insertOrupdate = Operate.Update; if (model == null) { insertOrupdate = Operate.Insert; model = new TB_Dzsrcdel(); model.FmanuscriptID = manuscript.FID; model.Fjde_sono = p.Fjde_sono; model.FsoentryID = p.FsoentryID; model.Fsrcbillno = p.Fsrcbillno; model.fsrcdelno = p.Fsrcdelno; model.fdeldate = p.Fdeldate; model.Fproductcode = p.Fproductcode; } model.fdelqty = p.Fdelqty; model.Fcasecode = p.Fcasecode; model.Fisdelover = p.Fisdelover; model.Fbuttstatus3 = p.Fbuttstatus3; model.Fremark1 = p.Fremark2; model.FTime = DateTime.Now; //插入或更新子表记录 sql = DbUtils.GetSqlWithObject(model, insertOrupdate); sql = insertOrupdate == Operate.Update ? sql += where:sql; cmd.CommandText = sql; DbUtils.GetParams(model, cmd); count += cmd.ExecuteNonQuery(); }); //按销售单号,行号进行汇总求和发货数量 var group = input.GroupBy(p => new { p.Fjde_sono, p.FsoentryID }) .Select(p => new { Fjde_sono = p.Key.Fjde_sono, FsoentryID = p.Key.FsoentryID, Fdelqty_total = p.Sum(item => item.Fdelqty) }) .ToList(); //更新WHERE语句,根据销售单号和行号更新发货数量 where = "WHERE Fjde_sono=:Fjde_sono AND FsoentryID=:FsoentryID"; group.ForEach(p => { // 更新底表发货数量 sql = string.Format("UPDATE TB_DZmanuscript SET Fdelqty_total={0} WHERE Fjde_sono={1} AND FsoentryID={2}", p.Fdelqty_total, p.Fjde_sono, p.FsoentryID); cmd = DbUtils.GetCommand(new TB_DZmanuscript(), Operate.Update); cmd.CommandText = sql; count += cmd.ExecuteNonQuery(); }); cmd.Transaction.Commit(); cmd.Connection.Close(); if (count == 0) { throw new Exception("同步失败,插入了0条数据"); } return(result); } catch (Exception ex) { LogHelper.WriteLog(ex); LogHelper.WriteLog("Input: " + JsonHelper.ToJson(input)); LogHelper.WriteLog("SQL: " + sql); result.errMsg = ex.Message; result.errCode = -1; if (cmd != null) { cmd.Transaction.Rollback(); cmd.Connection.Close(); } return(result); } }
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e) { Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm("ÕýÔÚ¼ìË÷"); Application.DoEvents(); Neusoft.FrameWork.Management.Connection.Instance = new System.Data.OracleClient.OracleConnection(Neusoft.FrameWork.Management.Connection.Instance.ConnectionString); Neusoft.FrameWork.Management.Connection.Instance.Open(); Neusoft.FrameWork.Management.DataBaseManger m = new Neusoft.FrameWork.Management.DataBaseManger(); DataSet ds = new DataSet(); if (m.ExecQuery("select id,name from com_sql", ref ds) == -1) { Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show(m.Err); return; } //Neusoft.FrameWork.Management.Connection.Instance.Close(); //Neusoft.FrameWork.Management.Connection.Instance = new Oracle.DataAccess.Client.OracleConnection(Neusoft.FrameWork.Management.Connection.Instance.ConnectionString); //Neusoft.FrameWork.Management.Connection.Instance.Open(); Neusoft.FrameWork.Management.PublicTrans.BeginTransaction(); m.SetTrans(Neusoft.FrameWork.Management.PublicTrans.Trans); //Neusoft.FrameWork.Management.Transaction t = new Neusoft.FrameWork.Management.Transaction(Neusoft.FrameWork.Management.Connection.Instance); //t.BeginTransaction(); //m.SetTrans(t.Trans); foreach (DataRow dr in ds.Tables[0].Rows) { string s = "update com_sql set name1 = :a where id='" + dr[0].ToString() + "'"; System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(s); command.Connection = Neusoft.FrameWork.Management.Connection.Instance as System.Data.OracleClient.OracleConnection; command.CommandText = s; command.CommandType = System.Data.CommandType.Text; command.Transaction = Neusoft.FrameWork.Management.PublicTrans.Trans as System.Data.OracleClient.OracleTransaction; System.Data.OracleClient.OracleParameter param = command.Parameters.Add("a", System.Data.OracleClient.OracleType.Clob); param.Direction = System.Data.ParameterDirection.Input; // Assign Byte Array to Oracle Parameter param.Value = dr[1].ToString(); // Step 5 // Execute the Anonymous PL/SQL Block try { if (command.ExecuteNonQuery() == -1) { Neusoft.FrameWork.Management.PublicTrans.RollBack(); Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show(m.Err); return; } } catch (Exception ex) { Neusoft.FrameWork.Management.PublicTrans.RollBack(); Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show(ex.Message); return; } //if (m.InputBlob(s, b)== -1) //{ // t.RollBack(); // Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); // MessageBox.Show(m.Err); // return; //} Neusoft.FrameWork.WinForms.Classes.Function.ShowWaitForm(dr[0].ToString() + "\n" + dr[1].ToString()); Application.DoEvents(); } Neusoft.FrameWork.Management.PublicTrans.Commit(); Neusoft.FrameWork.WinForms.Classes.Function.HideWaitForm(); MessageBox.Show("³É¹¦£¡"); }
public int ExecuteNonQuery() { return(_cmd.ExecuteNonQuery()); }