C# OracleCommand.Dispose方法代码示例

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

 private void button1_Click(object sender, EventArgs e)
 {
     if (string.IsNullOrWhiteSpace(this.textBox1.Text) || string.IsNullOrWhiteSpace(this.textBox2.Text) || string.IsNullOrWhiteSpace(richTextBox1.Text))
     {
         MessageBox.Show("Fill all the requierd field correctly", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
     }
     else
     {
         try
         {
             conn.Open();
             string sqlquery = "INSERT INTO MFG_INFO(MGF_NAME,MFG_ADD,MFG_PHN) VALUES('" + textBox1.Text + "','" + richTextBox1.Text + "','" + textBox2.Text + "')";
             OracleCommand cmd = new OracleCommand(sqlquery,conn);
             int i = cmd.ExecuteNonQuery();
             if (i > 0)
             {
                 cmd.Dispose();
                 this.Hide();
                 adminControls adc = new adminControls();
                 adc.Show();
             }
         }
         catch (Exception exe)
         {
             MessageBox.Show(exe.Message);
         }
     }
 }

        public List<Profesional> buscarProfesional(string valorBusqueda)
        {
            base.abrirConexion();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conexion;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "profesionales_paquete.buscarProfesional";
            OracleParameter valorBusquedaP = new OracleParameter("valorBusqueda", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
            valorBusquedaP.Value = valorBusqueda;
            OracleParameter cursor_datos = new OracleParameter("cursor_datos", OracleDbType.RefCursor, System.Data.ParameterDirection.Output);
            cmd.Parameters.AddRange(new OracleParameter[] { valorBusquedaP, cursor_datos});
            List<Profesional> profesionalesConsulta = new List<Profesional>();
            OracleDataReader lectorDatos = cmd.ExecuteReader();
            if (lectorDatos.HasRows)
            {
                while (lectorDatos.Read())
                {
                    profesionalesConsulta.Add(new Profesional()
                    {
                        id = lectorDatos.GetString(0),
                        password = lectorDatos.GetString(1),
                        nombre = lectorDatos.GetString(2),
                        apellidos = lectorDatos.GetString(3),
                        cedula = lectorDatos.GetString(5),
                        correo = lectorDatos.GetString(6),
                        area = lectorDatos.GetString(7)
                    });
                }
                lectorDatos.Close();
                cmd.Dispose();
                base.cerrarConexion();

            }
            return profesionalesConsulta;
        }

 public void adicionarProfesional(string idProfesional,string passwordProfesional,string nombreProfesional,string apellidosProfesional,string cedulaProfesional ,string correoProfesional ,string areaProfesional)
 {
     base.abrirConexion();
     OracleCommand cmd = new OracleCommand();
     cmd.Connection = conexion;
     cmd.CommandType = System.Data.CommandType.StoredProcedure;
     cmd.CommandText = "profesionales_paquete.adicionarProfesional";
     OracleParameter idProfesionalP = new OracleParameter("idProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     idProfesionalP.Value = idProfesional;
     OracleParameter passwordProfesionalP = new OracleParameter("passwordProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     passwordProfesionalP.Value = passwordProfesional;
     OracleParameter nombreProfesionalP = new OracleParameter("nombreProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     nombreProfesionalP.Value = nombreProfesional;
     OracleParameter apellidosProfesionalP = new OracleParameter("apellidosProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     apellidosProfesionalP.Value = apellidosProfesional;
     OracleParameter cedulaProfesionalP = new OracleParameter("cedulaProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     cedulaProfesionalP.Value = cedulaProfesional;
     OracleParameter correoProfesionalP = new OracleParameter("correoProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     correoProfesionalP.Value = correoProfesional;
     OracleParameter areaProfesionalP = new OracleParameter("areaProfesional", OracleDbType.Varchar2, System.Data.ParameterDirection.Input);
     areaProfesionalP.Value = areaProfesional;
     cmd.Parameters.AddRange(new OracleParameter[] { idProfesionalP, passwordProfesionalP, nombreProfesionalP, apellidosProfesionalP, cedulaProfesionalP, correoProfesionalP, areaProfesionalP });
     cmd.ExecuteNonQuery();
     cmd.Dispose();
     base.cerrarConexion();
 }

        public void editarAtencionCliente(string idAC, string passwordAC, string nombreAC, string apellidosAC, string cedulaAC, string correoAC)
        {
            base.abrirConexion();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conexion;
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.CommandText = "atencion_cliente_paquete.editarAtencionCliente";

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

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

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

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

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

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

            cmd.Parameters.AddRange(new OracleParameter[] { idACP,passwordACP, nombreACP, apellidosACP, cedulaACP, correoACP });

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

        /// <summary>  
        /// 执行数据库事务查询操作,返回受影响的行数  
        /// </summary>  
        /// <param name="transaction">数据库事务对象</param>  
        /// <param name="cmdType">Command类型</param>  
        /// <param name="cmdText">Oracle存储过程名称或PL/SQL命令</param>  
        /// <param name="commandParameters">命令参数集合</param>  
        /// <returns>当前事务查询操作影响的数据行数</returns>  
        public static int ExecuteNonQuery(OracleTransaction transaction, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            OracleCommand command = new OracleCommand();
            OracleConnection connection = transaction.Connection;
            int result = 0;

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

            return result;
        }

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

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

        private void button3_Click(object sender, EventArgs e)
        {
            DialogResult dialogResult = MessageBox.Show("Are You Sure", "Some Title", MessageBoxButtons.YesNo);
            if (dialogResult == DialogResult.Yes)
            {
                try
                {
                    conn.Open();
                    string sqlquery = "DELETE FROM MED_INFO WHERE MED_ID='" + ni + "'";
                    OracleCommand cmd = new OracleCommand(sqlquery, conn);
                   cmd.ExecuteNonQuery();
                   cmd.Dispose();

                   sqlquery = "DELETE FROM MED_STORE WHERE MED_ID='" + ni + "'";
                   cmd = new OracleCommand(sqlquery, conn);
                    cmd.ExecuteNonQuery();
                   cmd.Dispose();

                    conn.Close();
                }
                catch (Exception exe)
                {
                    MessageBox.Show(exe.Message);
                }
                this.Hide();
            }
            else if (dialogResult == DialogResult.No)
            {
                ///
            }
        }

    /// <summary>
    /// Метод, реализующий insert-запрос в базу данных Oracle
    /// </summary>
    /// <param name="cmdQuery">Текст sql-запроса</param>
    public static void Insert(string cmdQuery)
    {
        _logger.WriteLine(cmdQuery);
        try
        {
            _open();

            OracleCommand cmd = new OracleCommand(cmdQuery, _conn);

            cmd.ExecuteNonQuery();
            cmd.Dispose();
            _logger.WriteLine("Удачно!");
        }
        catch (TimeoutException)
        {
            throw;
        }
        catch (OracleException ex)
        {
            string mess = "Ошибка в запросе к БД!" + Environment.NewLine + ex;
            Message.Show(mess);
            _logger.WriteError(mess);
            throw new BadQueryExeption();
        }
        catch (Exception ex)
        {
            string mess = "Ошибка в запросе к БД!" + Environment.NewLine + ex;
            _logger.WriteError(mess);
            Message.Show(mess);
        }
        finally
        {
            _close();
        }
    }

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

        public static Employee GetInfo(OracleDataReader reader, DPFP.Sample Sample, string Finger)
        {
            OracleConnection conn = new OracleConnection(ConStr);
            Employee _info = null;
            DPFP.Template _template = null;
            bool IsFound = false;
            if (reader[Finger] != DBNull.Value)
            {
                _template = Util.ProcessDBTemplate((byte[])reader[Finger]);
                IsFound = Util.Verify(Sample, _template);
            }
            if (IsFound == true)
            {
                string sqlEmp = "select * from employees where Empl_Id_No=" + reader["Empl_Id_No"];
                OracleCommand cmd = new OracleCommand(sqlEmp, conn);
                cmd.CommandType = CommandType.Text;
                conn.Open();
                OracleDataReader odr = cmd.ExecuteReader();
                if (odr.HasRows)
                {
                    _info = new Employee();
                    _info.Empl_Id_No = Convert.ToInt32(reader["Empl_Id_No"]);
                    _info.Empl_Name = (string)odr["Empl_Name"];
                    _info.Empl_Deptname = (string)odr["Empl_Deptname"];
                    _info.Shift_Id = Convert.ToInt32(odr["Shift_Id"]);
                }
                odr.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }

            return _info;
        }

        public int ExecuteNonQuery(List<string> sqlList)
        {
            int rowsUpdated = 0;
            try
            {
                var con = CONNECTION.OpenCon();

                foreach (var sql in sqlList)
                {
                    var cmd = new OracleCommand(sql, con);
                    rowsUpdated += cmd.ExecuteNonQuery();

                    cmd.Dispose();
                }

                CONNECTION.CloseCon(con);
            }
            catch (Exception ex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "ExecuteNonQuery Error!",
                    Ex = ex,
                });
                return -1;
            }

            return rowsUpdated;
        }

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

 /// <summary>
 /// 查询
 /// </summary>
 /// <param name="sql">SQL语句</param>
 /// <returns></returns>
 private static DataTable Select(string sql)
 {
     OracleCommand cmd = new OracleCommand(sql, Connection);
     OracleDataAdapter oda = new OracleDataAdapter(cmd);
     DataTable dt = new DataTable();
     oda.Fill(dt);
     cmd.Dispose();
     return dt;
 }