C# OracleConnection.Close方法代码示例

        static void Main()
        {
            const String cs = @"DATA SOURCE=127.0.0.1/ORCL;DBA PRIVILEGE=SYSDBA;USER ID=SYS;PASSWORD=queC0isa;";

            var c = new OracleConnection { ConnectionString = cs };

            c.StateChange += CStateChange;

            c.Disposed += CDisposed;

            c.Open();

            Thread.Sleep(2000);

            c.Close();

            //tentativa de liberar o objeto da memória

            //c.Dispose();

            //ou

            //c = null;
            //GC.Collect();

            Console.ReadKey();
        }

        public object DLookUp(String column, string table, string condition)
        {
            OracleConnection objConexion = default(OracleConnection);
            OracleDataAdapter objComando = default(OracleDataAdapter);
            DataSet requestquery = new DataSet();
            object result = null;
            objConexion = new OracleConnection(driver);
            objConexion.Open();
            if (string.IsNullOrEmpty(condition))
            {
                objComando = new OracleDataAdapter("Select " + column + " from " + table, objConexion);
            }
            else
            {
                objComando = new OracleDataAdapter("Select " + column + " from " + table + " where " + condition, objConexion);
            }

            objComando.Fill(requestquery);

            try
            {
                result = requestquery.Tables[0].Rows[0][requestquery.Tables[0].Columns.IndexOf(column)];
            }
            catch (Exception ex)
            {
                result = -1;

            }
            objConexion.Close();
            return result;
        }

        public static IEnumerable<Model.Post> Selecionar()
        {
            using (var c = new OracleConnection(Helper.Oracle.CS))
            {
                const String cmd = "SELECT P.*, C.DESCRICAO_CATEGORIA FROM BLOG.POST P INNER JOIN BLOG.POST_CATEGORIA PC ON P.CODIGO_POST = PC.CODIGO_POST INNER JOIN BLOG.CATEGORIA C ON C.CODIGO_CATEGORIA = PC.CODIGO_CATEGORIA WHERE C.DATA_EXCLUSAO_CATEGORIA IS NULL AND P.DATA_LIBERACAO_POST <= SYSDATE";

                using (var k = new OracleCommand(cmd, c))
                {
                    c.Open();

                    var dr = k.ExecuteReader();

                    Model.Post post = null;

                    while (dr.Read())
                    {
                        if (post != null && post.Codigo != Convert.ToInt32(dr[0]))
                            yield return post;

                        if (post == null || post.Codigo != Convert.ToInt32(dr[0]))
                            post = new Model.Post(Convert.ToInt32(dr[0]), dr[1].ToString(), dr[2].ToString(), Convert.ToDateTime(dr[3]), Convert.ToDateTime(dr[4]), Convert.ToInt32(dr[5]), Convert.ToChar(dr[6]), new List<Model.Categoria>());

                        post.Categorias.Add(new Model.Categoria(descricao: dr[7].ToString()));
                    }

                    if (post != null)
                        yield return post;

                    c.Close();
                }
            }
        }

        /// <summary>
        /// Metodo utilizado para validad la conexion a una base de datos Oracle
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool TestConnection(string username, string password)
        {
            this.username = username;
            this.password = password;

            string[] parametrosConnectionString = CadenaConexion.Split(';');
            
            var dataSourceEncontrado = CadenaConexion.ToUpper().Split(';').FirstOrDefault(c => c.Contains("DATA SOURCE"));
            DataSource = dataSourceEncontrado.Substring(dataSourceEncontrado.IndexOf("DATA SOURCE") + 12);
            
            CadenaConexion = ConstruirCadenaConexion(parametrosConnectionString);

            //Se agrego referencia Oracle.DataAccess.Client para framework 4
            using (OracleConnection oracle = new OracleConnection())
            {
                try
                {
                    var CadenaConexion1 = string.Format("Data Source={0}; User Id={1}; Password={2}; Pooling=true; Min Pool Size=1; Max Pool Size=2; Connection Timeout=30", DataSource, Username, Password);
                    oracle.ConnectionString = CadenaConexion1;
                    oracle.Open();
                    return true;
                }
                catch
                {
                    return false;
                }
                finally 
                {
                    oracle.Close();
                    oracle.Dispose();
                }
            }
        }

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

        /// <summary>
        ///     Writes the customer to the database.
        /// </summary>
        public int CallWriteCustomer(int firstValue, int secondValue)
        {
            // create Oracle connection
            var con = new OracleConnection("User Id=pts22;Password=pts22;Data Source=127.0.0.1:1521/xe;");
            var comm = new OracleCommand(); // create command
            int retVal = -1;

            try
            {
                con.Open(); // open connection
                comm.Connection = con; // add connection to command
                comm.CommandText = "WRITE_CUSTOMER"; // name of the Oracle procedure
                comm.CommandType = CommandType.StoredProcedure; // set type to stored procedure

                // add input parameters
                comm.Parameters.Add("ReturnValue", OracleDbType.Int16, ParameterDirection.ReturnValue);
                comm.Parameters.Add(new OracleParameter("USERID", firstValue));
                comm.Parameters.Add(new OracleParameter("RESERVATIONSID", secondValue));
                comm.ExecuteNonQuery(); // execute procedure

                retVal = int.Parse(comm.Parameters["ReturnValue"].Value.ToString()); // read return value
            }
            finally
            {
                con.Close();
            }

            return retVal;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OracleConnection conn = new OracleConnection();
            conn.ConnectionString = "User ID=SYSTEM; Password=19; Data Source=PC";

            try
            {
                conn.Open();
                MessageBox.Show("Veritabanına Baglantı Basarılı.");
            }
            catch
            {
                MessageBox.Show("Veritabanına Baglanılamadı.");
            }

            OracleCommand cmd = new OracleCommand();
            cmd.CommandText = "SELECT ogrenci_tablosu.ogrenci_ad, fakulte_tablosu.fakulte_ad FROM ogrenci_tablosu, bolum_tablosu, fakulte_tablosu WHERE ogrenci_tablosu.ogrenci_bolum = bolum_tablosu.bolum_id AND bolum_tablosu.bolum_fakulte = fakulte_tablosu.fakulte_id";
            cmd.Connection = conn;

            OracleDataAdapter adapter = new OracleDataAdapter();
            adapter.SelectCommand = cmd;

            DataSet dataset = new DataSet();

            adapter.Fill(dataset);

            dataGridView1.DataSource = dataset.Tables[0];

            conn.Close();
        }

        public static void CloseCon(OracleConnection con)
        {
            con.Close();
            con.Dispose();

            GC.Collect();
        }

        public DataSet GetProdOCRData(string queryName,string startDate, string endDate)
        {
            string queryPath = string.Format(@"{0}\Query\{1}", Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location),queryName);
            string query = File.ReadAllText(queryPath);
            query = string.Format(query, startDate, endDate);
            DataSet ds = new DataSet();
            using (oraconnection = new OracleConnection(ConfigurationManager.AppSettings["DSN_PNRP"]))
            {
                try
                {
                    oraconnection.Open();
                    oracommand = new OracleCommand(query, oraconnection);
                    oracommand.CommandType = CommandType.Text;
                    OracleDataAdapter adp = new OracleDataAdapter(oracommand);
                    ds = new DataSet();
                    adp.Fill(ds);
                }
                catch (Exception)
                {

                }
                finally
                {
                    oraconnection.Close();
                }

                return ds;
            }
        }

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

        private void bProcedure_Click(object sender, EventArgs e)
        {
            //Verbinding openen (waarschijnlijk doe je dit in je applicatie niet voor ieder database commando)
            using (OracleConnection objConn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=webdb.hi.fontys.nl)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=cicdb.informatica.local))); User ID=" + mUsername.Text + "; Password=" + tPassword.Text))
            {

                //Nieuw Oracle commando aanmaken
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandType = CommandType.StoredProcedure; //Instellen dat het om een stored procedure aanroep gaat
                objCmd.CommandText = "Verdubbel";  //Naam van de stored procedure welke we aanroepen

                //Mee te geven parameters instellen (richting, type en eventueel een waarde)
                objCmd.Parameters.Add("teVerdubbelen",OracleDbType.Decimal).Value = Convert.ToInt32(nTeVerdubbelenGetal.Value);
                objCmd.Parameters.Add("resultaat", OracleDbType.Decimal).Direction = ParameterDirection.Output;

                //Connectie maken en het commando uitvoeren
                try
                {
                    objConn.Open();
                    objCmd.ExecuteNonQuery(); //Voert de stored procedure uit

                    //Tonen van de waarde in de resultaat parameter na uitvoeren van de stored procedure
                    MessageBox.Show("De verdubbelde waarde is: "+objCmd.Parameters["resultaat"].Value);
                }
                catch (Exception ex)
                {
                    //Voor het geval "iets" mis gaat, de letterlijke foutmelding tonen (doe je natuurlijk niet in een "echte" applicatie)
                    MessageBox.Show("De volgende fout is opgetreden: "+ex.ToString());
                }
                //Verbinding sluiten (waarschijnlijk doe je dit in je applicatie niet per database commando)
                objConn.Close();
            }
        }

        public void TestConnectionWithString() 
        {
            int ret = 0;
            string connstring = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=pdborcl)));User Id=system;Password=admin1234;";

            string sql = @"select *from user_tables;";
            sql = getSqlString();
//            string sql = @"begin 
//create sequence TABLE22_SEQ;
//create table table22(id NUMBER DEFAULT 1);
//end;";
            using (DbConnection conn = new  OracleConnection(connstring))
            {
                conn.Open();
                DbCommand command = conn.CreateCommand();
                command.CommandType = CommandType.Text;
                command.CommandText = sql;

                ret = command.ExecuteNonQuery();

                conn.Close();
            }

            Assert.AreNotEqual(ret,0);
        }

        public Object DLookUp(String column,String table,String condition)
        {
            OracleConnection objConexion;
            OracleDataAdapter objComando;
            DataSet requestquery = new DataSet();
            object result;

            objConexion = new OracleConnection(driver);
            objConexion.Open();

            if(condition.Equals("")){
                objComando = new OracleDataAdapter("Select " + column + " from " + table, objConexion);

            }
            else
            {
                objComando = new OracleDataAdapter("Select " + column + " from " + table + " where " + condition, objConexion);

            }

            try
            {
                objComando.Fill(requestquery);
                result = requestquery.Tables[0].Rows[0][requestquery.Tables[0].Columns.IndexOf(column)];
            }
            catch (Exception e)
            {
                result = -1;
            }
            objConexion.Close();
            return result;
        }

        /// <summary>
        /// 将数据读取到 DataSet 中.
        /// </summary>
        public void ReadDataToDataSet()
        {

            Console.WriteLine("使用DataAdapter，将数据填充到DataSet中，然后脱离数据库，直接对DataSet进行处理。");

            // 建立数据库连接.
            OracleConnection conn = new OracleConnection(connString);

            // 创建一个适配器
            OracleDataAdapter adapter = new OracleDataAdapter(SQL, conn);

            // 创建DataSet，用于存储数据.
            DataSet testDataSet = new DataSet();

            // 执行查询，并将数据导入DataSet.
            adapter.Fill(testDataSet, "result_data");

            // 关闭数据库连接.
            conn.Close();

            // 处理DataSet中的每一行数据.
            foreach (DataRow testRow in testDataSet.Tables["result_data"].Rows)
            {
                // 将检索出来的数据，输出到屏幕上.
                Console.WriteLine("NO:{0} ;  Date:{1} ; Money:{2}   ",
                    testRow["NO"], testRow["SALE_DATE"], testRow["SUM_MONEY"]
                    );
            }
        }

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