21 de enero de 2015

C# - Oracle: Procedimientos Almacenados

Obtener Conjunto de Resultados Procedimientos Almacenados.

En Oracle no puedes recuperar un conjunto de resultados utilizando solamente la sentencia select * from... esto se logra utilizando el tipo de datos REF CURSOR y para utilizarlo a fin de recuperar el conjunto de resultados, se debe definir un parámetro de salida CURSOR REF en el procedimiento para pasar el cursor de nuevo a su aplicación.

Ahora vamos a definir un procedimiento que se abre y envía una variable de cursor para nuestra aplicación, se utilizara la tabla BLOGTABLE y la conexión a la base de datos lingonet creada en la primera parte de esta entrada, encuentrala aquí, se crearan las definiciones en un paquete al que llamo pabletoreto, antes de continuar,la definición de paquete:

Un Paquete es un objeto PL/Sql dividido en dos partes(especificación y cuerpo) que agrupa lógicamente otros objetos PL/Sql relacionados entre sí, encapsulándolos y convirtiéndolos en una unidad dentro de la base de datos.

Para acceder a los elementos declarados en un paquete basta con anteceder el nombre del objeto referenciado con el nombre del paquete donde está declarado y un punto, de esta manera: Paquete.Objeto donde Objeto puede ser un tipo, una variable, un cursor, un procedimiento o una función declarados dentro del paquete.

ahora vamos al procedimiento almacenado

create or replace package pabletoreto
as
 type t_cursor is ref cursor;
 procedure TodosDatos(cursorDatos out t_cursor);
end pabletoreto;

create or replace package body pabletoreto
as
 procedure TodosDatos(cursorDatos out t_cursor)
 is
 begin
  open cursorDatos for select * from BLOGTABLE;
 end TodosDatos;
end pabletoreto;

y desde el formulario lo llamamos así...recuerden revisar la primera parte de esta entrada para referencias, estoy utilizando el patron Singleton para obtener los valores de user y password que necesita la cadena de conexión, mira la primera entrada aquí

private void button1_Click(object sender, EventArgs e){
            OracleDataAdapter objAdapter = new OracleDataAdapter();
            DataTable dt = new DataTable();
            OracleCommand objSelectCmd = new OracleCommand();
            objSelectCmd.Connection = Conectar();
            objSelectCmd.CommandText = "pabletoreto.TodosDatos";
            objSelectCmd.CommandType = CommandType.StoredProcedure;
            objSelectCmd.Parameters.Add("cur_employees", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            objAdapter.SelectCommand = objSelectCmd;
            objAdapter.Fill(dt);
            dataGridView1.DataSource = dt;
        }

        private OracleConnection Conectar() {
            string username = Registro.Instance.Username;
            string password = Registro.Instance.Password;
            string CadenaConexion = ConfigurationManager.ConnectionStrings["conexion"].ConnectionString;
            CadenaConexion = string.Format(CadenaConexion, username, password);
            OracleConnection conn = new OracleConnection();
            conn.ConnectionString = CadenaConexion;
            try{
                conn.Open();
            }
            catch (Exception ex){
               MessageBox.Show(ex.Message);
            }
            return conn;
        }

CRUD con Procedimientos Almacenados PL/SQL

Voy a modificar el diseño del Form1.cs que hemos venido utilizando a fin de tener las funciones de agregar, modificar, eliminar, consultar y mostrar en una sola ventana, el diseño se muestra:


como ven hay un comboBox para definir el campo cargo de la tabla BLOGTABLE, para hacerlo interesante creare una nueva tabla en la base de datos Lingonet a la que llamare Cargo, los campos de la tabla serán:


y sobre esta tabla se ingresaran datos tal como muestro...puros gerentes agregue

Listo, ahora vamos a agregar al paquete pabletoreto los procedimientos almacenados IngresarDato, ModificarDato, EliminarDato y BuscarDato

create or replace package pabletoreto
as
 type t_cursor is ref cursor;
 procedure Cargar_Datos_CBX(cursorCargar out t_cursor);
 procedure Cargar_Datos_DGV(cursorDatos out t_cursor);
 procedure insert_Datos(id_ varchar2, nombre_ varchar2, cargo_ varchar2);
 procedure update_Datos(id_ varchar2, nombre_ varchar2, cargo_ varchar2);
 procedure delete_Datos(id_ varchar2);
end pabletoreto;


create or replace package body pabletoreto
as
procedure Cargar_Datos_CBX(cursorCargar out t_cursor)
is
BEGIN
open cursorCargar for select IDCARGO ,
CARGO  from CARGOS;
end Cargar_Datos_CBX;

procedure Cargar_Datos_DGV(cursorDatos out t_cursor)
 is
 begin
   open cursorDatos for select ID, NOMBRE, CARGO from BLOGTABLE;
 end Cargar_Datos_DGV;

 procedure insert_Datos(id_ varchar2, nombre_ varchar2, cargo_ varchar2)
 is
 begin
   insert into BLOGTABLE(ID, NOMBRE, CARGO)
   values(id_,nombre_, cargo_);
   Commit;

Exception
when others then
RAISE_APPLICATION_ERROR(-20100, 'Error en insert_Datos: '||SQLERRM);
end insert_Datos;

 procedure update_Datos(id_ varchar2, nombre_ varchar2, cargo_ varchar2)
 is
 begin
   update BLOGTABLE
   set NOMBRE=nombre_, CARGO=cargo_
   where ID=id_;
   Commit;

Exception
when others then
RAISE_APPLICATION_ERROR(-20100, 'Error en update_Datos: '||SQLERRM);
end update_Datos;

procedure delete_Datos(id_ varchar2)
 is
 begin
    delete from BLOGTABLE
    where ID=id_;
       Commit;

Exception
when others then
RAISE_APPLICATION_ERROR(-20100, 'Error en Delete_Datos: '||SQLERRM);
 end delete_Datos;
end pabletoreto;

Ahora mostrare el código para la opción de ingresar datos a la tabla BLOGTABLE utilizando el procedimiento almacenado procedure insert_Datos(id_ varchar2, nombre_ varchar2, cargo_ varchar2); pueden tomar ese como ejemplo para utilizar los procedimientos almacenados para modificar y eliminar datos pues lo unico que hace falta es enviar los parametros que cada procedimiento necesite para realizar las operaciones sobre la base de datos.

private void btxIngresar_Click(object sender, EventArgs e){
            string id = Convert.ToString(txtID.Text.Trim());
            string nombre = Convert.ToString(txtNombre.Text.Trim());
            string cargo = cbxCargo.Text;

            if ((string.IsNullOrEmpty(id)) || (string.IsNullOrEmpty(nombre) || (string.IsNullOrEmpty(cargo))))
            {
                MessageBox.Show("Favor especificar todos los campos");
                return;
            }

            OracleDataAdapter objAdapter = new OracleDataAdapter();
            DataTable dt = new DataTable();
            OracleCommand objSelectCmd = new OracleCommand();
      using (OracleConnection cnn = Conectar())
          {
           objSelectCmd.Connection = cnn;
           objSelectCmd.CommandText = "pabletoreto.insert_Datos";
           objSelectCmd.CommandType = CommandType.StoredProcedure;
           objSelectCmd.Parameters.Add("id_", OracleDbType.Varchar2, 20).Value = id;
           objSelectCmd.Parameters.Add("nombre_", OracleDbType.Varchar2, 20).Value = nombre;
           objSelectCmd.Parameters.Add("cargo_", OracleDbType.Varchar2, 20).Value = cargo;
      
      try{  objSelectCmd.ExecuteNonQuery(); }
      catch (Exception ex){ MessageBox.Show(ex.Message); }
                 Datos();
            }
        }

y el metodo Datos() que se muestra al final del evento Click del boton se utiliza para cargar el DataGridView con los datos actualizados de la tabla BLOGTABLE, el codigo es el siguiente:

private void Datos() {
            OracleDataAdapter objAdapter = new OracleDataAdapter();
            DataTable dt = new DataTable();
            OracleCommand objSelectCmd = new OracleCommand();
      using (OracleConnection cnn = Conectar())
            {
                objSelectCmd.Connection = cnn;
                objSelectCmd.CommandText = "pabletoreto.Cargar_Datos_DGV";
                objSelectCmd.CommandType = CommandType.StoredProcedure;
                objSelectCmd.Parameters.Add("cursorDatos", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                objAdapter.SelectCommand = objSelectCmd;
                objAdapter.Fill(dt);
                dataGridView1.DataSource = dt; 
            }
         }

por ultimo, para cargar los datos del ComboBox(que muestra los cargos a los que u empleado puede optar desde la tabla CARGOS)  utiliza el siguiente codigo el cual pueden colocar en el evento Load

private void CargarCbx(){
            OracleDataAdapter objAdapter = new OracleDataAdapter();
            DataTable dt = new DataTable();
            OracleCommand objSelectCmd = new OracleCommand();
            using (OracleConnection cnn = Conectar()){
                objSelectCmd.Connection = cnn;
                objSelectCmd.CommandText = "pabletoreto.Cargar_Datos_CBX";
                objSelectCmd.CommandType = CommandType.StoredProcedure;
                objSelectCmd.Parameters.Add("cursorCargar", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                objAdapter.SelectCommand = objSelectCmd;
                objAdapter.Fill(dt);
                cbxCargo.DataSource = dt;
                cbxCargo.DisplayMember = "CARGO";
                cbxCargo.ValueMember = "IDCARGO";
            }

        }

el funcionamiento de los procedimientos para actualizar y eliminar datos de la tabla BLOGTABLE solo trata de capturar y enviar los parámetros adecuados, queda a su pendiente.