.NET: SqlConnection y compañía

La clase SqlConnection es la principal para trabajar con bases de datos en .NET, ya que es precisamente esta clase la responsable de la comunicación con el servidor de base de datos, además de la interacción entre sentencias enviadas y resultados obtenidos.

Un objeto de tipo SqlConnection no tiene mucha complejidad, pero debemos saber utilizarlo correctamente, ya que hay aspectos que debemos tomar en cuenta para maximizar el ahorro de recursos en nuestras aplicaciones.

Básicamente, el trabajo de un objeto SqlConnection es: guardar los datos del servidor SQL al que nos conectaremos, abrir la conexión para realizar operaciones, y cerrar la conexión cuando ya no la ocupemos. Siempre debemos cuidar que esas operaciones se realicen.

private void SentenciaSQL(string query){
  SqlConnection connection = new SqlConnection(@"server=servidor\instancia;database=base_de_datos;user id=usuario;password=contraseña");
  SqlCommand command = connection.CreateCommand();
  command.CommandText = query;
  connection.Open();
  command.ExecuteNonQuery();
  connection.Close();
}

Algo muy sencillo. Pero, ¿qué pasará si hay un error en la ejecución de este código? Por ejemplo, que la sentencia esté mal escrita, que la definición de tablas en la base de datos haya cambiado, que nuestro comando incluya parámetros que no definimos en el código, o que estos comandos no hayan recibido datos correctos desde la interfaz de usuario, o simplemente que el servidor esté caído o la conexión no puede darse con el cliente. Tal vez este último no sea tanto problema, pues simplemente le indicamos al usuario que hubo un error y lo reintente.

Pero los demás errores sucederán después de haber abierto la conexión con el servidor. Incluso, por la misma complejidad del código, tal vez tengamos operaciones ajenas a la base de datos, las cuales también pueden provocar un error. Entonces, cuando nuestro código sea interrumpido por un error, se "brincará" la instrucción para cerrar la conexión, es decir, este paso nunca se dará, y la conexión quedará innecesariamente abierta hasta que el servidor la interrumpa por inactividad.

El asunto es estar seguros que la conexión que se abra siempre se cerrará. Tal vez no siempre podamos evitar que surja un error, pero lo que sí podemos es controlarlos con ayuda del bloque try - catch - finally.

Los bloques try – catch son muy comunes en la mayoría de los lenguajes de programación, y muy conocidos. Básicamente lo que se hace es agrupar una serie de instrucciones bajo el bloque try, seguido del bloque catch para que, si se presenta un error, evitar que éste se escale hasta el nivel de interfaz, permitiéndonos controlar dicho error y mostrar al usuario lo que queremos que vea, en lugar de la ventana de error de .NET (la que cierra nuestras aplicaciones) o el "pantallazo amarillo" de ASP.NET.

Lo que es menos conocido es el bloque finally, el cual se siempre se ejecuta, sin importar si lo agrupado dentro de try se ejecuta correctamente o si se presentó un error y se cayó dentro de catch. Es aquí donde podemos hacer todas las operaciones necesarias para el cierre de las operaciones, como es el terminar la conexión a la base de datos.

SqlConnection connection = new SqlConnection("[connection string]");
try {
  connection.Open();
  SqlCommand command = connection.CreateCommand();
  command.CommandText = "INSERT INTO Tabla (var1) VALUES (1)";
  command.ExecuteNonQuery();
} catch (Exception ex) {
  throw new Exception("Error en la inserción", ex);
} finally {
  if (connection.State != ConnectionState.Closed)
    connection.Close();
  connection.Dispose();
}

Para terminar, les dejo un ejemplo más completo utilizando distintos bloques try – catch, SqlConnection, SqlCommand, SqlDataReader y SqlTransaction, que son la mayoría de los objetos que podrían utilizar en una operación de base de datos. Puse comentarios donde consideré necesario.

public int Guardar() {
  // Un objeto que implementa IDisposable puede ir dentro de un bloque using
  // para ahorrarnos el paso de ejecutar la función Dispose.
  // Esto se hará automáticamente al final del bloque.
  using (SqlConnection connection = new SqlConnection("[cadena de conexion]")) {
    int newId = 0;
    SqlCommand command = connection.CreateCommand();
    SqlDataReader reader = null;
    command.CommandText = "SELECT MAX(id) FROM Tabla";

    try {
      // Paso 1: Agrupamos todas las operaciones en un bloque try-catch
      // BEGIN TRAN desde .NET
      command.Transaction = connection.BeginTransaction();

      // Paso 2: Abrir la conexión
      try {
        connection.Open();
      } catch (Exception ex2) {
        throw new Exception(
          "No se pudo conectar a la base de datos.", ex2);
      }

      // Paso 3: Obtener último ID de la tabla
      try {
        reader = command.ExecuteReader();
        if (reader.Read() && !DBNull.Value.Equals(reader.GetValue(0)))
          newId = reader.GetInt32(0) + 1;
        else
          newId = 1;
      } catch (Exception ex3) {
        throw new Exception(
          "No se pudo consultar la información de la base de datos.", ex3);
      } finally {
        if (!reader.IsClosed)
          reader.Close();
      }

      // Paso 4: Inserción en la tabla
      command.CommandText = "INSERT INTO Tabla VALUES(@id, @nombre)";
      command.Parameters.AddWithValue("@id", id);
      command.Parameters.AddWithValue("@nombre", nombre);
      try {
        command.ExecuteNonQuery();
      } catch (Exception ex4) {
        throw new Exception(
          "No se pudo insertar registro en la base de datos.", ex4);
      }

      // Paso 5: Guardar en bitácora usando Stored Procedure
      command.CommandText = "Registrar_En_Bitacora";
      command.CommandType = CommandType.StoredProcedure;
      command.Parameters.Clear();
      command.Parameters.AddWithValue("@texto", "Registro agregado a la Tabla.");
      try { 
        command.ExecuteNonQuery();
      } catch (Exception ex5) {
        throw new Exception(
          "No se pudo registrar la operación en bitácora.", ex5);
      }

      // COMMIT TRAN desde .NET
      command.Transaction.Commit();

    } catch (Exception ex1) {
      // ROLLBACK TRAN desde .NET
      // Lo colocamos en un try - catch por si la transacción ya está cerrada
      try { command.Transaction.Rollback(); }
      catch { }

      if (connection.State != ConnectionState.Closed)
        connection.Close();
      throw ex1;
    }
    return newId;
  }
}