Base de datos bloqueada en SQLite y .NET

En post anteriores se ha hablado sobre las bondades de SQLite trabajando en conjunción con .NET, un gran equipo que es eficiente y rápido (amén de optimizado por lo recursos que consume), pero puede dar alguno que otro quebradero de cabeza, especialmente con el bloqueo de base de datos (database locked).

La principal razón de que esto ocurra es que SQLite trabaja en una única sesión, por lo que la concurrencia queda descartada para grandes pretensiones. Incluso con esto en mente, trabajando en una aplicación de escritorio (no en servidor), esto puede suponer también un problema cuando queremos trabajar con varias consultas anidadas y actualizaciones.

En este post voy a contar mi experiencia y cómo he capeado este problema.

Ante todo, hay que reutilizar el código, por lo que el acceso a base de datos la realizo desde una clase con métodos públicos. En esta clase defino los objetos clave para el uso de la base de datos:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite; // No olvidar esta referencia

// Clase específica para acceso y carga de datos desde la base de datos
namespace TI_Facturas
{
  class DatosManager
  {
    private SQLiteConnection con = null;
    private SQLiteCommand cmd = null;
    private SQLiteDataReader dtr = null;
 }
}


Los objetos deben tener un uso exclusivo cada vez, por lo que se definen dos métodos: uno para iniciarlos y otro para despacharlos.


  // Inicializa los objetos de base de datos
  private void initSQL()
  {
    con = null;
    cmd = null;
    dtr = null;

    con = new SQLiteConnection("Data Source=ti_facturas.db;Version=3;New=False;Compress=True;");
    con.Open();
  }

  // Prepara la clase para ser despachada
  public void dispose()
  {
    if (cmd != null)
      cmd.Dispose();
    if (dtr != null)
    {
      dtr.Close();
      dtr.Dispose();
    }
    if (con != null)
    {
      con.Close();
      con.Dispose();
    }

    con = null;
    cmd = null;
    dtr = null;

    GC.Collect();
  }


El método initSQL() ha de invocarse antes de empezar cualquier operación con la base de datos. A continuación se realizan las operaciones con la base de datos (accesos y actualizaciones). Y por último se invoca al método dispose() para finiquitar los objetos.

La sentencia GC.Collect() permite vaciar el Garbage Collector, un repositorio que .NET utiliza para depositar los objetos que dejan de ser utilizados. Es posible que en algunas ocasiones, aunque cerremos convenientemente los objetos para despacharlos al Garbage Collector, éste aún pueda tener la referencia a SQLite y no haya procesado su limpieza automática, por lo que nos dará, inevitablemente, el conocido error de "database locked", a pesar de ser meticulosos y despachar a medida que dejamos de utilizar.

Recomiendo utilizar en esta clase los métodos de acceso a la base de datos, e ir invocándolo desde nuestras propias clase. Por ejemplo, ahí van las más utilizadas de forma genérica:


// Ejecuta una sentencia de actualizacion (NO SELECT)
public String executeNonQuery(string sql)
{
  String result = "" ;

  try
  {
    initSQL();

    cmd = new SQLiteCommand(sql, con);
    int rows = cmd.ExecuteNonQuery();

    if (rows == 0)
      result = "No se ha realizado ningun cambio [" + sql + "]";
  }
  catch (Exception argEx)
  {
    result = "Error al ejecutar SQL ["+sql+"]: " + argEx.Message;
  }

  dispose();

  return result;
}

// Obtiene el valor de un unico campo para una consulta de un solo registro
public String getUniqueValue(string sql)
{
  String result = "";

  try
  {
    initSQL();
    cmd = new SQLiteCommand(sql, con);
    dtr = cmd.ExecuteReader();
    dtr.Read();
    result = (String)dtr[0].ToString();
  }
  catch (Exception argEx)
  {
    result = "";
  }

  dispose();

  return result;
}

// Obtiene el conjunto de registros de una consulta SQL Select
public SQLiteDataReader executeReader(string sql)
{
  try
  {
    initSQL();
    cmd = new SQLiteCommand(sql, con);
  dtr = cmd.ExecuteReader();
  }
  catch (Exception argEx)
  {
  dtr = null;
  }

  return dtr;
}


EJEMPLOS DE USO:

Ejemplo 1: Actualización


string sql="insert into paises (id_pais, pais) values (1, 'Spain')";
DatosManager dm = new DatosManager();
dm.executeNonQuery(sql);
dm.dispose();


Ejemplo 2: Consulta campo especifico en registro unico


string sql="select nombre from clientes where id_cliente=32";
DatosManager dm = new DatosManager();
string nombre=dm.getUniqueValue(sql);
dm.dispose();


Ejemplo 3: Consulta con conjunto de varios registros


string sql="select id_provincia, provincia from provincias order by provincia";
DatosManager dm = new DatosManager();
SQLiteDataReader dr=dm.executeReader(sql);
while (dr.Read())
{
  // Tratamiento de cada registro
}
dr.Close(); // Despachar primero el DataReader
dr.Dispose();
dm.dispose();