Guía Práctica: ADO.Net para principiantes

El Fin del Misterio

Fecha: 12/Ago/2005 (11-08-05)
Autor: Facundo Dhers

Un poco de teoría

Muchos habrán escuchado hablar de ADO.NET, bases de datos, conexiones, Comandos, consultas, Adaptadores, DataSets, y muchas otras cosas que no tienen ningún significado. Bueno, es la idea de esta Guía explicar en forma simple y precisa que son todas estas cosas, como usarlas y para qué.

Empezando por el principio

¿Qué es una base de datos?

Principalmente una Base de Datos es un conjunto de datos almacenados.
-Solo eso???
-Sí solo eso.
-Entonces que es eso de MsAccess, SQL, ODBC, Oracle…
-Bueno, no dije que fuera fácil. Hay en el mercado distintas formas de almacenar información y de tal manera distintas formas de exponer los datos a los usuarios.

Una Base de datos tiene 2 partes:
    • Los Datos (por supuesto) que estarán guardados en un Archivo (siempre)
    • El Motor de la base de datos, que entre otras cosas será el encargado de decidir quien accede a los datos, quien no, que permisos tiene, prioridades, etcétera.

Los Datos

Si bien las bases de datos a nivel archivo pueden ser distintas hay algunos elementos que están presentes en todas: Las famosas Tablas.

Tablas

Las Tablas son un modelo estructurado donde guardaremos nuestros datos y está formado por Columnas que se llaman Campos

Por ejemplo:
Podemos tener una Tabla Clientes que podría tener un Campo Nombre y otro Apellido, en este caso sabremos que cuando querramos guardar el nombre de un cliente, lo ponderemos dentro del campo Nombre y cuando deseemos guardar el Apellido lo haremos en el campo Apellido, Simple no? Este modelo nos facilitará el día de mañana buscar a nuestros clientes por nombre o por apellido (o por los 2 a la vez, en fin)

Ejemplo:

Fijense también que los campos tiene un Tipo de Datos (Data Type) esto me permite que si voy a dar de alta un Campo: FechaNacimi puedo garantizar que este campo solo contendrá fechas al asignarle el Tipo Fecha (o Date como más les guste)

Eso es todo? Sí, una tabla es simplemente el Diseño de cómo quiero guardar los datos.
-Pero… y lo datos?
-Bueno ahí vamos…

Así como tenemos estos famosos campos (o columnas) que nos indican que guardaremos, tenemos Rows (o filas) que son nuestros datos guardados

Ejemplo:

En este ejemplo se puede ver que tenemos una Tabla que tiene 2 campos: el Campo Nombre y el Campo Apellido. También podemos ver que tiene 2 Filas: una compuesta por los valores: {Juan;Perez} y otra compuesta por los valores {Pablo;Garcia}

Relaciones

Las tablas pueden tener relaciones (ahora sí que se complicó)

¿Qué son las relaciones?
Bueno, digamos que es un modo de ahorro…
-Ahorro??? (eh???)
Si dijimos que la información se guarda en archivos, ¿no? Y como todo archivo, estos tiene un tamaño ¿no?, bueno las relaciones son la manera más sencilla (que alguien encontró) de tratar que el espacio de estos archivos no crezca desmedidamente.

Ahorran tiempo, espacio, tiempo de procesador...

Les explico: En el ejemplo anterior teníamos una Tabla clientes, que tenía el nombre y apellido de nuestros clientes, bueno, vamos a agregarle un campo más que llamaremos ID y seleccionaremos que sea un Campo del tipo autonumérico (en MsSQL elegimos el tipo numérico y elegimos la opción Identity=Yes).
Para qué? Bueno esta tecnica le dará a cada uno de mis clientes un número de Cliente único.

Ejemplo:

A partir de ahora cuando quiera hacer referencia a 'Juan Perez', no le diré más 'Juan Perez', sino que le diré mi Cliente 1.

- Perdón, pero sigo sin ver el ahorro (de hecho agrandaste el tamaño al agregar un campo)
- Ya voy, ya voy

Supongamos ahora que tenemos otra tabla: Pedidos: Que tiene un campo NroPedido, un campo fecha y un campo Cliente.

Fijensé que el tipo de Cliente no es un Texto, sino que es un Numérico… porque en el momento de hacer un nuevo pedido, no escribiré 'Juan Perez', sino que simplemente pondré su número de Cliente (que efectivamente es un número)

Para el motor de Base de Datos, es mucho más facil manejar números que cadenas de caractérs, estos ocupan menos espacio en disco y me permite oredenar la Base de datos de tal manera que voy a terner, todos los clientes en la tabla Cliente, todos los Pedidos en la Tabla Pedidos, etc.
De la misma manera si cambio el nombre de un cliente en la Tabla Cliente, se cambia para todos los Pedidos (ya que en la tabla pedidos solo tengo una referencia al Cliente y no me importa que nombre tiene)

Por supuesto este es un ejemplo que busca ser didáctico, pero imagínense, el ahorro que representaría si en vez de tener solo 2 tablas, tuviéramos 15 y en vez de tener 2 registros (o filas), tuvieramos 1.000.000.

Las relaciones pueden ser de muchos tipos y exigir distintas condiciones (por ejemplo que no se pueda borrar un cliente, que tiene un pedido, o que no pueda existir un pedido sin un cliente, etc.)

Motor de Base de Datos

El Motor de Base de Datos es el programa que maneja los archivos de la base de datos, el acceso a las Tablas (y relaciones), puede permitir tener seguridad, o bloquear una fila que está siendo modificada, para que nadie más la modifique, entre otras cosas.

A su vez todos los Motores tienen un Cliente, este programa DEBE estar en la pc que intentará acceder a los datos de mis tablas (excepto que lo estén accediendo a través de una página Web donde el cliente deberá estar instalado en el Server donde esté la página)

Existen cientos de Motores, algunos que permiten que los usuarios acceden físicamente al archivo donde está guardada la información (como por ejemplo el Microsoft Access), otros que no permiten que los archivos estén disponibles (como Microsoft SQL Server, Oracle, DB2, etc) y un tercero que si bien no es un motor en si mismo como es una práctica habitual, lo mencionaremos que es el uso de Archivos de texto (que pueden o no estar en formato XML)

Por supuesto cada motor brinda diversa cantidad de servicios y uno puede ser más convenientes que otro y en cada caso se debe evaluar, la relación Costo – Beneficio para elegir uno.

El lenguaje

Ahora que ya sabemos aunque sea un poco de Base de datos, ustedes se estarán preguntando como hacemos para leer los datos de nuestra Base. Bueno resulta que existe un lenguaje conocido como Transac (o SQL Transac) que "en general" es común para todos los motores de bases de datos.

Este lenguaje cuenta principalmente con 4 comando fundamentales.
    • SELECT
    • INSERT
    • UPDATE
    • DELETE

Veremos solo algunas formas de usarlos si quieren más información les recomiendo que busquen en la documentación del Motor que eligieron:

SELECT

Este comando se utiliza para leer datos de una o más tablas.
Sintaxis
SELECT [campo1], [campo2], …
FROM [tabla1]
WHERE [campo1]='valor buscado'
Por ejemplo si quisiera ver la fila 'Nombre' de mi Tabla 'Clientes' escribiría:
SELECT nombre FROM Clientes
Y si quisiera ver toda la información de mi clientes de Apellido: 'Perez' escribiría:
SELECT * FROM Clientes WHERE Apellido='Perez'
Noten que para ver todos los campos usé un asterisco (*) y que el valor de texto que busco está entre comillas simples (') para definirlo como una cadena de caracteres

INSERT

Insertar una fila en una tabla
Sintaxis:
INSERT [Tabla] ( [Campo1], [Campo2], …)
VALUES ('Valor1','Valor2',…)
Ejemplo: Insertar un Nuevo Cliente
INSERT Clientes (nombre, apellido)
VALUES ('Facundo', 'Dhers')
UPDATE (actualizar)

Actualiza la información de un campo en una o muchas filas
Sintaxis
UPDATE [Tabla]
SET [campo1]='Nuevo Valor'
WHERE [Campo1]='Viejo Valor'
Ejemplo si quisiera actualizar en mi tabla Productos, el campo Precio a '$1,5' de la 'Bebida de Cola'
UPDATE productos
SET Precio=1,5
WHERE NombreProducto='Bebida de Cola'
DELETE (Borrar)

Elimina una o muchas filas
Sintaxis
DELETE [tabla1]
WHERE campo1='Valor'
Ejemplo quiero sacar todas las bebidas Cola de mi tabla Productos
DELETE Productos
WHERE NombreProducto='Bebida de Cola'
Tengan en cuenta que la expresión WHERE acepta los operadores OR y AND que dan mucha precisión a las consultas (o Queries)

Por supuesto hay mucho más sobre Transac y como usarlo (mezclas y usos combinados) pero solo quiero mostrarles aquellos que van a usar directamente con ADO.NET (como para empezar)

También escucharán hablar de Procedimientos Almacenados (o Stored Procedures) estos son un conjunto de comandos Transac que están guardados en el Motor de Base de datos y que al ejecutarlos hacen algo (como por ejemplo eliminar un registro de una tabla, darlo de alta en otra y devolver el nuevo identificador único). Estos SP pueden recibir o devolver parámetros (valores que le indiquen que es lo que tiene que hacer o que indiquen que es lo que hizo)

ADO.NET en VB

ADO.NET explicado de una forma muy sencilla, es un conjunto de Assemblies que forman parte del .Net Framework que nos permiten comunicarnos con los motores de Bases de Datos, leer datos, actualizarlos y demás, de una manera muy rápida y extremadamente sencilla.

Para esto ADO.NET tiene varios "Clientes" de Bases de Datos (se acuerdan de que eran los clientes de Bases de datos?) y todos se encuentran en el espacio de nombres: System.Data

   • System.Data.SqlClient (para conectarse a los Motores MsSQL Server 7.0 o superior)
   • System.Data.OleDb (para todo lo que no se MsSQL Server 7.0 o superior)
   • System.Data.Oracle (que está disponible a partir del .NET Framework 2.0 y es para conectarse a motores Oracle)

Los componentes de ADO.NET:

Como ADO.NET acepta varios clientes, tendremos un componente para cada Cliente.
Por ejemplo: ADO.NET tiene un control que se llama Conexion, bueno habrá un objeto Conexion para SQL (SqlDataConection) y uno para OleDB (OleDBDataConection)

Conexión

Sin ningún lugar a duda lo primero que alguien debe hacer para poder utilizar una Base de Datos es crear una Conexión con está.
¿Y que significa crear una conexión?, es simplemente darle a con componente Conection los datos necesarios para que podueda encontrar la base de datos y leer información de esta.
Por ejemplo en el caso de SQL, debemos darle a la conexión:

   • El nombre del servidor donde está la base de datos
   • El nombre de la base de datos (en si mismo)
   • El usuario y la password (o indicarle que pase por relación de confianza usando el usuario de Windows)

En el caso de Access, será el nombre y el Path de donde está el archivo .mdb (y usuario y password de ser necesario)

Y ahora sí llegamos al fin, después de tanto palabrerio: el código (vamos a usar de ejemplo un MsSQL):
    Dim SqlString As String

     'Parametros:
     '    data source = Nombre del Servidor (puede ser dirección IP o 
     '                  localhost si el Server es la misma pc en 
     '                  donde estoy)
     '    initial catalog = Nombre de la base de datos
     '    integrated security = Indica que usa Seguridad integrada
     SqlString = "data source=localhost;" & _
                 "initial catalog=Northwind" & _
                 "integrated security=SSPI;"

   Dim MiConexion As SqlConnection = New SqlConnection(SqlString)
Listo, acabamos de crear una conexión a la Base Northwind que está en el servidor local

Adaptadores

Los adaptadores serán los encargados de llamar a los comandos de lectura, actualización, inserción y eliminación en la base de datos.

Esto significa que cada adaptador tendrá:
   • Una conexión: Para saber de donde sacar los datos
   • Un Comando Select: Para saber que datos leer
   • Un Comando Insert para saber como insertar los datos
   • Un Comando Update para saber como actualizar los datos
   • Un Comando Delete para saber como borrar datos

Con lo cual el adaptador podría hacer cualquier operación que nosotros deseemos.

Para crear un Adaptador por ejemplo de MsSQL:
Dim MiAdapter As SqlDataAdapter = New SqlDataAdapter("Select * from Customers", MiConexion)
Esta línea de código crea la instancia del Adapter y le asigna al SelectComand una sentencia de Transac que lee todos los campos de la tabla 'Customers'

Estos commandos a su vez tienen funciones que se las podemos llamar para que ejecuten un comando.
Y una de las grandes ventas es que le podemos indicar que datos debe devolver.
Por ejemplo si deseo ejecutar SP (Stored Procedures) y no recibir ningún valor o si quiere recibir solo un valor

Estos comandos son:
ExecuteNonQuery: No devuelve nada
ExecuteScalar: Devuelve solo 1 valor

Ejemplo:
'Le carga a la Variable Valor el resulta del InsertCommand
Dim Valor As String = MiAdapter.InsertCommand.ExecuteScalar

'Ejecuta el DeleteComand y no devuelve ningún valor 
MiAdapter.DeleteCommand.ExecuteNonQuery
También podemos con el resultado del SelectComand, podemos llenar contenedores de datos como DataReaders y DataSets

DataReaders

Un DataReader una representación en memoria de un Row de una Tabla y me permite recorrer la Tabla (solo hacia abajo) de a un Row por vez. Este objeto podrá mostrar los datos siempre y cuando se mantenga conectado a la base de datos. Si la conexión se pierde, los datos también.

Para poder conectar un DataReader a un Adapter se utiliza el comando:
Dim MiReader As SqlDataReader = MiAdapter.SelectCommand.ExecuteReader()
Este comendo llena a MiReader con los datos resultantes de llamar al comando SelectComand de MiAdapter

DataSets

Un DataSet es una representación en Memoria de una o muchas Tablas y relaciones. Este objeto copia los datos a la memoria y permite cortar la conexión (por eso se dice que es un modelo desconectado). Permite recorrer los datos en cualquier dirección y permite actualizar todos los modificados en un momento determinado.

Para poder poner una Tabla dentro de un DataSet lo podemos hacer de esta manera:
        Dim MiDataSet As New DataSet
        MiAdapter.Fill(MiDataSet, "MiTabla")
Este método crea la tabla MiTabla y lo llena con todos los datos que devolvió el SelectComand de MiAdapter y la pega dentro del DataSet MiDataSet. Si la tabla ya existiera, volvería a llenar los datos.

Tranquilamente más tarde se podría hacer una modificación en la información de mi DataSet por ejemplo:
MiDataSet.Tables("MiTabla").Rows(1).Item("Nombre") = "Juan"
Esta línea escribe la cadena de carateres Juan en la Columna Nombre de la Row 1 de la MiTabla dentro de MiDataSet. Pero aún no hemos actualizado esta modificación en el origen (en la base de datos desde donde traje los datos), para esto se utiliza el método:
MiAdapter.Update(MiDataSet)
Este método llama a los métodos InsertCommand, UpdateCommand y DeleteCommand de MiAdapter impactando todas las modificaciones hechas en el DataSet y actualizando la base de datos. Maravilloso no?

Ahora me dirán… que a excepción del SeletCommand en ningún momento le dijimos al Adapter cuales son los otros Commands, no?

Commands

Como ya dijimos en los Adaptadores, estos pueden ser 4: Select, Update, Insert y Delete. Cada uno de ellos contendrá una sentencia de Transac que le dirá al Adapter como borrar o como insertar un campo en la base de datos de origen.

Ahora bien, ya dijimos que para la gran mayoría de los motores de Bases de Datos las instrucciones Transac son parecidas y entonces por qué las tengo que escribir yo? Bueno es que quizás alguien necesite hacer algo más complejo que un simple Insert o Update (por ejemplo confirmar si el producto está en el Stock o algo así).

Pero si simplemente desea hacer algo estandar (estamos hablando de algo muy estandar), pueden usar el CommandBuilder (que sería como un constructor automático de aquellos comandos que yo no quiero escribir)
Dim MiCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(MiAdapter)
Esta simple línea crea los comandos: Insert, Update y Delete en MiAdapter, es como si fuer mágico no?

Entonces como quedaría al final el código?
        Dim SqlString As String

        'Parametros:
        '    data source = Nombre del Servidor (puede ser dirección IP)
        '    initial catalog = Nombre de la base de datos
        '    integrated security = Indica que usa Seguridad integrada
        SqlString = "data source=localhost;" & _
                    "initial catalog=NorthWind" & _
                    "integrated security=SSPI;"

        'Se crea la conexión
        Dim MiConexion As SqlConnection = New SqlConnection(SqlString)

        'Se declara el Adaptador que utilizará el Select como SelectCommand
        'y MiConexión como conexión
        Dim MiAdapter As SqlDataAdapter = New SqlDataAdapter("Select * from Customers", MiConexion)
        'El commandBuilder agrega los comandos de Insert, Update y Delete
        Dim MiCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(MiAdapter)


        Dim MiDataSet As DataSet
        'Y por ultimo Fill llena MiDataSet
        MiAdapter.Fill(MiDataSet, "MiTabla")
Ya tenemos un DataSet lleno de datos, no es espectacular...

Ya para ir terminado me gustaría mostrarles como serían pasarle los comandos manualmente:
        Dim SqlString As String
        SqlString = "data source=latbuamsq02;" & _
                    "initial catalog=NorthWind;" & _
                    "integrated security=SSPI;"
        'Se crea la conexión
        Dim MiConexion As SqlConnection = New SqlConnection(SqlString)

        'Se declara el Adaptador
        Dim MiAdapter As SqlDataAdapter = New SqlDataAdapter

        'Declare 4 Comandos 1 para cada transacción
        Dim SqlSelectCommand1 As SqlCommand = New SqlCommand
        Dim SqlInsertCommand1 As SqlCommand = New SqlCommand
        Dim SqlUpdateCommand1 As SqlCommand = New SqlCommand
        Dim SqlDeleteCommand1 As SqlCommand = New SqlCommand

        'Pego cada comando a la transaccion que hará en el Adaptador
        MiAdapter.DeleteCommand = SqlDeleteCommand1
        MiAdapter.InsertCommand = SqlInsertCommand1
        MiAdapter.SelectCommand = SqlSelectCommand1
        MiAdapter.UpdateCommand = SqlUpdateCommand1

        'Crea el mapeo de la Tabla para que el Adaptador sepa que 
        'columna es cual en la tabla
        MiAdapter.TableMappings.AddRange(New DataTableMapping() _
            {New DataTableMapping("Table", "Customers", _
             New DataColumnMapping() _
             {New DataColumnMapping("CustomerID", "CustomerID"), _
              New DataColumnMapping("CompanyName", "CompanyName"), _
              New DataColumnMapping("ContactName", "ContactName")})})


        'Configuro el SqlSelectCommand1 para que haga el SELECT
        SqlSelectCommand1.CommandText = "SELECT CustomerID, CompanyName, ContactName " & _
                                        "FROM Customers"
        'Le pego la conexión
        SqlSelectCommand1.Connection = MiConexion



        'Configuro el SqlInsertCommand1 para que inserte los nuevos campos
        'La sentencia de Transac tiene además una setencia Select esto es para que
        'Me devuelva el registro que insertó
        SqlInsertCommand1.CommandText = "INSERT INTO Customers(CustomerID, CompanyName, ContactName) " & _
                                        "VALUES (@CustomerID, @CompanyNa, @ContactName); " & _
                                        "SELECT CustomerID, CompanyName, ContactName, ContactTitle " & _
                                        "FROM Customers WHERE (CustomerID = @CustomerID)"
        'Le pego la conexión
        SqlInsertCommand1.Connection = MiConexion

        'Declare 3 parametros uno por cada valor que le voy a pasar al comando
        'Por supuesto si uso el metodo Adapter.Update este los pasa automáticamente
        SqlInsertCommand1.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID"))
        SqlInsertCommand1.Parameters.Add(New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"))
        SqlInsertCommand1.Parameters.Add(New SqlParameter("@ContactName", SqlDbType.NVarChar, 30, "ContactName"))



        'Configuro el SqlUpdateCommand1 para actualizar los valores
        'Igual que el anterior la sentencia Select que devuelve el registro actualizado
        SqlUpdateCommand1.CommandText = "UPDATE Customers " & _
                                        "SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName " & _
                                        "WHERE (CustomerID = @Original_CustomerID); " & _
                                        "SELECT CustomerID, CompanyName, ContactName " & _
                                        "FROM Customers " & _
                                        "WHERE (CustomerID = @CustomerID)"

        'Pego la Conexión
        SqlUpdateCommand1.Connection = MiConexion

        'Fijense que además de los 3 parametros creo uno más que tenía el ID anterios
        SqlUpdateCommand1.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.NVarChar, 5, "CustomerID"))
        SqlUpdateCommand1.Parameters.Add(New SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName"))
        SqlUpdateCommand1.Parameters.Add(New SqlParameter("@ContactName", SqlDbType.NVarChar, 30, "ContactName"))
        'Este parámetro trae el valor que tenía originalmente el registro
        SqlUpdateCommand1.Parameters.Add(New SqlParameter("@Original_CustomerID", SqlDbType.NVarChar, 5, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CustomerID", DataRowVersion.Original, Nothing))



        'Configuro el SqlDeleteCommand1 para eliminar el registro
        SqlDeleteCommand1.CommandText = "DELETE FROM Customers " & _
                                        "WHERE (CustomerID = @Original_CustomerID)"
        SqlDeleteCommand1.Connection = MiConexion

        'Este parámetro trae el valor que tenía originalmente el registro
        SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Original_CustomerID", SqlDbType.NVarChar, 5, ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CustomerID", DataRowVersion.Original, Nothing))

        Dim MiDataSet As New DataSet
        MiAdapter.Fill(MiDataSet, "MiTabla")

        'Y para actualizar  
        MiAdapter.Update(MiDataSet)
Una vez que tenemos el DataSet lleno con nuestros datos y que actualiza el origen, deberán asociar este a algún componente, por ejemplo un DataGrid, para que se muestren sus datos.

Solo a modo de ejemplo les dejo el comando para que un DataGrid (llamado DataGrid1) muestre los datos de MiDataSet
DataGrid1.SetDataBinding(MiDataSet, "MiTabla")
Recuerden que MiDataSet debe ser una variable Global para que se vea desde el formulario.

Para quienes quiera seguir investigando les recomiendo que averiguen que es un DataBinding.

Espacios de nombres usados en el código de este artículo:

System.Data
System.Data.SqlClient
System.Data.Common