} //end of this method // Madan Saini 03/29/2004 -- Clear Text Passwords for PMT // This method validate an Internet user's membership of a web application public static bool IsDBUserValid(string loginID, string password, string appName, System.Data.OracleClient.OracleConnection conVITAP) { bool valid = false; //string encodedPassword = SharedPassword.Encode(password); loginID = loginID.ToUpper(); System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand(); if (conVITAP.State != ConnectionState.Open) { conVITAP.Open(); } cmdVITAP.Connection = conVITAP; cmdVITAP.CommandText = Queries.getValidUser(loginID, password, appName); System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader(); if (rdrReader.Read()) { valid = true; } else { valid = false; } cmdVITAP.Dispose(); return(valid); } //end of this method
public int NuevaArea(CE_Area objce_area) { //el metodo m try { int filasafectadas = 0; OracleConnection cnx = Conexion.ObtenerConexionOracle(); OracleCommand cmd = new OracleCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cnx; cmd.CommandText = "sp_Nueva_Area"; //asignar paramentros al procedimiento almacenado cmd.Parameters.AddWithValue("nombreareaX", objce_area.nombrearea); //abrir la conexion cnx.Open(); //ejecutar el procedimiento almacenado filasafectadas = cmd.ExecuteNonQuery(); //Cerrar conexion cnx.Close(); return filasafectadas; } catch (Exception ex) { throw ex; } }
protected void GV_RowUpdating(object sender, GridViewUpdateEventArgs e) { using (OracleConnection conn = new OracleConnection(DBHelper.ConnectionString)) { string reason_id = GV.DataKeys[e.RowIndex].Values[0].ToString(); string reason_desc = ((TextBox)GV.Rows[e.RowIndex].FindControl("TxtDesc")).Text; string active = ((CheckBox)(GV.Rows[e.RowIndex].FindControl("ChkActive"))).Checked == true ? "1" : "0"; string sqlupdate = "update jp_lack_reason set reason_desc = '" + reason_desc + "',is_valid='" + active + "' where reason_id = '" + reason_id + "' "; OracleCommand updatecomm = new OracleCommand(sqlupdate, conn); try { conn.Open(); updatecomm.ExecuteNonQuery(); GV.EditIndex = -1; GVDataBind(); } catch (Exception ex) { conn.Close(); Response.Write("<script language=javascript>alert('" + ex.Message + "')</script>"); } finally { updatecomm.Dispose(); conn.Dispose(); conn.Close(); } } }
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); } }
/// <summary></summary> /// 对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于其他所有类型的语句,返回值为 -1 /// /// UPDATE、INSERT 和 DELETE 语句 public int ExecuteNonQuery(string sql) { using (oracleConnection = this.GetOracleConnection()) { if (oracleConnection == null) { return(-1); } int rv = -1; OracleTransaction oracleTransaction = null; try { if (oracleConnection.State == System.Data.ConnectionState.Closed) { oracleConnection.Open(); } oracleCommand = new OracleCommand(sql, oracleConnection); oracleTransaction = oracleConnection.BeginTransaction(); oracleCommand.Transaction = oracleTransaction; rv = oracleCommand.ExecuteNonQuery(); oracleTransaction.Commit(); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif oracleTransaction.Rollback(); rv = -1; } return(rv); } }
public ArrayList getDataORA2(String str) { try { using (System.Data.OracleClient.OracleConnection con_ora = new System.Data.OracleClient.OracleConnection(strConnectionORA)) { con_ora.Open(); using (System.Data.OracleClient.OracleCommand command = new System.Data.OracleClient.OracleCommand(str, con_ora)) { using (OracleDataReader reader = command.ExecuteReader()) { ArrayList list = new ArrayList(); while (reader.Read()) { object[] values = new object[reader.FieldCount]; reader.GetValues(values); list.Add(values); } return(list); } } } } catch (Exception e) { // strError = e.Message.ToString(); // MessageBox.Show(strError); return(null); } }
/// <summary></summary> /// 执行查询,并将查询返回的结果集中第一行的第一列作为 .NET Framework 数据类型返回。忽略额外的列或行。 /// /// SELECT 语句 /// <returns></returns>.NET Framework 数据类型形式的结果集第一行的第一列;如果结果集为空或结果为 REF CURSOR,则为空引用 public object ExecuteScalar(string sql) { using (oracleConnection = this.GetOracleConnection()) { if (oracleConnection == null) { return(null); } try { if (oracleConnection.State == System.Data.ConnectionState.Closed) { oracleConnection.Open(); } oracleCommand = new OracleCommand(sql, oracleConnection); return(oracleCommand.ExecuteScalar()); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return(null); } } }
/// <summary></summary> /// 执行Sql数组语句查询,并将查询返回的结果作为一个数据读取器返回 /// /// /// <returns></returns>OracleDataReader public OracleDataReader RetriveDataReader(string sql) { if (sql == null || sql == string.Empty) { #if DEBUG System.Diagnostics.Debug.WriteLine("sql 为空"); #endif return(null); } using (oracleConnection = this.GetOracleConnection()) { if (oracleConnection == null) { return(null); } using (oracleCommand = new OracleCommand(sql, oracleConnection)) { try { OracleDataReader oracleDataReader = oracleCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); return(oracleDataReader); } catch (Exception ex) { #if DEBUG System.Diagnostics.Debug.WriteLine(ex.ToString()); #endif return(null); } } } }
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(); } }
public static OracleParameter AddOutParamToSqlCommand(OracleCommand comm, string pName, OracleType pType) { OracleParameter param = new OracleParameter(pName, pType); param.Direction = ParameterDirection.Output; comm.Parameters.Add(param); return param; }
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(); } } }
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(); } }
//2/09/2005 - Madan - web_users table in VITAP.world //Input parameter UserName = First Name Initial, LastName public static string IsValidWebUser(string UserName, string Password, System.Data.OracleClient.OracleConnection App_Connection) { string Message = ""; try { System.Data.OracleClient.OracleCommand CmdLotusMail = new System.Data.OracleClient.OracleCommand(); CmdLotusMail.CommandText = "SELECT password from web_users WHERE (externalservice ='T' or app_dfas_fl ='T') and upper(login_id) = '" + Utilities.SqlEncode(UserName.ToUpper().Trim()) + "'"; CmdLotusMail.Connection = App_Connection; //ConnLotusMail; if (App_Connection.State != System.Data.ConnectionState.Open) { CmdLotusMail.Connection.Open(); } System.Data.OracleClient.OracleDataReader myReader = CmdLotusMail.ExecuteReader(); if (myReader.Read()) { Message = myReader["password"].ToString().Trim(); } myReader.Close(); CmdLotusMail.Connection.Close(); } catch (System.Exception ex) { // if exception occurs, make an entry in the server event log EventLog.AddWebErrors("GSA.R7BD.Utility", "Security", "IsLutusNotesUser", ex.Message); } return(Message.Trim()); } //end of this method
/// <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(); } }
public void NuevoRegistroES(CE_Registro objce_registro) { //el metodo me permite almacenar los datos del nuevo colaborador. try { OracleConnection cnx = Conexion.ObtenerConexionOracle(); OracleCommand cmd = new OracleCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cnx; cmd.CommandText = "sp_nuevo_registro_es"; //asignar paramentros al procedimiento almacenado cmd.Parameters.AddWithValue("idregistro",OracleType.VarChar).Value = objce_registro.idregistro; cmd.Parameters.AddWithValue("dni",OracleType.VarChar).Value = objce_registro.dni; cmd.Parameters.AddWithValue("idestado_es", OracleType.VarChar).Value =objce_registro.idestado_es; //abrir la conexion cnx.Open(); //ejecutar el procedimiento almacenado cmd.ExecuteNonQuery(); //Cerrar conexion cnx.Close(); } catch (Exception ex) { throw ex; } }
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 { } }
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(); }
}//end of method public static string AuthenticateLotusNotesUser(string firstName, string lastName, System.Data.OracleClient.OracleConnection App_Connection) { string Message = ""; string UserName1 = firstName.Trim().ToUpper() + " " + lastName.Trim().ToUpper(); string UserName2 = lastName.Trim().ToUpper() + ", " + firstName.Trim().ToUpper(); try { System.Data.OracleClient.OracleCommand CmdLotusMail = new System.Data.OracleClient.OracleCommand(); CmdLotusMail.CommandText = "SELECT Notesname from maildir WHERE Notesname = '" + Utilities.SqlEncode(UserName1.ToUpper().Trim()) + "' or upper(Name) ='" + Utilities.SqlEncode(UserName2) + "'"; CmdLotusMail.Connection = App_Connection;// ConnLotusMail; if (App_Connection.State != System.Data.ConnectionState.Open) { CmdLotusMail.Connection.Open(); } System.Data.OracleClient.OracleDataReader NotesReader = CmdLotusMail.ExecuteReader(); if (NotesReader.Read()) { Message = NotesReader["Notesname"].ToString().Trim(); } NotesReader.Close(); CmdLotusMail.Connection.Close(); } catch (System.Exception ex) { // if exception occurs, make an entry in the server event log EventLog.AddWebErrors("GSA.R7BD.Utility", "Security", "IsLutusNotesUser", ex.Message); } return(Message); }//end of method
public bool VerificarExisteArea(CE_Area objce_area) { //la funcion me permite recuperar los datos try { OracleConnection cnx = Conexion.ObtenerConexionOracle(); OracleCommand cmd = new OracleCommand(String.Format("select * from area where nombrearea ='{0}'", objce_area.nombrearea), cnx); cnx.Open(); OracleDataReader reader; reader = cmd.ExecuteReader(); Boolean existearea = reader.HasRows; cnx.Close(); return existearea; } catch (Exception ex) { throw ex; } }
public OracleDataAdapter (OracleCommand selectCommand) { SelectCommand = selectCommand; #if NET_2_0 UpdateBatchSize = 1; #endif }
public List<Afdeling> GetAfdelingen() { List<Afdeling> result = new List<Afdeling>(); try { connection.Open(); string query = "SELECT * FROM afdeling"; OracleCommand command = new OracleCommand(query, connection); OracleDataReader reader = command.ExecuteReader(); Afdeling a; while (reader.Read()) { a = new Afdeling( (string)reader["afd_afk"], (string)reader["afd_naam"], (string)reader["afd_omschrijving"] ); result.Add(a); } return result; } catch (Exception e) { System.Windows.Forms.MessageBox.Show(e.ToString()); return null; } finally { connection.Close(); } }
public static LoanList GetList(int applicantId) { LoanList loans = new LoanList(); using(OracleConnection oraDbConn = new OracleConnection( ConnStringFactory.getConnString( ConnStringFactory.ConnStringType.Oracle))){ oraDbConn.Open(); using(OracleCommand getLoansByAppIdCommand = new OracleCommand()){ getLoansByAppIdCommand.CommandType = CommandType.StoredProcedure; getLoansByAppIdCommand.CommandText = "LoansPKG.getLoansByAppId"; getLoansByAppIdCommand.Connection = oraDbConn; getLoansByAppIdCommand.Parameters.AddWithValue("AppId", applicantId); OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor); outputCursor.Direction = ParameterDirection.Output; getLoansByAppIdCommand.Parameters.Add(outputCursor); using (OracleDataReader loanListReader = getLoansByAppIdCommand.ExecuteReader()) { while(loanListReader.Read()){ loans.Add(FillDataRecord(loanListReader)); } } } } return loans; }
/// <summary> /// 通过sql语句获得字段值 /// </summary> /// <param name="sqlstr">sql语句</param> /// <returns>返回sql语句查询结果的第一记录的第一个字段内容 /// 如果sql语句执行异常或者没有记录,返回空字符串"" /// </returns> public string GetFieldValue(string sqlstr) { string result = string.Empty; DbCommand dbCommand = new DbCommand(); dbCommand.Connection = mConn; dbCommand.CommandText = sqlstr; try { if (mConn.State == ConnectionState.Closed) { mConn.Open(); } object obj = dbCommand.ExecuteScalar(); if (obj != null) { result = obj.ToString(); } } catch (Exception e) { WriteLogInfo("error on " + this.GetType().Name + " GetFieldValue:" + sqlstr + "\r\n" + e.Message); return(result); } return(result); }
public void NuevoAbastecimiento(CE_Abastecimiento objce_abastecimiento) { //el metodo me permite try { OracleConnection cnx = Conexion.ObtenerConexionOracle(); OracleCommand cmd = new OracleCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cnx; cmd.CommandText = "sp_Nuevo_Abastecimiento"; //asignar paramentros al procedimiento almacenado cmd.Parameters.AddWithValue("codigo_abastecimiento", objce_abastecimiento.codigo_abastecimiento); cmd.Parameters.AddWithValue("dni", objce_abastecimiento.dni); cmd.Parameters.AddWithValue("idtanque", objce_abastecimiento.idtanque); cmd.Parameters.AddWithValue("volumen_autorizado", objce_abastecimiento.volumen_autorizado); cmd.Parameters.AddWithValue("idplacavehiculo", objce_abastecimiento.idplacavehiculo); cmd.Parameters.AddWithValue("estado", objce_abastecimiento.estado); //abrir la conexion cnx.Open(); //ejecutar el procedimiento almacenado cmd.ExecuteNonQuery(); //Cerrar conexion cnx.Close(); } catch (Exception ex) { throw ex; } }
private void bt_edit_Click(object sender, EventArgs e) { query = txb_sql1.Text; string comando = "Create or replace view \"" + cmb_view1.GetItemText(cmb_view1.SelectedItem) + "\" as " + txb_sql1.Text; OracleConnection con = new System.Data.OracleClient.OracleConnection("Data Source=XE; User Id=" + Form1.user + "; Password="******";"); OracleCommand cmd = new OracleCommand(comando, con); try { con.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Se ha Editado el View " + "\"" + txt_nomview.Text + "\"", "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Information); Ver_Views(); show_viewtbx.Text = txt_nomview.Text; tabControl1.SelectedTab = this.tabPage4; } catch (Exception ex) { MessageBox.Show("Error en la Sentencia o tiene el Siguiente Error: \n" + ex.Message.ToString(), "Proyecto TDB1", MessageBoxButtons.OK, MessageBoxIcon.Warning); // Form1.conn.Close(); System.Drawing.Color clr; clr = System.Drawing.Color.Red; label12.ForeColor = clr; } }
public void NuevoAcceso(CE_Acceso objce_acceso) { //el metodo me permite almacenar los datos del nuevo acceso. try { OracleConnection cnx = Conexion.ObtenerConexionOracle(); OracleCommand cmd = new OracleCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = cnx; cmd.CommandText = "sp_NuevoAcceso"; //asignar paramentros al procedimiento almacenado cmd.Parameters.AddWithValue("dni", objce_acceso.dni); cmd.Parameters.AddWithValue("fechaacceso", objce_acceso.fechaacceso ); cmd.Parameters.AddWithValue("fechadesde", objce_acceso.fechadesde ); cmd.Parameters.AddWithValue("fechahasta", objce_acceso.fechahasta ); cmd.Parameters.AddWithValue("observaciones", objce_acceso.observaciones ); cmd.Parameters.AddWithValue("estado", objce_acceso.estado ); //abrir la conexion cnx.Open(); //ejecutar el procedimiento almacenado cmd.ExecuteNonQuery(); //Cerrar conexion cnx.Close(); } catch (Exception ex) { throw ex; } }
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); } }
/// <summary> /// 执行SQL语句,返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { using (OracleConnection connection = new OracleConnection(connectionString)) { using (OracleCommand cmd = new OracleCommand(SQLString, connection)) { try { connection.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.OracleClient.OracleException E) { connection.Close(); throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); } } } }
protected void btnAsignar_Click(object sender, EventArgs e) { try { // Get the currently selected row using the SelectedRow property. GridViewRow rowTarea = tablaPendientes.SelectedRow; string textTarea = rowTarea.Cells[1].Text; ora2.Open(); System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("update_tarea_reasignar"); comando.Connection = ora2; comando.CommandType = System.Data.CommandType.StoredProcedure; comando.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreo.Text; comando.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea); comando.ExecuteNonQuery(); ora2.Close(); if (tablaPendientes.Rows.Count == 0) { lblMensaje.Visible = true; // Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('No hay tareas pendientes');</script>"); } else { lblMensaje.Visible = false; } CargarQuery(); Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Asignada');</script>"); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>"); } }
public static int AffectData(string TSQL, IDbConnection myConn, IDbTransaction myTrans, List<IDbDataParameter> myParams) { bool mustClose = false; if (myConn == null) { mustClose = true; myConn = clsConn.getConnOracle(); } OracleCommand myCMD = new OracleCommand(); // myCMD.Connection = myConn as OracleConnection; if (myTrans != null) myCMD.Transaction = myTrans as OracleTransaction; // myCMD.CommandType = CommandType.Text; myCMD.CommandText = TSQL; myCMD.CommandTimeout = 180000;//3 phut // if (myParams != null) AttachParameters(myCMD, myParams); int CMDResult = myCMD.ExecuteNonQuery(); // if (mustClose) myConn.Close(); return CMDResult; }
public bool VerificarAutorizaciondeAcceso(CE_Acceso objce_acceso) { //la funcion me permite recuperar los datos del colaborador en el objeto CE_Colaborador try { CE_Colaborador objce_colaboradortemp = new CE_Colaborador(); OracleConnection cnx = Conexion.ObtenerConexionOracle(); OracleCommand cmd = new OracleCommand(String.Format("SELECT * FROM ACCESO WHERE DNI='{0}' AND (TO_DATE(SYSDATE) BETWEEN TO_DATE(FECHADESDE) AND TO_DATE(FECHAHASTA))", objce_acceso.dni), cnx); cnx.Open(); OracleDataReader reader; reader = cmd.ExecuteReader(); //verifico si hay filas devueltas Boolean hayfilas = reader.HasRows; //Cerrar conexion cnx.Close(); return hayfilas; } catch (Exception ex) { throw ex; } }
/// <summary> /// execute a query£¬return DataSet /// </summary> /// <param name="SQLString"></param> /// <returns>DataSet</returns> public static DataSet Query(string connectionString, CommandType cmdType, string SQLString, params OracleParameter[] cmdParms) { using (OracleConnection connection = new OracleConnection(connectionString)) { OracleCommand cmd = new OracleCommand(); PrepareCommand(cmd, connection, null, cmdType, SQLString, cmdParms); using (OracleDataAdapter da = new OracleDataAdapter(cmd)) { DataSet ds = new DataSet(); //try //{ da.Fill(ds, "ds"); cmd.Parameters.Clear(); //} //catch (System.Data.OracleClient.OracleException ex) //{ // throw new Exception(ex.Message); //} //finally //{ // if (connection.State != ConnectionState.Closed) // { // connection.Close(); // } //} return ds; } } }
public List<TicketCategorie> GetCategorieen() { List<TicketCategorie> result = new List<TicketCategorie>(); try { connection.Open(); string query = "SELECT naam, omschrijving FROM categorie"; OracleCommand command = new OracleCommand(query, connection); OracleDataReader reader = command.ExecuteReader(); TicketCategorie c; while (reader.Read()) { c = new TicketCategorie( (string)reader["naam"], (string)reader["omschrijving"] ); result.Add(c); } return result; } catch (Exception e) { System.Windows.Forms.MessageBox.Show("Niet mogelijk om categorieen binnen te halen: " + e.Message); return null; } finally { connection.Close(); } }
public DataAccess() { m_Connection = new OracleConnection(); m_Command = new OracleCommand(); //read connection string from xml file //string str1 = ""; //XmlTextReader xmlR = new XmlTextReader(MapPath("mycompany.xml") "connString.xml"); //str1 = xmlR.GetAttribute("connstring"); //str1 = System.Configuration.ConfigurationManager.AppSettings.Get("connString"); // XmlDocument doc = new XmlDocument(); //doc.Load("~/App_Data/sample.xml"); //XmlNode root = doc.DocumentElement; //str1 = root.SelectSingleNode("author").ChildNodes[0].Value; //string str1 = BDGovPayroll.Properties.Settings.Default.connString; string str1 = @"Data Source=(DESCRIPTION= (ADDRESS_LIST= (ADDRESS= (PROTOCOL=TCP)(HOST=soft-server)(PORT=1521))) (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME= orcl))); User Id=librarry;Password=softech;"; //////// string str1 = @" Data Source = (DESCRIPTION = //////// (ADDRESS_LIST = //////// (ADDRESS = //////// (PROTOCOL = TCP)(HOST = soft1)(PORT = 1521)))(CONNECT_DATA = //////// (SERVER = DEDICATED)(SERVICE_NAME = XE))); //////// User Id = payroll_db; Password = payroll;"; //m_Connection.ConnectionString = @"Data Source=soft1;User Id=payroll_db;Password=payroll;"; m_Connection.ConnectionString = str1; m_Command.Connection = m_Connection; }
/// <summary> /// 执行SQL文 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int ExecSQL(string sql, ref string ErrorInfo) { int ret=1; OracleCommand cmd = new OracleCommand(sql, conn); try { ret = cmd.ExecuteNonQuery(); } catch (OracleException ex) // catches only Oracle errors { ret = -1; switch (ex.Code) { case 1: ErrorInfo = "Error attempting to insert duplicate data."; return ret; case 12545: ErrorInfo = "The database is unavailable."; return ret; default: ErrorInfo = "Database error:" + ex.Message.ToString(); return ret; } } catch (Exception e) { ret = -1; ErrorInfo = e.Message; } return ret; }
/// <summary> /// Simplify the creation of a Oracle command object by allowing /// a stored procedure and optional parameters to be provided /// </summary> /// <remarks> /// e.g.: /// OracleCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName"); /// </remarks> /// <param name="connection">A valid OracleConnection object</param> /// <param name="spName">The name of the stored procedure</param> /// <param name="sourceColumns">An array of string to be assigned as the source columns of the stored procedure parameters</param> /// <returns>A valid OracleCommand object</returns> public static OracleCommand CreateCommand(OracleConnection connection, string spName, params string[] sourceColumns) { if( connection == null ) throw new ArgumentNullException( "connection" ); if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" ); // Create a OracleCommand OracleCommand cmd = new OracleCommand( spName, connection ); cmd.CommandType = CommandType.StoredProcedure; // If we receive parameter values, we need to figure out where they go if ((sourceColumns != null) && (sourceColumns.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) OracleParameter[] commandParameters = OracleHelperParameterCache.GetSpParameterSet(connection, spName); // Assign the provided source columns to these parameters based on parameter order for (int index=0; index < sourceColumns.Length; index++) commandParameters[index].SourceColumn = sourceColumns[index]; // Attach the discovered parameters to the OracleCommand object AttachParameters (cmd, commandParameters); } return cmd; }
public frmSheetMetaData(IWorkspace workSpace,OracleCommand oraCmd) { m_textBoxActive = new List<TextBox>(); m_labelActive = new List<Label>(); m_customField = new List<string>(); InitializeComponent(); m_textBox = new TextBox[9]; m_textBox[0] = textBox24; m_textBox[1] = textBox18; m_textBox[2] = textBox20; m_textBox[3] = textBox16; m_textBox[4] = textBox15; m_textBox[5] = textBox17; m_textBox[6] = textBox21; m_textBox[7] = textBox19; m_textBox[8] = textBox14; m_label = new Label[9]; m_label[0] = label26; m_label[1] = label20; m_label[2] = label25; m_label[3] = label18; m_label[4] = label17; m_label[5] = label19; m_label[6] = label24; m_label[7] = label21; m_label[8] = label16; this.m_workSpace = workSpace; m_gdata = new GDBData(this.m_workSpace); m_oraCmd = oraCmd; InitCustom(); }
public DataTable ListarAreas() { DataTable dt = new DataTable(); try { OracleConnection cnx = Conexion.ObtenerConexionOracle(); OracleCommand cmd = new OracleCommand(String.Format("select * from area"), cnx); cnx.Open(); OracleDataReader reader; reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // lleno el DataTable con el datareader dt.Load(reader); } catch (Exception) { return null; } return dt; }
//Filling the booking details into the return cars datagridview public void bookingsDetails(DataGridView grd_Bookings) { var tomorrow = DateTime.Today.AddDays(1).ToString("dd-MMM-yyyy"); var dueDate = DateTime.Today.AddDays(-5).ToString("dd-MMM-yyyy"); string query_String = string.Format("SELECT DISTINCT B.Customer_Id ,Forename, Surname ,B.Car_Reg ,Cost ,Customer_Type,Return_Date FROM Cars CA, " + "Customers C JOIN Bookings B ON C.Customer_Id = B.Customer_Id " + "WHERE Car_Status = 'U' AND CA.Car_Reg = B.Car_Reg AND Return_Date BETWEEN '{0}' AND '{1}'", dueDate, tomorrow ); // MessageBox.Show(query_String); try { connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = query_String; data_Adapter = new OracleDataAdapter(cmd); data_Set = new DataSet(); data_Adapter.Fill(data_Set, "Bookings"); grd_Bookings.DataSource = data_Set.Tables["Bookings"]; data_reader = cmd.ExecuteReader();//the reader is used to read in the required record data_reader.Read(); connection.Close(); } catch (Exception ex) {MessageBox.Show("" + ex);} }
/// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. /// </summary> /// <param name="command">the OracleCommand to be prepared</param> /// <param name="connection">a valid OracleConnection, on which to execute this command</param> /// <param name="transaction">a valid OracleTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or PL/SQL command</param> /// <param name="commandParameters">an array of OracleParameters to be associated with the command or 'null' if no parameters are required</param> private static void PrepareCommand(OracleCommand command, OracleConnection connection, OracleTransaction transaction, CommandType commandType, string commandText, OracleParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; //set the command text (stored procedure name or Oracle statement) command.CommandText = commandText; command.CommandTimeout = 200000; //if we were provided a transaction, assign it. if (transaction != null) { command.Transaction = transaction; } //set the command type command.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; }
public int GetNextIdWithTransNewAppWithArea(string area, System.Data.OracleClient.OracleCommand cmd) { cmd.CommandText = ""; cmd.CommandText = @"SELECT nvl(to_number(max(SUBSTR(APPLICATION_NO, 4, 10))),0)+1 FROM CGD_APPLICANT_MASTER where to_number(SUBSTR(APPLICATION_NO, 2, 2)) ='" + area + "'"; return(Convert.ToInt32(cmd.ExecuteScalar())); }
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>"); } }
protected void btnCargarTareas_2_Click(object sender, EventArgs e) { int id = ddlCambiarEstado2.SelectedIndex + 1; if (id == 4) { try { // Get the currently selected row using the SelectedRow property. GridViewRow rowTarea = tablaCarga2.SelectedRow; // In this example, the first column (index 0) contains string textTarea = rowTarea.Cells[1].Text; string idTarea = id.ToString(); ora2.Open(); System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("update_tarea_reasignar3"); comando2.Connection = ora2; comando2.CommandType = System.Data.CommandType.StoredProcedure; comando2.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea); comando2.ExecuteNonQuery(); ora2.Close(); CargarQuery(); Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea devuelta, revisar el apartado de Agregar Tarea Subordinada');</script>"); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error');</script>"); } } else { try { // Get the currently selected row using the SelectedRow property. GridViewRow rowTarea = tablaCarga2.SelectedRow; // In this example, the first column (index 0) contains string textTarea = rowTarea.Cells[1].Text; string idTarea = id.ToString(); ora2.Open(); System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("update_tarea_sub_estado"); comando2.Connection = ora2; comando2.CommandType = System.Data.CommandType.StoredProcedure; comando2.Parameters.Add("p_estado", OracleType.Int32).Value = id; comando2.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea); comando2.ExecuteNonQuery(); ora2.Close(); Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Actualizada');</script>"); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error');</script>"); } } CargarQuery(); }
//This rountine writes log entry to web_log table in vitap database public static void WriteWebLog(string appName, string allProcess, string clientIP, string userID, System.Data.OracleClient.OracleConnection conVITAP) { System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand(); if (conVITAP.State != ConnectionState.Open) { conVITAP.Open(); } cmdVITAP.Connection = conVITAP; cmdVITAP.CommandText = Queries.newWebLog(appName, allProcess, clientIP, userID); cmdVITAP.ExecuteNonQuery(); }
/// <summary> /// 执行SQL语句 /// </summary> /// <param name="tablename"></param> /// <param name="optype"></param> /// <param name="sqlstr"></param> /// <param name="htParam"></param> /// <param name="strParamList"></param> /// <param name="tr"></param> /// <returns></returns> private bool Execute(string tablename, string optype, string sqlstr, Hashtable htParam, string strParamList, Transaction tr) { #region 创建命令 DbCommand dbCommand = new DbCommand(); dbCommand.Connection = mConn; dbCommand.CommandText = sqlstr; dbCommand.Transaction = tr; #endregion #region 设置参数(对于OLEDB方式而言,参数的顺序必须与SQL语句中一致) foreach (string strkey in strParamList.Split(new char[] { ',' })) { #if DT_OLEDB if (htParam[strkey].GetType() != typeof(DateTime)) { dbCommand.Parameters.Add(new DbParameter(strkey, htParam[strkey])); } else { //OleDB连Access时,这个日期类型必须特别指定;连Oracle不需要;SQL没测过 dbCommand.Parameters.Add(new DbParameter(strkey, System.Data.OleDb.OleDbType.Date)).Value = htParam[strkey]; } #else dbCommand.Parameters.Add(new DbParameter(strkey, htParam[strkey])); #endif } #endregion #region 提交命令 try { if (mConn.State == ConnectionState.Closed) { mConn.Open(); } dbCommand.ExecuteNonQuery(); //DisConnection(); return(true); } catch (Exception e) { this.WriteLogInfo("error on " + this.GetType().Name + " " + tablename + ".Execute." + optype + ":\r\nsql:" + sqlstr + "\r\n" + e.Message); return(false); } #endregion }
protected void ingresarTarea_Click(object sender, EventArgs e) { try { lblidddl.Text = ""; //Para el Usuario ora.Open(); //Llamar al Stored Procedure Oracle.DataAccess.Client.OracleCommand comando1 = new Oracle.DataAccess.Client.OracleCommand("seleccionar_usuarios", ora); comando1.CommandType = System.Data.CommandType.StoredProcedure; //Asignar variable de cursor comando1.Parameters.Add("p_correo", OracleDbType.Varchar2).Value = txtCorreo.Text; comando1.Parameters.Add("o_id", OracleDbType.Int32, null, ParameterDirection.Output); comando1.ExecuteNonQuery(); Oracle.DataAccess.Client.OracleDataReader dr = comando1.ExecuteReader(); int id = Int32.Parse(comando1.Parameters["o_id"].Value.ToString()); lblidddl.Text = id.ToString(); ora.Close(); //Para la Tarea string tipo = ddlTipoTarea.SelectedItem.ToString(); lblidddl.Text = tipo; int flujo = ddlFlujo.SelectedIndex + 1; //FECHA var date = fecha.SelectedDate; ora2.Open(); System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_TAREA", ora2); comando2.CommandType = System.Data.CommandType.StoredProcedure; comando2.Parameters.Add("P_NOMBRE", OracleType.VarChar).Value = txtNombre.Text; comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtDescripcion.Text; comando2.Parameters.Add("P_IDUSUARIO", OracleType.Int32).Value = id; comando2.Parameters.Add("P_ESTADO", OracleType.Int32).Value = 4; comando2.Parameters.Add("P_TIPOTAREA", OracleType.VarChar).Value = tipo; comando2.Parameters.Add("P_FLUJO", OracleType.VarChar).Value = flujo; comando2.Parameters.Add("P_FECHA", OracleType.DateTime).Value = date.ToString(); comando2.ExecuteNonQuery(); // ora2.Close(); Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Tarea Insertada');</script>"); txtNombre.Text = ""; txtDescripcion.Text = ""; // txtCorreo.Text = ""; } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error' " + ex.ToString() + "');</script>"); } }
} //end of this method public static bool IsPassowrdExpired(string loginID, System.Data.OracleClient.OracleConnection conVITAP) { bool valid = false; System.Data.OracleClient.OracleCommand cmdVITAP = new System.Data.OracleClient.OracleCommand(); if (conVITAP.State != ConnectionState.Open) { conVITAP.Open(); } cmdVITAP.Connection = conVITAP; cmdVITAP.CommandText = ""; System.Data.OracleClient.OracleDataReader rdrReader = cmdVITAP.ExecuteReader(); return(valid); }
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); }
public int InputLong(string strSql, string data) { System.Data.OracleClient.OracleCommand command = null; command = new OracleCommand(); command.Connection = this.dbConnection as OracleConnection; command.Transaction = dbTrans as OracleTransaction; command.CommandType = System.Data.CommandType.Text; command.Parameters.Clear(); command.CommandText = strSql + ""; string strParam = ""; int i = strSql.IndexOf(":", 0); if (i <= 0) { this.Err = "未指定参数!" + strSql; this.WriteErr(); return(-1); } strParam = strSql.Substring(i + 1, 1); OracleParameter param = command.Parameters.Add(strParam, OracleType.LongVarChar); param.Direction = System.Data.ParameterDirection.Input; // Assign Byte Array to Oracle Parameter param.Value = data; try { command.ExecuteNonQuery(); } catch (OracleException ex) { this.Err = "执行产生错误!" + ex.Message; this.ErrCode = strSql; this.DBErrCode = ex.Code; this.WriteErr(); return(-1); } catch (Exception ex) { this.Err = ex.Message; this.WriteErr(); return(-1); } return(0); }
private DbParameter[] DiscoverSpParameterSet(string strProcedureName, bool includeReturnValueParameter) { DbCommand dbCommand = new DbCommand(); dbCommand.Connection = mConn; dbCommand.CommandText = strProcedureName; dbCommand.CommandType = CommandType.StoredProcedure; try { if (mConn.State == ConnectionState.Closed) { mConn.Open(); } // 查找存储过程的参数 DbCommandBuilder.DeriveParameters(dbCommand); // 如果包含返回Result value参数(自动生成的,不需要传进去),如果有,则删除 // Oracle中,function产生此参数,procedure则不产生 // SQL SERVER中,procedure 产生此参数,function 未测试,应该也会有 if (includeReturnValueParameter) { dbCommand.Parameters.RemoveAt(0); } // 返回找到的参数的副本 DbParameter[] discoveredParameters = new DbParameter[dbCommand.Parameters.Count]; dbCommand.Parameters.CopyTo(discoveredParameters, 0); // 参数一刀两断,没有关系了. dbCommand.Parameters.Clear(); // 初始化各参数为DBNull foreach (DbParameter discoveredParameter in discoveredParameters) { discoveredParameter.Value = DBNull.Value; } return(discoveredParameters); } catch (Exception e) { WriteLogInfo("error on " + this.GetType().Name + " GetStoredProcedureParamters:" + strProcedureName + "\r\n" + e.Message); return(null); } }
private static void prepareCommand(ref System.Data.OracleClient.OracleCommand command, System.Data.OracleClient.OracleConnection connection, System.Data.CommandType commandType, string commandText) { try { if (connection.State != System.Data.ConnectionState.Open) { connection.Open(); } command.Connection = connection; command.CommandText = commandText; command.CommandType = commandType; } catch (Exception ex) { // Handle the exception in UI level only throw ex; } }
protected void btnGuardarFlujo_Click(object sender, EventArgs e) { try { ora2.Open(); System.Data.OracleClient.OracleCommand comando2 = new System.Data.OracleClient.OracleCommand("INSERT_flujo", ora2); comando2.CommandType = System.Data.CommandType.StoredProcedure; comando2.Parameters.Add("P_DESCRIPCION", OracleType.VarChar).Value = txtNombreFlujo.Text; comando2.Parameters.Add("P_ESTADO", OracleType.VarChar).Value = "Pendiente de Aprobación"; comando2.ExecuteNonQuery(); ora2.Close(); Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Flujo Agregado');</script>"); } catch (Exception ex) { lblError.Text = ex.ToString(); } 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(); adaptador.SelectCommand = comando; DataTable dt = new DataTable(); adaptador.Fill(dt); tblFlujo.DataSource = dt; tblFlujo.DataBind(); ora2.Close(); } catch (Exception ex) { lblError.Text = ex.ToString(); } }
/// <summary> /// 执行存储过程,什么值也不返回 /// </summary> /// <param name="storedProcName"></param> /// <param name="parameters"></param> public static void RunProcedure(string storedProcName, OracleParameter[] parameters) { using (OracleConnection connection = new OracleConnection(connectionString)) { System.Data.OracleClient.OracleCommand cmd = BuildQueryCommand(connection, storedProcName, parameters); //new OracleCommand(storedProcName, connection); //cmd.CommandType = CommandType.StoredProcedure; //foreach (OracleParameter parameter in parameters) //{ // cmd.Parameters.Add(parameter); //} //cmd.Parameters.Add(new OracleParameter("p_userid", OracleType.Int32, 9)).Value = p_userid; //cmd.Parameters.Add(new OracleParameter("p_yyyy", OracleType.Int32, 7)).Value = p_yyyy; //cmd.Parameters.Add(new OracleParameter("p_mm", OracleType.Int32, 9)).Value = p_mm; try { if (connection.State != ConnectionState.Open) { connection.Open(); } cmd.ExecuteNonQuery(); } catch (Exception ex) { ex.Message.ToString(); throw new Exception(ex.Message); // Console.WriteLine(ex.Message.ToString()); } finally { connection.Close(); } } }
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(); } }
protected void reasignar_Click(object sender, EventArgs e) { try { // Get the currently selected row using the SelectedRow property. GridViewRow rowTarea = tablasTareasDevueltas.SelectedRow; string textTarea = rowTarea.Cells[1].Text; ora2.Open(); System.Data.OracleClient.OracleCommand comando = new System.Data.OracleClient.OracleCommand("update_tarea_reasignar2"); comando.Connection = ora2; comando.CommandType = System.Data.CommandType.StoredProcedure; comando.Parameters.Add("p_correo", OracleType.VarChar).Value = txtCorreoReasignar.Text; comando.Parameters.Add("P_IDTAREA", OracleType.Int32).Value = Int32.Parse(textTarea); comando.ExecuteNonQuery(); ora2.Close(); } catch (Exception ex) { Page.ClientScript.RegisterStartupScript(this.GetType(), "Scripts", "<script>alert('Ha ocurrido un error actualizando');</script>"); } Response.Redirect(HttpContext.Current.Request.Url.ToString(), true); }
/// <summary> /// 执行SQL语句 /// </summary> /// <param name="sqlstr">sql语句</param> /// <param name="tr">事务,null表示不启用事务</param> /// <returns>是否执行成功</returns> public bool ExecSQL(string sqlstr, Transaction tr) { DbCommand dbCommand = new DbCommand(); dbCommand.Connection = mConn; dbCommand.CommandText = sqlstr; dbCommand.Transaction = tr; try { if (mConn.State == ConnectionState.Closed) { mConn.Open(); } dbCommand.ExecuteNonQuery(); return(true); } catch (Exception e) { WriteLogInfo("errorn on " + this.GetType().Name + " ExecSQL:" + sqlstr + "\r\n" + e.Message); return(false); } }
public static System.Data.DataTable ExecuteDataTable(ref System.Data.OracleClient.OracleCommand command, System.Data.OracleClient.OracleConnection connection, System.Data.CommandType commandType, string commandText) { System.Data.OracleClient.OracleDataAdapter oracleDataAdapter = new System.Data.OracleClient.OracleDataAdapter(); System.Data.DataSet dataSet = new System.Data.DataSet(); System.Data.DataTable dataTable = new System.Data.DataTable(); try { prepareCommand(ref command, connection, commandType, commandText); oracleDataAdapter.SelectCommand = command; oracleDataAdapter.Fill(dataSet); if (dataSet != null && dataSet.Tables.Count > 0) { dataTable = dataSet.Tables[0]; } } catch (Exception ex) { // Handle the exception in UI level only throw ex; } return(dataTable); }
public OracleParameterCollection(OracleCommand parent) : base(parent) { }
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; }
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 (); } }