C# OracleClient.OracleCommand类代码示例

 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();
     }
 }

        /// <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 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 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 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;
            }
        }

        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;
        }

        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>
 /// 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;
         }
     }
 }

        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();
                }
            }
        }

        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>
 /// 执行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();
             }
         }
     }
 }

        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>
        /// 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 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 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=" + Form1.pass + ";");

            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;

            }
        }