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;

            }
        }

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

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

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