/// <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; } }
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(); }
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; } }
public void run() { Exception exp = null; OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); OracleCommand cmd = new OracleCommand("Select * From Orders", con); OracleDataReader rdr = cmd.ExecuteReader(); //change a connection's state without closing the datareader (should fail try { BeginCase("InvalidOperationException"); try { ((IDbConnection)con).ChangeDatabase("msdb"); ExpectedExceptionNotCaught(typeof(InvalidOperationException).FullName); } catch (InvalidOperationException ex) { ExpectedExceptionCaught(ex); } } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
private void button1_Click(object sender, EventArgs e) { #region connection try { con = new OracleConnection(); con.ConnectionString = "Data Source=orcl.iacsd.org;User ID=user101;Password=user101;Unicode=True"; cmd = new OracleCommand(); cmd.CommandType = CommandType.Text; //Provide the name of stored procedu re cmd.CommandText = "select * from Emp"; cmd.Connection = con; con.Open(); MessageBox.Show(" Number of Rows =" + cmd.ExecuteScalar().ToString()); } catch (OracleException ex) { MessageBox.Show(ex.Message); } finally { if (con.State == ConnectionState.Open) { con.Close(); } } #endregion }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { try { ora2.Open(); System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado"); comando.Connection = ora2; comando.Connection = ora2; comando.CommandType = System.Data.CommandType.StoredProcedure; comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output; System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter(); adaptador.SelectCommand = comando; DataTable dt = new DataTable(); adaptador.Fill(dt); tblFlujo.DataSource = dt; tblFlujo.DataBind(); ora2.Close(); } catch (Exception ex) { lblError.Text = ex.ToString(); } } }
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(); } }
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); } }
public DataSet ExecuteDataSet(System.Data.IDbConnection connection, System.Data.CommandType commandType,string CommandText,System.Data.DataSet ds,string tablename) { if (connection.State != System.Data.ConnectionState.Open) connection.Open(); if (!this.needTransaction) tran = null; OracleCommand cmd = new OracleCommand(); PrepareCommand( cmd , connection,tran,CommandType.Text,CommandText); OracleDataAdapter da = new OracleDataAdapter(cmd); try { da.Fill(ds,tablename); da.Dispose(); } catch (System.Data.OracleClient.OracleException ex) { throw ex; } finally { if (mustCloseConnection) { if(!needTransaction) { Connection.Close(); } } } return ds; }
public static DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(); try { PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); OracleDataAdapter MyAdapter = new OracleDataAdapter(); MyAdapter.SelectCommand = cmd; DataSet ds = new DataSet(); MyAdapter.Fill(ds); cmd.Parameters.Clear(); DataTable table = ds.Tables[0]; ds.Dispose(); connection.Close(); return table; } catch { connection.Close(); throw; } } }
public void CargarQuerys() { try { ora2.Open(); System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas2"); comando3.Connection = ora2; comando3.CommandType = System.Data.CommandType.StoredProcedure; comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output; System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter(); adaptador.SelectCommand = comando3; DataTable dt = new DataTable(); adaptador.Fill(dt); tablasTareasDevueltas.DataSource = dt; tablasTareasDevueltas.DataBind(); if (tablasTareasDevueltas.Rows.Count == 0) { // Response.Write("<script>('No hay tareas pendientes')</script>"); } ora2.Close(); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>"); } try { ora2.Open(); System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_y_flujo"); comando3.Connection = ora2; comando3.CommandType = System.Data.CommandType.StoredProcedure; comando3.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreoEncargado_2.Text; comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output; System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter(); adaptador.SelectCommand = comando3; DataTable dt = new DataTable(); adaptador.Fill(dt); tablaTareas.DataSource = dt; tablaTareas.DataBind(); if (tablaTareas.Rows.Count == 0) { Response.Write("<script>('No hay tareas pendientes')</script>"); } ora2.Close(); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>"); } }
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); } }
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); } }
public void run() { Exception exp = null; OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); OracleCommand cmd = new OracleCommand("Select * From Customers", con); OracleDataReader rdr = cmd.ExecuteReader(); try { BeginCase("column REGION ordinal"); Compare(rdr.GetOrdinal("REGION"),6 ); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("column not exists"); try { int i = rdr.GetOrdinal("blabla"); } catch (Exception ex) {exp=ex;} Compare(exp.GetType().FullName,typeof(IndexOutOfRangeException).FullName); exp=null; } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
protected DataSet cmis_search(string ma_khang, string so_cto, out string txt_err) { DataSet ds = new DataSet(); try { string txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={1}; Unicode=True", "THAINGUYEN", "CMIS01PC1"); OracleConnection ora_con = new OracleConnection(txt_cmd); txt_cmd = @"select TEN_KHANG, DCHI_KHANG, DCHI_TTOAN, KY, THANG, NAM, NGAY_DKY, NGAY_CKY, LOAI_HDON, TONG_TIEN, DIEN_TTHU, NGAY_PHANH from cmis01.hdn_hdon where MA_KHANG=:ma_khang or SO_CTO=:so_cto order by NGAY_CKY desc"; OracleParameter[] pa = new OracleParameter[2]; pa[0] = new OracleParameter("ma_khang", ma_khang.ToUpper()); pa[1] = new OracleParameter("so_cto", so_cto); OracleDataAdapter ora_da = new OracleDataAdapter(txt_cmd, ora_con); ora_da.SelectCommand.CommandType = CommandType.Text; ora_da.SelectCommand.Parameters.Add(pa[0]); ora_da.SelectCommand.Parameters.Add(pa[1]); ora_con.Open(); ora_da.Fill(ds); ora_con.Close(); txt_err = ""; } catch (Exception ex) { txt_err = ex.Message; } return ds; }
public static void AddTrayORClass(string queryString, string project, string drawing, string spool,string trayorclass) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接 conn.Open(); OracleTransaction trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(queryString, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("project_in", OracleType.VarChar).Value = project; cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing; cmd.Parameters.Add("spool_in", OracleType.VarChar).Value = spool; cmd.Parameters.Add("trayorclass_in", OracleType.VarChar).Value = trayorclass; cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } }
public static bool TestConnect(string user, string pwd, string serviceName, out string msg) { //Data Source=lan_189;User ID=user_ahdy_shi;Password=user_ahdy_shi string conn = string.Format("Data Source={0};User ID={1};Password={2}", serviceName.Trim(), user, pwd); try { using (OracleConnection con = new OracleConnection(conn)) { OracleConnection.ClearPool(con); System.Threading.Thread.Sleep(100); con.Open(); con.Close(); con.Dispose(); } msg = "连接" + serviceName.ToUpper() + "成功"; return true; } catch (Exception ex) { msg = ex.Message; return false; } }
public void run() { OracleConnection con = null; OracleTransaction txn; Exception exp = null; try { BeginCase("OracleTransaction Rollback"); // //prepare data base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); string Result = ""; con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); txn = con.BeginTransaction(); OracleCommand cmd = new OracleCommand("Update Employees Set LastName = 'StamLastName' Where EmployeeID = 100", con, txn); cmd.ExecuteNonQuery(); txn.Rollback(); // // cmd = new OracleCommand("Select LastName From Employees Where EmployeeID = 100", con); Result = cmd.ExecuteScalar().ToString(); Compare(Result,"Last100" ); this.Log(Result); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
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(); }
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; } }
public ActionResult Create(CreateChart Chart) { if (ModelState.IsValid) { string connectionString = "Data Source=HRMSDEV;user id=PERSON;password=PERSON;Unicode=True;"; string CHART_ID ; using (OracleConnection connection = new OracleConnection(connectionString)) { string number = null; number = ""; OracleCommand cmd1 = new OracleCommand(); cmd1.CommandType = CommandType.Text; cmd1.CommandText = "select nvl(max(substr(CHART_ID,7,3)),0) from KMIINTRANET_MASTER_CHART where substr(CHART_ID,1,6)='" + Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + "'"; cmd1.Connection = connection; connection.Open(); objDataReader = cmd1.ExecuteReader(); if (objDataReader.HasRows) { objDataReader.Read(); number = Convert.ToString(Convert.ToInt32(objDataReader[0]) + 1); if (number.Length == 1) { number = "00" + number; } else if (number.Length == 2) { number = "0" + number; } else if (number.Length == 3) { number = number; } } cmd1 = null; connection.Close(); OracleCommand cmd2 = new OracleCommand(); CHART_ID = Convert.ToDateTime(System.DateTime.Now).ToString("yyyyMM") + number; byte[] tempFile = new byte[Chart.ChartFile.InputStream.Length]; Chart.ChartFile.InputStream.Read(tempFile, 0, tempFile.Length); cmd2.CommandText = "INSERT INTO KMIINTRANET_MASTER_CHART(CHART_ID,CHART_TITLE,CHART_FILE,CREATE_BY,CREATE_DATE) VALUES (:chartid,:title,:chartfile,'" + Session["USER"] + "',sysdate)"; cmd2.CommandType = CommandType.Text; cmd2.Connection = connection; connection.Open(); cmd2.Parameters.Add(":chartfile", OracleType.Blob).Value = tempFile; cmd2.Parameters.Add(":title", OracleType.VarChar).Value = Chart.title.ToString(); cmd2.Parameters.Add(":chartid", OracleType.VarChar).Value = CHART_ID; cmd2.ExecuteNonQuery(); cmd2 = null; connection.Close(); return RedirectToAction("Index"); } } return View(); }
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(); } }
protected void ddlAmphur_SelectedIndexChanged(object sender, EventArgs e) { try { using (OracleConnection sqlConn = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { using (OracleCommand sqlCmd = new OracleCommand()) { sqlCmd.CommandText = "select * from TB_DISTRICT where AMPHUR_ID=:DISTRICT_ID"; sqlCmd.Parameters.Add(":DISTRICT_ID", ddlAddressAmphurCondition.SelectedValue); sqlCmd.Connection = sqlConn; sqlConn.Open(); OracleDataAdapter da = new OracleDataAdapter(sqlCmd); DataTable dt = new DataTable(); da.Fill(dt); ddlAddressDistrictCondition.DataSource = dt; ddlAddressDistrictCondition.DataValueField = "DISTRICT_ID"; ddlAddressDistrictCondition.DataTextField = "DISTRICT_TH"; ddlAddressDistrictCondition.DataBind(); sqlConn.Close(); ddlAddressDistrictCondition.Items.Insert(0, new ListItem("--กรุณาเลือก ตำบล--", "0")); } } } catch { } }
public static void MarkDeletedSpoolRecord(string projectid, string spname, string drawing, string username) { OracleConnection conn = new OracleConnection(DataAccess.OIDSConnStr);//获得conn连接 conn.Open(); string queryString = "SP_MarkDeleteSpoolRecord"; OracleTransaction trans = conn.BeginTransaction(); OracleCommand cmd = new OracleCommand(queryString, conn); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("projectid_in", OracleType.VarChar).Value = projectid; cmd.Parameters.Add("spname_in", OracleType.VarChar).Value = spname; cmd.Parameters.Add("drawing_in", OracleType.VarChar).Value = drawing; cmd.Parameters.Add("username_in", OracleType.VarChar).Value = username; cmd.Transaction = trans; try { cmd.ExecuteNonQuery(); trans.Commit(); } catch (OracleException ee) { trans.Rollback(); MessageBox.Show(ee.Message.ToString(), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { conn.Close(); } }
/// <summary> /// 释放当前连接 /// </summary> /// <param name="conn">oracle连接通道</param> /// <param name="connStr">oracle连接字符串</param> public static void freeConnectionToPool(OracleConnection conn,string connStr) { //如果当前连接字符串不在连接管理器内,首先在管理器中创建 if (queueManager.ContainsKey(connStr) == false) queueManager.Add(connStr, new Queue<OracleConnection>()); lock (queueManager[connStr]) { if (queueManager[connStr].Count < maxOpen) { queueManager[connStr].Enqueue(conn); } else { try { conn.Close(); conn = null; } catch (Exception e) { Console.WriteLine(e); } } } }
/// <summary> /// ִ�в�ѯ��䣬����DataSet /// </summary> /// <param name="SQLString">��ѯ���</param> /// <returns>DataSet</returns> public static DataSet Query(string SQLString) { if (SQLString != null && SQLString.Trim() != "") { using (OracleConnection connection = new OracleConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); OracleDataAdapter command = new OracleDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (System.Data.OracleClient.OracleException ex) { throw new Exception(ex.Message); } finally { connection.Close(); } return ds; } } else { return null; } }
public void run() { Exception exp = null; OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); OracleTransaction txn = null; try { BeginCase("BeginTransaction - connection close"); try { txn = con.BeginTransaction(); } catch (Exception ex) {exp = ex;} Compare(exp.GetType().FullName ,typeof(InvalidOperationException).FullName ); exp=null; } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} con.Open(); try { BeginCase("BeginTransaction - connection close"); txn = con.BeginTransaction(); Compare(txn == null,false ); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.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; } }
protected void ddlCampus_SelectedIndexChanged(object sender, EventArgs e) { try { using (OracleConnection sqlConn = new OracleConnection(DatabaseManager.CONNECTION_STRING)) { using (OracleCommand sqlCmd = new OracleCommand()) { sqlCmd.CommandText = "select * from TB_FACULTY where CAMPUS_ID = " + ddlCampus.SelectedValue; sqlCmd.Connection = sqlConn; sqlConn.Open(); OracleDataAdapter da = new OracleDataAdapter(sqlCmd); DataTable dt = new DataTable(); da.Fill(dt); ddlFaculty.DataSource = dt; ddlFaculty.DataValueField = "FACULTY_ID"; ddlFaculty.DataTextField = "FACULTY_NAME"; ddlFaculty.DataBind(); sqlConn.Close(); ddlFaculty.Items.Insert(0, new ListItem("--กรุณาเลือกสำนัก / สถาบัน / คณะ--", "0")); ddlDivision.Items.Clear(); ddlDivision.Items.Insert(0, new ListItem("--กรุณาเลือกกอง / สำนักงานเลขา / ภาควิชา--", "0")); ddlWorkDivision.Items.Clear(); ddlWorkDivision.Items.Insert(0, new ListItem("--กรุณาเลือกงาน / ฝ่าย--", "0")); } } } catch { } }
/// <summary> /// execute a query£¬return DataSet /// </summary> /// <param name="SQLString"></param> /// <returns>DataSet</returns> public static DataSet Query(string connectionString, string SQLString) { using (OracleConnection connection = new OracleConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); OracleDataAdapter command = new OracleDataAdapter(SQLString, connection); command.Fill(ds, "ds"); } catch (OracleException ex) { throw new Exception(ex.Message); } finally { if (connection.State != ConnectionState.Closed) { connection.Close(); } } return ds; } }
public void run() { Exception exp = null; OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString); con.Open(); OracleCommand cmd = new OracleCommand("Select * From Customers", con); OracleDataReader rdr = cmd.ExecuteReader(); try { BeginCase("Before execute"); Compare(rdr.Depth , 0); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} try { BeginCase("After execute"); rdr.Read(); Compare(rdr.Depth , 0); } catch(Exception ex){exp = ex;} finally{EndCase(exp); exp = null;} if (con.State == ConnectionState.Open) con.Close(); }
/// <summary> /// 执行存储过程 /// </summary> /// <param name="name"></param> /// <param name="paramList"></param> /// <returns></returns> public static bool ExecuteProduce(string name, IList<DbParameter> paramList) { OracleConnection conn = new OracleConnection(); conn.ConnectionString = ConnectionString; conn.Open(); OracleCommand dbCommand = new OracleCommand(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = name; try { foreach (DbParameter param in paramList) { dbCommand.Parameters.Add(param); } dbCommand.ExecuteNonQuery(); return true; } catch (Exception ex) { conn.Close(); return false; } finally { conn.Close(); } }
protected void showDonVi() { try { string user = "******"; string dataSource = showSiteName(); if (dataSource.ToUpper() == "BACGIANG") user = "******"; string txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={1}; Unicode=True", dataSource, user); if (dataSource.ToUpper() == "NGHEAN") txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={2}; Unicode=True", dataSource, "CMIS01PC1", "PC1CMIS01"); if (dataSource.ToUpper() == "NINHBINH") txt_cmd = string.Format("Data Source={0}; User ID={1}; Password={2}; Unicode=True", dataSource, "CMIS01PC1", "pc1cmis01"); OracleConnection ora_con = new OracleConnection(txt_cmd); txt_cmd = "select MA_DVIQLY, TEN_DVIQLY from cmis01.d_dvi_qly where CAP_DVI=3 order by MA_DVIQLY"; OracleDataAdapter ora_da = new OracleDataAdapter(txt_cmd, ora_con); DataSet ds = new DataSet(); ora_con.Open(); ora_da.Fill(ds); ora_con.Close(); ddDonViQL.DataSource = ds; ddDonViQL.DataBind(); } catch (Exception ex) { lbError.Text = ex.Message; } }
/// <summary> /// 模块定义 /// </summary> /// <param name="con">OracleConnection</param> /// <param name="ModelCode">模块代码</param> /// <returns></returns> public static List<FLOW_MODELDEFINE_T> GetModelDefineByCode(OracleConnection con,string ModelCode) { if (string.IsNullOrEmpty(con.ConnectionString)) { con.ConnectionString = ADOHelper.ConnectionString; } List<FLOW_MODELDEFINE_T> list = new List<FLOW_MODELDEFINE_T>(); OracleDataReader dr = null; try { #region //OracleCommand cmd = con.CreateCommand(); //cmd.CommandText = "select * from FLOW_MODELDEFINE_T where MODELCODE='" + ModelCode + "'"; //dr = cmd.ExecuteReader(); string sql = "select * from FLOW_MODELDEFINE_T where MODELCODE='" + ModelCode + "'"; dr= MsOracle.ExecuteReaderByTransaction(con, sql, null); while (dr.Read()) { #region define FLOW_MODELDEFINE_T define = new FLOW_MODELDEFINE_T(); define.CREATECOMPANYID = dr["CREATECOMPANYID"] == DBNull.Value ? null : dr["CREATECOMPANYID"].ToString(); define.CREATEDATE = (DateTime)dr["CREATEDATE"]; define.CREATEDEPARTMENTID = dr["CREATEDEPARTMENTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString(); define.CREATEPOSTID = dr["CREATEPOSTID"] == DBNull.Value ? null : dr["CREATEDEPARTMENTID"].ToString(); define.CREATEUSERID = dr["CREATEUSERID"] == DBNull.Value ? null : dr["CREATEUSERID"].ToString(); define.CREATEUSERNAME = dr["CREATEUSERNAME"] == DBNull.Value ? null : dr["CREATEUSERNAME"].ToString(); define.DESCRIPTION = dr["DESCRIPTION"] == DBNull.Value ? null : dr["DESCRIPTION"].ToString(); define.EDITDATE = dr["EDITDATE"] == DBNull.Value ? null : (DateTime?)dr["EDITDATE"]; define.EDITUSERID = dr["EDITUSERID"] == DBNull.Value ? null : dr["EDITUSERID"].ToString(); define.EDITUSERNAME = dr["EDITUSERNAME"] == DBNull.Value ? null : dr["EDITUSERNAME"].ToString(); define.MODELCODE = dr["MODELCODE"] == DBNull.Value ? null : dr["MODELCODE"].ToString(); define.MODELDEFINEID = dr["MODELDEFINEID"] == DBNull.Value ? null : dr["MODELDEFINEID"].ToString(); define.PARENTMODELCODE = dr["PARENTMODELCODE"] == DBNull.Value ? null : dr["PARENTMODELCODE"].ToString(); define.SYSTEMCODE = dr["SYSTEMCODE"] == DBNull.Value ? null : dr["SYSTEMCODE"].ToString(); list.Add(define); #endregion } dr.Close(); #endregion return list; } catch (Exception ex) { if (dr != null && !dr.IsClosed) { dr.Close(); } if (con.State == ConnectionState.Open) { con.Close(); } throw new Exception("GetModelDefineByCode-->" + ex.Message); } }
private void EditStudentFrm_Load(object sender, EventArgs e) { //Author: Niall Stack - t00174406 string CloudDB = "Data Source=cp3dbinstance.c4pxnpz4ojk8.us-east-1.rds.amazonaws.com:1521/cp3db;User Id=sw4;Password=sw4;"; try { OracleConnection conn = new OracleConnection(CloudDB); OracleCommand cmd = new OracleCommand("SELECT * FROM Students", conn); cmd.CommandType = CommandType.Text; OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, "ss"); studGrd.DataSource = ds.Tables["ss"]; conn.Close(); } catch (OracleException ex) { MessageBox.Show(ex.Message); } }
public string MianProcess(string Company_code, string ConfID, string FielName, string TransType, string sp_name) { string retval = ""; try { System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(ConnectionString); System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(); cmd.Parameters.Clear(); cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = sp_name; cmd.Parameters.Add("p_company_code", OracleType.VarChar, 2000).Value = Company_code; cmd.Parameters.Add("P_CONF_ID", OracleType.VarChar, 2000).Value = ConfID; cmd.Parameters.Add("P_FILE_NAME", OracleType.VarChar, 2000).Value = FielName; cmd.Parameters.Add("P_trans_type", OracleType.VarChar, 2000).Value = TransType; cmd.Parameters.Add("v_retval", OracleType.VarChar, 2000).Direction = ParameterDirection.Output; con.Open(); cmd.ExecuteNonQuery(); con.Close(); retval = cmd.Parameters["v_retval"].Value.ToString(); } catch (Exception ex) { throw new Exception("Data Base Error: " + ex.Message); } #region //try //{ // System.Data.OracleClient.OracleConnection con = new System.Data.OracleClient.OracleConnection(ConnectionString); // con.Open(); // System.Data.OracleClient.OracleCommand cmd = new System.Data.OracleClient.OracleCommand(); // cmd.CommandText = "sp_raw_DATALOAD1"; // cmd.CommandType = CommandType.StoredProcedure; // cmd.Parameters.Add("p_company_code", OracleType.VarChar, 2000).Value = Company_code; // cmd.Parameters.Add("P_CONF_ID", OracleType.VarChar, 2000).Value = ConfID; // cmd.Parameters.Add("P_FILE_NAME", OracleType.VarChar, 2000).Value = FielName; // int result = cmd.ExecuteNonQuery(); // con.Close(); // retval = "Data successfully Processed."; //} //catch (Exception ex) //{ // retval = ex.Message; //} #endregion return(retval); }
/// <summary> /// 添加Oracle移动终端资讯内容表 /// </summary> /// <returns></returns> public long AddTB_PUSH_INFO(PopupMsgPlan popupMsgPlan) { long res = 0; OracleConnection Con = new System.Data.OracleClient.OracleConnection(SqlConnectFactory.BaiduPush); Con.Open(); string cmdText = "insert into tb_push_info " + "(fid, infoid, title, infoabstract, author, createdtime, infotype, infocontent,OperateDate,PlanCount,RealCount,ClickCount,EDITOR,PLATFORM) " + "values " + "(seq_push_info.nextval, 'ds" + popupMsgPlan.PlanId + "', '" + popupMsgPlan.Title + "', '" + popupMsgPlan.Content + "', '系统推送', to_date('" + popupMsgPlan.CreatedTime + "','yyyy-mm-dd hh24:mi:ss'), '" + popupMsgPlan.PushColumn + "', :infocontent,sysdate,0,0,0,'" + popupMsgPlan.Editor + "','" + popupMsgPlan.PushPlatform + "')"; OracleCommand cmd = new OracleCommand(cmdText, Con); OracleParameter op = new OracleParameter("infocontent", OracleType.Clob); op.Value = StringHelper.RetentionHTML(popupMsgPlan.Content); cmd.Parameters.Add(op); cmd.ExecuteNonQuery(); Con.Close(); return(res); }
public bool TestConnection() { try { if (Connection.State != ConnectionState.Open) { Connection.Open(); } return(true); } catch (SqlException ex) { if (Connection.State != ConnectionState.Closed) { Connection.Close(); } return(false); } }
protected void DataList1_SelectedIndexChanged(object sender, EventArgs e) { int idx = DataList1.SelectedIndex; Label lbl = (Label)DataList1.Items[idx].FindControl("Label2"); System.Data.OracleClient.OracleConnection conn = new System.Data.OracleClient.OracleConnection(ConfigurationManager.ConnectionStrings["CGConnectionString"].ToString()); System.Data.OracleClient.OracleCommand myCommand = new System.Data.OracleClient.OracleCommand("SELECT * FROM wf_cm_images WHERE ID = '" + lbl.Text + "'", conn); conn.Open(); System.Data.OracleClient.OracleDataReader myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.Default); try { while (myReader.Read()) { System.Data.OracleClient.OracleLob myLob = myReader.GetOracleLob(myReader.GetOrdinal("IMAGE")); if (!myLob.IsNull) { string FN = myReader.GetString(myReader.GetOrdinal("IMAGE_NAME")); //Use buffer to transfer data byte[] b = new byte[myLob.Length]; //Read data from database myLob.Read(b, 0, (int)myLob.Length); Response.AddHeader("content-disposition", "attachment;filename=" + FN); Response.ContentType = "application/octectstream"; Response.BinaryWrite(b); Response.End(); } } } finally { myReader.Close(); conn.Close(); } }
public int ExcuteDataTable(DataTable srcTable, string commandText, System.Data.CommandType commandType) { if (this.Connection.State != System.Data.ConnectionState.Open) { this.Connection.Open(); } if (!this.needTransaction) { tran = null; } OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, this.Connection, tran, CommandType.Text, commandText); OracleDataAdapter da = new OracleDataAdapter(cmd); try { da.Fill(srcTable); da.Dispose(); } catch (System.Data.OracleClient.OracleException ex) { return(-1); } finally { if (mustCloseConnection) { if (!needTransaction) { Connection.Close(); } } } return(1); }
/// <summary> /// 添加Oracle移动终端资讯内容表 /// </summary> /// <returns></returns> public long AddTB_PUSH_INFO(News news, string infoType, PopupMsgPlan popupMsgPlan) { long res = 0; //var dbFactory = new OrmLiteConnectionFactory(SqlConnectFactory.BaiduPush, OracleDialect.Provider); //TB_PUSH_INFO pushInfo = new TB_PUSH_INFO(); //pushInfo.INFOID = news.NewsId; //pushInfo.TITLE = news.Title; //pushInfo.INFOABSTRACT = news.NewsAbstract; //pushInfo.AUTHOR = news.Author; //pushInfo.CREATEDTIME = news.CreatedTime; //pushInfo.INFOTYPE = infoType; //pushInfo.INFOCONTENT = news.Content; //using (var db = dbFactory.OpenDbConnection()) //{ // res = db.InsertParam<TB_PUSH_INFO>(pushInfo); //} //return res; OracleConnection Con = new System.Data.OracleClient.OracleConnection(SqlConnectFactory.BaiduPush); Con.Open(); string cmdText = "insert into tb_push_info " + "(fid, infoid, title, infoabstract, author, createdtime, infotype, infocontent,OperateDate,PlanCount,RealCount,ClickCount,EDITOR,PLATFORM) " + "values " + "(seq_push_info.nextval, '" + news.NewsId + "', '" + news.Title + "', '" + news.NewsAbstract + "', '" + news.Author + "', to_date('" + news.CreatedTime + "','yyyy-mm-dd hh24:mi:ss'), '" + infoType + "', :infocontent,sysdate,0,0,0,'" + popupMsgPlan.Editor + "','" + popupMsgPlan.PushPlatform + "')"; OracleCommand cmd = new OracleCommand(cmdText, Con); OracleParameter op = new OracleParameter("infocontent", OracleType.Clob); op.Value = StringHelper.RetentionHTML(news.Content); cmd.Parameters.Add(op); cmd.ExecuteNonQuery(); Con.Close(); return(res); }
// This routine writes a User Event audit entry // into the SYS_USE_ADUIT table public void WriteUserEvent(string AppName, string UserID, UserEvent uEvent) { System.Data.OracleClient.OracleConnection ConnOracle; System.Data.OracleClient.OracleCommand CmdOracle; //Parameters for the Oracle Package OracleParameter return_val = new OracleParameter("v_success", OracleType.VarChar, 1); return_val.Direction = ParameterDirection.ReturnValue; OracleParameter p_external_user_id = new OracleParameter("p_external_user_id", OracleType.VarChar, 250); OracleParameter p_system_use = new OracleParameter("p_system_use", OracleType.VarChar, 250); OracleParameter p_system_activity = new OracleParameter("p_system_activity", OracleType.VarChar, 250); ConnOracle = new System.Data.OracleClient.OracleConnection(); CmdOracle = new System.Data.OracleClient.OracleCommand(); try { p_external_user_id.Value = UserID; switch (uEvent) { case UserEvent.LogonSuccessful: p_system_activity.Value = "Logon successful"; break; case UserEvent.LogonFailed: p_system_activity.Value = "Logon failed"; break; case UserEvent.Logoff: p_system_activity.Value = "Logoff"; break; case UserEvent.SessionTimeout: p_system_activity.Value = "Session timeout"; break; } p_system_use.Value = AppName; //Use Oracle Package ConnOracle.ConnectionString = GSA.R7BD.Utility.DataAccess.ConnStrBDFApps(); CmdOracle.Connection = ConnOracle; CmdOracle.CommandText = "bdfapps.SYS_USE_AUDIT_PKG.CREATE_SYS_USE_AUDIT_REC"; CmdOracle.CommandType = CommandType.StoredProcedure; CmdOracle.Parameters.Clear(); CmdOracle.Parameters.Add(p_external_user_id); CmdOracle.Parameters.Add(p_system_activity); CmdOracle.Parameters.Add(p_system_use); CmdOracle.Parameters.Add(return_val); if (ConnOracle.State != ConnectionState.Open) { ConnOracle.Open(); } CmdOracle.ExecuteScalar(); //Check the return value if (CmdOracle.Parameters["v_success"].Value.ToString().Trim() == "F") { GSA.R7BD.Utility.EventLog.AddWebErrors(AppName, "GSA.R7BD.Utility", "Audit", "Call to bdfapps.SYS_USE_AUDIT_PKG.CREATE_SYS_USE_AUDIT_REC failed."); } } catch (Exception ex) { // Write the exception to the error log GSA.R7BD.Utility.EventLog.AddWebErrors(AppName, "GSA.R7BD.Utility", "Audit", ex.Message); } finally { if (ConnOracle.State == ConnectionState.Open) { ConnOracle.Close(); } } }
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 override void Close() { con.Close(); }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { lblidddl.Visible = false; txtCorreo.Text = Session["Value"].ToString(); Array enumList1 = Enum.GetValues(typeof(tiposTarea)); foreach (tiposTarea getTipoTarea in enumList1) { ddlTipoTarea.Items.Add(new ListItem(getTipoTarea.ToString(), ((int)getTipoTarea).ToString())); } try { ora2.Open(); System.Data.OracleClient.OracleCommand comando3 = new System.Data.OracleClient.OracleCommand("listar_tareas_devueltas"); comando3.Connection = ora2; comando3.CommandType = System.Data.CommandType.StoredProcedure; comando3.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output; System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter(); adaptador.SelectCommand = comando3; DataTable dt = new DataTable(); adaptador.Fill(dt); tablasTareasDevueltas.DataSource = dt; tablasTareasDevueltas.DataBind(); if (tablasTareasDevueltas.Rows.Count == 0) { // Response.Write("<script>('No hay tareas pendientes')</script>"); } ora2.Close(); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>"); } try { ora2.Open(); System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("listar_flujo_aprobado"); comando.Connection = ora2; comando.CommandType = System.Data.CommandType.StoredProcedure; comando.Parameters.Add("p_recordset", OracleType.Cursor).Direction = ParameterDirection.Output; System.Data.OracleClient.OracleDataAdapter adaptador = new System.Data.OracleClient.OracleDataAdapter(); System.Data.OracleClient.OracleDataReader rdr = comando.ExecuteReader(); while (rdr.Read()) { ddlFlujo.Items.Add(rdr.GetString(0).ToString()); } ora2.Close(); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>"); } } }
public string AggiornaMPWrUSER_N(int wr_id, string p_data, string p_data1, int p_stato, string p_motivo, int p_addetto_id//, string p_materiali ) { //-----inserire nome del file nuovo string numrdl = ""; string STORENAME = "PACK_MAN_PROG.AggiornaWRMPUSER_NOTE_N"; conn.Open(); System.Data.OracleClient.OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = STORENAME; OracleParameter par1 = new OracleParameter(); par1.ParameterName = "p_wr_id"; par1.OracleType = System.Data.OracleClient.OracleType.Number; par1.Direction = ParameterDirection.Input; par1.Value = wr_id; par1.Size = 10; cmd.Parameters.Add(par1); OracleParameter par2 = new OracleParameter(); par2.ParameterName = "p_data"; par2.OracleType = System.Data.OracleClient.OracleType.VarChar; par2.Direction = ParameterDirection.Input; par2.Size = 50; par2.Value = p_data; cmd.Parameters.Add(par2); OracleParameter par3 = new OracleParameter(); par3.ParameterName = "p_data1"; par3.OracleType = System.Data.OracleClient.OracleType.VarChar; par3.Direction = ParameterDirection.Input; par3.Size = 50; par3.Value = p_data1; cmd.Parameters.Add(par3); OracleParameter par4 = new OracleParameter(); par4.ParameterName = "p_stato"; par4.OracleType = System.Data.OracleClient.OracleType.Number; par4.Direction = ParameterDirection.Input; par4.Value = p_stato; par4.Size = 10; cmd.Parameters.Add(par4); OracleParameter par5 = new OracleParameter(); par5.ParameterName = "p_motivo"; par5.OracleType = System.Data.OracleClient.OracleType.VarChar; par5.Direction = ParameterDirection.Input; par5.Size = 255; par5.Value = p_motivo; cmd.Parameters.Add(par5); OracleParameter par6 = new OracleParameter(); par6.ParameterName = "p_addetto_id"; par6.OracleType = System.Data.OracleClient.OracleType.Number; par6.Direction = ParameterDirection.Input; par6.Size = 10; par6.Value = p_addetto_id; cmd.Parameters.Add(par6); // OracleParameter par7 = new OracleParameter(); // par7.ParameterName="p_materiali"; // par7.OracleType = System.Data.OracleClient.OracleType.VarChar; // par7.Direction = ParameterDirection.Input; // par7.Size=255; // par7.Value=p_materiali; // cmd.Parameters.Add(par7); OracleParameter par8 = new OracleParameter(); par8.ParameterName = "p_username"; par8.OracleType = System.Data.OracleClient.OracleType.VarChar; par8.Direction = ParameterDirection.Input; par8.Size = 255; par8.Value = System.Web.HttpContext.Current.User.Identity.Name; cmd.Parameters.Add(par8); OracleParameter par9 = new OracleParameter(); par9.ParameterName = "p_IdOut"; par9.OracleType = System.Data.OracleClient.OracleType.VarChar; par9.Direction = ParameterDirection.Output; par9.Size = 255; par9.Value = ""; cmd.Parameters.Add(par9); cmd.ExecuteNonQuery(); numrdl = par9.Value.ToString(); //numrdl = Int32.Parse(cmd.Parameters["p_IdOut"].Value.ToString()); conn.Close(); cmd.Dispose(); return(numrdl); }
public string GENERAPDFSGA(int wr_id, string sga, string username) { System.Web.UI.Page pg = new Page(); string pathRptSource; ReportDocument crReportDocument = new ReportDocument(); Classi.ManCorrettiva.MANCORRETTIVASFOGLIARDL _RichiestaIntervento = new Classi.ManCorrettiva.MANCORRETTIVASFOGLIARDL(); DataSet Ds = _RichiestaIntervento.ReportSGA(wr_id); dsRapportino dsP = new dsRapportino(); int i = 0; for (i = 0; i <= Ds.Tables[0].Rows.Count - 1; i++) { dsP.Tables["sga"].ImportRow(Ds.Tables[0].Rows[i]); } pathRptSource = pg.Server.MapPath("../Report/RptSGA1.rpt"); crReportDocument.Load(pathRptSource); crReportDocument.SetDataSource(dsP); //string Fname = pg.Server.MapPath("../Report/" + pg.Session.SessionID.ToString() + ".pdf");// ExportOptions optExp; DiskFileDestinationOptions optDsk = new DiskFileDestinationOptions(); PdfRtfWordFormatOptions optPdfRtf = new PdfRtfWordFormatOptions(); optExp = crReportDocument.ExportOptions; optExp.ExportFormatType = ExportFormatType.PortableDocFormat; optExp.FormatOptions = optPdfRtf; optExp.ExportDestinationType = ExportDestinationType.DiskFile; //-----inserire nome del file nuovo string STORENAME = "PACK_TRACCIA_DOC.SP_INS_SGA"; conn.Open(); System.Data.OracleClient.OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = STORENAME; OracleParameter par1 = new OracleParameter(); par1.ParameterName = "p_id_wr"; par1.OracleType = System.Data.OracleClient.OracleType.Number; par1.Direction = ParameterDirection.Input; par1.Value = wr_id; par1.Size = 100; cmd.Parameters.Add(par1); OracleParameter par2 = new OracleParameter(); par2.ParameterName = "p_user"; par2.OracleType = System.Data.OracleClient.OracleType.VarChar; par2.Direction = ParameterDirection.Input; par2.Size = 50; par2.Value = username; cmd.Parameters.Add(par2); OracleParameter par3 = new OracleParameter(); par3.ParameterName = "p_sga"; par3.OracleType = System.Data.OracleClient.OracleType.VarChar; par3.Direction = ParameterDirection.Input; par3.Size = 100; par3.Value = sga; cmd.Parameters.Add(par3); OracleParameter par4 = new OracleParameter(); par4.ParameterName = "p_codice"; par4.OracleType = System.Data.OracleClient.OracleType.VarChar; par4.Direction = ParameterDirection.Output; par4.Size = 50; par4.Value = ""; cmd.Parameters.Add(par4); cmd.ExecuteNonQuery(); string NOMESGA = cmd.Parameters["p_codice"].Value.ToString(); // OracleDataReader rdr = cmd.ExecuteReader(); // string pr=rdr.GetString(); //string pr=cmd.ExecuteScalar(); conn.Close(); cmd.Dispose(); //@"C:\Inetpub\wwwroot\RL\Doc_DB\" //optDsk.DiskFileName = pathRptSource+sga+".pdf"; optDsk.DiskFileName = @System.Configuration.ConfigurationSettings.AppSettings["DIRSGA"] + NOMESGA; //Fname; optExp.DestinationOptions = optDsk; crReportDocument.Export(); string FileZip = Path.GetDirectoryName(optDsk.DiskFileName) + @"\" + Path.GetFileNameWithoutExtension(optDsk.DiskFileName) + ".zip"; if (File.Exists(FileZip)) { File.Delete(FileZip); } ZipOutputStream s = new ZipOutputStream(File.Create(FileZip)); s.SetLevel(5); // 0 - store only to 9 - FileStream fs = File.OpenRead(optDsk.DiskFileName); byte[] buffer = new byte[fs.Length]; fs.Read(buffer, 0, buffer.Length); ZipEntry entry = new ZipEntry(Path.GetFileName(optDsk.DiskFileName)); s.PutNextEntry(entry); s.Write(buffer, 0, buffer.Length); s.Finish(); s.Close(); fs.Close(); TheSite.Classi.MailSend mail = new TheSite.Classi.MailSend(); //mail.SendMail(optDsk.DiskFileName,wr_id,DocType.SGA); mail.SendMail(FileZip, wr_id, DocType.SGA); return(optDsk.DiskFileName); }
public bool Open(string connectionString, bool testIt) { int pos = connectionString.IndexOf(":"); if (pos == -1) { return(false); } try { Close(); string type = connectionString.Substring(0, pos); string connStr = connectionString.Substring(pos + 1, connectionString.Length - pos - 1); switch (type.ToLower()) { case "oledb": oledbConnection = new OleDbConnection(connStr); if (testIt) { oledbConnection.Open(); oledbConnection.Close(); } break; case "odbc": odbcConnection = new OdbcConnection(connStr); if (testIt) { odbcConnection.Open(); odbcConnection.Close(); } break; case "oracleclient": case "oracle": oracleConnection = new OracleConnection(connStr); if (testIt) { oracleConnection.Open(); oracleConnection.Close(); } break; case "sqlclient": case "sql": sqlConnection = new SqlConnection(connStr); if (testIt) { sqlConnection.Open(); sqlConnection.Close(); } break; case "npgsql": try { dbFactory = DataProvider.PostgresProvider; dbConnection = dbFactory.CreateConnection(); dbConnection.ConnectionString = connStr; if (testIt) { dbConnection.Open(); dbConnection.Close(); } break; } catch (Exception ex) { _errMsg = ex.Message; return(false); } break; default: _errMsg = "Test Connection is not supported for '" + type + "'"; return(false); } return(true); } catch (Exception ex) { _errMsg = ex.Message; return(false); } }
public static int ExecuteSql(string SQLString) { int num2; using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand command = new OracleCommand(SQLString, connection); try { connection.Open(); num2 = command.ExecuteNonQuery(); } catch (OracleException exception) { connection.Close(); throw new Exception(exception.Message); } finally { if (command != null) { command.Dispose(); } } } return num2; }