buenas prácticas

ADO.NET es un conjunto de clases que exponen servicios de acceso a datos para programadores de .NET Framework. […]
Constituye una parte integral de .NET Framework y proporciona acceso a datos relacionales, XML y de aplicaciones.[…]

Las clases que nos brinda ADO.NET representan todos los conceptos básicos y necesarios para acceder a cualquier orígen de datos y realizar cualquier tipo de operación: Conexión, Transacción, Comando, Parametro, Set de datos, Tabla, y más…

Es importante que conozcamos qué es y cómo funciona ADO.NET porque siempre lo vamos a estar utilizando al acceder a datos, ya sea de manera directa mediante el uso de sus clases o de manera indirecta al utilizar alguna herramienta de mapeo de objetos a datos (ORM) o librería de terceros, las cuales internamente harán uso de ADO.NET.

Es muy importante leer la documentación existente en el Sitio oficial y siempre es muy útil seguir las novedades en el Blog oficial.

La cantidad de artículos y videos no oficiales que existen con tutoriales del estilo «Cómo conectarse a SQL Server desde C#» o «Accediendo a datos con .NET», es incontable y crece día a día. Pero, lamentablemente, es común encontrar en ellos errores y/o malas prácticas que afectan la seguridad y/o performance de la aplicación final. Es por eso que este artículo intento destacar las mejores prácticas que podemos seguir y también mencionar los principales errores que debemos evitar, basado en mis propias experiencias y en libros/documentación oficial.

Cadenas de conexión

Para poder conectarnos a un orígen de datos, vamos a necesitar una cadena de conexión o Connection String. Es altamente probable que una cadena de conexión cambie, por ej.: porque cambia el ambiente en el cual se está ejecutando la aplicación (desarrollo, testing, producción, etc.) o porque cambiaron las credenciales de acceso a una BD.

Es por esto que debemos evitar definir las cadenas de conexión dentro del código (hard-coded), ya que para cambiarlas vamos a tener que: modificar el código, compilar e instalar la nueva versión de la aplicación. Lo recomendable entonces, es definirlas en algún medio en el cual puedan ser modificadas de manera fácil, rápida y sin afectar la aplicación.

Siguiendo esta recomendación, el medio recomendado para las cadenas de conexión es el archivo de configuración de la aplicación (web.config o app.config, según el tipo de aplicación). Allí contamos con la sección dedicada al respecto <connectionStrings>.

Modo ineficaz:

string connectionString = "Server=.;Database=Ejemplo;Integrated Security=True";
using (var conn = new SqlConnection(connectionString))
{
    //  ...
}

Modo eficiente:

&lt;connectionStrings&gt;
  &lt;add name="EjemploBD"
        connectionString="Server=.;Database=Ejemplo;Integrated Security=True"
        providerName="System.Data.SqlClient" /&gt;
&lt;/connectionStrings&gt;

string connectionString = ConfigurationManager.ConnectionStrings["EjemploBD"].ConnectionString;
using (var conn = new SqlConnection(connectionString))
{
    // ...
}

Opcionalmente, también contamos con la funcionalidad de encriptar todas las cadenas de conexión del archivo de configuración para una mayor seguridad. Mas info al respecto acá.

En una sección posterior de este artículo también veremos una interesante utilidad que le podemos dar al dato providerName…

NO usar «conexiones estáticas»

En muchas oportunidades encontré proyectos que usan una variable estática para guardar el objeto de conexión a la BD. Probablemente la intención de esto es evitar abrir nuevas conexiones para cada operación que se desea realizar, ya que es un proceso costoso (tanto en tiempo como recursos).

Guardar la conexión en una variable estática implica que ésta será única para toda la aplicación. Lo cual sería completamente aceptable si la aplicación utilizara un único hilo de ejecución y fuera usado por un único usuario, sin ningún tipo de concurrencia. Aunque difícilmente una aplicación tendrá dichas características…

Pongamos como ejemplo una aplicación web que usa una «conexión estática»: cada usuario que acceda al sitio será atendido usando un nuevo hilo de ejecución del proceso que ejecuta la aplicación, imaginemos que un primer usuario accederá a una página que utilizará la conexión para obtener una lista de registros a mostrar, y ahora imaginemos que un segundo usuario accede en ese preciso instante a otra página de la aplicación la cual también intentará utilizar la conexión para obtener otra lista de registros a mostrar, pero lamentablemente se encontrará con que la conexión no está disponible y verá un «hermoso» mensaje de error en pantalla… Sólo por mencionar uno de los tantos escenarios de error posibles.

En definitiva: «conexión estática» = muy mala práctica.

Entonces, ¿cómo abrir una conexión sin problemas y de la manera mas performante? La respuesta es simple:

Crear una conexión.
Abrir la conexión.
Usar la conexión para realizar las operaciones necesarias.
Cerrar la conexión.
Liberar los recursos.

Pero, ¿no es acaso costoso abrir una nueva conexión? Si. Pero ADO.NET emplea un proceso llamado Connection Pooling o agrupación de conexiones para resolver este problema. Para explicarlo de manera resumida y muy simplificada, se encarga de llevar una lista de las últimas conexiones abiertas y cada vez que se intente abrir una nueva conexión busca en dicha lista alguna similar para reutilizarla. Todo esto sucede mágicamente bajo nuestras narices, y por lo tanto podemos despreocuparnos del tema.

Pueden ver mas info al respecto acá.

Liberar los recursos luego de utilizarlos

Este aspecto no es algo propio del ADO.NET, sino más bien de todo el Framework .NET y que siempre debemos tenerlo en cuenta en el desarrollo de nuestras aplicaciones.

Haciendo un brevísimo y simplificado resúmen podemos decir lo siguiente:
Existen objetos que internamente utilizan ciertos recursos que deben ser liberados luego de ser utilizados (entre ellos, las conexiones a bases de datos). Estos objetos implementan la interfaz IDisposable, que publica un único método Dispose, el cual se encarga de liberar dichos recursos. Quien utiliza estos objetos tiene la responsabilidad de llamar siempre al método Dispose una vez que haya finalizado con su utilización. No liberar estos recursos puede deteriorar la performance de la aplicación, y en algunos casos extremos incluso puede causar la caída del servidor (al agotar el procesamiento/memoria). La manera más conveniente de asegurar la llamada al método Dispose de un objeto es mediante el uso del bloque using. Este bloque permite declarar cualquier objeto que implemente la interfaz Dispose, dentro del cual estará disponible para ser utilizado dicho objeto, y se encargará de llamar automáticamente al método Dispose cuando se finalice la ejecución de todas las instrucciones que contiene, incluso si se produce un error.

Pueden encontrar más info acá.

Volviendo a ADO.NET, debemos asegurarnos de liberar todos los recursos de los objetos luego de utilizarlos. Podemos determinar fácilmente cuales son estos objetos verificando si implementan la interfaz IDisposable.

Modo ineficaz:

var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EjemploBD"].ConnectionString);
conn.Open();


var command = new SqlCommand("SELECT * FROM Usuarios", conn);
var reader = command.ExecuteReader();
while (reader.Read())
{
    // ...
}

//  Si se produce un error antes de llegar a esta línea, "reader" no se cierra ni libera correctamente sus recursos
reader.Close();
reader.Dispose();

//  Si se produce un error antes de llegar a esta línea, "command" no libera correctamente sus recursos
command.Dispose();

//  Si se produce un error antes de llegar a esta línea, "conn" no se cierra ni libera correctamente sus recursos
conn.Close();
conn.Dispose();

Modo eficiente:

//  Este bloque using cierra y libera automáticamente los recursos utilizados por "conn"
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EjemploBD"].ConnectionString))
{
    conn.Open();


    //  Este bloque using libera automáticamente los recursos utilizados por "command"
    using (var command = new SqlCommand("SELECT * FROM Usuarios", conn))

    //  Este bloque using cierra y libera automáticamente los recursos utilizados por "reader"
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // ...
        }
    }
}

Uso de parámetros

En la mayoría de los casos, definimos la consulta o comando a ejecutar de manera dinámica, en base a datos ingresados/seleccionados por el usuario o según las reglas de negocio definidas. En estos casos, hay quienes directamente definen en una cadena de texto la consulta, concatenando directamente los datos ingresados por el usuario formateando (cuando corresponda) textos, números, fechas, etc.

Por ejemplo:

string cmdTexto = String.Format("SELECT * FROM Usuarios WHERE Login = '{0}'", login);


using (var command = new SqlCommand(cmdTexto, conn))
{
    //  ...
}

Esta metodología tiene muchas falencias, pero las dos mas importantes a destacar son:

Presenta un grave riesgo de seguridad, frente a la técnica SQL Injection.
Necesita una «conversión manual» de tipos de datos al armar la consulta SQL. Lo cual es «trabajoso» y muy propenso a error.

La manera mas eficiente y segura de especificar los parámetros a utilizar en una consulta es mediante el uso del objeto ADO.NET que representa un parámetro. Podemos crearlo indicando solamente su nombre y su valor, para finalmente agregarlo a la lista de parámetros del comando. De esta manera, tanto la consulta SQL como la lista de parámetros serán enviados al orígen de datos sin que nosotros tengamos que hacer ninguna verificación de seguridad ni conversión de datos alguna.

Modo ineficaz e inseguro:

var cmdTexto = String.Format(
    "INSERT INTO Usuarios (Login, EstaActivo, FechaCreacion) VALUES ('{0}', {1}, '{2})",
    login, estaActivo ? 1 : 0, fechaCreacion.ToString("yyyy-MM-dd HH:mm:ss"));


using (var command = new SqlCommand(cmdTexto, conn))
{
    int filasAfectadas = command.ExecuteNonQuery();
}

Modo eficiente:

var cmdTexto = "INSERT INTO Usuarios (Login, EstaActivo, FechaCreacion) VALUES (@Login, @EstaActivo, @FechaCreacion)";


using (var command = new SqlCommand(cmdTexto, conn))
{
    command.Parameters.Add(new SqlParameter("@Login", login));
    command.Parameters.Add(new SqlParameter("@EstaActivo", estaActivo));
    command.Parameters.Add(new SqlParameter("@FechaCreacion", fechaCreacion));

    int filasAfectadas = command.ExecuteNonQuery();
}

Manejo de transacciones

Las clases ADO.NET tienen las clases y métodos necesarios para gestionar transacciones. Uno debe utilizar el método BeginTransaction en el objeto de la conexión, el cual inicia una transacción y devuelve un nuevo objeto que la representa. Este objeto debe ser asignado en los comandos que se desean ejecutar dentro del contexto de dicha transacción, y finalmente, debe ser aplicada (commit) o deshechada (rollback).

Sin embargo, existe una manera más sencilla e incluso más potente de manejar transacciones, mediante el uso de una clase del Framework llamada System.Transactions.TransactionScope. Para poder utilizar esta clase será necesario agregar una referencia a la librería System.Transactions del Framework.

En su uso más básico, simplemente basta definir un bloque en el cual se crea una instancia de la clase TransactionScope, y automáticamente todas las operaciones siguientes se ejecutarán dentro del contexto de una transacción. Todo esto se gestionará de manera automática y transparente para nosotros. Lo único que debemos hacer es llamar al método Complete antes de finalizar el bloque para aplicar todas las operaciones realizadas, de lo contrario se desharán todos los cambios.

También cuenta con la capacidad de decidir si es necesario crear una transacción distribuída, en caso de haber realizado operaciones que afecten a más de un orígen de datos, y lo hace también de manera automática. Esto se hace sólo si es estrictamente necesario, eficientizando al máximo el procesamiento.

Pueden encontrar más info acá.

Un ejemplo:

using (var scope = new TransactionScope())
{
    using (var conn1 = new System.Data.SqlClient.SqlConnection(connString))
    {
        conn1.Open();


        using (var cmd1 = conn1.CreateCommand())
        {
            cmd1.CommandText = commandText1;
            cmd1.ExecuteNonQuery();
        }
    }

    using (var conn2 = new System.Data.SqlClient.SqlConnection(connString))
    {
        conn2.Open();

        using (var cmd2 = conn2.CreateCommand())
        {
            cmd2.CommandText = commandText2;
            cmd2.ExecuteNonQuery();
        }
    }

    scope.Complete();
}

Uso de herramientas de mapeo de objetos a datos (ORM’s)

En mi opinión personal, una de las mejores alternativas es utilizar algún ORM que se encargue de resolver el acceso a datos, permitiéndonos enfocar pura y exclusivamente en la función principal de nuestra aplicación, brindando así valor agregado constantemente.

Estas herramientas también suelen ser la mejor opción ya que, al ser precisamente su principal función resolver el acceso a datos, lograrán una gran madurez y performance que probablemente nos tomaría a nosotros mucho tiempo.

EntityFramework es el ORM que recomiendo usar en las aplicaciones .NET.

Pueden encontrar más info acá.

Anexo: Acceso a datos único para cualquier tipo de BD.

Generalmente, al crear nuestras aplicaciones ya decidimos cual será el tipo de BD que usaremos: SQL Server, MySQL, Oracle, etc. Y podemos crear nuestra lógica de acceso a datos utilizando las clases ADO.NET correspondientes.

Sin embargo, existen aplicaciones que usan más de un tipo de BD. O quizás exista una alta probabilidad de migrar a otro tipo en alguna etapa avanzada del desarrollo.

Gracias a los patrones de diseño aplicados al diseñar las clases de ADO.NET, es muy fácil crear lógica de acceso a datos reutilizable para cualquier tipo de BD. Para ello es necesario hacer uso de las clases bases, de las cuales todos los Providers o proveedores de ADO.NET deben heredar.

Un ejemplo de cómo sería ese código sería algo así:

var dbFactory = DbProviderFactories.GetFactory(providerName);
if (dbFactory == null)
    throw new ArgumentException("providerName");


using (var conn = dbFactory.CreateConnection())
{
    conn.ConnectionString = connectionString;
    conn.Open();

    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = cmdText;

        //  Se pueden agregar parámetros
        var p1 = cmd.CreateParameter();
        p1.ParameterName = "@Parametro1";
        p1.Value = new DateTime(2014, 2, 24);
        cmd.Parameters.Add(p1);

        //  Ejecutar comando con la operación necesaria
        int filasAfectadas = cmd.ExecuteNonQuery();

        object valor = cmd.ExecuteScalar();

        //  Abrir un DataReader para recorrer la lista de registros obtenidos por el comando
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                //  hacer lo necesario con cada registro
            }
            reader.Close();
        }
    }
}

En el código pueden ver las variables:

providerName. Nombre del proveedor ADO.NET a utilizar. Ejemplos:
"System.Data.SqlClient" "System.Data.Odbc"
connectionString. Cadena de conexión a utilizar. Ejemplos:
"Server=(localhost);Database=Sistema;Integrated Security=True" "Server=(localhost);Database=Sistema;User ID=sa;Password=*****"
cmdText. Texto de la sentencia SQL a ejecutar. Ejemplos:
"SELECT [UsuarioId], [NombreUsuario], [EstaActivo] FROM [Usuarios]" "UPDATE [Usuarios] SET [EstaActivo] = @EstaActivo WHERE [UsuarioId] = @UsuarioId"

Este era el uso interesante del dato providerName que les había mencionado anteriormente.

Pueden encontrar el código completo de acceso a datos genérico acá.

Espero que les haya sido de utilidad. Se agradece mucho compartir/comentar o regalarme un café :)!

Matias Lopez

Mi memoria online

ADO.NET: Buenas prácticas en el acceso a datos.

Cadenas de conexión

NO usar «conexiones estáticas»

Liberar los recursos luego de utilizarlos

Uso de parámetros

Manejo de transacciones

Uso de herramientas de mapeo de objetos a datos (ORM’s)

Anexo: Acceso a datos único para cualquier tipo de BD.