C# OracleCommand.ExecuteReader方法代码示例

 /*
  * This method selects all details of a selected combo box class description and displays the details in the selected text boxes
  */
 public void selectDetails(ComboBox cmo_Description, TextBox txt_Class_Id, TextBox txt_Display_Desc, TextBox txt_Rate)
 {
     try
     {
         //Select query that brings back the details of car class using description
         string query_String2 = String.Format("SELECT * FROM Car_Class WHERE Description = '{0}'", cmo_Description.Text);
         connection.Open();
         cmd = connection.CreateCommand();
         /*
         * Creating the query string to retrieve the required record when a customer id is supplied
        */
         cmd.CommandText = query_String2;
         data_reader = cmd.ExecuteReader();//the reader is used to read in the required record
         while (data_reader.Read())
         {
             /*
              * Assigning the values of the Car_Class table to the appropriate text boxes after retreving
              */
             if (!data_reader.HasRows)
                 return;
             txt_Class_Id.Text = data_reader.GetValue(0).ToString();
             txt_Display_Desc.Text = data_reader.GetValue(1).ToString();
             txt_Rate.Text = data_reader.GetValue(2).ToString();
         }
         connection.Close();
     }
     catch (Exception) // Catching exception if the id dosent match any records
     {
         //MessageBox.Show("No Record Found");
         MessageBox.Show("No Records Found", "ERROR", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
     }
 }

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

        /*
         * Finding the difference between the start booking date and end booking date
         * to calculate the cost of a booking
         */
        public void calculateBookingCost(DateTimePicker startDate, DateTimePicker endDate, ComboBox cbo_Description, TextBox txt_Cost)
        {
            connection.Close();

            DateTime dt1 = Convert.ToDateTime(startDate.Text);
            DateTime dt2 = Convert.ToDateTime(endDate.Text);

            TimeSpan timeSpan = dt2 - dt1;
            int numberOfDays = timeSpan.Days;

            string query_String = "SELECT Car_Rate FROM Car_Class WHERE Description ='" + cbo_Description.Text + "'";

            connection.Open();

            try
            {
                cmd = connection.CreateCommand();
                cmd.CommandText = query_String;
                data_reader = cmd.ExecuteReader();//the reader is used to read in the required record

                if (data_reader.HasRows)
                {
                    data_reader.Read();

                    txt_Cost.Text = ("" + data_reader.GetInt32(0) * numberOfDays);

                }
                connection.Close();
            }
            catch (Exception) { }
        }

        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 string CampoChavesDaTabela(string tabela)
        {
            if (!string.IsNullOrEmpty(tabela))
            {

                string SQL = @"SELECT ALL_CONS_COLUMNS.COLUMN_NAME
                                FROM ALL_CONSTRAINTS, ALL_CONS_COLUMNS
                                WHERE ALL_CONSTRAINTS.OWNER         = :OWNER
                                AND ALL_CONSTRAINTS.TABLE_NAME      = :TABLE_NAME
                                AND ALL_CONSTRAINTS.CONSTRAINT_TYPE = 'P'
                                AND ALL_CONSTRAINTS.OWNER = ALL_CONS_COLUMNS.OWNER
                                AND ALL_CONSTRAINTS.CONSTRAINT_NAME = ALL_CONS_COLUMNS.CONSTRAINT_NAME";
                IConexao conexao = FactoryConexao.GetConexao(BD);
                using (var select = new OracleCommand(SQL, conexao.Oralceconnection))
                {
                    select.Parameters.Add("OWNER", this.owner);
                    select.Parameters.Add("TABLE_NAME", tabela);
                    var read = select.ExecuteReader();
                    string toReturn = string.Empty;
                    while (read.Read())
                    {
                        toReturn = (read["COLUMN_NAME"].ToString().ToUpper());
                    }
                    read.Close();
                    conexao.Oralceconnection.Close();
                    return toReturn;
                }

            }
            else
            {
                MessageBox.Show(Messagen.IMFORMARTABELAECAMPO, Messagen.ATENCAO, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return null;
            }
        }

	public void run()
	{
		Exception exp = null;
		OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
		con.Open();
		OracleCommand cmd = new OracleCommand("Select * From Orders", con);
		OracleDataReader rdr = cmd.ExecuteReader();

		//change a connection's state without closing the datareader (should fail
		try
		{
			BeginCase("InvalidOperationException");
			try
			{
				((IDbConnection)con).ChangeDatabase("msdb");
				ExpectedExceptionNotCaught(typeof(InvalidOperationException).FullName);
			}
			catch (InvalidOperationException ex) 
			{
				ExpectedExceptionCaught(ex);
			}
		} 
		catch(Exception ex){exp = ex;}
		finally{EndCase(exp); exp = null;}

		if (con.State == ConnectionState.Open) con.Close();
	}

        public CE_Servidor FechayHoradelServidor()
        {
            //la funcion me permite recuperar los datos del colaborador en el objeto CE_Colaborador
            try
            {

                CE_Servidor objce_servidortemp = new CE_Servidor();
                OracleConnection cnx = Conexion.ObtenerConexionOracle();

                OracleCommand cmd = new OracleCommand("select current_timestamp from dual", cnx);
                cnx.Open();
                OracleDataReader reader;

                reader = cmd.ExecuteReader();

                //verifico si hay filas devueltas
                Boolean hayfilas = reader.HasRows;
                if (hayfilas == true)
                {//si hay filas devuelvo el resultado de la consulta
                    while (reader.Read())
                    {
                        objce_servidortemp.datetimeservidor = Convert.ToDateTime(reader["current_timestamp"]);
                    }

                }

                //Cerrar conexion
                cnx.Close();
                return objce_servidortemp;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public OracleDataReader execQuery(string sql)
        {
            OracleCommand cmd = new OracleCommand(sql);
            OracleDataReader reader = null;

            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            try
            {

                reader = cmd.ExecuteReader();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                cmd.Dispose();
            }

            return reader;
        }

        public List<String> make_connection()
        {
            OracleConnection conn = new OracleConnection(oradb);
            conn.Open();
            string selectStmt = string.Format("select car.*, mar.IMENAMARKA from VOZILA car, MODELI mod, MARKA mar where car.IMENAMODEL = mod.IMENAMODEL and mod.IMENAMARKA = mar.IMENAMARKA ");

            OracleCommand cmd = new OracleCommand(selectStmt, conn);
            OracleDataReader dataReader = cmd.ExecuteReader();

            List<String> redovi = new List<string>();
            while (dataReader.Read())
            {
                string temp = dataReader.GetString(1).ToString() + " ";
                temp = temp + dataReader.GetString(16).ToString() + " ";
                temp = temp + dataReader.GetString(15).ToString() + " ";
                temp = temp + dataReader.GetValue(2).ToString() + " ";
                temp = temp + dataReader.GetValue(3).ToString() + " ";
                temp = temp + dataReader.GetValue(4).ToString() + " ";
                temp = temp + dataReader.GetValue(5).ToString() + " ";
                temp = temp + dataReader.GetValue(6).ToString() + " ";
                temp = temp + dataReader.GetValue(7).ToString() ;

                redovi.Add(temp);
            }

            conn.Close();
            conn.Dispose();

            return redovi;
        }

        private void OK_Click( object sender, EventArgs e )
        {
            try
            {
                OracleConnection conn = ConnectionOracle.creaConnection();
                conn.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select count(*) from admins where id='"+tno.Text+"' and psw='"+Psw.Text+"'";
                OracleDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    if (reader[0].ToString ().Equals ( "1" ))
                    {
                        this.Hide();
                        View_illstudent stu = new View_illstudent();
                        stu.Show();
                        return;
                    }
                }
                MessageBox.Show("用户没有找到");
            //                cmd.ExecuteScalar() + "";
                //               int raw = cmd.ExecuteOracleScalar();
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message);

                throw;
            }
        }

		public void run()
		{
			Exception exp = null;

			//prepare data
			base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);

			int intRecordsAffected = 0;
			OracleConnection con = new OracleConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
			con.Open();
			OracleCommand cmd = new OracleCommand("Update Employees set Title = 'title' where EmployeeID = 100", con);
			OracleDataReader rdr = cmd.ExecuteReader();
			rdr.Read();
			intRecordsAffected = rdr.RecordsAffected;

			try
			{
				BeginCase("RecordsAffected");
				Compare(intRecordsAffected,1 );
			} 
			catch(Exception ex){exp = ex;}
			finally{EndCase(exp); exp = null;}

			if (con.State == ConnectionState.Open) con.Close();
		}

 public static OracleDataReader ExecuteReader(string sqlStr, List<OracleParameter> parListOracleParameters)
 {
     OracleDataReader oracleDataReader = null;
     OracleConnection oracleConnection = new OracleConnection();
     oracleConnection.ConnectionString = OracleHelper.connectStr;
     try
     {
         if (oracleConnection.State != ConnectionState.Open)
         {
             oracleConnection.Open();
         }
         OracleCommand oracleCommand = new OracleCommand();
         oracleCommand.Connection = oracleConnection;
         oracleCommand.CommandText = sqlStr;
         for (int i = 0; i < parListOracleParameters.Count; i++)
         {
             oracleCommand.Parameters.Add(parListOracleParameters[i]);
         }
         oracleDataReader = oracleCommand.ExecuteReader(CommandBehavior.CloseConnection);
         oracleCommand.Dispose();
     }
     catch (Exception ex)
     {
         oracleDataReader.Close();
         oracleConnection.Close();
         throw ex;
     }
     return oracleDataReader;
 }

        private void button3_Click(object sender, EventArgs e)
        {
            Evento evento;
            EventoDAO eventoDAO = new EventoDAO();
            evento = eventoDAO.Buscar(textBox1.Text);

            try
            {
                using (OracleCommand Comando = new OracleCommand())
                {
                    Comando.Connection = ConexaoBD.Conectar();
                    Comando.CommandType = System.Data.CommandType.Text;
                    Comando.CommandText = "SELECT ID_EVENTO, NOME, DATA_INICIO, DATA_FIM, LOCAL, ID_RESPONSAVEL FROM EVENTO WHERE ID_EVENTO = :idevento";
                    Comando.Parameters.Add(":idevento", OracleType.Number).Value = OracleNumber.Parse(textBox1.Text);

                    OracleDataReader leitor = Comando.ExecuteReader();
                    while (leitor.HasRows)
                    {
                        leitor.Read();

                        leitor.NextResult();
                    }

                }
            }
            catch (OracleException ex) {
                MessageBox.Show(ex.Message);
            }
        }

        public static Applicant GetItem(int Id)
        {
            Applicant applicant = null;
            using (OracleConnection loanDbConn =
                    new OracleConnection(
                            ConnStringFactory.getConnString(
                                ConnStringFactory.ConnStringType.Oracle))) {
                loanDbConn.Open();
                using (OracleCommand getApplicantByIdCommand = new OracleCommand()) {
                    getApplicantByIdCommand.CommandType = CommandType.StoredProcedure;
                    getApplicantByIdCommand.CommandText = "ApplicantsPKG.getApplicantById";
                    getApplicantByIdCommand.Connection = loanDbConn;
                    getApplicantByIdCommand.Parameters.AddWithValue("Id", Id);

                    OracleParameter outputCursor = new OracleParameter("IO_CURSOR", OracleType.Cursor);
                    outputCursor.Direction = ParameterDirection.Output;
                    getApplicantByIdCommand.Parameters.Add(outputCursor);

                    using (OracleDataReader applicantReader =
                            getApplicantByIdCommand.ExecuteReader()) {
                        if (applicantReader.Read()) {
                            applicant = FillDataRecord(applicantReader);
                        }
                    }
                }
            }
            return applicant;
        }