01 - unidad 04 - ado.net.pdf

ADO .NET

© Francisco Pérez [email protected]

CONTENIDO

1. ACCESO A DATOS ...................................................................................................... 1

2. COMPONENTES DE ADO.NET .................................................................................... 2

2.1. PROVEEDORES DE DATOS ............................................................................................. 3

2.1.1. ESTRUCTURA INTERNA DE UN PROVEEDOR DE DATOS ..................................................... 3

2.1.2. MODO CONECTADO vs. MODO DESCONECTADO .............................................................. 5

2.1.3. EL ACCESO A DATOS CON EL PROVEEDOR ODBC ............................................................... 6

2.2. Dataset ....................................................................................................................... 6

2.3. ¿DataReader o Dataset?....................................................................................... 7

3. CÓMO ACCEDER AL ORIGEN DE DATOS .................................................................... 8

4. ESTABLECIMIENTO DE UNA CONEXIÓN .................................................................... 8

4.1. GENERACIÓN DE UNA CADENA DE CONEXIÓN ............................................................. 9

4.1.1. SEGURIDAD EN LA CADENA DE CONEXIÓN ...................................................................... 11

4.2. INSTANCIACIÓN DE SqlConnection Y APERTURA DE LA CONEXIÓN ...................... 12

4.3. CIERRE DE LA CONEXIÓN Y LIBERACIÓN DE RECURSOS .............................................. 12

4.4. CAPTURA DE EXCEPCIONES ......................................................................................... 13

4.5. CONNECTION POOLING ............................................................................................... 15

5. EJECUCIÓN DE CONSULTAS ..................................................................................... 16

5.1. INSTANCIACIÓN DE SqlCommand ............................................................................... 16

5.2. ESTABLECIMIENTO DE LAS PROPIEDADES DE SqlCommand ...................................... 17

5.3. EJECUCIÓN DE LA CONSULTA Y LECTURA DE RESULTADOS EN MODO CONECTADO . 19

5.4. EJECUCIÓN DE LA CONSULTA Y RECOGIDA DE RESULTADOS EN MODO DESCONECTADO ...................................................................................................................... 23

5.5. CONSULTAS PRECOMPILADAS Y PROCEDIMIENTOS ALMACENADOS ........................ 25

ANEXO I ........................................................................................................................... 26

ANEXO II .......................................................................................................................... 28

1

1. ACCESO A DATOS

La mayoría de aplicaciones, tanto en ventana como web, relegan gran parte de las responsabilidades de la capa de acceso a datos a un sistema gestor de base de datos (DBMS). Esto permite un acceso seguro y eficiente al origen de datos de la aplicación, al tiempo que se centraliza la administración de los datos y se aísla a la aplicación de las características de los datos almacenados.

ADO.NET (ActiveX Data Objects .NET) es la infraestructura de la plataforma .NET que posibilita la interacción con orígenes de datos (relacionales y XML). Está formado por un conjunto de clases de la BCL que proporcionan conectividad, soporte para la ejecución de consultas y una representación para almacenar los resultados recibidos del motor de datos.

Con ADO.NET es posible acceder a prácticamente cualquier DBMS comercial o bien a un origen de datos XML. Entre las principales características se pueden citar las siguientes:

Una organización uniforme del espacio de nombres y de sus miembros, de manera que el acceso a datos se desarrolla siempre de la misma manera, aunque se trabaje con distintos DBMS.

Pooling de conexiones nativo, transparente al programador, que no necesita ser implementado explícitamente.

Soporta dos modos de operación, conectado y desconectado del origen de datos.

Por último, para trabajar con el acceso a datos a nivel conceptual (entidades, relaciones, etc.), en vez de hacerlo en el nivel subyacente (conexiones, consultas, etc.) se dispone de ADO.NET Entity Framework, que permite trabajar con un modelo de datos en un nivel de abstracción superior.

2

2. COMPONENTES DE ADO.NET Las clases de ADO.Net viven en el espacio de nombres System.Data y se organizan en torno a dos grandes grupos de clases:

Proveedores de datos: son paquetes de clases que permiten la conexión al origen de datos, la ejecución de consultas y la lectura de los resultados de la consulta. Es necesario disponer de un proveedor de datos específico para el motor de datos sobre el que se desarrolla. Todas las clases de un proveedor de datos trabajan en modo conectado, es decir, necesitan mantener una conexión abierta con el servidor de bases de datos para solicitar la ejecución de una consulta y para leer el resultado de la consulta.

La clase Dataset: representa un almacén de datos relacionales o XML que trabaja de manera desconectada. Un Dataset puede albergar una o varias tablas con sus relaciones, claves y restricciones. Los datos del Dataset se cargan desde el origen de datos y, a partir de ese momento, puede liberarse la conexión. Sobre el Dataset cargado (y desconectado) se pueden realizar consultas, filtrado o modificaciones a los datos y, posteriormente, reestablecer la conexión y consolidar dichos cambios en el origen de datos.

3

2.1. PROVEEDORES DE DATOS Un proveedor de datos es un conjunto de clases que permiten la conexión al origen de datos, la ejecución de consultas y la lectura de los resultados de la consulta. El proveedor "conoce", a bajo nivel, la forma de conectar y de comunicarse específicamente con el DBMS para solicitar la ejecución de consultas y recoger los resultados de las mismas. Por esa razón es necesario disponer de un proveedor de datos específico para cada motor de datos sobre el que se quiere desarrollar.

2.1.1. ESTRUCTURA INTERNA DE UN PROVEEDOR DE DATOS

ADO .NET impone una estructura común para todos los proveedores de datos. Para ello, define una serie de interfaces y clases abstractas que deben ser implementadas por los proveedores de datos. Estos miembros abstractos están recogidos en el namespace System.Data.Common. Los más importantes son:

DbConnection: representa una conexión con el servidor de bases de datos.

DbCommand: representa una consulta lanzada a través de la conexión de DbConnection.

DbDataReader: representa un lector utilizado para recorrer los resultados de la ejecución de la consulta de DbCommand.

DbDataAdapter: representa un puente para transferir los resultados de la consulta de DbCommand a un Dataset.

Cualquier proveedor de datos debe derivar de estas clases base comunes, por lo que su utilización es siempre la misma aunque trabajemos con proveedores para diferentes motores de datos. Cualquier proveedor define las mismas clases, métodos y propiedades básicos, aunque puede añadir otros miembros específicos que hagan uso de una funcionalidad particular de un DBMS. Por ejemplo, para desarrollar sobre SQL Server, algunas de las clases disponibles en el namespace System.Data.SqlClient son:

SqlConnection

SqlCommand

SqlDataReader SqlDataAdapter

Y, del mismo modo, para desarrollar sobre una base de datos Access, que tiene un motor JET OleDb, el proveedor definido en el namespace System.Data.OleDb expone, entre otras, las siguientes clases:

OleDbConnection OleDbCommand OleDbDataReader OleDbDataAdapter

4

Para otros proveedores como ODP.NET o MySQL Connector encontraremos la misma estructura: generalmente, el nombre de las clases comienza con el nombre del proveedor y utiliza un sufijo común (Connection, Command, DataReader, DataAdapter, etc.). En todos los casos, las propiedades y los métodos de las clases son idénticos entre sí.

Como ya se ha indicado previamente, para trabajar con un determinado DBMS es necesario disponer de un proveedor de datos específico para ese motor de datos. Los proveedores externos (proporcionados por terceros, y no incluidos en la BCL) reciben a veces el nombre de drivers o conectores de la base de datos. Estos drivers se proporcionan en forma de bibliotecas (archivos ".dll") que deben incluirse y referenciarse en el proyecto. De manera nativa, ADO .NET incorpora proveedores de datos para desarrollo con SQL Server (namespace System.Data.SqlClient), motores OleDb (namespace System.Data.OleDb) y conexiones ODBC (namespace System.Data.Odbc). En versiones previas de ADO.NET se incluía también un proveedor de datos para Oracle pero en la actualidad se ha abandonado en favor del proveedor de datos específico proporcionado por Oracle (ODP.NET).

5

2.1.2. MODO CONECTADO vs. MODO DESCONECTADO

Un proveedor de datos permite trabajar en cualquiera de los siguientes modos:

Modo conectado: tras lanzar una consulta al servidor de bases de datos, los resultados pueden ser leídos mediante un DbDataReader, que consiste en un lector de resultados secuencial, de sólo-avance y sólo lectura. Mientras el DataReader efectúa la lectura de los datos, la conexión con el origen de datos debe permanecer abierta.

Al no permitir el posicionamiento aleatorio ni tampoco operaciones de edición, el DataReader está muy optimizado y consigue recorrer los resultados en un tiempo mínimo y con la mínima sobrecarga de procesamiento, memoria y flujo de red.

Operando en modo conectado también es posible realizar inserciones, modificaciones o eliminación de los datos de la base de datos, mediante el envío de consultas SQL de inserción, modificación o borrado (o la llamada a un procedimiento almacenado), a través de un objeto DbCommand. Estas operaciones también requieren de una conexión activa.

Modo desconectado: en este modo se opera en tres fases. En la primera fase (fase conectada) se abre una conexión para lanzar una consulta al servidor de bases de datos. Cuando el servidor devuelve los resultados, éstos son cargados en un Dataset por medio de un objeto DataAdapter. A partir de entonces, se inicia una fase desconectada en la que se puede trabajar realizando lecturas, filtrado, inserción, modificación o borrado sobre los datos contenidos en el Dataset. Cuando se ha finalizado el trabajo con el Dataset, si se quiere consolidar los cambios en la base de datos, será preciso volver a establecer una conexión para guardar en la base de datos las modificaciones que se hayan efectuado en el Dataset.

En el apartado 2.3 veremos cómo determinar el modo más apropiado de acceder a los resultados en cada caso.

6

2.1.3. EL ACCESO A DATOS CON EL PROVEEDOR ODBC

ODBC (Open Database Connectivity) es una tecnología introducida a principios de los años 90 para proporcionar una pasarela de conexión con sistemas gestores de bases de datos heterogéneos. ODBC está implementado en la mayoría de sistemas operativos actuales aunque el desarrollo de esta tecnología ya no está activo y en la actualidad se considera una opción de conexión obsoleta. Aunque, por motivos de compatibilidad, ADO .NET incluye un proveedor ODBC, en la actualidad NO se deben realizar desarrollos sobre ODBC puesto que las exigencias de la mayoría de aplicaciones actuales exceden las capacidades que ofrece esta pasarela, tanto en rendimiento como en capacidad para gestionar múltiples conexiones concurrentes. A menudo, un desarrollo bajo ODBC es indicativo de un desarrollo muy deficiente, propio de inexpertos.

2.2. Dataset Dataset es un tipo definido en el espacio de nombres System.Data y representa un almacén de datos ubicado en memoria principal. Este tipo está concebido para albergar datos organizados en forma de tablas. El origen de datos de un Dataset pueden ser los resultados de la ejecución de una consulta sobre una base de datos o bien un origen de datos XML que el Dataset transforma en una representación en forma de tabla. A diferencia de los proveedores de datos, el tipo Dataset implementado en la BCL es apto para trabajar con cualquier DBMS y, por tanto, no es necesario disponer de un Dataset específico para cada DBMS. Generalmente, para trabajar con este tipo, se abre una conexión con el origen de datos y se realiza una consulta. A continuación, los resultados de la consulta se cargan en el Dataset y la conexión puede liberarse. Desde ese momento, el Dataset puede trabajar desconectado del origen, pudiendo efectuar operaciones de selección, filtrado, modificación o borrado de los datos. En el momento en que se haya terminado de operar con los datos, puede volver a establecerse una conexión para consolidar los cambios del Dataset en el origen de datos.

7

2.3. ¿DataReader o Dataset? Cuando se diseña una aplicación, conviene determinar si es mejor utilizar un Datareader o un Dataset, es decir, si trabajamos en modo conectado o en modo desconectado. En general un Dataset impone una sobrecarga adicional de recursos por lo que debe usarse en estas circunstancias:

Realizar procesamientos exhaustivos de datos sin necesidad de tener una conexión abierta con el origen de datos, lo que libera la conexión para que la utilicen otros clientes. Si solamente se necesita leer los resultados de una consulta, el DataReader es la mejor elección.

Consumir datos procedentes de un servicio Web XML.

Acceder a datos desde una aplicación en ventana convencional.

Combinar y relacionar datos procedentes de varios orígenes. Estas características suelen hacer al Dataset poco apropiado en la mayoría de aplicaciones web, puesto que cada petición del cliente forzaría al servidor a crear un Dataset en memoria, lo cual supondría un consumo desproporcionado de recursos. No obstante, el Dataset podría ser una opción viable en una aplicación web si se dan las siguientes condiciones (simultáneamente):

No es posible realizar la tarea requerida de manera sencilla utilizando únicamente las clases del proveedor de datos.

La aplicación web se diseña para utilizar un único Dataset, compartido por todos los clientes, que se almacena en la caché de aplicación.

La aplicación web dispone de un mecanismo de control de concurrencia para el acceso al Dataset.

8

3. CÓMO ACCEDER AL ORIGEN DE DATOS En la mayoría de plataformas de desarrollo el acceso a datos sigue las mismas etapas:

1. Establecimiento de una conexión con el origen de datos. 2. Solicitud de ejecución de una consulta o procedimiento. 3. Recuperación de los resultados de la consulta para su procesamiento por la

aplicación. 4. Cierre de la conexión.

Cada una de estas etapas puede ejecutarse en varias fases, como veremos a continuación. Vamos a ilustrar estos pasos con una conexión a SQL Server aunque el proceso sería idéntico con cualquier otro proveedor de datos.

4. ESTABLECIMIENTO DE UNA CONEXIÓN Una conexión con SQL Server está representada por el tipo SqlConnection, en el espacio de nombres System.Data.SqlClient, que hereda del tipo DbConnection, del espacio de nombres System.Data.Common. Las fases para establecer una conexión con el servidor de bases de datos son:

1. Generar una cadena de conexión. 2. Instanciar un objeto SqlConnection, pasando al constructor la cadena de

conexión anterior. 3. Invocar al método Open() para abrir la conexión.

9

4.1. GENERACIÓN DE UNA CADENA DE CONEXIÓN Para instanciar un objeto SqlConnection hemos de pasar al constructor una cadena de conexión, que es una cadena de texto, similar a una URL, en la que se especifican, mediante pares [clave = valor], parámetros tales como la dirección del servidor de bases de datos al que queremos conectar, las credenciales de autenticación y las opciones de configuración para la conexión. Cada proveedor de datos posee su propio formato para la cadena de conexión por lo que, para proporcionar una cadena de conexión válida, deberemos consultar la documentación del driver. La mayoría de hostings de Internet proporcionan la cadena de conexión apropiada al servidor de base de datos que se haya contratado, con opciones de conexión predeterminadas. Construir una cadena de conexión puede resultar tedioso y propenso a errores. Para evitarlo, la BCL expone el tipo abstracto DbConnectionStringBuilder, que representa un constructor de cadenas de conexión. Cada proveedor de datos debe exponer una implementación de DbConnectionStringBuilder, específica para el motor de datos sobre el que opera el proveedor. En el caso del proveedor de datos de SQL Server, esta clase es SqlConnectionStringBuilder. Una vez instanciado este tipo, podemos configurar sus propiedades, que se corresponden con cada uno de los parámetros de la cadena de conexión. Cuando las opciones de conexión que queremos configurar están establecidas en el objeto, se puede invocar al método ToString() del constructor de cadenas de conexión, que devuelve una cadena de conexión con las opciones configuradas y con el formato adecuado de cadena de conexión para el motor de datos empleado. Para obtener una cadena de conexión apta para el motor de datos SQL Server tendremos que implementar un código similar al siguiente (se muestran las propiedades mínimas que hay que establecer en el objeto para obtener una cadena de conexión correcta):

// […] const String serverIpOrHostName = "192.168.150.150,1433"; const String dbName = "Web"; const String user = "pepe"; const String password = "sesamo"; SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder(); connStringBuilder.DataSource = serverIpOrHostName; connStringBuilder.InitialCatalog = dbName; connStringBuilder.UserID = user; connStringBuilder.Password = password; String connectionString = connStringBuilder.ToString(); // […]

10

La propiedad DataSource representa la dirección IP o el nombre DNS/Netbios del servidor de bases de datos al que se quiere conectar. Opcionalmente, este campo puede incluir un número de puerto separado por una coma del nombre o dirección IP. Para referirse al equipo local puede especificarse la cadena "(local)".

La propiedad InitialCatalog representa el nombre de la base de datos a la que conectar.

El usuario y contraseña se introducen en las propiedades UserId y Password, respectivamente.

Si se ha establecido el modo de autenticación en "Autenticación de Windows" (a veces referido como "autenticación integrada"), se debe establecer la propiedad IntegratedSecurity en true. Con este modo los usuarios de inicio de sesión se mapean con los usuarios de Windows por lo que es necesario especificar el usuario y contraseña en la cadena de conexión, lo cual mejora la seguridad.

La invocación del método ToString() en el ejemplo anterior proporcionaría una cadena de conexión similar a la siguiente:

Cuando se contrata un servicio de hosting en Internet que incluye bases de datos hay que tener en cuenta algunas consideraciones:

La empresa de hosting suele proporcionar una cadena de conexión preconfigurada por lo que, si las opciones de configuración se ajustan a nuestras necesidades, podemos usar esta cadena de conexión directamente, sin necesidad de generar una. En este caso, resulta conveniente almacenar la cadena de conexión en el archivo de configuración de la aplicación, Web.config, debidamente encriptada, puesto que el nombre de usuario y contraseña se almacenan en claro.

Si queremos tomar la cadena de conexión que nos proporciona el servicio de hosting y añadir o modificar propiedades sobre esta cadena, la clase DbConnectionStringBuilder dispone de un constructor que permite pasar como parámetro una cadena de conexión previamente generada. De este modo, se construye el objeto con las opciones de la cadena de conexión suministrada por el hosting, por lo que sólo será necesario realizar pequeños cambios en las propiedades del objeto para generar la nueva cadena de conexión con los parámetros modificados.

Por último, si se construye la cadena de conexión a partir de parámetros de la aplicación almacenados en Web.config, o bien si la cadena de conexión está almacenada, ya creada, en Web.config, hay que utilizar la clase WebConfigurationManager, en el paquete System.Web.Configuration, para leer o modificar este fichero.

Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;

11

4.1.1. SEGURIDAD EN LA CADENA DE CONEXIÓN

A la hora de manejar cadenas de conexión deben tenerse en cuenta algunas medidas de seguridad:

Si utilizamos una cadena de conexión ya creada, ésta debe almacenarse en el fichero de configuración de la aplicación, Web.config. Este fichero no es accesible por los clientes pero la información que contiene se almacena en claro y no suele resultar adecuado que los administradores u otros usuarios con acceso físico al servidor puedan disponer de esta información. Por tanto, la cadena de conexión almacenada en Web.config debe encriptarse.

Existe una funcionalidad llamada "Protected Configuration", que permite generar una clave de encriptación que se almacena en el servidor. Con esta clave de encriptación se puede encriptar la cadena de conexión. El proceso de desencriptación es transparente al programador, ya que la aplicación desencripta leyendo automáticamente esta clave. El problema que plantea "Protected Configuration" es que requiere acceso al archivo de configuración de ASP .NET del servidor, machine.config, para almacenar la clave de encriptación, lo cual no suele ser posible en un servicio de hosting. En estos casos debe implementarse una solución "manual" basada en los servicios de encriptación de la plataforma.

Si, como en el ejemplo del apartado anterior, utilizamos parámetros para construir la cadena de conexión dinámicamente mediante un ConnectionStringbuilder, dichos parámetros también deben almacenarse en Web.config, debidamente encriptados.

Jamás debe generarse una cadena de conexión dinámicamente de forma directa, concatenando parámetros en vez de utilizar un ConnectionStringBuilder, puesto que constituye un riesgo de inyección de código.

Es recomendable utilizar el modo de autenticación integrado, para evitar incluir el nombre de usuario y contraseña en la cadena de conexión. Además, bajo este modo se centraliza la administración de usuarios a través del propio sistema operativo. Sin embargo, en servicios de hosting, no siempre es posible utilizar esta configuración.

En todo caso, cualquier String embebido ("hard-coded") en la aplicación y que almacene información sensible debe encapsularse en un tipo SecureString. De no hacerlo así, la seguridad de la aplicación podría quedar expuesta, puesto que un ensamblado compilado puede ser revertido a lenguaje intermedio (MSIL) usando una herramienta de decompilación, como Ildasm.exe (MSIL Disassembler).

12

4.2. INSTANCIACIÓN DE SqlConnection Y APERTURA DE LA CONEXIÓN

Una vez obtenida la cadena de conexión, para establecer la conexión con el servidor, basta con instanciar un objeto de tipo SqlConnection y, a continuación, invocar al método Open(), que se encarga de establecer la conexión:

En este fragmento de código no se ha tenido en cuenta que durante la apertura de la conexión podrían generarse excepciones. Además, tan pronto como sea posible, debe cerrarse la conexión y los recursos mantenidos por ésta deben liberarse. Por eso, en el apartado siguiente aplicaremos algunas mejoras imprescindibles a este fragmento.

4.3. CIERRE DE LA CONEXIÓN Y LIBERACIÓN DE RECURSOS

Cuando el código de la aplicación ha interactuado con el DBMS y la conexión ya no es necesaria, ésta debe cerrarse explícitamente para optimizar los recursos. El cierre de la conexión se efectúa mediante el método Close() de la clase SqlConnection. Por tanto, el código del apartado anterior debería reformularse así:

Sin embargo, este código está incompleto: la clase SqlConnection implementa la interfaz IDisposable, lo cual indica que la clase accede a recursos no administrados (unmanaged resources), es decir, recursos de bajo nivel administrados por el sistema operativo (como conexiones de red, manejadores de ficheros y ventanas, y otras estructuras administradas por el sistema operativo) que quedan fuera del control del recolector de basura del .Net Framework.

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" SqlConnection connection = new SqlConnection(connectionString); connection.Open(); // Ejecución de consultas […] connection.Close(); // […]

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" SqlConnection connection = new SqlConnection(connectionString); connection.Open(); // […]

13

Cualquier clase que implemente la interfaz IDisposable expone el método Dispose(). Es un requisito indispensable que cualquier instancia de IDisposable invoque al método Dispose cuando el objeto ya no sea útil y antes de que quede fuera de alcance. De este modo se liberan los recursos no administrados mantenidos por el objeto.

Si no se invoca a Dispose(), los recursos mantenidos por el objeto no serán liberados y permanecerán en memoria, degradando el rendimiento del sistema. Por otro lado, el método Dispose() de la clase SqlConnection invoca internamente al método Close() por lo que basta con llamar al método Dispose() para cerrar la conexión y liberar los recursos mantenidos por ésta, en una sola invocación. Teniendo esto en cuenta, deberíamos reformular el código anterior de la siguiente forma:

De nuevo, este código sigue siendo incompleto, pues no tiene en cuenta que el constructor y método Open pueden lanzar excepciones. En el apartado siguiente tendremos en cuenta esta situación para mejorar el código.

4.4. CAPTURA DE EXCEPCIONES Si consultas la documentación del método Open() de la clase SqlConnection (http://msdn.microsoft.com/es-es/library/system.data.sqlclient.sqlconnection.open(v=vs.110).aspx), comprobarás que este método puede lanzar excepciones bajo determinadas circunstancias (por ejemplo, si se invoca Open() sobre una conexión que ya estaba abierta o si no se puede conectar con el servidor de bases de datos, se lanzará una excepción de tipo InvalidOperationException). De igual forma, el constructor puede lanzar una excepción de tipo ArgumentException si la cadena de conexión que recibe como parámetro no tiene un formato adecuado. Si en el código de la aplicación se realizan llamadas a un método que puede lanzar excepciones y éstas no se capturan, la excepción ascenderá (burbujeará) por la pila de llamadas. Si la excepción no se captura en ningún punto, la aplicación se detendrá.

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" SqlConnection connection = new SqlConnection(connectionString); connection.Open(); // Ejecución de consultas […] // No es necesario llamar a Close(). El método Dispose() lo llama internamente. // connection.Close(); connection.Dispose(); // […]

http://msdn.microsoft.com/es-es/library/system.data.sqlclient.sqlconnection.open(v=vs.110).aspx)

14

En el código del apartado anterior no se ha controlado la posibilidad de que se genere una excepción por lo que, en caso de producirse, la aplicación podría detenerse. Además, se suma la circunstancia de que, en caso de producirse una excepción en el método Open, no se llamaría al método Dispose y, por tanto, no se liberarían los recursos mantenidos por el objeto SqlConnection, por lo que se degradaría el rendimiento del sistema.

Una implementación más correcta del código anterior sería ésta:

En esta implementación, el código se encuentra dentro de un bloque try - finally. En esta construcción del lenguaje, las sentencias que pueden ser susceptibles de lanzar excepciones se sitúan en el bloque try. Si se quiere capturar la excepción para realizar un tratamiento de la misma, se puede añadir una cláusula catch. Por último, en la cláusula finally debe añadirse el código para liberar recursos. Tanto si el bloque de instrucciones try termina correctamente como si se generan excepciones, se ejecutará el bloque finally, donde se llama al método Dispose() que, en primer lugar, cierra la conexión y, a continuación libera los recursos mantenidos por el objeto.

Si además se quiere realizar algún tratamiento de la excepción (como registrarla en un log) se puede añadir una cláusula catch a la estructura try anterior.

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" SqlConnection connection = null; try

{ connection = SqlConnection(connectionString); connection.Open();

// Ejecución de consultas […] } finally { if (connection != null) {

connection.Dispose(); connection = null; }

} // […]

15

4.5. CONNECTION POOLING Establecer y mantener una conexión con un servidor de bases de datos es una tarea que consume recursos en la máquina que establece la conexión y además introduce cierta latencia derivada de la transmisión de datos por la red. En cada operación de apertura o cierre es necesario realizar una serie de pasos como la apertura de un socket, realizar el handshake inicial, analizar la cadena de conexión, autenticar al usuario contra el servidor, etc. que consumen tiempo y recursos. Del mismo modo, mantener una conexión abierta también supone un consumo de recursos. La estrategia a aplicar para manejar las conexiones al servidor consiste en abrir la conexión inmediatamente antes de usarla y cerrarla en cuanto ya no es necesaria. De este modo, cuando la conexión no se necesita, no ocupa espacio en memoria y no consume recursos derivados del mantenimiento de la conexión. Sin embargo, esta estrategia no está libre de desventajas:

Introduce una pérdida de rendimiento, derivada de la carga de trabajo que supone abrir y cerrar una conexión cada vez que la aplicación necesita ejecutar una consulta.

Introduce tiempos de espera ocasionados por el tráfico de red que genera el establecimiento y cierre de la conexión

Para evitar estas desventajas, ADO .NET incorpora en todos sus proveedores de datos una técnica de optimización denominada connection pooling (http://msdn.microsoft.com/es-es/library/8xx3tyca(v=vs.110).aspx), que permite reducir el número de veces que debe abrirse una conexión, utilizando para ello una "piscina" (pool) de conexiones reutilizables. Cuando la aplicación invoca al método Open de una clase derivada de DbConnection, el motor de ejecución busca en la piscina de conexiones una conexión disponible. Si la encuentra, devuelve esta conexión a la aplicación en vez de abrir una nueva. Cuando la aplicación invoca al método Close de una clase derivada de DbConnection, el motor de ejecución retorna la conexión a la piscina en vez de cerrarla. Una vez que la conexión ha sido devuelta a la piscina queda disponible para ser reutilizada en otra llamada al método Open. El motor de ejecución es el encargado de mantener las piscinas de conexiones. Se crea una piscina de conexiones distinta por cada cadena de conexión distinta utilizada en un objeto DbConnection (aunque pueden existir otros criterios de creación de piscinas para los distintos proveedores de datos). Por tanto, es imprescindible garantizar que las conexiones de la aplicación que quieran participar en el pool utilicen exactamente la misma cadena de conexión.

http://msdn.microsoft.com/es-es/library/8xx3tyca(v=vs.110).aspx)

16

5. EJECUCIÓN DE CONSULTAS La clase SqlCommand representa una consulta lanzada contra el servidor de bases de datos. El código para ejecutar la consulta se lleva a cabo en cuatro fases:

1. Obtener una conexión (visto en el apartado 4). 2. Instanciar un objeto del tipo SqlCommand a partir de la conexión. 3. Establecer las propiedades del objeto SqlCommand. 4. Lanzar la consulta.

5.1. INSTANCIACIÓN DE SqlCommand Para crear una instancia de SqlCommand basta con invocar al método CreateCommand() de la clase SqlConnection. La clase SqlCommand implementa la interfaz IDisposable, por lo que, una vez que el objeto ya no es necesario y, antes de quede fuera de alcance, debe llamarse al método Dispose, tal y como hicimos con la conexión.

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" SqlConnection connection = null; SqlCommand command = null; try

{ connection = SqlConnection(connectionString); connection.Open();

command = connection.CreateCommand(); // Ejecución de consultas […]

} finally { if (command != null) {

command.Dispose(); command = null; }

if (connection != null) {


} // […]

17

5.2. ESTABLECIMIENTO DE LAS PROPIEDADES DE SqlCommand

Una vez que disponemos de una instancia de SqlCommand podemos configurar las propiedades del objeto para establecer la consulta a ejecutar y las opciones de ejecución. Como mínimo, debe establecerse el tipo de consulta, mediante la propiedad CommandType y la sentencia SQL o el nombre del procedimiento almacenado a ejecutar, mediante la propiedad CommandText. Si la consulta o el procedimiento almacenado esperan recibir parámetros, además será necesario instanciar y configurar un objeto de tipo SqlParameter por cada parámetro y agregarlo a la colección Parameters del objeto SqlCommand. Al trabajar con el proveedor de datos de SqlServer hay que tener en cuenta lo siguiente:

Si en la propiedad CommandText se utilizan parámetros, estos deben identificarse mediante el carácter '@' seguido del nombre del parámetro.

Los parámetros almacenados en la propiedad Parameters deben tener un nombre que coincida exactamente con el nombre del parámetro introducido en la consulta de la propiedad CommandText (incluyendo el carácter '@')

Si se invoca a un procedimiento almacenado, el nombre de los parámetros de la propiedad Parameters debe coincidir exactamente con el nombre de los parámetros definidos en el procedimiento almacenado.

Por el contrario, al trabajar con los proveedores de datos OleDb y Odbc, se debe tener en cuenta que:

Los parámetros se señalizan con un carácter '?'

El nombre de los parámetros en la propiedad Parameters es irrelevante, puesto que la asociación se realiza por el orden de los mismos, es decir, el primer ? se hace coincidir con el primer parámetro y así sucesivamente.

18

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" ; SqlConnection connection = null; SqlCommand command = null; try

{ // Obtiene una conexión del pool

connection = new SqlConnection(connectionString); connection.Open();

// Obtiene el objeto SqlCommand y establece sus propiedades command = connection.CreateCommand();

command.CommandType = CommandType.StoredProcedure; command.CommandText = "ListProducts";

// Crea un parámetro y lo añade a la colección de parámetros // del objeto SqlCommand.

SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@id";

parameter.SqlDbType = SqlDbType.BigInt; parameter.Direction = ParameterDirection.Input; parameter.Value = 27; command.Parameters.Add(parameter); // […]





} // […]

19

5.3. EJECUCIÓN DE LA CONSULTA Y LECTURA DE RESULTADOS EN MODO CONECTADO

El objeto SqlCommand dispone de tres métodos para ejecutar la consulta y recoger los resultados:

ExecuteScalar(): ejecuta la consulta y devuelve la primera columna de la primera fila del conjunto de resultados devuelto por la consulta o null si el conjunto resultante está vacío. Las demás columnas o filas no se tienen en cuenta. Este método suele utilizarse cuando la consulta debe devolver algún resultado numérico (como en un SELECT COUNT… ) o un valor único. Si el valor de la primera columna es de tipo texto, devuelve un máximo de 2033 caracteres.

ExecuteReader(): ejecuta la consulta y devuelve un objeto SqlDataReader que representa un lector en modo de sólo-lectura y sólo-avance que puede recorrer los resultados. Este método se utiliza siempre que la consulta devuelve un conjunto de filas y es necesario recorrerlas para su tratamiento (por ejemplo, para mostrarlas al usuario en una página). También se puede utilizar cuando la consulta devuelve más de un conjunto de resultados (por ejemplo, si se ejecuta un procedimiento que lanza varias sentencias SELECT y se quiere recorrer los resultados de cada una de las sentencias).

ExecuteNonQuery(): ejecuta un consulta que no genera resultados y devuelve el número de filas afectadas por la consulta. Se utiliza con:

o Procedimientos almacenados que no generan resultados. o Sentencias DDL (CREATE, ALTER, DROP) o Sentencias DML de modificación (INSERT, UPDATE, DELETE)

20

En el siguiente ejemplo se utiliza una consulta que contiene un parámetro. La consulta contiene una sentencia SQL COUNT, por lo que el resultado que devuelve es un entero: El método ExecuteReader es un método conectado puesto que, para recorrer el conjunto de resultados, el lector debe permanecer conectado a la base de datos. Además es un método de sólo-lectura, que no permite posicionarse aleatoriamente en una fila para modificar los datos.

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" SqlConnection connection = null; SqlCommand command = null; try


connection = SqlConnection(connectionString); connection.Open();


command.CommandType = CommandType.Text; command.CommandText = "SELECT COUNT(*) FROM Productos WHERE Precio > @precio";

// Crea un parámetro y lo añade a la colección de parámetros // del objeto SqlCommand.

SqlParameter parameter = new SqlParameter(); parameter.ParameterName = "@precio";

parameter.SqlDbType = DbType.Currency; parameter.Direction = ParameterDirection.Input; parameter.Value = 200.0; command.Parameters.Add(parameter); int result = (int) command.ExecuteScalar(); // […]





} // […]

21

En este ejemplo se ejecuta el siguiente procedimiento almacenado, llamado "ListClients":

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" SqlConnection connection = null; SqlCommand command = null; SqlDataReader reader = null; try




command.CommandType = CommandType.StoredProcedure; command.CommandText = "ListClients";

reader = command.ExecuteReader(); if (reader.HasRows) { while(reader.Read()) { String name = null; Decimal? amount = null; if (!reader.IsDBNull(0)) name = reader.GetString(0); if (!reader.IsDBNull(1)) amount = reader.GetDecimal(1); Console.Out.WriteLine(name); Console.Out.WriteLine(amount); } }

(continúa...)

SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE ListClients AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT Name, Amount FROM OldClients SELECT Name, LastDate FROM NewClients END GO

22

if (reader.NextResult() && reader.HasRows) { while(reader.Read()) { String name = null; DateTime? lastDate = null; if (!reader.IsDBNull(0)) name = reader.GetString(0); if (!reader.IsDBNull(1)) lastDate = reader.GetDateTime(1); Console.Out.WriteLine(name); Console.Out.WriteLine(lastDate); } } } finally {

if (reader != null) { reader.Dispose(); reader = null; } if (command != null) {




}

23

5.4. EJECUCIÓN DE LA CONSULTA Y RECOGIDA DE RESULTADOS EN MODO DESCONECTADO

Para almacenar los resultados de la consulta en un Dataset hay que instanciar un objeto SqlDataAdapter, que representa un puente o adaptador entre los resultados de la consulta y el Dataset. El objeto SqlDataAdapter dispone de cuatro propiedades esenciales:

SelectCommand: permite especificar un objeto SqlCommand que contiene la consulta a ejecutar para cargar el Dataset.

InsertCommand: permite especificar un objeto SqlCommand que contiene la consulta a ejecutar para insertar en la base de datos las filas que se han insertado en el Dataset.

UpdateCommand: permite especificar un objeto SqlCommand que contiene la consulta a ejecutar para modificar en la base de datos las filas que han sido modificadas en el Dataset.

DeleteCommand: permite especificar un objeto SqlCommand que contiene la consulta a ejecutar para eliminar de la base de datos las filas que han sido eliminados del Dataset.

No es obligatorio especificar las cuatro propiedades:

Antes de invocar al método Fill debe haberse establecido la propiedad SelectCommand.

Antes de invocar al método Update debe haberse establecido una o varias de las propiedades InsertCommand, UpdateCommand y/o DeleteCommand.

Por ejemplo, si únicamente se quieren cargar datos en el Dataset para su lectura (no se van a insertar, modificar o eliminar filas del Dataset), basta con especificar la propiedad SelectCommand. Una vez establecidas las propiedades anteriores, basta con invocar al método Fill del DataAdapter para cargar los resultados en un Dataset. Si, por el contrario, se quieren consolidar los cambios del Dataset en el origen de datos, hay que invocar al método Update.

24

// […] const String connectionString = "Data Source=192.168.150.150,1433;Initial Catalog=Web;User ID=pepe;Password=sesamo;" SqlConnection connection = null; SqlCommand command = null; SqlDataAdapter dataAdapter = null; DataSet ds = null; try




command.CommandType = CommandType.StoredProcedure; command.CommandText = "ListClients";

// Crea un DataAdapter y le establece el objeto SqlCommand para cargar // los datos en el Dataset. dataAdapter = new SqlDataAdapter();

dataAdapter.SelectCommand = command;

ds = new DataSet(); // Ejecuta la sentencia contenida en el objeto SqlCommand y carga los // resultados en el Dataset.

dataAdapter.Fill(ds);

// Leer el dataset ... }

finally {

if (ds != null) {

ds.Dispose(); ds = null; }

if (dataAdapter != null) {

dataAdapter.Dispose(); dataAdapter = null; }

if (command != null) {




} // […]

25

5.5. CONSULTAS PRECOMPILADAS Y PROCEDIMIENTOS ALMACENADOS

Un procedimiento almacenado es una consulta almacenada en el servidor de bases de datos. La utilización de procedimientos almacenados en vez de consultas embebidas en el código de la aplicación reporta dos grandes beneficios:

Rendimiento: al conocer de antemano la consulta, el servidor puede analizarla, optimizar su ejecución, aplicando información estadística, y almacenar el plan de ejecución resultante, donde se determina el modo más eficiente de acceder a la información solicitada en la consulta.

La ejecución de una consulta utilizando el plan de ejecución almacenado en el servidor puede llegar a suponer mejoras drásticas en el tiempo de obtención de resultados (respecto a la misma consulta sin un plan de ejecución previo).

Arquitectura: al llevar las consultas al servidor de bases de datos se centraliza su administración y se consigue una mayor independencia entre la capa de acceso a datos y la capa de aplicación.

De este modo, se hace posible modificar las consultas de los procedimientos almacenados sin necesidad de realizar cambios en el código de la aplicación (siempre que se mantengan los nombres, número y tipo de los parámetros del procedimiento).

Introducir una consulta SQL embebida en el código de una aplicación se considera una práctica desaconsejable. Si la consulta se ejecuta repetidamente y el servidor de bases de datos no es capaz de reutilizar un plan de ejecución para esa consulta, el rendimiento se vería afectado. Si en determinadas circunstancias resulta imprescindible incluir una consulta en el código y dicha consulta va a ser ejecutada repetidamente, la clase DbCommand incluye un método Prepare() que permite generar un plan de ejecución temporal para dicha consulta. Mientras dure el ciclo de vida de la aplicación se podrá reutilizar el plan de ejecución creado. No obstante, a este tipo de consultas "precompiladas" no se le aplican las mismas optimizaciones que a un procedimiento almacenado y tienen un tiempo de vida limitado.

26

ANEXO I

ACERCA DE LA CONFIGURACIÓN DE APLICACIONES EN .NET ASP .NET almacena la información de configuración necesaria para el servidor y las aplicaciones en archivos de configuración XML. Al instalar el motor de ejecución en el servidor web, se crea un archivo de configuración principal llamado machine.config, ubicado en el directorio:

En este fichero se almacena la configuración básica para el servidor y la configuración compartida por todas las aplicaciones. Normalmente, este fichero no es accesible directamente, aunque su información se hereda por los ficheros de configuración de la aplicación. Para especificar información de configuración específica para una determinada aplicación, es posible utilizar uno o varios ficheros de configuración de aplicación, con el nombre Web.config. Cada fichero de configuración Web.config establece la configuración para el directorio en el que se encuentra y también, recursivamente, para todos los subdirectorios del mismo. Además, cada archivo Web.config hereda la configuración (salvo que se especifique lo contrario) del archivo de configuración de nivel superior. El archivo Web.config principal de la aplicación (situado en el directorio raíz de la aplicación) hereda directamente la configuración de machine.config. Normalmente, estos archivos se editan en tiempo de diseño para establecer valores de configuración y la aplicación accede a estos valores en tiempo de ejecución en modo de sólo-lectura. No suele ser necesario -ni apropiado- modificar los valores de configuración en tiempo de ejecución, como veremos a continuación. Para acceder a la configuración de un archivo Web.config de la aplicación se utiliza la clase WebConfigurationManager del paquete System.Web.Configuration. Esta clase expone métodos para acceder directamente en modo lectura a las distintas secciones del documento.

<directorio_de_instalación>:\Windows\Microsoft.Net\<Framework_32_ó_64>\<versión>\Config

27

Como ya se ha indicado, para modificar una determinada configuración de un archivo Web.config se edita el fichero directamente en tiempo de diseño. Sin embargo, si queremos modificar el fichero en tiempo de ejecución debemos abrir el archivo mediante el método OpenWebConfiguration de la clase WebConfigurationManager. Este método carga en un objeto de tipo Configuration toda la configuración del archivo Web.config. Sobre el objeto Configuration pueden realizarse modificaciones que luego pueden ser consolidadas en el archivo Web.config mediante el método Save. Escribir en el fichero de configuración (ya sea en tiempo de ejecución o subiendo un nuevo archivo Web.config al servidor en producción para sobrescribir el antiguo) plantea dos inconvenientes:

Problemas de acceso concurrente: como ocurre al manipular cualquier fichero, si se espera que múltiples clientes puedan acceder simultáneamente a este archivo, será necesario establecer algún mecanismo de bloqueo. De no hacerlo, si después de crear el objeto Configuration otro proceso o usuario realiza cambios sobre Web.config, se lanzará una excepción al invocar a Save.

Reinicio de la aplicación: cada vez que se realiza una modificación a alguno de los ficheros Web.config (exceptuando los fichero externos), se produce un reinicio de la aplicación. Esto implica que los valores almacenados en el contexto de aplicación y sesión son eliminados, así como los valores de cualquier tipo estático cargado en la aplicación, iniciando un nuevo ciclo de vida e invocando al método Application_Start del archivo global.asax.

Abordaremos de manera más extensa la configuración de aplicaciones en la unidad 06.

28

ANEXO II

GESTIÓN DE USUARIOS Y CONTRASEÑAS La gestión de usuarios y contraseñas en un sistema en producción requiere de la toma de ciertas medidas de seguridad para mantener la privacidad de los datos de los usuarios y prevenir ataques. Aunque los requerimientos de diseño son sencillos, no siempre se aplican, con la consiguiente pérdida de seguridad del sistema. Las pautas de diseño generales, aptas para la mayoría de escenarios, son las siguientes:

Cualquier cadena manejada en el código de la aplicación que contenga información sensible debe encapsularse en un tipo SecureString. Este tipo es un String normal pero garantiza que:

o El valor de la cadena únicamente estará disponible en memoria (no será posible serializarla ni guardarla en memoria secundaria).

o El valor de la cadena sólo será accesible desde el código de la propia aplicación. No será posible acceder con una herramienta externa de auditoría al valor de la cadena.

NUNCA se deben almacenar las claves de los usuarios directamente en la base de datos, NI SIQUIERA SI ESTÁN ENCRIPTADAS. La práctica correcta consiste en codificar las contraseñas mediante un algoritmo de digestión digital o "hash". El resultado de la contraseña "hasheada" es el valor que se almacena en la base de datos. Un algoritmo de hash define una operación matemática que, realizada sobre una secuencia de caracteres, permite obtener una cadena alfanumérica de tamaño fijo. El algoritmo de hash cumple dos condiciones:

o Dos cadenas de caracteres distintas, producen resultados distintos. Incluso si las cadenas sólo difieren en un carácter, los resultados del hash son completamente diferentes.

o Es matemáticamente "difícil" revertir un hash, es decir, resulta computacionalmente inviable obtener la cadena de caracteres original a partir del resultado de un hash.

29

No obstante, existe una técnica de fuerza bruta, basada en el empleo de "tablas rainbow", que hace posible reducir la criba del espacio numérico, facilitando la tarea de revertir el hash. Para evitar este tipo de ataques, se utiliza una contratécnica mediante la cual el hash no se realiza directamente sobre la contraseña sino que se realiza sobre una combinación de la contraseña y un valor llamado salt, que añade entropía (desorden) al sistema criptográfico, iterando, además, la operación de digestión varias veces.

El almacenamiento de contraseñas "hasheadas" impone dos condiciones en la operativa de acceso a las contraseñas:

o Para verificar si un usuario ha introducido correctamente su contraseña será necesario tomar la contraseña introducida por el usuario, hashearla y comparar ese valor hasheado con el almacenado en la base de datos.

o Si el usuario pierde u olvida su contraseña no es posible

recordársela, puesto que lo que se almacena en la base de datos es el valor hasheado de su contraseña y no existe un método para obtener la contraseña original. Será necesario que el usuario se autentique mediante un mecanismo alternativo (como las preguntas de seguridad) para generar una nueva contraseña.

En la consulta ejecutada para realizar la autenticación del usuario debe tenerse en cuenta lo siguiente:

o Se deben tratar los datos introducidos por el usuario, en el cliente y en el servidor, para verificar que no contienen inyección de código. Por ejemplo, pueden codificarse a una secuencia equivalente caracteres y secuencias como "--", "'", "=", etc. que podrían ser reconocidas como elementos de SQL (o Transact-SQL) y producir inyección de código.

o NUNCA se debe generar la consulta de autenticación de manera dinámica, concatenando la consulta SQL con los valores introducidos por el usuario, ni siquiera si los valores han sido tratados previamente. La práctica correcta consiste en almacenar los valores suministrados por el usuario en parámetros del procedimiento o consulta y ejecutar dicho procedimiento o consulta con parámetros.

o La sentencia SELECT que realiza la autenticación no debe devolver columnas de la tabla de usuarios sino valores discretos preestablecidos.

Abordaremos de manera más extensa la gestión de la seguridad en la unidad 08.

01 - unidad 04 - ado.net.pdf

Documents