C# OracleCommand.ExecuteReader方法代码示例

 public string[] GetSearchItems(string prefix)
 {
     List<string> items = new List<string>();
     using (OracleConnection conn = new OracleConnection())
     {
         conn.ConnectionString = ConfigurationManager
                 .ConnectionStrings["Bazi"].ConnectionString;
         using (OracleCommand cmd = new OracleCommand())
         {
             cmd.CommandText = "select ime,igraid from Igra where upper(ime) like upper(:SearchText)||'%'";
             cmd.Parameters.Add(new OracleParameter("SearchText", prefix));
             cmd.Connection = conn;
             conn.Open();
             using (OracleDataReader sdr = cmd.ExecuteReader())
             {
                 while (sdr.Read())
                 {
                     items.Add(string.Format("{0}`{1} Game", sdr.GetString(0), sdr.GetInt64(1)));
                 }
             }
             cmd.CommandText = "select IMEMODEL,HARDID from HARDVERPROIZVOD where upper(IMEMODEL) like upper(:SearchText)||'%'";
             using (OracleDataReader rdr = cmd.ExecuteReader())
             {
                 while (rdr.Read())
                 {
                     items.Add(string.Format("{0}`{1} Hardware", rdr.GetString(0), rdr.GetInt64(1)));
                 }
             }
             conn.Close();
         }
         return items.ToArray();
     }
 }

        public DataTable CheckTableExistence()
        {
            DataTable dataTable = new DataTable(((Type)recordItem).UnderlyingSystemType.Name);
            Type metainformation = Type.GetType("Object.Meta." + dataTable.TableName+ "Metainformation");

            string SqlCommand = string.Empty;
            SqlCommand = "SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME='" + dataTable.TableName.ToUpper() + "' ";
            OracleCommand OracleTriggerCommand = new OracleCommand(SqlCommand, oracleConnection);
            OracleDataReader dr = OracleTriggerCommand.ExecuteReader();
            if (!dr.HasRows)
            {
                dataTable = CreateTable(((Type)recordItem).UnderlyingSystemType.Name);
                dr = OracleTriggerCommand.ExecuteReader();
            }
            else
            {
                dataTable.Columns.Clear();
                while (dr.Read())
                {
                    if (!string.IsNullOrEmpty(dr[0].ToString()))
                    {
                        Type columnType = typeof(string);
                        string type = dr[2].ToString();
                        switch (type)
                        {
                            case "NUMBER": columnType = typeof(decimal); break;
                        }
                        DataColumn newColumn = new DataColumn(dr[1].ToString(), columnType);
                        dataTable.Columns.Add(newColumn);
                        newColumn.AllowDBNull = true;
                    }
                }
            }


            System.Reflection.FieldInfo[] mi = metainformation.GetFields();

            dataTable.Columns["NAME"].AllowDBNull = false;
                foreach (System.Reflection.FieldInfo fi in mi)
                {
                    if (fi.Name.StartsWith("isNullable"))
                    {
                        dataTable.Columns[fi.Name.Substring(10)].AllowDBNull = false;
                    }
                }
            
            // Assign MetaInformations
            //foreach(MemberInfo in Cats)

            return dataTable;
        }

        private static void OracleTest()
        {
            var connection = new OracleConnectionHolder("192.168.0.108", "customer", "customer");
            connection.SetCallback(new UniversalConnectionHolderCallback());

            connection.OpenConnection();

            var commandCount = 0;

            while (true) {
                try {
                    if (connection.IsConnected()) {
                        using (var command = new OracleCommand("select * from DEMO_USERS", connection.GetOracleConnection()))
                        using (var reader = command.ExecuteReader()) {
                            var count = 0;
                            while (reader.Read()) {
                                count++;
                            }
                            Console.WriteLine(commandCount++ + ") Команда выполнена. Count = " + count);
                        }
                    }
                }
                catch (Exception ex) {
                    if (!connection.ProcessError(ex)) {
                        throw;
                    }
                }
                finally {
                    Thread.Sleep(1000);
                }
            }
        }

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

        //发表评论
        public ActionResult AddComment(string content)
        {
            //假设登录用户为123，需换为session
            string loginuser = "123";
            //string loginuser = Session["username"] as string
            OracleConnection conn = new OracleConnection(oradb);
            conn.Open();

            string selectStr = "select textcommentid from textcomment";
            OracleCommand cmd = new OracleCommand(selectStr, conn);
            OracleDataReader dr = cmd.ExecuteReader();
            int temp = 0;
            while (dr.Read())
            {
                if (Convert.ToInt32(dr[0]) > temp)
                {
                    temp = Convert.ToInt32(dr[0]);
                }
            }
            temp++;

            string addStr = "insert into textcomment(textcommentid, textid, content, textcomment_date, username)values(" + temp + "," + textId + ",'" + content + "','" + DateTime.Now.ToString() + "','" + loginuser + "')";
            OracleCommand cmd2 = new OracleCommand(addStr, conn);
            int insertedLines = cmd2.ExecuteNonQuery();
            conn.Close();
            return RedirectToAction("Comment", "Account");
        }

        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 static Category FindById(int id)
        {
            string query = "SELECT * FROM TBL_CATEGORY WHERE ID=:id";

            using (OracleConnection connection = CreateConnection())
            using (OracleCommand command = new OracleCommand(query, connection))
            {
                command.BindByName = true;
                command.Parameters.Add(new OracleParameter("id", id));

                using (OracleDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        var dicId = GetCategoryIdFromRecord(reader);
                        if (!Dictionaries.Categories.ContainsKey(dicId))
                        {
                            Dictionaries.Categories.Add(dicId, GetCategoryFromDataRecord(reader));
                        }
                        return Dictionaries.Categories[dicId];
                    }
                }
            }
            return null;
        }

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

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

 /// <summary>
 /// Use only to retrieve user on initialization
 /// </summary>
 /// <param name="query">The input query</param>
 /// <returns>A DataTable containing the results of the query</returns>
 public static DataTable HaalGebruikersOp(string query)
 {
     using (OracleConnection conn = new OracleConnection(connectionstring2))
     {
         try
         {
             conn.Open();
             OracleCommand cmd = new OracleCommand(query);
             cmd.Connection = conn;
             try
             {
                 OracleDataReader reader = cmd.ExecuteReader();
                 DataTable result = new DataTable();
                 result.Load(reader);
                 conn.Close();
                 return result;
             }
             catch (OracleException ex)
             {
                 Console.WriteLine(ex.Message);
                 throw;
             }
         }
         catch (OracleException ex)
         {
             Console.WriteLine(ex.Message);
             return new DataTable();
         }
     }
 }

 static void Main(string[] args)
 {
     try
     {
         string connstring =
        "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora-002.cafe24.com)(PORT=1521))" +
        "(CONNECT_DATA=(SID=orcl)));User Id=gds;Password=1234;";
         using (OracleConnection conn = new OracleConnection(connstring))
         {
             conn.Open();
             Console.WriteLine("접속 성공");
             string sql = "select test from testdb";
             using (OracleCommand comm = new OracleCommand(sql, conn))
             {
                 using (OracleDataReader rdr = comm.ExecuteReader())
                 {
                     while (rdr.Read())
                     {
                         Console.WriteLine(rdr.GetString(0));
                         Console.ReadLine();
                     }
                 }
             }
         }
     }
     catch
     {
         Console.WriteLine("접속 실패");
     }
 }

        public static bool AddIncident(Incident i, int missieUID)
        {
            using (OracleConnection o = new OracleConnection(connstring))
            {
                using (OracleCommand c = new OracleCommand())
                {
                    try
                    {
                        c.Connection = o;
                        o.Open();
                        c.CommandText = "INSERT INTO TIncident (Beschrijving) VALUES ('" + i.Beschrijving + "')";
                        c.ExecuteNonQuery();

                        c.CommandText = "SELECT MAX(ID) FROM TIncident";
                        OracleDataReader dr = c.ExecuteReader();

                        if (dr.Read())
                        {
                            c.CommandText = "INSERT INTO TMissie_Incident (MissieID, IncidentID) VALUES ('" + missieUID + "', '" + GetInt(dr[0]) + "')";
                            c.ExecuteNonQuery();
                        }
                        return true;
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                        return false;
                    }
                    finally
                    {
                        o.Close();
                    }
                }
            }
        }

 public List<Employee> datos()
 {
     base.abrirConexion();
     OracleCommand cmd = new OracleCommand();
     cmd.Connection = conexion;
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.CommandText = "myProcedure";
     cmd.Parameters.Add("co", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;
     OracleDataReader dr = cmd.ExecuteReader();
     List<Employee> employees = new List<Employee>();
     if (dr.HasRows)
     {
         while (dr.Read())
         {
             int id = dr.GetInt32(0);
             string first_name = dr.GetString(1);
             string last_name = dr.GetString(2);
             employees.Add(new Employee(id, first_name, last_name));
             Console.WriteLine(dr.GetString(1));
         }
     }
     else
     {
         Console.WriteLine("No data found");
     }
     dr.Close();
     cmd.Dispose();
     base.cerrarConexion();
     return employees;
 }

        //____________________________________________________________
        //Lista "objeto" con Text
        public static List<LocalidadDTO> ListarLocalidadText()
        {
            OracleCommand cmd = new OracleCommand();
            List<LocalidadDTO> ListaLocalidades = new List<LocalidadDTO>();

            cmd.Connection = new OracleConnection(ConfigurationManager.ConnectionStrings["CHAPA"].ConnectionString);
            string strParcial = "Select  * from localidad";
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strParcial;
            cmd.Connection.Open();
            OracleDataReader odr = cmd.ExecuteReader();
            while (odr.Read())
            {
                LocalidadDTO objLoc = new LocalidadDTO();

                objLoc.locId = Convert.ToInt32(odr[0]);
                objLoc.locDescripcion = odr[1].ToString();

                ListaLocalidades.Add(objLoc);
            }
            cmd.Connection.Close();
            cmd.Dispose();

            return ListaLocalidades;
        }

        //____________________________________________________________
        //Lista "objeto" con StoredProcedure
        public static List<LocalidadDTO> ListarLocalidadSP()
        {
            OracleCommand cmd = new OracleCommand();
            List<LocalidadDTO> ListaLocalidades = new List<LocalidadDTO>();

            cmd.Connection = new OracleConnection(ConfigurationManager.ConnectionStrings["CHAPA"].ConnectionString);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "LOCALIDAD_SELECT";
            cmd.BindByName = true;

            cmd.Parameters.Add("o_c_loc", OracleDbType.RefCursor, ParameterDirection.Output);

            cmd.Connection.Open();
            OracleDataReader odr = cmd.ExecuteReader();
            while (odr.Read())
            {
                LocalidadDTO objLoc = new LocalidadDTO();

                objLoc.locId = Convert.ToInt32(odr[0]);
                objLoc.locDescripcion = odr[1].ToString();

                ListaLocalidades.Add(objLoc);
            }
            cmd.Connection.Close();
            cmd.Dispose();

            return ListaLocalidades;
        }