C# OracleConnection.Dispose方法代码示例

        /// <summary>  
        /// 执行数据库查询操作,返回受影响的行数  
        /// </summary>  
        /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
        /// <param name="commandParameters">命令参数集合</param>  
        /// <returns>当前查询操作影响的数据行数</returns>  
        public static int ExecuteNonQuery(string cmdText, params OracleParameter[] commandParameters)
        {

            OracleCommand command = new OracleCommand();
            OracleConnection connection = new OracleConnection(connstr);
            int result = 0;

            try
            {
                PrepareCommand(command, connection, null, CommandType.Text, cmdText, commandParameters);
                result = command.ExecuteNonQuery();
                command.Parameters.Clear();
            }
            catch
            {
                throw;
            }
            finally
            {
                command.Dispose();
                connection.Close();
                connection.Dispose();
            }

            return result;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string connection = "Data Source=ORCL; User Id=pharmacy; Password=pharmacy";
            OracleConnection InsertData = new OracleConnection(connection);
            InsertData.Open();

            OracleParameter empno = new OracleParameter();
            empno.OracleDbType = OracleDbType.Int16;
            empno.Value = Quantity.Text;

            OracleParameter deptno = new OracleParameter();
            deptno.OracleDbType = OracleDbType.Int16;
            deptno.Value = Price.Text;

            OracleCommand insertEmpno = new OracleCommand();
            insertEmpno.Connection = InsertData;
            insertEmpno.Parameters.Add(empno);
            insertEmpno.CommandText = "select ename from employees where empid = 101";

            insertEmpno.CommandType = CommandType.Text;

            OracleDataReader DataRead = insertEmpno.ExecuteReader();

            DataRead.Read();

            while (DataRead.Read())
            {
                listBox1.Items.Add(DataRead.GetString(1));
            }

            InsertData.Dispose();
        }

        public List<PaisDTO> getPais() 
        {
            List<PaisDTO> lstPais = new List<PaisDTO>();
            Parametros p = new Parametros();
            PaisDTO itemPais;

            using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
            {
                con.Open();

                OracleCommand cmd = con.CreateCommand();                
                cmd.CommandText = "select IDPAIS, NOMBREPAIS from pais";

                using (OracleDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        itemPais = new PaisDTO();
                        itemPais.idPais = reader.GetInt32(0);
                        itemPais.nombrePais = reader.GetString(1);
                        lstPais.Add(itemPais);
                    }
                }

                con.Close();
                con.Dispose();
            }

            return lstPais;
        }

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

        public List<DepartamentoDTO> getDepartamento(int idPais = -1)
        {
            List<DepartamentoDTO> lstDepar = new List<DepartamentoDTO>();
            Parametros p = new Parametros();
            DepartamentoDTO itemDep;

            using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
            {
                con.Open();

                OracleCommand cmd = con.CreateCommand();
                
                cmd.CommandText = (idPais != -1) ? "select IDDEPARTAMENTO, IDPAIS, NOMBREDEPARTAMENTO from departamento where IDPAIS = " + idPais.ToString() : "select IDDEPARTAMENTO, IDPAIS, NOMBREDEPARTAMENTO from departamento";

                using (OracleDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        itemDep = new DepartamentoDTO();
                        itemDep.idDepartamento = reader.GetInt32(0);
                        itemDep.idPais = reader.GetInt32(1);
                        itemDep.nombreDepartamento = reader.GetValue(2).ToString();
                        
                        lstDepar.Add(itemDep);
                    }
                }

                con.Close();
                con.Dispose();
            }

            return lstDepar;
        }

        public static void ConnectionPoolTest(int iterations)
        {
            // used to track execution duration
            DateTime timeStart;
            DateTime timeEnd;
            double totalSeconds;

            // the connection object to use for the test
            OracleConnection con;

            // display simple prompt text
            Console.WriteLine("Beginning Connection Pool Test with {0} iterations...", iterations.ToString());

            // capture test start time for no pooling test
            timeStart = DateTime.Now;

            // loop creating a connection with no connection pooling
            // number of loops is determined by the iterations parameter
            for (int i = 0; i < iterations; i++) {
                con = new OracleConnection(no_pool);
                con.Open();
                con.Dispose();
            }

            // capture test end time for no pooling test
            timeEnd = DateTime.Now;

            // calculate total seconds for this test
            totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;

            // display time used for no pooling test
            Console.WriteLine("    No Pooling: {0} total seconds.", totalSeconds.ToString());

            // capture test start time for pooling test
            timeStart = DateTime.Now;

            // loop creating a connection with connection pooling
            // number of loops is determined by the iterations parameter
            for (int i = 0; i < iterations; i++) {
                con = new OracleConnection(with_pool);
                con.Open();
                con.Dispose();
            }

            // capture test end time for pooling test
            timeEnd = DateTime.Now;

            // calculate total seconds for this test
            totalSeconds = timeEnd.Subtract(timeStart).TotalSeconds;

            // display time used for pooling test
            Console.WriteLine("  With Pooling: {0} total seconds.", totalSeconds.ToString());
            Console.WriteLine();
        }

        public AddAnEmployee()
        {
            InitializeComponent();
            ex_empid.Hide();
            ex_ename.Hide();
            ex_inss.Hide();
            ex_payroll.Hide();
            ex_sal.Hide();
            msg_bno.Show();
            msg_bno.Text = "Please Select A Branch";
            msg_mgr.Show();
            msg_mgr.Text = "Please Select A Manager";

               // string _sql;
            try
            {
                DataSet _ds;
                OracleConnection _connObj = new OracleConnection(_connstring);
                _ds = new DataSet();
                _connObj.Open();

                OracleCommand ShowBNO = new OracleCommand();
                ShowBNO.Connection = _connObj;
                ShowBNO.CommandText = "select bno from branch";
                ShowBNO.CommandType = CommandType.Text;

                OracleDataReader DataRead = ShowBNO.ExecuteReader();

                DataRead.Read();

                while (DataRead.Read())
                {
                    bno.Items.Add(DataRead.GetString(1));
                }

                ShowBNO.Dispose();

                //_sql = "SELECT * FROM branch";
               // OracleDataAdapter _adapterObj = new OracleDataAdapter(_sql, _connObj);
              //  _adapterObj.Fill(_ds);
                _connObj.Close();
                _connObj.Dispose();
                _connObj = null;

                bno.DataSource = _ds.Tables[0] ;
                dataGridView1.DataSource = _ds.Tables[0];

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

    protected void AgentBtnClick(object sender, EventArgs e)
    {
        long iAgentId;
        long iOfficePhone;
        long iPager;
        long iCellPhone;
        long iHomePhone;
        DateTime dHireDate;
        string sTitle;
        string sLastName;
        string sFirstName;
        string sMiddleInital;

        s = "Data Source=nova; User Id=#######; Password=#######";
        con = new OracleConnection(s);
        con.Open();
        cmd = new OracleCommand();
        cmd.Connection = con;

        iAgentId = Convert.ToInt64(agentIdTxtBox.Text);
        iAgentId = int.Parse(agentIdTxtBox.Text);
        iOfficePhone = Convert.ToInt64(agentOfficePhTxtBox.Text);
        iOfficePhone = int.Parse(agentOfficePhTxtBox.Text);
        iPager = Convert.ToInt64(agentPagerTxtBox.Text);
        iPager = int.Parse(agentPagerTxtBox.Text);
        iCellPhone = Convert.ToInt64(agentCellPhTxtBox.Text);
        iCellPhone = Int64.Parse(agentCellPhTxtBox.Text);
        iHomePhone = Convert.ToInt64(agentHomePhTxtBox.Text);
        iHomePhone = int.Parse(agentHomePhTxtBox.Text);
        dHireDate = DateTime.Parse(agentHireDtTxtBox.Text);
        sTitle = agentTitleTxtBox.Text;
        sLastName = agentLastNameTxtBox.Text;
        sFirstName = agentFirstNameTxtBox.Text;
        sMiddleInital = agentMidInitTxtBox.Text;

        cmd.CommandText = "INSERT INTO AGENT VALUES(" + iAgentId + "," + iOfficePhone +
            "," + iPager + "," + iCellPhone + ", " + iHomePhone + ", Trunc(TO_DATE('" + dHireDate.ToString("dd-MMM-yyyy") + "','DD-MON-YYYY')), '" +
            sTitle + "', '" + sLastName + "', '" + sFirstName + "', '" + sMiddleInital +"')";

        int rowsUpdated = cmd.ExecuteNonQuery();

        if (rowsUpdated == 0)
            Result.InnerText = "Record not inserted";
        else
            Result.InnerText = "Information Submitted Successfully";
        con.Dispose();

        System.Threading.Thread.Sleep(50);
        Response.Redirect("Insert.aspx");
    }

        public void insertUserOracle(string user, string password) 
        {
            Parametros p = new Parametros();
            using (OracleConnection con = new OracleConnection(p.oracleConnString().ToString()))
            {
                con.Open();

                OracleCommand cmd = con.CreateCommand();
                cmd.CommandText = "INSERT INTO USUARIO(IDUSUARIO, EMAIL, PASSWORD, USERNAME) VALUES(SEQ_CLIENTE.nextval,'" + user + "','" + password + "','" + user + "')";
                cmd.ExecuteNonQuery();

                con.Close();
                con.Dispose();
            }
        }

        public static bool TableExists(string name, OracleConnection con)
        {
            string sql = "SELECT count(*) AS c FROM " + name;
            string count = string.Empty;
            long output;

            using (OracleCommand command = new OracleCommand(sql, con))
            {
                try
                {
                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        try
                        {
                            while (reader.Read())
                            {
                                count = reader["c"].ToString();
                            }
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                }
                catch (Exception ex)
                {
                    if (!ex.Message.Contains("ORA-00942"))
                    {
                        throw new Exception(string.Format(Resources.NoSniff, name), ex);
                    }
                }
            }

            con.Close();
            con.Dispose();

            if (Int64.TryParse(count, out output))
            {
                return true;
            }

            return false;
        }

    public int checkExistance(int employeeid)
    {
        OracleConnection con = new OracleConnection(cnString);

        con.Open();

        OracleCommand cmd = new OracleCommand("Select * from employeess where employeeid=" + employeeid + "", con);

        OracleDataReader dr;

        try
        {

            dr = cmd.ExecuteReader();

            if (dr.Read())
            {

                return 1;

            }

            else

                return 0;

           }

        catch (Exception ex)
        {

            throw;

        }

        finally
        {

            con.Close();

            con.Dispose();

        }
    }

        // GET: WebView
        public ActionResult Fakebook()
        {
            Console.WriteLine("fakebook");

            string oradb = "Data Source=(DESCRIPTION="
             + "(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))"
             + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
             + "User Id=monzy;Password=123456;";
            OracleConnection conn = new OracleConnection(oradb);
            try
            {
                conn.Open();
                string selectStr = "select * from test1";
                string insertStr = "insert into test(t) values(500)";
                OracleCommand cmd = new OracleCommand(insertStr, conn);
                //OracleDataReader dr = cmd.ExecuteReader();
                //dr.Read();
                int insertedLines = cmd.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
                //return View("insertedLines: " + insertedLines);
            }
            catch (OracleException ex) // catches only Oracle errors
            {
                switch (ex.Number)
                {
                    case 1:
                        //return "Error attempting to insert duplicate data.";
                    case 12545:
                        //return "The database is unavailable.";
                    default:
                        return View();
                        //return "Database error: " + ex.Message.ToString();
                }
            }
            catch (Exception ex) // catches any error not previously caught
            {
                Console.Write("Other exception");
            }
            Console.WriteLine("success");

            return View();
        }

 //新增照片
 public string AddPhoto(string id,string address)
 {
     OracleConnection conn = new OracleConnection(oradb);
     try
     {
         conn.Open();
         string insertStr = "insert into photo(photoId,albumId,photoAddress) values('"+ id+"','"
             +(Session["currentAlbumId"] as string) + "','"
             +address +"')";
         OracleCommand cmd = new OracleCommand(insertStr, conn);
         int insertedLines = cmd.ExecuteNonQuery();
         conn.Close();
         conn.Dispose();
         return "new PHOTO:" + (Session["username"] as string);
     }
     catch (Exception ex)
     {
         return "exception";
     }
 }

    public void BookItem(string username, string password)
    {
        //Creating Connection with SQL Server Database

        OracleConnection con = new OracleConnection(cnString);

        con.Open();
        //store procedure name
        OracleCommand cmd = new OracleCommand("user_login", con);

        cmd.CommandType = CommandType.StoredProcedure;

        try
        {
            //inserting with data procedure paramenters
            cmd.Parameters.Add("firstname", OracleDbType.Varchar2, username, ParameterDirection.Input);
            cmd.Parameters.Add("lastname", OracleDbType.Varchar2, password, ParameterDirection.Input);

            cmd.ExecuteNonQuery();

        }

        catch (Exception ex)
        {

            throw ex;

        }

        finally
        {

            con.Close();

            con.Dispose();

        }
    }

    /**Funcion de insercion a la DB*/
    private int oracleInsert(ArrayList q)
    {
        /**Almacenamos data en BD*/
        int count = 0;
        OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString)/*"Data Source=localhost;Persist Security Info=True;User ID=webadmin;Password=webadmin;Unicode=True")*/;
        conn.Open();
        OracleTransaction t = conn.BeginTransaction();
        try
        {
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.Transaction = t;
            cmd.CommandType = CommandType.Text;
            //Ejecutamos cada insert en la lista
            foreach (String s in q)
            {
                cmd.CommandText = s;
                count += cmd.ExecuteNonQuery();
            }
            //Si fue exitoso, Commit
            t.Commit();
        }
        catch (Exception ex)
        {
            //Sino rollback
            Response.Write(ex);
            t.Rollback();
        }
        finally
        {
            //Cerramos conexion
            t.Dispose();
            conn.Dispose();
            conn.Close();

        }
        return count;
    }