public static bool EditFeature(string labelid,string title,string content) { StringBuilder sb = new StringBuilder(); using (OracleConnection conn = new OracleConnection(OraHelper.str)) { conn.Open(); OracleCommand comm = conn.CreateCommand(); OracleParameter[] parms = new OracleParameter[]{ new OracleParameter(":LABELID",OracleType.VarChar), new OracleParameter(":TITLE",OracleType.VarChar), new OracleParameter(":CONTENT",OracleType.VarChar) }; parms[0].Value = labelid; parms[1].Value = title; parms[2].Value = content; comm.CommandText = SQL_UPDATE_LABEL; comm.CommandType = CommandType.Text; foreach (OracleParameter parm in parms) { comm.Parameters.Add(parm); } comm.ExecuteNonQuery(); conn.Close(); return true; } }
protected void Page_Load(object sender, EventArgs e) { string guid = string.Empty; guid = Request["q"]; int ww = Convert.ToInt32(Request["w"]); int wh = Convert.ToInt32(Request["h"]); if (guid == null || guid == string.Empty) { this.map.InnerHtml = "<h1 style='font-size:24px;text-align:center;'>输入参数无效</h1>"; Response.End(); } else { using (OracleConnection conn = new OracleConnection(OraHelper.str)) { conn.Open(); OracleCommand comm = conn.CreateCommand(); comm.CommandText = string.Format(SQL_SELECT, guid); OracleDataReader dr = comm.ExecuteReader(); if (dr.Read()) { string innerHTML = dr["DATA"].ToString(); this.map.InnerHtml = innerHTML; this.map.Style.Value = "display:block;width:" + ww + "px;height:" + wh + "px;"; } else { this.map.InnerHtml = "<h1 style='font-size:24px;text-align:center;'>该分享不存在</h1>"; } } } }
public static string GetFeature(string p_key, int p_size, int p_page) { StringBuilder sb = new StringBuilder(); using (OracleConnection conn = new OracleConnection(OraHelper.str)) { conn.Open(); OracleCommand comm = conn.CreateCommand(); comm.CommandText = string.Format(SQL_SEARCH_STATION_TOTAL, p_size, p_key); object total = comm.ExecuteScalar(); comm.CommandText = string.Format(SQL_SEARCH_STATION, p_key, p_page * p_size, (p_page - 1) * p_size); OracleDataReader rdr = comm.ExecuteReader(); string str = Reader2JSON.ToJSON(rdr); sb.Append("{\"total\":"); sb.Append(total); sb.Append(",\"page\":"); sb.Append(p_page); sb.Append(",\"content\":"); sb.Append(str); sb.Append("}"); conn.Close(); } return sb.ToString(); }
/// <summary> /// 填充combox /// </summary> /// <param name="cb"></param> /// <param name="sql"></param> private void FillComb(ComboBox cb,string sql) { try { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; OracleDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { cb.Items.Clear(); cb.Items.Add(""); while (dr.Read()) { cb.Items.Add(dr[0].ToString()); } conn.Close(); dr.Close(); } } catch (OracleException ox) { MessageBox.Show(ox.Message.ToString()); return; } }
private void button1_Click(object sender, EventArgs e) { try { OracleConnection OrclConn = new OracleConnection(DataAccess.OIDSConnStr); OrclConn.Open(); OracleDataAdapter OrclNestAdapter = new OracleDataAdapter(); OracleCommand OrclNestCmd = OrclConn.CreateCommand(); OracleCommand OrclCmd = OrclConn.CreateCommand(); //OracleCommandBuilder myCommandBuilder = new OracleCommandBuilder(mOrclAdapter); //mOrclAdapter.Update(MyDataSet); //conn.Close(); //for each row and each spool , insert into single_inventory_table for (int i = 0; i < InventoryTable.Rows.Count; i++) { string pipetype = InventoryTable.Rows[i]["类型"].ToString(); Int32 pipelength = Convert.ToInt32(InventoryTable.Rows[i]["长度"].ToString()); string erpcode = InventoryTable.Rows[i]["ERP编码"].ToString(); double weight = (Convert.ToDouble(InventoryTable.Rows[i]["重量"].ToString())) / (Convert.ToInt32(InventoryTable.Rows[i]["数量"].ToString())); string batch_no = InventoryTable.Rows[i]["炉批号"].ToString(); for (int j = 0; j < Convert.ToInt32(InventoryTable.Rows[i]["数量"].ToString()); j++) { //insert into oracle OrclCmd.CommandText = "INSERT INTO pipe_inventory_single_spool (PIPETYPE,PIPELENGTH,ERPCODE,WEIGHT,BATCH_NO,STATE) VALUES ('" + pipetype + "'," + pipelength + ",'" + erpcode + "'," + weight + ",'" + batch_no + "',1)"; OrclCmd.ExecuteNonQuery(); } } } catch (Exception err) { MessageBox.Show(err.Message); } MessageBox.Show("更新成功!"); }
public void createMember(string firstname, string lastname, string address, string city, string username, string password, string email, string picture) //insert member { String oradb = "Data Source= cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw1;Password=CampBand4;"; OracleConnection conn = new OracleConnection(oradb); try { String strSQL = "INSERT INTO MEMBERS VALUES(Members_seq.nextval, '" + firstname + "', '" + lastname + "', '" + address + "', '" + city + "', '" + username + "', '" + password + "', '" + picture + "', '" + email + "', '" + DateTime.Now.ToString("dd-MMM-yyyy") + "' )"; conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = strSQL; cmd.ExecuteNonQuery(); conn.Close(); MessageBox.Show("Welcome to BandCamp!"); registered = true; } catch (OracleException ex) { registered = false; MessageBox.Show("Something went wrong when accessing the database."); MessageBox.Show("Registration was unsuccessful"); MessageBox.Show(ex.Message); } finally { conn.Close(); } }
private void createOracleConnection() { try { if (dbType == DbType.Oracle) { if (conOra == null) { conOra = new System.Data.OracleClient.OracleConnection(url); } if (cmdOra == null) { cmdOra = conOra.CreateCommand(); } if (conOra.State != ConnectionState.Open) { conOra.Open(); } } } catch { try { conOra.Close(); } catch { } throw new StaConnectException(); } }
public static string GetFeature(string p_key, int p_size, int p_page, double p_left, double p_bottom, double p_right,double p_top ) { StringBuilder sb = new StringBuilder(); using (OracleConnection conn = new OracleConnection(OraHelper.str)) { conn.Open(); OracleCommand comm = conn.CreateCommand(); comm.CommandText = string.Format(SQL_RECTANGLE_KEYWORD_TOTAL, p_size,p_left,p_right,p_bottom,p_top,p_key); object total = comm.ExecuteScalar(); comm.CommandText = string.Format(SQL_RECTANGLE_KEYWORD, p_key,p_left, p_right,p_bottom,p_top,p_page * p_size,(p_page-1)*p_size); OracleDataReader rdr = comm.ExecuteReader(); string str = Reader2JSON.ToJSON(rdr); sb.Append("{total:"); sb.Append(total); sb.Append(",page:"); sb.Append(p_page); sb.Append(",content:"); sb.Append(str); sb.Append("}"); conn.Close(); } return sb.ToString(); }
private DataTable ConnectAndQuery(string devicename) { devicename = string.IsNullOrEmpty(devicename) ? "V-0516" : devicename; string connectionString = GetOracleConnectionString(); using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection()) { try { connection.ConnectionString = connectionString; try { connection.Close(); } catch (Exception ex) { } connection.Open(); OracleCommand command = connection.CreateCommand(); string sql = "SELECT * FROM v_conduit_test where separator_id='" + devicename + "' and conduit_name like '%" + ddlConduit.SelectedValue + "%' and rownum<100"; command.CommandText = sql; DataSet ds = new DataSet(); OracleDataAdapter adapter = new OracleDataAdapter(command); adapter.Fill(ds); connection.Close(); return(ds.Tables[0]); } catch (Exception ex) { connection.Close(); } return(null); } }
/// <summary> /// 将生成的材料信息更新到数据库中 /// </summary> /// <param name="sql"></param> /// <param name="excelinfo"></param> public static void UpdateExcelInfo(string sql,byte[] excelinfo) { try { byte[] file = excelinfo; using (OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr)) { conn.Open(); using (OracleCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; OracleParameter op = new OracleParameter("dfd", OracleType.Blob); op.Value = file; if (file.Length == 0) { MessageBox.Show("插入信息表不能为空!", "WARNNING", MessageBoxButtons.OK, MessageBoxIcon.Asterisk); return; } else { cmd.Parameters.Add(op); cmd.ExecuteNonQuery(); } } conn.Close(); } } catch (IOException ee) { MessageBox.Show(ee.Message.ToString()); return; } }
/// <summary> /// 测试 调用 Oracle 返回结果集的函数. /// </summary> private void CallFuncWithTable(OracleConnection conn) { // 创建一个 Command. OracleCommand testCommand = conn.CreateCommand(); // 定义需要执行的SQL语句. testCommand.CommandText = "pkg_HelloWorld.getHelloWorld"; // 定义好,本次执行的类型,是存储过程. testCommand.CommandType = CommandType.StoredProcedure; // 定义好,我这个参数,是 游标 + 返回值. OracleParameter para = new OracleParameter("c", OracleType.Cursor); para.Direction = ParameterDirection.ReturnValue; testCommand.Parameters.Add(para); // 执行SQL命令,结果存储到Reader中. OracleDataReader testReader = testCommand.ExecuteReader(); // 处理检索出来的每一条数据. while (testReader.Read()) { // 将检索出来的数据,输出到屏幕上. Console.WriteLine("调用函数:{0}; 返回:{1} - {2}", testCommand.CommandText, testReader[0], testReader[1] ); } // 关闭Reader. testReader.Close(); }
public static void FillTreeView(TreeNode node, string sql) { try { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; OracleDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { node.Nodes.Add(dr[0].ToString()); } conn.Close(); dr.Close(); } } catch (OracleException ox) { MessageBox.Show(ox.Message.ToString()); return; } }
public static bool AddFeature(string usrid,string usrname,string password,int privilege) { StringBuilder sb = new StringBuilder(); using (OracleConnection conn = new OracleConnection(OraHelper.str)) { conn.Open(); OracleCommand comm = conn.CreateCommand(); OracleParameter[] parms = new OracleParameter[]{ new OracleParameter(":USRID",OracleType.VarChar), new OracleParameter(":USRNAME",OracleType.VarChar), new OracleParameter(":PASSWORD",OracleType.VarChar), new OracleParameter(":PRIVILEGE",OracleType.UInt32) }; parms[0].Value = usrid; parms[1].Value = usrname; parms[2].Value = password; parms[3].Value = privilege; comm.CommandText = SQL_INSERT_USER; comm.CommandType = CommandType.Text; foreach (OracleParameter parm in parms) { comm.Parameters.Add(parm); } comm.ExecuteNonQuery(); conn.Close(); return true; } }
int MSOracleClient() { System.Data.OracleClient.OracleConnectionStringBuilder connBuilder = new System.Data.OracleClient.OracleConnectionStringBuilder(); connBuilder.DataSource = txtDataSource.Text.Trim(); connBuilder.UserID = txtUserId.Text.Trim(); connBuilder.Password = txtPwd.Text.Trim(); connBuilder.LoadBalanceTimeout = 60; connBuilder.MinPoolSize = 0; connBuilder.MaxPoolSize = 50; int rows = 0; using (System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString)) { //System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(connBuilder.ConnectionString); System.Data.OracleClient.OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = txtSql.Text.Trim(); cmd.CommandTimeout = 300; //cmd.ResetCommandTimeout(); conn.Open(); using (System.Data.OracleClient.OracleDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { object[] objs = new object[500]; dr.GetValues(objs); rows++; } } return(rows); } }
public void ProcessRequest(HttpContext context) { string htmltext = string.Empty; string guid = string.Empty; int ww; int wh; htmltext = context.Request["HTML"]; ww = Convert.ToInt32(context.Request["ww"]); wh = Convert.ToInt32(context.Request["wh"]); guid = Guid.NewGuid().ToString(); using (OracleConnection conn = new OracleConnection(context.Cache["ORASTR"].ToString())) { conn.Open(); OracleCommand comm = conn.CreateCommand(); OracleParameter[] parms = new OracleParameter[]{ new OracleParameter(":GUID",OracleType.VarChar), new OracleParameter(":DATA",OracleType.NClob), }; parms[0].Value = guid; parms[1].Value = htmltext; comm.CommandText = SQL_INSERT; comm.CommandType = CommandType.Text; foreach (OracleParameter parm in parms) { comm.Parameters.Add(parm); } comm.ExecuteNonQuery(); string url = context.Request.Url.AbsoluteUri.Substring(0, context.Request.Url.AbsoluteUri.IndexOf("SVR/ShareMap.ashx")); context.Response.Write("window.open(\'"+url+"Delegate.aspx?q="+guid+"&w="+ww+"&h="+wh+"\')"); } }
public static bool DeckIsRefD(int Deck_id) { try { OracleConnection OraCon = new OracleConnection("Data Source=oids;User ID=plm;Password=123!feed;Unicode=True"); OraCon.Open(); OracleDataAdapter OrclPrjAdapter = new OracleDataAdapter(); OracleCommand OrclPrjCmd = OraCon.CreateCommand(); OrclPrjAdapter.SelectCommand = OrclPrjCmd; OrclPrjCmd.CommandText = @"SELECT T.TABLE_NAME,T.COLUMN_NAME FROM use_deckid_tables_view T"; DataSet Mydata = new DataSet(); OrclPrjAdapter.Fill(Mydata); DataSet tmpData = new DataSet(); string QueryPrjIdCmdStr = string.Empty; for (int i = 0; i < Mydata.Tables[0].Rows.Count; i++) { QueryPrjIdCmdStr = @"SELECT T." + Mydata.Tables[0].Rows[i][1] + " FROM " + Mydata.Tables[0].Rows[i][0] + " T WHERE TO_CHAR(T." + Mydata.Tables[0].Rows[i][1] + ")=TO_CHAR(" + Deck_id + ")"; OrclPrjCmd.CommandText = QueryPrjIdCmdStr; OrclPrjAdapter.Fill(tmpData); if (tmpData.Tables[0].Rows.Count > 0) return true; } return false; } catch (Exception err) { System.Console.WriteLine(err.Message); return true; } }
public static void GetFlowStatus(ComboBox cbox, string sql) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); try { //OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; OracleDataReader datareader = cmd.ExecuteReader(); while (datareader.Read()) { cbox.Items.Add(datareader[0].ToString()); } datareader.Close(); //conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); //return; } finally { conn.Close(); } }
private void btnAlter_Click(object sender, EventArgs e) { bool check = true; if (this.comboxSL.Text == "") { check = false; MessageBox.Show("税率没有选择"); } else if (this.ComboxJSFS.Text == "") { check = false; MessageBox.Show("结算方式没有选择"); } else if (this.txtGHDW.Text == "") { check = false; MessageBox.Show("单位没有选择"); } else { check = true; } if (check == true) { using (OracleConnection connection = new OracleConnection(StrCon)) { try { connection.Open(); OracleCommand cmd = connection.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Jt_C_XSJSD_update"; cmd.Parameters.Add("LS_XSJSDid", OracleType.VarChar).Value = this.txtJSDH.Tag.ToString(); cmd.Parameters.Add("ls_GHDWID", OracleType.VarChar).Value = this.txtGHDW.Tag.ToString(); cmd.Parameters.Add("ls_JSR", OracleType.VarChar).Value = this.txtJSR.Text.ToString().Trim(); cmd.Parameters.Add("ls_JSFSID", OracleType.VarChar).Value = GetJSFSID(this.ComboxJSFS.Text.ToString().Trim()); cmd.Parameters.Add("ls_SL", OracleType.VarChar).Value = this.comboxSL.Text.ToString().Trim(); cmd.Parameters.Add("ls_ZT", OracleType.VarChar).Value = "02"; cmd.Parameters.Add("ls_BZ", OracleType.VarChar).Value = this.txtBZ.Text.ToString().Trim(); cmd.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output; cmd.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); xpServerCollectionSource1.FixedFilterString = "[XSJSDID] = \'" + this.txtJSDH.Tag.ToString() + "\'"; unitOfWork1.DropIdentityMap(); xpServerCollectionSource1.Reload(); gridView1.BestFitColumns(); MessageBox.Show(cmd.Parameters["DescErr"].Value.ToString() + cmd.Parameters["Message"].Value.ToString()); } catch (OracleException ex) { MessageBox.Show(ex.Message); } finally { connection.Close(); this.btnDWselect.Enabled = false; this.comboxSL.Enabled = false; this.ComboxJSFS.Enabled = false; this.txtJSR.ReadOnly = true; this.txtBZ.ReadOnly = true; this.txtZT.Text = "存盘"; this.txtZT.Tag = "02"; } } } }
//Author: Aleksandar Zoric public void checkLogin() { string username = userNameTxt.Text; string password = passwordTxt.Text; string connectionString = GetConnectionString("sw4", "sw4"); using (OracleConnection connection = new OracleConnection()) { connection.ConnectionString = connectionString; connection.Open(); OracleCommand command = connection.CreateCommand(); OracleCommand command1 = connection.CreateCommand(); string usernameCmd = "SELECT studentNumber FROM Students WHERE StudentNumber = '" + username + "'"; string passwordCmd = "SELECT studentPassword FROM Students WHERE StudentNumber = '" + username + "'"; command.CommandText = usernameCmd; command1.CommandText = passwordCmd; OracleDataReader usernameReader = command.ExecuteReader(); OracleDataReader passwordReader = command1.ExecuteReader(); if(usernameReader.HasRows && passwordReader.HasRows) { OracleCommand command2 = connection.CreateCommand(); string studentIDcmd = "SELECT studentID FROM Students WHERE StudentNumber = '" + username + "' AND studentPassword = '******'"; command2.CommandText = studentIDcmd; string extractStudentID = command2.ExecuteScalar().ToString(); if(extractStudentID != null) { studentIDasString = extractStudentID; } //open profile form } else { MessageBox.Show("Invalid Login. Try Again"); } } }
private void btnConfirm_Click(object sender, EventArgs e) { if (selection.SelectedCount == 0) { MessageBox.Show("没有选择单据"); } else { using (OracleConnection connection = new OracleConnection(StrCon)) { connection.Open(); OracleCommand command = connection.CreateCommand(); OracleTransaction transaction; transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); command.Transaction = transaction; try { for (int i = 0; i < selection.SelectedCount; ++i) { int RowIndex = selection.GetSelectedRowIndex(i); int RowHandle = gridView1.GetRowHandle(RowIndex); string strXSJSDID = gridView1.GetRowCellValue(RowHandle, colXSJSDID).ToString(); command.CommandText = "insert into Temp_Save_Id (tempid,id) Values (TEMP_SAVE_ID_SEQ.nextval,'" + strXSJSDID + "')"; command.ExecuteNonQuery(); } selection.ClearSelection(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "JC_C_XSTSD_XD"; command.Parameters.Add("LS_XSTSDid", OracleType.VarChar).Value = this.btnConfirm.Tag.ToString(); command.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output; command.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output; command.ExecuteNonQuery(); transaction.Commit(); string mess = command.Parameters["Message"].Value.ToString(); string alarm = command.Parameters["DescErr"].Value.ToString(); MessageBox.Show(mess + alarm); } catch (OracleException ex) { transaction.Rollback(); MessageBox.Show(ex.Message); } finally { connection.Close(); this.DialogResult = DialogResult.OK; this.Close(); } } } }
public void SyncBankAccountInfo() { var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); var bankAccountUpdateTime = DateTime.MinValue; if (!string.IsNullOrEmpty(config.AppSettings.Settings["BankAccountUpdateTime"].Value)) { bankAccountUpdateTime = DateTime.Parse(config.AppSettings.Settings["BankAccountUpdateTime"].Value); } var bankAccounts = new List<BankAccountDTO>(); var connectionString = ConfigurationManager.ConnectionStrings["OracleNC"].ToString(); var conn = new OracleConnection(connectionString); //进行连接 try { conn.Open(); //打开指定的连接 var com = conn.CreateCommand(); com.CommandText = "select * from v_jdxt_yhzh t where t.TS > '" + bankAccountUpdateTime.ToString("yyyy-MM-dd HH:mm:ss") + "'"; ; var odr = com.ExecuteReader(); while (odr.Read()) //读取数据,如果返回为false的话,就说明到记录集的尾部了 { var bankAccount = new BankAccountDTO { CustCode = odr["CUSTCODE"].ToString(), //odr.GetOracleString(0).ToString(), Address = odr["CUSTNAME"].ToString(), //odr.GetOracleString(1).ToString(), Name = odr["CUSTNAME"].ToString(), UpdateDate = DateTime.Parse(odr["TS"].ToString()), }; if (bankAccount.UpdateDate.CompareTo(bankAccountUpdateTime) > 0) { bankAccountUpdateTime = bankAccount.UpdateDate; } if (odr.GetOracleString(2).IsNull || odr.GetOracleString(3).IsNull) { continue; } bankAccount.Branch = odr["BANKDOCNAME"].ToString(); // odr.GetOracleString(2).ToString(); bankAccount.Account = odr["ACCOUNTCODE"].ToString(); // odr.GetOracleString(3).ToString(); bankAccounts.Add(bankAccount); } odr.Close(); //关闭reader.这是一定要写的 } catch { //如果发生异常,则提示出错 } finally { conn.Close(); //关闭打开的连接 } _supplierAppService.SyncBankAccountInfo(bankAccounts); config.AppSettings.Settings["BankAccountUpdateTime"].Value = bankAccountUpdateTime.ToString("yyyy-MM-dd HH:mm:ss"); config.Save(ConfigurationSaveMode.Modified); ConfigurationManager.RefreshSection("appSettings"); }
private void btnAlter_Click(object sender, EventArgs e) { if (MessageBox.Show("确定要进行修改操作", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK) { bool checktxtghdw = false; if (this.txtGHDW.Text == "") { checktxtghdw = false; MessageBox.Show("单位没有选择"); } else { checktxtghdw = true; } if (checktxtghdw == true) { OracleConnection connection = new OracleConnection(StrCon); try { connection.Open(); OracleCommand cmd = connection.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "JT_C_XSTSD_update"; cmd.Parameters.Add("LS_XSTSDid", OracleType.VarChar).Value = this.txtTSDH.Tag.ToString(); cmd.Parameters.Add("LS_KHID", OracleType.VarChar).Value = this.txtGHDW.Tag.ToString(); cmd.Parameters.Add("LS_KHMC", OracleType.VarChar).Value = this.txtGHDW.Text.ToString(); cmd.Parameters.Add("LS_SH", OracleType.VarChar).Value = this.txtSH.Text.ToString(); cmd.Parameters.Add("LS_DZ", OracleType.VarChar).Value = this.txtDZ.Text.ToString(); cmd.Parameters.Add("LS_KHYH", OracleType.VarChar).Value = this.txtKHYH.Text.ToString(); cmd.Parameters.Add("LS_ZH", OracleType.VarChar).Value = this.txtZH.Text.ToString().Trim(); cmd.Parameters.Add("LS_DH", OracleType.VarChar).Value = this.txtDH.Text.ToString().Trim(); cmd.Parameters.Add("LS_KXNRID", OracleType.VarChar).Value = this.txtKXNR.Tag.ToString().Trim(); cmd.Parameters.Add("LS_TSPJMCID", OracleType.VarChar).Value = this.txtTSPZ.Tag.ToString().Trim(); cmd.Parameters.Add("LS_SPFYQKID", OracleType.VarChar).Value = this.txtSPYF.Tag.ToString().Trim(); cmd.Parameters.Add("LS_XSHTID", OracleType.VarChar).Value = this.txtHTH.Text.ToString(); cmd.Parameters.Add("LS_ZDR", OracleType.VarChar, 255).Value = this.txtZDR.Tag.ToString(); cmd.Parameters.Add("ls_BZ", OracleType.VarChar, 255).Value = this.txtBZ.Text.ToString(); cmd.Parameters.Add("DescErr", OracleType.VarChar, 255).Direction = ParameterDirection.Output; cmd.Parameters.Add("Message", OracleType.VarChar, 255).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); unitOfWork1.DropIdentityMap(); xpServerCollectionSource1.Reload(); MessageBox.Show(cmd.Parameters["DescErr"].Value.ToString() + cmd.Parameters["Message"].Value.ToString()); } catch (OracleException ex) { MessageBox.Show(ex.Message); } finally { connection.Close(); } } } }
public static string GetReturnValue(string str, string sql) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand command = conn.CreateCommand(); command.CommandText = sql + str; string value = command.ExecuteOracleScalar().ToString(); conn.Close(); return value; }
private void btnSave_Click(object sender, EventArgs e) { if (string.IsNullOrEmpty(txtRoleName.Text.Trim())) { MessageBox.Show("请输入角色名称"); txtRoleName.Focus(); } else { using (OracleConnection connection = new OracleConnection(StrCon)) { connection.Open(); OracleCommand cmd = connection.CreateCommand(); OracleTransaction transaction; transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = transaction; try { cmd.CommandText = "insert into sys_role (role_id,role_name,super_id,description) values (seq_sys_role_role_id.nextval,'" + txtRoleName.Text.Trim() + "','0','" + txtRoleDirs.Text.Trim() + "')"; cmd.ExecuteNonQuery(); for (int i = 0; i < treeView2.Nodes.Count;i++ ) { if (treeView2.Nodes[i].Checked == true) { cmd.CommandText = "insert into SYS_ROLE_MODULE (ID,role_id,module_id) values (seq_SYS_ROLE_MODULE_id.nextval,seq_sys_role_role_id.currval,'" + treeView2.Nodes[i].Tag.ToString() + "')"; cmd.ExecuteNonQuery(); for (int j = 0; j < treeView2.Nodes[i].Nodes.Count;j++ ) { if (treeView2.Nodes[i].Nodes[j].Checked == true) { cmd.CommandText = "insert into SYS_ROLE_MODULE_ACTION t" + " (t.id, t.roleid, t.modeleid, t.actioncode)" + "values" + " (seq_action.nextval,seq_sys_role_role_id.currval, '" + treeView2.Nodes[i].Tag.ToString() + "', '" + treeView2.Nodes[i].Nodes[j].Tag.ToString() + "')"; cmd.ExecuteNonQuery(); } } } } transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); MessageBox.Show(ex.Message); } finally { connection.Close(); this.DialogResult = DialogResult.OK; this.Close(); } } } }
public void SetUp () { connection = new OracleConnection (connection_string); connection.Open (); using (command = connection.CreateCommand ()) { // create the tables command.CommandText = "create table utf8test (id number(10), text nvarchar2(64), text2 nvarchar2(64) )"; command.ExecuteNonQuery (); } }
private DataTable GetConduitDeviceData(string devicename, string wellname, string startDate, string endDate) { if (string.IsNullOrEmpty(devicename)) { devicename = "%"; } if (string.IsNullOrEmpty(wellname)) { wellname = "F315"; } string connectionString = GetOracleConnectionString(); using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection()) { try { connection.ConnectionString = connectionString; try { connection.Close(); } catch (Exception ex) { } connection.Open(); //Console.WriteLine("State: {0}", connection.State); //Console.WriteLine("ConnectionString: {0}", // connection.ConnectionString); OracleCommand command = connection.CreateCommand(); string sql = "select count(*) total, extract(month from start_Date) AS monthname,extract(year from start_Date)as yearname from v_Conduit_test where separator_id like '" + devicename + "' and conduit_name = '" + wellname + "' and validity_Status = 'valid test' "; /*if (!includeAbondoned) * { * sql += "and vc.actual_status not like '%ABAN_%' "; * }*/ if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"])) { sql += "and start_date >= '" + startDate + "' and end_date <= '" + endDate + "' "; } sql += " group by extract(month from start_date),extract(year from start_Date) order by 3"; //sql += "order by 2"; command.CommandText = sql; DataSet ds = new DataSet(); OracleDataAdapter adapter = new OracleDataAdapter(command); adapter.Fill(ds); connection.Close(); return(ds.Tables[0]); } catch (Exception ex) { connection.Close(); } return(null); } }
public void SetUp () { connection = new OracleConnection (connection_string); connection.Open (); using (command = connection.CreateCommand ()) { // create the tables command.CommandText = "create table lob_test (id number(10), lobo blob)"; command.ExecuteNonQuery (); } }
private DataTable GetWellTestData(string devicename, string wellname, string startDate, string endDate) { if (string.IsNullOrEmpty(devicename)) { devicename = "%"; } if (string.IsNullOrEmpty(wellname)) { wellname = "F315"; } string connectionString = GetOracleConnectionString(); using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection()) { try { connection.ConnectionString = connectionString; try { connection.Close(); } catch (Exception ex) { } connection.Open(); //Console.WriteLine("State: {0}", connection.State); //Console.WriteLine("ConnectionString: {0}", // connection.ConnectionString); OracleCommand command = connection.CreateCommand(); string sql = "select vct.separator_id,NVL(vct.water,0) as notNullWater,NVL(vct.oil,0) as notNullOil,NVL(vct.bsw,0) as notNullBSW,NVL(vct.gas_out,0) as notNullGasOut,vct.* from v_conduit_test vct inner join v_conduit vc on VCT.CONDUIT_NAME=VC.CONDUIT_NAME where VCT.SEPARATOR_ID IS NOT NULL and vct.separator_id like '" + devicename + "' and vct.conduit_name = '" + wellname + "' and vct.validity_status='valid test' "; //if (!includeAbondoned) //{ // sql += "and vc.actual_status not like '%ABAN_%' "; //} if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"])) { sql += "and vct.start_date >= '" + startDate + "' and vct.end_date <= '" + endDate + "' "; } sql += " order by start_date"; command.CommandText = sql; DataSet ds = new DataSet(); OracleDataAdapter adapter = new OracleDataAdapter(command); adapter.Fill(ds); connection.Close(); return(ds.Tables[0]); } catch (Exception ex) { connection.Close(); } return(null); } }
private DataSet GetDs() { MaterAttDS ds = new MaterAttDS(); string strSql = "select * from materialattachment_view " + User.pipepart + ""; OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = strSql; OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; oda.Fill(ds, "materialattachment"); conn.Close(); return ds; }
private DataSet GetDs() { SpoolDataSet ds = new SpoolDataSet(); string strSql = "select * from SPOOLSTATISTICS_TAB"; OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = strSql; OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; oda.Fill(ds,"SPOOLSTATISTICS_TAB"); conn.Close(); return ds; }
private DataSet GetDs() { SPLPIPEDataSet ds = new SPLPIPEDataSet(); string strSql = "select * from SPL_VIEW "+User.pipematerial+""; OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = strSql; OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; oda.Fill(ds, "SPLPIPE_TAB"); conn.Close(); return ds; }
public void run() { Exception exp = null; OracleConnection con = new OracleConnection(); OracleCommand cmd = null; try { BeginCase("CreateCommand"); cmd = con.CreateCommand(); Compare(cmd!=null, true); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} }
private DataSet GetDs() { TrayMaterialDeliveryTimeDs ds = new TrayMaterialDeliveryTimeDs(); string strSql = @"select t.*, t.rowid from tray_material_deliverytime_tab t"; string whereSql = User.traymatrialplan; strSql += whereSql; OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = strSql; OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; oda.Fill(ds, "TRAY_MATERIAL_DELIVERYTIME_TAB"); conn.Close(); return ds; }
private DataSet GetDs() { TrayPrePlanDs ds = new TrayPrePlanDs(); string strSql = @"select t.*, t.rowid from tray_preplan_tab t"; string whereSql = User.traypreplan; strSql += whereSql; OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr); conn.Open(); OracleCommand cmd = conn.CreateCommand(); cmd.CommandText = strSql; OracleDataAdapter oda = new OracleDataAdapter(); oda.SelectCommand = cmd; oda.Fill(ds, "TRAY_PREPLAN_TAB"); conn.Close(); return ds; }
protected override void GatherKeyColumns( DBSqlParserTable table ) { // Called after the table and column information is completed to // identify which columns in the select-list are key columns for // their table. OracleCommand cmd = null; OracleDataReader rdr = null; try { try { cmd = _connection.CreateCommand(); cmd.Transaction = _connection.Transaction; // must set the transaction context to be the same as the command, or we'll throw when we execute. string schemaName = CatalogCase(table.SchemaName); string tableName = CatalogCase(table.TableName); string synonymSchemaName = schemaName; string synonymTableName = tableName; // First, we have to "dereference" a synonym, if it was specified, because // synonyms don't have catalog items stored for them, they're for the table // or view that the synonym references. cmd.CommandText = GetSynonymQueryStatement(schemaName, tableName); rdr = cmd.ExecuteReader(); if (rdr.Read()) { synonymSchemaName = rdr.GetString(0); synonymTableName = rdr.GetString(1); } rdr.Dispose(); // Now we have the real schema name and table name, go and derive the key // columns cmd.CommandText = GetConstraintQueryStatement(synonymSchemaName, synonymTableName); rdr = cmd.ExecuteReader(); ArrayList constraintColumnNames = new ArrayList(); bool isUniqueConstraint; if (true == (_moreConstraints = rdr.Read())) { while (GetConstraint(rdr, out isUniqueConstraint, constraintColumnNames)) { bool foundAllColumns = true; int constraintColumnCount = constraintColumnNames.Count; DBSqlParserColumn[] constraintColumn = new DBSqlParserColumn[constraintColumnCount]; for (int j = 0; j < constraintColumnCount; ++j) { DBSqlParserColumn column = FindConstraintColumn( schemaName, tableName, (string)constraintColumnNames[j] ); if (null == column) { foundAllColumns = false; break; } constraintColumn[j] = column; } if (foundAllColumns) { for (int j = 0; j < constraintColumnCount; ++j) { constraintColumn[j].SetAsKey(isUniqueConstraint); } break; } } } } finally { if (null != cmd) { cmd.Dispose(); cmd = null; } if (null != rdr) { rdr.Dispose(); rdr = null; } } } catch { // Prevent exception filters from running in our space throw; } }
protected override DbCommand CreateDbCommand() { return(new OracleCommand(con.CreateCommand(), this)); }
private DataTable ConnectAndQuery(string devicename, bool includeAbondoned, string startDate, string endDate) { if (String.IsNullOrEmpty(devicename)) { devicename = "V-0516"; } string connectionString = GetOracleConnectionString(); using (System.Data.OracleClient.OracleConnection connection = new System.Data.OracleClient.OracleConnection()) { try { connection.ConnectionString = connectionString; try { connection.Close(); } catch (Exception ex) { } connection.Open(); //Console.WriteLine("State: {0}", connection.State); //Console.WriteLine("ConnectionString: {0}", // connection.ConnectionString); OracleCommand command = connection.CreateCommand(); string sqlDateFilter = (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"])) ? " and t.start_date >= '" + startDate + "' and t.end_date <= '" + endDate + "'" : ""; string sql = "select " + "vct.separator_id, vct.conduit_name," + "count(*) TotalTests, " + "CASE vc.actual_status " + "WHEN 'ABAN_01' THEN 0 WHEN 'ABAN_02' THEN 0 WHEN 'ABAN_03' THEN 0 WHEN 'ABAN_04' THEN 0 ELSE 1 END iconduitstatus," + "sum(case when VALIDITY_STATUS='valid test' then 1 else 0 end) Valid, " + "sum(case when VALIDITY_STATUS='invalid test' then 1 else 0 end) InValid," + "sum(case when VALIDITY_STATUS='valid test' then 0 when VALIDITY_STATUS= 'invalid test' then 0 else 1 end) NotValidated, " + "(select NVL(min(water),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minwater," + "(select NVL(max(water),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxwater," + "(select NVL(min(oil),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minoil," + "(select NVL(max(oil),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxoil," + "(select NVL(min(bsw),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") minbsw," + "(select NVL(max(bsw),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxbsw," + "(select NVL(min(gas_out),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") mingasout," + "(select NVL(max(gas_out),0) from V_Conduit_Test t where t.separator_id=vct.separator_id and t.conduit_name=vct.conduit_name and t.validity_status = 'valid test'" + sqlDateFilter + ") maxgasout " + "FROM V_CONDUIT_TEST VCT INNER JOIN V_CONDUIT VC ON VCT.CONDUIT_NAME=VC.CONDUIT_NAME " + "WHERE VCT.SEPARATOR_ID IS NOT NULL and vct.separator_id='" + devicename + "' "; if (!includeAbondoned) { sql += "and vc.actual_status not like '%ABAN_%' "; } if (!string.IsNullOrEmpty(Request.Form["txtStartDate"]) && !string.IsNullOrEmpty(Request.Form["txtEndDate"])) { sql += "and vct.start_date >= '" + startDate + "' and vct.end_date <= '" + endDate + "' "; } sql += "group by VCT.conduit_name, vct.separator_id, vc.actual_status order by 2"; command.CommandText = sql; DataSet ds = new DataSet(); OracleDataAdapter adapter = new OracleDataAdapter(command); adapter.Fill(ds); connection.Close(); return(ds.Tables[0]); } catch (Exception ex) { connection.Close(); } return(null); } }
public void SetUp () { if (connection_string == null) return; connection = new OracleConnection (connection_string); connection.Open (); using (command = connection.CreateCommand ()) { // create the tables command.CommandText = "create table oratest (id number(10), text varchar2(64)," + " text2 varchar2(64) )"; command.ExecuteNonQuery (); command.CommandText = "create table culture_test (id number(10), value1 float," + " value2 number(20,10), value3 number (20,10))"; command.ExecuteNonQuery (); command.CommandText = "create table oratypes_test (id NUMBER(10), value1 VARCHAR2(100)," + " value2 DATE)"; command.ExecuteNonQuery (); command.CommandText = "create or replace procedure params_pos_test (param1 in number," + "param2 in number,param3 in number,result out number) as" + " begin result:=param3; end;"; command.ExecuteNonQuery (); } }