public static User GetUser(string username)
        {
            User user = null;
            if (username != null)
            {
                try
                {
                    using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
                    {
                        OracleCommand command = new OracleCommand();
                        command.CommandText = "SELECT password,customerId,securityQuestion,securityAnswer,email FROM Users WHERE username LIKE :username";
                        command.Parameters.Add(":username", OracleDbType.NVarchar2).Value = username;
                        command.Connection = connection;
                        connection.Open();
                        OracleDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            string password = reader["password"].ToString();
                            string customerId = reader["customerId"].ToString();
                            string securityQuestion = reader["securityQuestion"].ToString();
                            string securityAnswer = reader["securityAnswer"].ToString();
                            string email = reader["email"].ToString();
                            user = new User(username, password, customerId, securityQuestion, securityAnswer, email);
                        }
                    }
                }

                catch (Exception e)
                {
                    user = null;
                    Logger.LogException(e);
                }
            }
            return user;
        }

        public OracleSessionDetails(OracleDBConnection connection)
        {
            InitializeComponent();
            this.dbConnection = connection;
            OracleConnection databaseConnection = dbConnection.getDatabaseConnection();
            String query;
            OracleCommand command = new OracleCommand("DBMS_SESSION.set_identifier",databaseConnection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(dbConnection.getUsername(), OracleDbType.Char);
            command.ExecuteNonQuery();

            query = "SELECT SYS_CONTEXT('userenv', 'SID') AS SID, "
                    + "SYS_CONTEXT('userenv', 'HOST') AS HOST, "
                    + "USER AS username "
                    + "FROM dual";
            command = new OracleCommand(query, databaseConnection);
            OracleDataReader reader;
            command.CommandText = query;
            command.CommandType = CommandType.Text;
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                this.sessionListing.Rows.Add("SID", reader["SID"]);
                this.sessionListing.Rows.Add("HOST", reader["HOST"]);
                this.sessionListing.Rows.Add("DATABASE", dbConnection.getDatabaseConnection().DataSource);
                this.sessionListing.Rows.Add("USER", reader["USERNAME"]);
            }
            reader.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string oradb = "Data Source=XE;User Id=system;Password=admin;";

            OracleConnection conn = new OracleConnection(oradb);

            conn.Open();

            OracleCommand cmd = new OracleCommand();

            cmd.Connection = conn;

            cmd.CommandText = "Delete from pharm.DRUG where DRUG_ID=" + textBox1.Text + " ";

            int rowsUpdated = cmd.ExecuteNonQuery();

            if (rowsUpdated == 0)

                MessageBox.Show("Record not deleted");

            else

                MessageBox.Show("Successfully deleted!");

            conn.Dispose();
        }

        /// <summary>
        /// Executes a query to the database, expecting no returning result
        /// </summary>
        /// <param name="query">Query to execute</param>
        /// <param name="args">List of OracleParameters containing the named arguments</param>
        /// <returns>True if the query succeeded (can still be 0 rows affected)</returns>
        public static bool ExecuteNonQuery(string query, List<OracleParameter> args = null)
        {
            var result = -1;
            try
            {
                using (var command = new OracleCommand(query, Connection) {BindByName = true})
                {
                    if (args != null)
                    {
                        foreach (var arg in args)
                        {
                            command.Parameters.Add(arg);
                        }
                    }

                    result = command.ExecuteNonQuery();
                }
            }
            catch (OracleException e)
            {
                Logger.Write(e.Message);
                return false;
            }
            finally
            {
                Close();
            }

            return result >= 0;
        }

        public void Upd(Assunto u)
        {
            using (OracleConnection conn = BaseDAO.GetConnection())
            {
                //Define o comando
                using (OracleCommand cmd = new OracleCommand("ASSUNTO_tapi.upd", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    //Define parametros
                    cmd.Parameters.Add("p_ID_ASSUNTO", u.Id_Assunto);
                    cmd.Parameters.Add("p_NOME", u.Nome);

                    //Executa
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception("Ocorreu o erro(BD): " + ex.Message);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Erro: " + ex.Message);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) { conn.Close(); }
                    }
                }
            }
        }

 public SesionUsuario inicioSesion(string userCorreo, string userPassword, string tipoUsuario)
 {
     base.abrirConexion();
     OracleCommand cmd = new OracleCommand();
     cmd.Connection = conexion;
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.CommandText = "pk_usuarios.session_login";
     OracleParameter userCorreoP = new OracleParameter("correoLogin", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     userCorreoP.Value = userCorreo;
     OracleParameter userPasswordP = new OracleParameter("passwordLogin", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     userPasswordP.Value = userPassword;
     //cmd.Parameters.Add("co", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
     OracleParameter tipoUsuarioP = new OracleParameter("rol", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     tipoUsuarioP.Value = tipoUsuario;
     OracleParameter resultado = new OracleParameter("resultado", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
     cmd.Parameters.AddRange(new OracleParameter[] { userCorreoP, userPasswordP, tipoUsuarioP, resultado });
     OracleDataReader lectorDatos = cmd.ExecuteReader();
     if (lectorDatos.HasRows)
     {
         lectorDatos.Read();
         return new SesionUsuario() { id = lectorDatos.GetString(0), operacionExitosa = true, rol = tipoUsuario };
     }
     else
         return new SesionUsuario() { id = string.Empty, operacionExitosa = false, rol = tipoUsuario };
 }

        /**
            Permite eliminar un cliente
        */
        public string deleteCustomer(string id)
        {
            string deleteCustomer = "DELETE FROM CUSTOMERS.CUSTOMER WHERE Id = :Id";

            OracleTransaction transaction;
            using (OracleConnection connection = new OracleConnection())
            {
                connection.ConnectionString = ConfigurationManager.ConnectionStrings["customerDb"].ConnectionString;

                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();
                    deleteCustomerAddress(id, connection, transaction);

                    using (OracleCommand command = new OracleCommand(deleteCustomer, connection))
                    {
                        command.Parameters.Add(new OracleParameter("Id", id));

                        command.ExecuteNonQuery();
                        transaction.Commit();
                    }
                }
                catch (Exception e)
                {
                    throw new PlatformException(e.Message);
                }
            }

            return "OK";
        }

        public void agregarNoticia(string fecha_expiracionN, string contenidoN,string encabezadoN,string tituloN)
        {
            base.abrirConexion();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conexion;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "noticias_paquete.agregarNoticia";

            OracleParameter fechaExpiracionP = new OracleParameter("fecha_expiracionN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            fechaExpiracionP.Value = fecha_expiracionN;

            OracleParameter contenidoP = new OracleParameter("contenidoN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            contenidoP.Value = contenidoN;

            OracleParameter encabezadoP = new OracleParameter("encabezadoN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            encabezadoP.Value = encabezadoN;

            OracleParameter tituloP = new OracleParameter("tituloN", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            tituloP.Value = tituloN;

            cmd.Parameters.AddRange(new OracleParameter[] { fechaExpiracionP, contenidoP, encabezadoP, tituloP});

            cmd.ExecuteNonQuery();

            cmd.Dispose();
            base.cerrarConexion();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            string CS = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
                OracleConnection conn = new OracleConnection(CS);
                using (OracleCommand cmd = new OracleCommand("insert into USER_TABLE (USER_ID,U_NAME,U_PASS,FULLNAME,CONTACT,EMAIL,DOB,U_LOCATION) values (U_ID_SEQ.NEXTVAL,'" + TextBox1.Text + "','" + TextBox3.Text + "','" + TextBox7.Text + "','" + TextBox5.Text + "','" + TextBox4.Text + "','" + TextBox6.Text + "','" + TextBox8.Text + "')", conn))
                {
                    //cmd.Parameters.("@U_NAME",TextBox1.Text);
                    //cmd.Parameters.AddWithValue("@U_PASS",TextBox3.Text);
                    //cmd.Parameters.AddWithValue("@FULLNAME", TextBox7.Text);
                    //cmd.Parameters.AddWithValue("@CONTACT",TextBox5.Text);
                    //cmd.Parameters.AddWithValue("@EMAIL",TextBox4.Text);
                    //cmd.Parameters.AddWithValue("@DOB",TextBox6.Text);
                    //cmd.Parameters.AddWithValue("@U_LOCATION",TextBox8.Text);
                    conn.Open();
                    cmd.ExecuteNonQuery();

                    Label1.ForeColor = System.Drawing.Color.Green;
                    Label1.Text = "Data Saved In The Database";
                }
                 //using(OracleCommand comd = new OracleCommand("select USER_ID from USER_TABLE where U_NAME = '"+TextBox1.Text+"'"))
                 //{

                 //    string id= comd.ExecuteScalar().ToString();
                 //    Session["Id"] = id;
                 //}
        }

        void AutocompleteText()
        {
            textBox5.AutoCompleteMode = AutoCompleteMode.Suggest;
            textBox5.AutoCompleteSource = AutoCompleteSource.CustomSource;
            AutoCompleteStringCollection coll = new AutoCompleteStringCollection();

            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                string sqluery = "SELECT * FROM MED_INFO WHERE MED_MGF='" + comboBox1.Text + "' ORDER BY MED_NAME";
                OracleCommand cd = new OracleCommand(sqluery, conn);
                OracleDataReader r;
                r = cd.ExecuteReader();
                while(r.Read())
                {
                    string sn = r.GetString(1);
                    coll.Add(sn);
                }

                r.Dispose();
                cd.Dispose();
                conn.Close();
            }
            catch (Exception exe)
            {
                MessageBox.Show(exe.Message);
            }

            textBox5.AutoCompleteCustomSource = coll;
        }

        // GET: Album
        //  [HttpPost]
        public List<AlbumModels> Album()
        {
            OracleConnection conn = new OracleConnection(oradb);

                conn.Open();
                string selectStr =   /* "insert into album(albumID,albumname,username) values('111','testalbum','KING')";*/
                                       "select * "
                                       + "from album ";
                    // " +(Session["username"] as string)+"

                OracleCommand cmd = new OracleCommand(selectStr, conn);
                OracleDataReader dr = cmd.ExecuteReader();
                var Models = new List<AlbumModels>();
                while (dr.Read())
                {

                    AlbumModels model = new AlbumModels();
                    model.AlbumName = dr["albumname"].ToString();
                    model.AlbumId = dr["albumid"].ToString();
                    //model.Username = Session["username"] as string ;
                    string selectStr1 = "select * from photo where albumid = " + model.AlbumId;
                    OracleCommand cmd1 = new OracleCommand(selectStr1, conn);
                    OracleDataReader dr1 = cmd1.ExecuteReader();
                    dr1.Read();
                    model.Firstphoto = dr1["photoaddress"].ToString();
                    Models.Add(model);
                    //Session.Add("currentAlbumName", albumName);
                    //Session.Add("currentAlbumId", albumId);         //点击时保存到session
                }
                dr.Close();
                int insertedLines = cmd.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
                return Models;
        }

        /// <summary>
        /// Handles the specified request.
        /// </summary>
        /// <param name="request">The request.</param>
        public void Handle(DeployRequestDto request)
        {
            //Get the connection string
            var sqlInputs = GetConnectionString(request);

            //get the scripts folder
            var directory = request.IntermediateDirectory;

            //get the files in the folder
            string[] fileEntries = Directory.GetFiles(directory);
            foreach (string fileName in fileEntries)
            {
                try
                {
                    string content = string.Format(File.ReadAllText(fileName));
                    using (var oracleConnection = new OracleConnection(sqlInputs.ConnectionString))
                    {
                        oracleConnection.Open();
                        var command = new OracleCommand();
                        var script = content.Replace("\t", " ");
                        script = script.Replace("\n", Environment.NewLine);
                        command.Connection = oracleConnection;
                        command.CommandText = script;
                        var result = command.ExecuteNonQuery();
                        Debug.Write(result);
                    }
                }
                catch (Exception ex)
                {
                    Debug.Write("Exception message: " + ex.Message);
                }
            }
        }

        /// <summary>
        /// Changes the thread
        /// </summary>
        /// <param name="id">id used too identify the</param>
        /// <param name="changes">all changes in the same order as the parameters</param>
        /// <param name="paramaterToChanged">all parameters in the same order as the changes</param>
        public static void AlterThread(int id, List<string> changes, List<string> paramaterToChanged)
        {
            OracleConnection conn = MakeConnection();
            conn.Open();

            string mainQuery = "UPDATE THREAD SET ";

            OracleCommand command = new OracleCommand();

            for (int i = 0; i < changes.Count; i++)
            {
                mainQuery += paramaterToChanged[i];
                mainQuery += " = :" + paramaterToChanged[i];
                command.Parameters.Add(new OracleParameter(paramaterToChanged[i], changes[i]));
            }

            mainQuery += " WHERE THREADID = :THREADID";
            command.CommandText = mainQuery;
            command.Connection = conn;
            command.Parameters.Add(new OracleParameter("THREADID", id));

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public static bool IsAvailable(string columnName, string value)
        {
            bool available = false;
            if (!String.IsNullOrWhiteSpace(columnName) && value != null)
            {
                try
                {
                    using (OracleConnection connection = new OracleConnection(CONNECTION_STRING))
                    {
                        OracleCommand command = new OracleCommand();
                        command.CommandText = String.Format("SELECT COUNT(*) FROM Users WHERE {0} LIKE :value",columnName);
                        command.Parameters.Add(":value", OracleDbType.NVarchar2).Value = value;
                        command.Connection = connection;
                        connection.Open();
                        int count = Convert.ToInt32(command.ExecuteScalar());
                        if (count==0)
                            available = true;
                        else
                            available = false;
                    }
                }

                catch (Exception e)
                {
                    available = false;
                    Logger.LogException(e);
                }
            }
            return available;
        }

        public void Ins(Fabricante u)
        {
            using (OracleConnection conn = BaseDAO.GetConnection())
            {
                //Define o comando
                using (OracleCommand cmd = new OracleCommand("FABRICANTE_tapi.ins", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    //Define parametros
                    cmd.Parameters.Add("p_ID_FABRICANTE", u.Id_Fabricante);
                    cmd.Parameters.Add("p_NOME", u.Nome);

                    //Executa
                    try
                    {
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    catch (OracleException ex)
                    {
                        throw new Exception("Ocorreu o erro(BD): " + ex.Message);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Erro: " + ex.Message);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) { conn.Close(); }
                    }
                }
            }
        }

        public static void GetEmployeeMatches(List<Employee> list, DPFP.Sample Sample,int FingerNo)
        {
            OracleConnection conn = new OracleConnection(ConStr);
            Employee info = null;
            string FingerDesc = Util.GetFingerDesc(FingerNo);
            try
            {
                string _result = string.Empty;
                string sql = "select Empl_Id_No,RTF,RIF,RMF,RRF,RSF,LTF,LIF,LMF,LRF,LSF from EMPTSI";
                OracleCommand command = new OracleCommand(sql, conn);
                command.CommandType = CommandType.Text;
                conn.Open();
                OracleDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        info =  dcEmployee.GetInfo(reader, Sample, FingerDesc);
                        AddInfo(list, info);
                    }
                }
                if (conn.State == ConnectionState.Open) conn.Close();
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        public List<Troca> Get()
        {
            using (OracleConnection conn = BaseDAO.GetConnection())
            {
                //Define o comando
                using (OracleCommand cmd = new OracleCommand("SELECT ID_TROCA, ID_USUARIO_SOLICITANTE, ID_USUARIO_DETENTOR, DATA_INCLUSAO, DATA_SITUACAO,"
                                                            +"SITUACAO, POSICAO_SOLICITANTE, POSICAO_DETENTOR FROM TROCA", conn))
                {

                    List<Troca> lista = new List<Troca>();
                    cmd.CommandType = CommandType.Text;

                    //Executando o select
                    OracleDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        Troca t = new Troca();
                        t.Id_Troca = Convert.ToInt32(reader["ID_TROCA"]);
                        t.Id_Usuario_Solicitante = Convert.ToInt32(reader["ID_USUARIO_SOLICITANTE"]);
                        t.Id_Usuario_Detentor = Convert.ToInt32(reader["ID_USUARIO_DETENTOR"]);
                        t.Data_Inclusao = Convert.ToDateTime(reader["DATA_INCLUSAO"]);
                        t.Data_Situacao = Convert.ToDateTime(reader["DATA_SITUACAO"]);
                        t.Situacao = Convert.ToInt32(reader["SITUACAO"]);
                        t.Posicao_Solicitante = Convert.ToInt32(reader["POSICAO_SOLICITANTE"]);
                        t.Posicao_Detentor = Convert.ToInt32(reader["POSICAO_DETENTOR"]);
                        lista.Add(t);
                    }
                    return lista;
                }
            }
        }

        public int Insert(EmployeeTSI info)
        {
            OracleConnection conn = new OracleConnection(ConStr);

            try
            {
                int EmpId=0;
                OracleCommand command = new OracleCommand("EmpTSI_update", conn);
                 command.CommandType = CommandType.StoredProcedure;
                var _params = command.Parameters;
                conn.Open();
                SetParameterValue(_params, "p_empl_id_no", info.Empl_Id_No, OracleDbType.Int32);
                SetParameterValue(_params, "p_tsi", info.TSI, OracleDbType.Varchar2);
                SetParameterValue(_params, "p_user_id", this.UserId, OracleDbType.Varchar2);
                SetParameterValue(_params, "p_img", info.IMG, OracleDbType.Blob);
                SetParameterValue(_params, "p_rtf", info.RTF, OracleDbType.Blob);
                SetParameterValue(_params, "p_rif", info.RIF, OracleDbType.Blob);
                SetParameterValue(_params, "p_rmf", info.RMF, OracleDbType.Blob);
                SetParameterValue(_params, "p_rrf", info.RRF, OracleDbType.Blob);
                SetParameterValue(_params, "p_rsf", info.RSF, OracleDbType.Blob);
                SetParameterValue(_params, "p_ltf", info.LTF, OracleDbType.Blob);
                SetParameterValue(_params, "p_lif", info.LIF, OracleDbType.Blob);
                SetParameterValue(_params, "p_lmf", info.LMF, OracleDbType.Blob);
                SetParameterValue(_params, "p_lrf", info.LRF, OracleDbType.Blob);
                SetParameterValue(_params, "p_lsf", info.LSF, OracleDbType.Blob);

                command.ExecuteNonQuery();
                conn.Close();
                return EmpId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 public int sesionActiva(string toquenId)
 {
     base.abrirConexion();
     OracleCommand cmd = new OracleCommand();
     cmd.Connection = conexion;
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.CommandText = "pk_usuarios.usuarioEnSistema";
     OracleParameter toquenIdP = new OracleParameter("userTokenID", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     toquenIdP.Value = toquenId;
     OracleParameter resultado = new OracleParameter("resultado", OracleDbType.Varchar2, System.Data.ParameterDirection.Output);
     resultado.Size = 1024;
     cmd.Parameters.AddRange(new OracleParameter[] { toquenIdP, resultado });
     try
     {
         cmd.ExecuteNonQuery();
         int respuesta = int.Parse(cmd.Parameters["resultado"].Value.ToString());
         cmd.Dispose();
         base.cerrarConexion();
         return respuesta;
     }
     catch (Exception e)
     {
         System.Diagnostics.Debug.WriteLine(e.Message);
         throw e;
     }
 }

            /// <summary>
            /// Selects and retrieves values from the database 
            /// </summary>
            /// <param name="query">The selection statement</param>
            /// <returns>A DataTable with the retrieved values></returns>
            public static DataTable RetrieveQuery(string query)
            {
                if (Regex.IsMatch(query, @"-{2,}"))
                {
                    throw new SQLInjectionException();
                }

                using (OracleConnection c = new OracleConnection(@connectionstring))
                {
                    try
                    {
                        c.Open();
                        OracleCommand cmd = new OracleCommand(@query);
                        cmd.Connection = c;
                        try
                        {
                            OracleDataReader r = cmd.ExecuteReader();
                            DataTable result = new DataTable();
                            result.Load(r);
                            c.Close();
                            return result;
                        }
                        catch (OracleException e)
                        {
                            Console.Write(e.Message);
                            throw;
                        }
                    }
                    catch (OracleException e)
                    {
                        Console.Write(e.Message);
                        return new DataTable();
                    }
                }
            }