[ADO.NET] – Parte 5 – Operaciones CRUD

 

Introducción

---

El objetivo de este articulo será el de demostrar con un ejemplo simple como hacer uso de ado.net para trabajar de forma simple con los datos, realizando operación de CRUD (Create, Read, Update and Delete) .

Si bien esta aplicación de ejemplo hace uso del código desarrollado directamente en el propio formulario, se vera que el uso de funciones en la estructurada ordena el código dejándolo mas legible, permitiendo además mover la funcionalidad de lugar si en el futuro hiciera falta sin afectar el funcionamiento.

La aplicación de ejemplo solo cuenta con un formularios, que observaran en esta imagen

 

1 – Obtener información

---

Si se analiza el código se vera que es por medio de ingreso de un id valido en el campo que corresponde y mediante la acción de la tecla Enter se realiza la búsqueda.

[C#]

private void txtId_KeyPress(object sender, KeyPressEventArgs e)
{
    if((int)e.KeyChar == (int)Keys.Enter)
    {
        //
        // Validaciones
        //
        errProvider.SetError(txtId, "");

        int Id = 0;
        if (!int.TryParse(txtId.Text, out Id))
        {
            errProvider.SetError(txtId, "El Id debe ser un valor numerico");
            return;
        }

        if (!Exists(Id))
        {
            errProvider.SetError(txtId,"El Id ingresado no existe.");
            return;
        }

        Obtener(Id);
    }

}

private void Obtener(int Id)
{
    string sql = @"SELECT Id
                          ,NombreCompleto
                          ,Direccion
                          ,FechaNacimiento
                      FROM Contactos
                      WHERE Id = @Id";


    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {

        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddWithValue("Id", Id);

        conn.Open();

        SqlDataReader reader = command.ExecuteReader();

        if (reader.Read())
        {
            txtNombre.Text = Convert.ToString(reader["NombreCompleto"]);
            txtDireccion.Text = Convert.ToString(reader["Direccion"]);
            dtpFechaNanimiento.Value = Convert.ToDateTime(reader["FechaNacimiento"]);
        }

    }
}

private bool Exists(int Id)
{
    string sql = @"SELECT COUNT(*)
                      FROM Contactos
                      WHERE Id = @Id";


    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {

        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddWithValue("Id", Id);

        conn.Open();

        int count = Convert.ToInt32(command.ExecuteScalar());

        if (count == 0)
            return false;
        else
            return true;

    }
}

[VB.NET]

   Private Sub txtId_KeyPress(ByVal sender As Object, ByVal e As KeyPressEventArgs)

       If AscW(e.KeyChar) = CInt(Keys.Enter) Then
           '
           ' Validaciones
           '
           errProvider.SetError(txtId, "")

           Dim Id As Integer = 0
           If Not Integer.TryParse(txtId.Text, Id) Then
               errProvider.SetError(txtId, "El Id debe ser un valor numerico")
               Return
           End If

           If Not Exists(Id) Then
               errProvider.SetError(txtId, "El Id ingresado no existe.")
               Return
           End If

           Obtener(Id)
       End If

   End Sub

Private Sub Obtener(Id As Integer)
       Dim sql As String = "SELECT Id" & _
                               ",NombreCompleto" & _
                               ",Direccion" & _
                               ",FechaNacimiento " & _
                           "FROM Contactos " & _
                           "WHERE Id = @Id"


	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())

		Dim command As New SqlCommand(sql, conn)
		command.Parameters.AddWithValue("Id", Id)

		conn.Open()

		Dim reader As SqlDataReader = command.ExecuteReader()

		If reader.Read() Then
			txtNombre.Text = Convert.ToString(reader("NombreCompleto"))
			txtDireccion.Text = Convert.ToString(reader("Direccion"))
			dtpFechaNanimiento.Value = Convert.ToDateTime(reader("FechaNacimiento"))

		End If
	End Using
End Sub

Private Function Exists(Id As Integer) As Boolean
       Dim sql As String = "SELECT COUNT(*) " & _
                           "FROM Contactos " & _
                           "WHERE Id = @Id"


	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())

		Dim command As New SqlCommand(sql, conn)
		command.Parameters.AddWithValue("Id", Id)

		conn.Open()

		Dim count As Integer = Convert.ToInt32(command.ExecuteScalar())

		If count = 0 Then
			Return False
		Else
			Return True

		End If
	End Using
End Function	

En esta sección de código se observara la recuperación de una entidad en base al id ingresado por el usuario.

En el evento KeyPress del textbox que presenta al Id se detecta la presión de la tecla Enter como medio para indicar la búsqueda de un contacto.

Entre las validaciones a realizar se verifica que el id ingresado sea numérico, también se valida que el registro exista en la base de datos, para ello es que se realiza la primer consulta que esta encapsulada en la función Exists(), esta tomara el id ingresado y ejecutara una consulta que devolverá el numero de registros que coincidan en este caso con el id ingresado, si el valor es cero entonces el registro no existe.

El método ExecuteScalar() del objeto SqlCommand es ideal para estos casos ya que devolver el valor del primer registro y la primer columna, como en este caso solo hay un valor pues se hizo uso de la función SQL Count() devolverá dato simple.

Una vez validada la existencia se procede a recuperar el registro, para ello la función de Obtener() cumplirá la misión, requiriendo que se le pase como parámetro el id del registro a obtener

Como se observa este tiene una estructura similar a la Exists() solo que no usa el ExcecuteScalar(), sino que obtiene un SqlReader, usando ExecuteReader() del objeto SqlCommand.

En este caso se sabe con anticipación que solo será devuelto un registro no hace falta hacer un loop por cada registro, por lo general se utiliza la sentencia “while”, en este caso el if cumple la función perfectamente para posicionar el cursor del reader a la primer posición, y poder realizar la lectura, entonces el if cumple básicamente dos funciones:

- permite validad que hay registros que leer, si por algún fallo no hubiera registro al llegar al if pasaría por false.

- al ejecutar el Read() del objeto SqlReader, posiciona el cursos para su lectura

Por ultimo se iguala el valor de cada campo de la consulta, realizando la conversión de tipos y asignadla a los respectivos controles.

Como se habrá observado hasta aquí y también se notaran en los ejemplos siguientes, los pasos para usar los objetos de ado.net son bastante repetitivos:

1- Se crea un objeto SqlConnection para establecer la conexión hacia la base de datos

2- Se crea el objeto SqlCommand, para establecer la consulta que se realizara, y se le asignan a este los parámetros si es que hacen falta

3- en este punto existirá un bifurcación

3a- Si se usa un DataReader, se podrá ejecutar directamente mediante el ExecuteReader()

3b- Si se usa un DataSet o DataTable, será necesario utilizar un SqlDataAdapter, para realizar el Fill() del objeto a cargar con los datos provenientes de la consulta.

 

2 - Insertar nuevo registro

---

La operación de insertar registro tiene algunas particularidades que necesitas ser detalladas.

Tanto la operación de insertar o actualizar será ejecutada mediante el mismo botón a nivel de interfaz del usuario, para ello es que se identifica si el id que se define existe o no (usando la función Exists(), antes comentada), esto podrá ser visto en el evento Click del botón Guardar.

El primer punto que hay que aclarar es que en este primer ejemplo no se hace uso de un campo autonumérico (o Identity) para el id de la tabla. Es por ello que se deberá obtener por medio de código cual será el próximo numero identificador a utilizar.

Para esta operación es que existe la función MaxId(), la cual hace uso de otra función SQL, en este caso MAX() que retornada el valor máximo existente en la tabla para un determinado campo que indiquemos. En este caso por tratarse de un valor simple, nuevamente se usa el ExecuteScalar() del SqlCommand.

[C#]

        private void btnGuardar_Click(object sender, EventArgs e)
        {
            //
            // Validaciones
            //
            int Id = 0;
            if (!string.IsNullOrEmpty(txtId.Text))
            {
                if (!int.TryParse(txtId.Text, out Id))
                {
                    MessageBox.Show("El Id debe ser un valor numerico");
                    return;
                }
            }

            if (Exists(Id))
            {
                bool result = Update(Id, txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value);

                if (result)
                    MessageBox.Show("El registro se ha actualizado correctamente.");
            }
            else
            {
                bool result = Insert(txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value);
                
                if (result)
                    MessageBox.Show("El registro se ha insertado correctamente.");
            }
        }

        private bool Insert(string nombre, string direccion, DateTime fechaNacimiento)
        {
            string sql = @"INSERT INTO Contactos (Id
                                  ,NombreCompleto
                                  ,Direccion
                                  ,FechaNacimiento)
                              VALUES (@Id, 
                                    @Nombre, 
                                    @Direccion, 
                                    @FechaNacimiento)";


            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
            {
                int NextId = MaxId() + 1;

                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddWithValue("Id", NextId);
                command.Parameters.AddWithValue("Nombre", nombre);
                command.Parameters.AddWithValue("Direccion", direccion);
                command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento);

                conn.Open();

                int rowsAffected = command.ExecuteNonQuery();

                if (rowsAffected > 0)
                    return true;
                else
                    return false;

            }
        }
		

        private static int MaxId()
        {
            string sql = @"SELECT MAX(Id)
                              FROM Contactos";


            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
            {

                SqlCommand command = new SqlCommand(sql, conn);

                conn.Open();

                return Convert.ToInt32(command.ExecuteScalar());

            }
        }

 

[VB.NET]

Private Sub btnGuardar_Click(sender As Object, e As EventArgs)
	'
	' Validaciones
	'
	Dim Id As Integer = 0
	If Not String.IsNullOrEmpty(txtId.Text) Then
		If Not Integer.TryParse(txtId.Text, Id) Then
			MessageBox.Show("El Id debe ser un valor numerico")
			Return
		End If
	End If

	If Exists(Id) Then
		Dim result As Boolean = Update(Id, txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value)

		If result Then
			MessageBox.Show("El registro se ha actualizado correctamente.")
		End If
	Else
		Dim result As Boolean = Insert(txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value)

		If result Then
			MessageBox.Show("El registro se ha insertado correctamente.")
		End If
	End If
End Sub

Private Function Insert(nombre As String, direccion As String, fechaNacimiento As DateTime) As Boolean
	If Not ValidateForm() Then
		Return False
	End If

       Dim sql As String = "INSERT INTO Contactos (Id" & _
                                       ",NombreCompleto" & _
                                       ",Direccion" & _
                                       ",FechaNacimiento)" & _
                                   "VALUES (@Id, " & _
                                       "@Nombre, " & _
                                       "@Direccion, " & _
                                       "@FechaNacimiento)"


	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())
		Dim NextId As Integer = MaxId() + 1

		Dim command As New SqlCommand(sql, conn)
		command.Parameters.AddWithValue("Id", NextId)
		command.Parameters.AddWithValue("Nombre", nombre)
		command.Parameters.AddWithValue("Direccion", direccion)
		command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento)

		conn.Open()

		Dim rowsAffected As Integer = command.ExecuteNonQuery()

		If rowsAffected > 0 Then
			ClearControls()
			Return True
		Else
			Return False

		End If
       End Using

End Function

Private Function MaxId() As Integer
       Dim sql As String = "SELECT MAX(Id)" & _
                           "FROM Contactos"


	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())

		Dim command As New SqlCommand(sql, conn)

		conn.Open()


		Return Convert.ToInt32(command.ExecuteScalar())
	End Using
End Function

En el método Insert() se observara que la consulta hace uso de parámetros para asignar los valores, es por ello que el objeto SqlCommand posee la propiedad Parameters, y en esta se hace uso de la forma rápida por medio de método AddWithValue(), definiendo el nombre del parámetro y el valor.

Además se hace uso de otra del las funciones del SqlCommand el ExecuteNonQuery(), este es útil ante consultas SQL de actualización, o sea no retornan un conjunto de datos. Este método si devolver un valor entero que indicara la cantidad de registros afectados, si este devuelve cero quiere decir que no se actualizaron registro, en este ejemplo esto indicaría un fallo en la aplicación ya que siempre al menos un registro debe ser insertado de forma correcta.

 

3 – Actualizar registro existente

---

La operación de actualización es bastante similar a la operación anterior de insert, salvando la diferencia que cambia completamente la sentencia sql utilizada.

Al igual que con el insert, se hacen uso de parámetros para asignar los valores a la consulta, salvo que este necesita contar con el id del registro que se quiere actualizar y será utilizado en la sección del WHERE que permitirá identificar que registro afectar con los cambios.

[C#]

        private void btnGuardar_Click(object sender, EventArgs e)
        {
            //
            // Validaciones
            //
            int Id = 0;
            if (!string.IsNullOrEmpty(txtId.Text))
            {
                if (!int.TryParse(txtId.Text, out Id))
                {
                    MessageBox.Show("El Id debe ser un valor numerico");
                    return;
                }
            }

            if (Exists(Id))
            {
                bool result = Update(Id, txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value);

                if (result)
                    MessageBox.Show("El registro se ha actualizado correctamente.");
            }
            else
            {
                bool result = Insert(txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value);
                
                if (result)
                    MessageBox.Show("El registro se ha insertado correctamente.");
            }
        }
		
		
        private static bool Update(int id, string nombre, string direccion, DateTime fechaNacimiento)
        {

            string sql = @"UPDATE Contactos SET 
                                  NombreCompleto = @Nombre
                                  ,Direccion = @Direccion
                                  ,[FechaNacimiento] = @FechaNacimiento
                            WHERE Id = @Id";


            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
            {

                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddWithValue("Id", id);
                command.Parameters.AddWithValue("Nombre", nombre);
                command.Parameters.AddWithValue("Direccion", direccion);
                command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento);

                conn.Open();

                int rowsAffected = command.ExecuteNonQuery();

                if (rowsAffected > 0)
                    return true;
                else
                    return false;

            }
        }

 

[VB.NET]

Private Sub btnGuardar_Click(sender As Object, e As EventArgs)
	'
	' Validaciones
	'
	Dim Id As Integer = 0
	If Not String.IsNullOrEmpty(txtId.Text) Then
		If Not Integer.TryParse(txtId.Text, Id) Then
			MessageBox.Show("El Id debe ser un valor numerico")
			Return
		End If
	End If

	If Exists(Id) Then
		Dim result As Boolean = Update(Id, txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value)

		If result Then
			MessageBox.Show("El registro se ha actualizado correctamente.")
		End If
	Else
		Dim result As Boolean = Insert(txtNombre.Text, txtDireccion.Text, dtpFechaNanimiento.Value)

		If result Then
			MessageBox.Show("El registro se ha insertado correctamente.")
		End If
	End If
End Sub

Private Function Update(id As Integer, nombre As String, direccion As String, fechaNacimiento As DateTime) As Boolean

       If Not ValidateForm() Then
           Return False
       End If

       Dim sql As String = "UPDATE Contactos SET " & _
                               "NombreCompleto = @Nombre" & _
                               ",Direccion = @Direccion" & _
                               ",[FechaNacimiento] = @FechaNacimiento " & _
                           "WHERE Id = @Id"


	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())

		Dim command As New SqlCommand(sql, conn)
		command.Parameters.AddWithValue("Id", id)
		command.Parameters.AddWithValue("Nombre", nombre)
		command.Parameters.AddWithValue("Direccion", direccion)
		command.Parameters.AddWithValue("FechaNacimiento", fechaNacimiento)

		conn.Open()

		Dim rowsAffected As Integer = command.ExecuteNonQuery()

		If rowsAffected > 0 Then
			Return True
		Else
			Return False

		End If
	End Using
End Function

4 – Eliminar registro

---

Eliminar un registro es una operación bastante simple, con solo proporcionar el identificador para definir la sección del WHERE en la sintaxis de eliminación es suficiente.

En el ejemplo se realiza una operación previa que valida que id exista, pero también se podría haber obviado esta validación utilizando el valor proporcionado por el usuario.

[C#]

private void btnEliminar_Click(object sender, EventArgs e)
{
    //
    // Validaciones
    //
    errProvider.SetError(txtId, "");

    int Id = 0;
    if (!int.TryParse(txtId.Text, out Id))
    {
        errProvider.SetError(txtId, "El Id debe ser un valor numerico");
        return;
    }

    if (!Exists(Id))
    {
        errProvider.SetError(txtId, "El Id ingresado no existe.");
        return;
    }

    //
    // Elimino contacto
    //
    bool result = Delete(Id);

    if (result)
    {
        MessageBox.Show("El registro se ha eliminado correctamente.");
        ClearControls();
    }
    
}

public bool Delete(int Id)
{
    string sql = @"DELETE FROM Contactos 
                   WHERE Id = @Id";


    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
    {

        SqlCommand command = new SqlCommand(sql, conn);
        command.Parameters.AddWithValue("Id", Id);

        conn.Open();

        int rowsAffected = command.ExecuteNonQuery();

        if (rowsAffected > 0)
            return true;
        else
            return false;

    }
}

[VB.NET]

Private Sub btnEliminar_Click(sender As Object, e As EventArgs)
	'
	' Validaciones
	'
	errProvider.SetError(txtId, "")

	Dim Id As Integer = 0
	If Not Integer.TryParse(txtId.Text, Id) Then
		errProvider.SetError(txtId, "El Id debe ser un valor numerico")
		Return
	End If

	If Not Exists(Id) Then
		errProvider.SetError(txtId, "El Id ingresado no existe.")
		Return
	End If

	'
	' Elimino contacto
	'
	Dim result As Boolean = Delete(Id)

	If result Then
		MessageBox.Show("El registro se ha eliminado correctamente.")
		ClearControls()
	End If

End Sub


Public Function Delete(Id As Integer) As Boolean
       Dim sql As String = "DELETE FROM Contactos " & _
                           "WHERE Id = @Id"


	Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("default").ToString())

		Dim command As New SqlCommand(sql, conn)
		command.Parameters.AddWithValue("Id", Id)

		conn.Open()

		Dim rowsAffected As Integer = command.ExecuteNonQuery()

		If rowsAffected > 0 Then
			Return True
		Else
			Return False

		End If
	End Using
End Function

Acerca de los ejemplos

---

Para poder ejecutar correctamente los ejemplo necesitar Visual Studio 2008 y Sql Server Express instalado y corriendo localmente.

 

[C#]

[VB.NET]