[ADO.NET] Parte 1 - Recuperar Información Sql Server

 

Introducción

---

He notado que a veces faltan ejemplo integradores sobre el uso de ciertos aspectos que a primera vista parecen simple, pero al no encontrase en uso de forma integradora pueden resultar algo difíciles de captar las ideas, especialmente cuando se comienza en el aprendizaje.

La objetivo de este articulo es mostrar como hacer so de ado.net paso a paso en un ejemplo concreto utilizando varios métodos de consultas y trabajo de la información obtenida

 

Algunas aclaraciones antes de comenzar

---

Si bien la explicación del ejemplo se centrara en el uso de ado.net bajo lenguaje c#, los ejemplos podrán descargarse también en vb.net

Las explicación del articulo aplica a ambos lenguajes sin problemas.

Con respecto al diseño del ejemplo por ahí algunas aclaraciones previas podrá facilitar la comprensión.

Se visualizaría que en el ejemplo se han declarado dos clases que por ahí no son familiares, estas llevan el nombre: ContactoDAL y ContactoEntity.

Para el que no conozca el DAL (Data Access Layer) en realidad apunta a separa en capas la aplicación para dividir las responsabilidades y poder así encapsular y abstraer funcionalidad facilitando la reutilización.

En este caso en particular aplique este mismo concepto pero no lo puse en un proyecto nuevo, simplemente para no complicar el ejemplo, es por eso que tanto estas clases como el formulario están en el mismo proyecto.

También verán una clase que termina con la palabra entity, esta básicamente representa la entidad del dominio, y define las propiedades que posee la tabla de contactos, esta entidad será la intermediaria entre la funcionalidad de la DAL y la presentación. Esta clase evitara que a la presentación le lleguen objetos que claramente son de datos.

Aunque vale aclarar que en este ejemplo al estar contenido en un solo proyecto se pierde un poco el objetivo de abstraer a la presentación de componente de datos, pero igualmente esta es una buen técnica para mantener el código prolijo.

 

1 - Recuperar un conjunto de datos (DataReader)

---

La primera operación que será utilizada será la de recuperar todos los ítems de una tabla y cargar una grilla con ellos.

Para esta operación hay dos formas para acceder a los datos, por medio de:

• DataReader
• DataSet

Para los DataReader es que se hace uso de la clase con sufijo “entity” de esta forma se cargan los datos en estas entidades evitando pasar el reader a la presentación, lo cual no es nada aconsejable.

Algo importante para contar acerca de los reader es que estos necesitan mantener la conexión a al db abierta durante el procesamiento (o lectura de los registros que se están recuperando), es por eso que usar clases de entidades ya que se procesa el reader en un tiempo muy corto y luego se cierra. Hay que destacar que lo reader en acceso secuencial de lectura son mas rápido que los dataset.

Para el dataset, se hace uso de un DataSet tipado, en el proyecto lleva el nombre de “dtoContacto”, este permite tener una estructura definida, similar a la que brinda “ContactoEntity”.

En la clase ContactoDAL, se encuentran dos métodos “GetAll” y “GetAllFromDataSet”.

Empezaremos explicando como hacer uso de DataReader.

public static List<ContactoEntity> GetAll()
{
    string sql = @"SELECT [IdContacto]
                          ,[Nombre]
                          ,[Apellido]
                          ,[FechaNacimiento]
                          ,[Localidad]
                          ,[Calle]
                          ,[Numero]
                      FROM Contacto";

    List<ContactoEntity> list = new List<ContactoEntity>();

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

        SqlCommand command = new SqlCommand(sql, conn);

        conn.Open();

        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            list.Add(LoadContacto(reader));
        }

        return list;
    }

}

> Creación del objeto de conexión a la base de datos (línea 14): Este siempre será el primer paso ya que es necesario definir el objeto que permitirá establecer el contacto con la base de datos. Para realizar la tarea se necesitara de un string de conexión, para mas detalles ver “Cadena de conexión”.

Algo interesante que observaran es el uso de la sentencia using, el objetivo de esta es permitir delimitar la sección en que estará disponible un objeto, o sea el ámbito en el cual la instancia será usada, verán como al final cuando se termina de utilizar los objetos no se realiza un cierre de la conexión o un Dispose de los objetos, es justamente el bloque using el que hará esto por nosotros.

> Objeto Command (línea 17): este objeto permitirá unificar la consulta sql de selección que vamos a utilizar con la conexión a la base de datos que establecimos en el paso anterior.

El objeto command posee la funcionalidad para ejecutar la consulta que se ha creado, ya sea esta para insertar o actualizar la información, o como en este caso para recuperarla.

> Ejecución de la consulta (línea 21): el objeto command posee un método de nombre ExecuteReader(), el cual devolverá como resultado un objeto del tipo SqlDataReader.

> Lectura de los datos devueltos (líneas 23-26): al tratarse de un grupo de registros los que se recuperaran de la consulta es necesario realizar un ciclo por cada uno de ellos realizando la transformación de los datos para adecuarlos al objeto definido como entidad de negocio de la aplicación, en este caso “ContactoEntity”

El método Read() del objeto SqlDataReader tiene dos funcionalidades básicas, devuelve un true o false según se encuentren registros para leer, y además posiciona el cursor el el siguiente registro habilitado, es por eso que el while cumple la función ideal para recorrer cada ítem del objeto DataReader.

> Transformación de los datos del reader a la entidad (línea 25): esta operación se ha encapsulado en un método separado ya que es una operación que será reutilizada:

private static ContactoEntity LoadContacto(IDataReader reader)
{
    ContactoEntity contacto = new ContactoEntity();

    contacto.IdContacto = Convert.ToInt32(reader["IdContacto"]);

    contacto.Nombre = Convert.ToString(reader["Nombre"]);
    contacto.Apellido = Convert.ToString(reader["Apellido"]);

    contacto.FechaNacimiento = Convert.ToDateTime(reader["FechaNacimiento"]);

    contacto.Localidad = Convert.ToString(reader["Localidad"]);
    contacto.Calle = Convert.ToString(reader["Calle"]);
    contacto.Numero = Convert.ToInt16(reader["Numero"]);


    return contacto;
}

Como se observara en el bloque de código este método crea una instancia nueva de la entidad que estamos utilizando, luego asigna a cada propiedad la columna que le corresponde del registro que se esta leyendo en ese momento, el cual se ha pasado como parámetro al método, y por ultimo retorna el objeto entity con los datos para ser asignado a la colección de “ContactoEntity”.

 

2- Recuperar un conjunto de datos (DataSet)

---

Esta operación tendrá mucho en común con la anterior solo diferirá en la utilización algunos objetos distintos ya que se hará uso de un DataSet, (en este caso tipado) para cargar los datos

 

public static dtoContacto GetAllFromDataSet()
{
    string sql = @"SELECT [IdContacto]
                          ,[Nombre]
                          ,[Apellido]
                          ,[FechaNacimiento]
                          ,[Localidad]
                          ,[Calle]
                          ,[Numero]
                      FROM Contacto";

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

        SqlCommand command = new SqlCommand(sql, conn);

        SqlDataAdapter da = new SqlDataAdapter(command);

        dtoContacto contactos = new dtoContacto();

        da.Fill(contactos, "Contacto");

        return contactos;
    }

}

Como se observara en el código los dos primeros pasos son coincidentes con la operación anterior que utilizaba DataReader para la obtención de los datos, se detallara a continuación las diferencias.

> Creación del adaptador (línea 17): Este objeto DataAdapter proporcionara la herramienta principal para poder cargar un objeto del tipo DataSet.

Como ser observara el constructor del objeto SqlDataReader en este caso recibe al objeto Command como parámetro, pero debe comentarse que no es la única alternativa, en este caso se uso el command para mantener una uniformidad en la codificación, pero podría haber pasado como parámetro la consulta sql y el objeto conexión directamente al constructor del DataAdapter, de esta forma ya no se necesitara mas del objeto Command.

> Creación y llenado de los datos (líneas 19-21): La definición del dataset tipado se encuentra en el archivo de nombre “dtoContacto.xsd”, al inspeccionar el archivo se notara que este contiene un DataTable de nombre “Contacto” y la definición de las columnas, las cuales coinciden con las de la tabla de la base de datos.

Nota: cuando se crea un DataSet Tipado lo mas común es que se agregue de forma automática un TablaAdapter, en este caso se removió dejando la entidad lo mas simple posible.

El método Fill() del DataAdapter realizara la ejecución y carga de los datos en el DataSet Tipado, al cual además le indicamos cual es el DataTable que se estará cargando. Debe recordarse que un DataSet puede contener mas de un DataTable, en este caso posee solo uno, pero podría haber mas en otros casos.

Nota: al diferencia del objetos Command el SqlAdapter no requiere la apertura previa de la conexión, es por eso que la línea conn.Open() no esta presente. El DataDapter realiza la apertura, utilización y cierre de la conexión de forma automática

 

3 – Recuperar un solo registro (DataReader)

---

A diferencia la la operación en donde recuperábamos todo la información de una tabla, en este caso se remarcan dos puntos

- la utilización de parámetros en la consulta

- la no utilización del while para recorrer los datos provenientes de la consulta

 

public static ContactoEntity GetById(int Id)
{
    string sql = @"SELECT [IdContacto]
                          ,[Nombre]
                          ,[Apellido]
                          ,[FechaNacimiento]
                          ,[Localidad]
                          ,[Calle]
                          ,[Numero]
                      FROM Contacto
                      WHERE IdContacto = @Id";

    ContactoEntity product = null;

    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())
        {
            product = LoadContacto(reader);
        }

        return product;
    }
}

> Creación del parámetro de la consulta (línea 19): como se observa el objeto Command posee una propiedad que permite definir los parámetros en la consulta, es allí donde serán agregados, y hay diferentes formas de hacerlo, la mas practica y directa para crear parámetros simples es utilizando el método AddWithValue().

Pero debe remarcarse un punto importante en el uso de este método, como se observara en ningún momento se define un tipo de dato para el parámetro, lo cual parecería hasta mágico como lo infiere, pero no es así, para que esto funcione correctamente el tipo de la variable que se utiliza debe ser del tipo correcto, en este caso el “Id” es del tipo int, el cual coincide con el tipo de la columna “Id” de la tabla en la base de datos, es por esto que todo es resulto fácilmente.

Por ejemplo, si el parámetro será una fecha la variable usada en el método AddWithValue, debería ser también del tipo fecha, o en su defecto se tendría que convertir (castear) al tipo DataTime. En definitiva el método para agregar parámetros es simple de usar pero requiere ciertos cuidados a tener en cuenta, ya que de otra forma no podrá determinar de forma dinámica el tipo de datos del parámetro.

> Lectura del registro (línea 25-28): a diferencia del proceso en donde se leían un grupo de registros, en este solo nos interesa uno solo, es por eso que al reemplazar el while por un simple if, este cumple la misma funcionalidad, el Read() del DataReader devuelve true si hay registro, y además posiciona el cursor en el mismo para su lectura.

Luego se reutiliza el método que devolverá la entidad completa, y esta es retornada como resultado del método.

 

4 – Recuperar un solo registro (DataSet)

---

A diferencia el método en donde se recuperaba un conjunto de registros aquí hay variantes

- se utiliza un parámetro en la consulta

- se devuelve un solo registro del DataTable y no el DataSet completo

public static dtoContacto.ContactoRow GetByIdFromDataSet(int Id)
{
    string sql = @"SELECT [IdContacto]
                          ,[Nombre]
                          ,[Apellido]
                          ,[FechaNacimiento]
                          ,[Localidad]
                          ,[Calle]
                          ,[Numero]
                      FROM Contacto
                      WHERE IdContacto = @Id";

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

        SqlCommand command = new SqlCommand(sql, conn);

        SqlParameter param = new SqlParameter("Id", SqlDbType.Int);
        param.Value = Id;
        command.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(command);

        dtoContacto contactos = new dtoContacto();

        da.Fill(contactos, "Contacto");

        if (contactos.Contacto.Rows.Count > 0)
            return contactos.Contacto.Rows[0] as dtoContacto.ContactoRow;
        else
            return null;
    }
}

> Creación del parámetro de la consulta (línea 18-20): a modo de ejemplo en este caso se ha utilizado una técnica distinta, en donde se define explícitamente cual es el tipo de datos del parámetro.

Se crea el objeto SqlParameter, pasando en el constructor el nombre y tipo de dato del parámetro, luego a la instancia se le asigna el valor, y por ultimo se agrega a la colección de parámetros del objeto Command

> Determinar si se encontró el registro, y retorno del ContactRow (lineas 28-31): Como en este caso lo que se carga es un Datset completo, en realidad un DataTable si se lo devuelve directamente quien deba manipular los datos deberá agregar validaciones que comprueben si esta o no el registro cargado.

Es por ello que estas validaciones se agrego del lado de los datos, en el if se pregunta si al menos hay un registro cargado, luego se toma el primero de la colección para retornarlo, en caso de no haber ninguno registro para los filtros asignados se devuelve null.

Al retornar el valor se castea a un tipo de dato algo particular “dtoContacto.ContactoRow”, esta es una clase que crea internamente el DataSet Tipado, y representa un registro del DataTable “Contacto” 

 

Cadena de Conexión

---

Seguramente se habrá notado en el código que al hacer uso del objeto SqlConnection se utiliza una clase que devuelve la cadena de conexión:

ConfigurationManager.ConnectionStrings["default"]

El ConfigurationManager permite el acceso al archivo de configuración situado en el “app.config”, este archivo al editarlo posee un formato xml en donde se podrá especificar la configuración del sistema, en este caso en particular se utiliza para especificar la cadena de conexión a la base de datos, pero podría servir para conservar otro tipo de datos variables para el sistema.

Lo bueno de esta implementación es que al compilar la aplicación el archivo de configuración queda libre como archivo de texto que podrá editarse con el notepad y cambiarse sin necesidad de recompilar la aplicación desarrollada. Esto es muy bueno para realizar cambios una vez que se ha realizado el deploy en la pc del usuario.

Para utilizar el ConfigurationManager es necesario agregar la referencia a la assembly de nombre “System.Configuration”

 

Consideraciones acerca de la aplicación de ejemplo

---

Los ejemplos de código están usando Sql Server como parte de la solución, por lo tanto se aconseja que localmente al menos se tenga instado el Sql Server 2008 Express con el servicio ejecutándose.

Para editar un item de la grilla una vez que este cargada, se deberá hacer click con el botón derecho del mouse sobre de la grilla, y seleccionar con que operación recuperar los datos. Se esta haciendo uso control ContextMenuStrip para la edición del registro seleccionado.

 

[C#]

[VB.NET]