27 de julio de 2012

Clase ACME en C# -SQL con SP

Anteriormente mostré un sencillísimo ejemplo de clase ACME para C# en el cual no se utilizaban procedimientos almacenados para realizar operaciones sobre los registros de una tabla en una base de datos:
Para empezar este ejemplo, puesto que estoy utilizando SQL Xpress 2008, hare los procedimientos almacenados sobre mi base de datos llamada registro utilizando el Microsoft SQl Server Management 2008


esta es la tabla empleado que utilizaremos, se encuentra en la base de datos registro y tiene la siguiente estructura

Lo que sigue es crear una nueva consulta y a continuación esta el código T-SQL para crear los procedimientos almacenados en la base de datos registro sobre la tabla empleado

 ***** Procedimiento Almacenado AgregarDatos *****
Este será el procedimiento almacenado que se utilizara para agregar registros de un nuevo empleado a la base de datos, se tiene un parámetro output que regresa un mensaje dependiendo si la acción de insertar datos se realizó con éxito o si hubo algún problema.

Se muestra el uso de TRANSACTION aunque se trabaja sobre una sola tabla, se puede omitir el uso de este pero en caso se trabaje con base de datos relacionales puede venir en uso.

USE registro
GO
IF OBJECT_ID('AgregarDatos','p')is not null
DROP PROCEDURE AgregarDatos
GO

CREATE PROCEDURE AgregarDatos
@nombres varchar(50),
@cargo varchar(50),
@departamento varchar(50),
@jefe varchar(50),
@mensaje varchar(100) output
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
INSERT INTO empleado VALUES(@nombres, @cargo, @departamento, @jefe)
COMMIT
SELECT @mensaje='Registro ingresado correctamente en Base de Datos !!!'
END TRY

BEGIN CATCH
ROLLBACK
SELECT @mensaje='Registro no ingresado en Base de Datos !!!'
END CATCH

***** Procedimiento Almacenado ModificarDatos *****
Este será el procedimiento almacenado que se utilizara para modificar registros de empleados existentes en la base de datos, se tiene un parámetro output que regresa un mensaje dependiendo si la acción de modificar datos se realizó con éxito o si hubo algún problema.

 USE registro
GO
if OBJECT_ID('ModificarDatos','p') is not null
DROP PROC ModificarDatos
GO

CREATE PROC ModificarDatos
@codigo int,
@nombres varchar(50),
@cargo varchar(50),
@departamento varchar(50),
@jefe varchar(50),
@mensaje varchar(100) output
AS
SET NOCOUNT ON
IF exists (select * from empleado where Codigo=@codigo)
BEGIN TRY
BEGIN TRAN
UPDATE empleado SET Nombres=@nombres, Cargo=@cargo,Departamento=@departamento, Jefe=@jefe WHERE Codigo=@codigo
COMMIT
SET @mensaje='El registro fue actualizado correctamente !!!'
END TRY

BEGIN CATCH
ROLLBACK
SET @mensaje='El registro no fue actualizado !!!'
END CATCH
ELSE
BEGIN
SET @mensaje='El registro no existe en la base de datos !!!'
END

***** Procedimiento Almacenado EliminarDatos *****
Este será el procedimiento almacenado que se utilizara para eliminar registros de empleados existentes en la base de datos, se tiene un parámetro output que regresa un mensaje dependiendo si la acción de eliminar datos se realizó con éxito o si hubo algún problema.

 USE registro
GO
IF OBJECT_ID('EliminarDatos','p')is not null
DROP PROC EliminarDatos
GO

CREATE PROCEDURE EliminarDatos
@codigo int,
@mensaje varchar(100) output
AS
SET NOCOUNT ON
IF EXISTS (select * from empleado where Codigo=@codigo)
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM empleado WHERE Codigo=@codigo
COMMIT
SELECT @mensaje='Registro eliminado correctamente de Base de Datos !!!'
END TRY

BEGIN CATCH
ROLLBACK
set @mensaje='Registro no fue eliminado de la Base de Datos !!!'
END CATCH
ELSE
BEGIN
SET @mensaje='Registro no se puede eliminar pues no existe !!!'
END

Ahora muestro el formulario que utilizare, algo a tomar en cuenta es que los datos de la conexión a la base de datos los guardo en un app.config, aquí dejo el link que lo explica: http://pabletoreto.blogspot.com/search/label/App.Config 


Evento Form_Load
Al cargar el formulario lo primero que se hará es llenar el DataGridView con los datos de la tabla empleado y habilitar el Bindingnavigator para poder moverse a través de los registros de la tabla, esto se logra invocando al método CargarDatos el cual además crea un enlace entra el BindingNavigator y los textBox para mostrar la información correspondiente

private void Form1_Load(object sender, EventArgs e)
        {
            CargarDatos();
        }

Función Connectar() y Desconnectar()
Lo único que hace esta función es realizar la conexión a la base de datos y regresar un objeto tipo SqlConnection, a la vez el metodo Desconnectar() recibe un objeto tipo SqlConnection y cierra la conexión, obteniendo la información del app.config tal como muestra el connectionstrings

<connectionStrings>
        <add name="conexion"
            connectionString="Data Source=.\LINGONET;Initial Catalog=registro;Integrated Security=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>

Y este es el código C# para la función Connectar() y el metodo Desconnectar()

private SqlConnection Connectar() { SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["conexion"].ConnectionString);
     cnn.Open();
     return cnn;
        }

private void Desconnectar(SqlConnection cnn) {
      if (cnn.State == ConnectionState.Open) {
      cnn.Close();
            }
        }

Metodo CargarDatos()

 private void CargarDatos() {
            String sql = "select * from empleado";
            SqlConnection cnn = Connectar();
            BindingSource bs=new BindingSource();
            DataTable dt=new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sql, cnn);
            Desconnectar(cnn);
            da.Fill(dt);
            bs.DataSource = dt;
            BNV.BindingSource = bs;
            DGV.DataSource = bs;
            CodigoText.DataBindings.Clear();
            CodigoText.DataBindings.Add(new Binding("text", bs, "Codigo"));
            NombresText.DataBindings.Clear();
            NombresText.DataBindings.Add(new Binding("text", bs, "Nombres"));
            CargoText.DataBindings.Clear();
            CargoText.DataBindings.Add(new Binding("text", bs, "cargo"));
            DptoText.DataBindings.Clear();
            DptoText.DataBindings.Add(new Binding("text", bs, "departamento"));
            JefeText.DataBindings.Clear();
            JefeText.DataBindings.Add("text", bs, "Jefe");
        }

Evento click botón AgregarDatos

private void BAgregar_Click(object sender, EventArgs e){
try{
SqlConnection cnn = Connectar();
SqlCommand cmd = new SqlCommand("AgregarDatos", cnn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add("@nombres", SqlDbType.VarChar, 50).Value = NombresText.Text;
cmd.Parameters.Add("@cargo", SqlDbType.VarChar, 50).Value = CargoText.Text;
cmd.Parameters.Add("@departamento", SqlDbType.VarChar, 50).Value = DptoText.Text;
cmd.Parameters.Add("@jefe", SqlDbType.VarChar, 50).Value = JefeText.Text;
cmd.Parameters.Add("@mensaje", SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;
    cmd.ExecuteNonQuery();
    String mensaje = Convert.ToString(cmd.Parameters["@mensaje"].Value);
    MessageBox.Show(mensaje);
    CargarDatos();
    Desconnectar(cnn);
    cmd.Dispose();
    }catch (Exception ex) {
     MessageBox.Show(ex.Message);
     }
    }


Evento click botón ModificarDatos

private void BModificar_Click(object sender, EventArgs e){
try{
SqlConnection cnn = Connectar();
SqlCommand cmd = new SqlCommand("Modificardatos", cnn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@codigo", CodigoText.Text);
cmd.Parameters.AddWithValue("@nombres", NombresText.Text);
cmd.Parameters.AddWithValue("@cargo", CargoText.Text);
cmd.Parameters.AddWithValue("@departamento", DptoText.Text);
cmd.Parameters.AddWithValue("@jefe", JefeText.Text);
cmd.Parameters.Add("@mensaje", SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;
    cmd.ExecuteNonQuery();
    String mensaje = Convert.ToString(cmd.Parameters["@mensaje"].Value);
    MessageBox.Show(mensaje);
    CargarDatos();
    Desconnectar(cnn);
    cmd.Dispose();
    }catch (Exception ex) {
     MessageBox.Show(ex.Message);
     }
    }


Evento click botón EliminarDatos

private void BEliminar_Click(object sender, EventArgs e){
try{
SqlConnection cnn = Connectar();
SqlCommand cmd = new SqlCommand("EliminarDatos", cnn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@codigo", CodigoText.Text);
cmd.Parameters.Add("@mensaje", SqlDbType.VarChar, 100).Direction = ParameterDirection.Output;
    cmd.ExecuteNonQuery();
    String mensaje=Convert.ToString(cmd.Parameters["@mensaje"].Value);
    MessageBox.Show(mensaje);
    CargarDatos();
    Desconnectar(cnn);
    cmd.Dispose();
    }catch (Exception ex) {
     MessageBox.Show(ex.Message);
     }
    }

eso es todo :)