SQLite y .NET Un ejemplo de como utilizarlo.

En este primer artículo comentaré de forma general las características de SQLite y cómo comenzar a crear bases de datos bajo esta plataforma.

¿Qué es SQLite?

SQLite es, como indican en un artículo de su propia web oficial, una base de datos de código libre con las siguientes características distintivas:

• No necesita configuración, ni tras la instalación inicial ni para el posterior mantenimiento.
• No utiliza servidor. Se puede utilizar embebida dentro de otra aplicación o gestionar los ficheros de datos a través de una pequeña aplicación de consola descargable desde su web.
• Utiliza un sólo fichero para almacenar los datos. Una base de datos de SQLite se compone de un único fichero que puede almacenarse en cualquier ruta de la máquina.
• Los ficheros de datos son multiplataforma. Una base de datos creada en una máquina y sistema operativo concreto puede copiarse y utilizarse bajo cualquier otra plataforma.
• Es muy compacta. La librería que se integra con otras aplicaciones ocupa unos 200 KBytes.
• Utiliza tipado dinámico (Manifest Typing). SQLite permite almacenar cualquier valor de cualquier tipo en cualquier registro de una tabla de la base de datos, independientemente del tipo declarado al crear la tabla.
• Utiliza registros de longitud variable. Cada dato almacenado en la base de datos ocupará su tamaño real y no el reservado según su tipo.

A estas características me gustaría añadir un par más que me parecen interesantes:

• Proporciona un muy buen rendimiento, muy por encima de bases de datos como Access.
• Se integra muy fácilmente con aplicaciones .NET ya que existe su proveedor ADO.NET 2.0, también de código libre.

¿Qué necesitamos descargar?

Para gestionar y utilizar bases de datos SQLite tan sólo deberemos descargar la aplicación de gestión en modo consola que nos servirá para crear el fichero de base de datos y crear todas las entidades necesarias (tablas, índices…), y el proveedor ADO.NET para su utilización desde nuestra aplicación .NET.

1. Página de descarga de SQLite.
2. Página de descarga de SQLite for ADO.NET 2.0.

¿Cómo crear una base de datos en SQLite?

La base de datos se creará mediante la aplicación de consola “sqlite3.exe” pasándole como parámetro el nombre deseado para nuestra base de datos:

c:\> sqlite3 basedatos.db
SQLite version 3.5.6
Enter “.help” for instructions
sqlite>

Una vez iniciada la aplicación se podrán ejecutar todas las sentencias SQL necesarias para crear las tablas que queramos que formen parte de nuestra base de datos.

1. CREATE TABLE tabla1(
2. campo1 INTEGER PRIMARY KEY,
3. campo2 TEXT,
4. campo3 NUMERIC,
5. campo4 INTEGER,
6. campo5 REAL
7. );

CREATE TABLE tabla1( campo1    INTEGER PRIMARY KEY, campo2    TEXT, campo3    NUMERIC, campo4    INTEGER, campo5    REAL ); 

Los tipos de datos declarados al crear una tabla serán meramente informativos y no impedirán que posteriormente se inserte cualquier valor de cualquier tipo en estos campos, siempre con las siguientes matizaciones:

• Si en un campo TEXT se inserta un valor numérico este valor se convertirá a texto antes de ser almacenado.
• Si en un campo NUMERIC se inserta un valor de tipo texto éste se intentará convertir a un valor numérico entero o real antes de su almacenamiento y si no es posible la conversión se almacenará directamente como texto.
• Los campos de tipo INTEGER se comportan de igual forma que NUMERIC salvo que si se inserta un valor real sin decimales o un valor textual que pueda convertirse a éste último se almacenará como entero.
• Los campos de tipo REAL se comportan de igual forma que NUMERIC salvo que se almacenará el dato siempre como valor de tipo real.
• Los campos INTEGER PRIMARY KEY servirán para crear un campo autonumérico o autoincremental (el campo se autoincrementa al realizar inserciones en la tabla con este campo nulo).

Lo primero que haremos será crear el proyecto en Visual Studio o cualquier otro IDE de desarrollo .NET y añadir a las referencias del proyecto el proveedor ADO.NET 2.0 para SQLite (en el artículo anterior comentamos dónde podíamos conseguir este proveedor). La librería DLL a añadir como referencia, y que habrá que distribuir con nuestra aplicación, será la denominada System.Data.SQLite.DLL que encontraremos dentro del fichero ZIP que descargamos. Una vez referenciado el proveedor para ADO.NET de SQLite ya podemos comenzar a escribir el código de nuestra aplicación. Para ello, deberemos añadir la directiva using correspondiente a la librería que acabamos de añadir:

1. using System.Data.SQLite;

using System.Data.SQLite; 

A partir de este momento el código necesario para acceder y gestionar nuestra base de datos SQLite será completamente análogo al ya tradicional que utilizamos para el acceso a cualquier otro tipo de bases de datos, salvo que los nombres de las clases comenzarán con el prefijo “SQLite”. Así, por ejemplo, tendremos a nuestra disposición las siguientes clases:

SQLiteConnection
SQLiteTransaction
SQLiteCommand
SQLiteParameter
SQLiteDataReader
…

Conexión con la base de datos

La conexión con la base de datos se realizará mediante la clase SQLiteConnection. Para el caso de SQLite, el único parámetro obligatorio de la cadena de conexión será el nombre del fichero de datos:

1. SQLiteConnection con =
2. new SQLiteConnection("Data Source=index.bd");

SQLiteConnection con = new SQLiteConnection("Data Source=index.bd"); 

Otros parámetros que pueden especificarse dentro de la cadena de conexión son la contraseña en caso de existir, el timeout por defecto para las operaciones sobre la base de datos, etc. Para ver todos los parámetros que podemos especificar en la conexión con la base de datos SQLite puede consultarse la ayuda proporcionada con el proveedor ADO.NET en el fichero SQLite.NET.chm

Consulta de datos

Para realizar una consulta parametrizada sobre la base de datos haremos uso de las clases SQLiteCommand, SQLiteParameter y SQLiteDataReader. Veamos cómo quedaría un método sencillo de consulta sobre una tabla especificando un parámetro de búsqueda:

1. public SQLiteDataReader selectClave(int clave)
2. {
3. SQLiteConnection con =
4. new SQLiteConnection("Data Source=basedatos.bd");
5. 
   
6. SQLiteParameter param = new SQLiteParameter();
7. param.Value = clave;
8. 
   
9. string cmdStr =
10. "SELECT campo2 FROM tabla1 WHERE campo1 = ?;";
11. 
    
12. SQLiteCommand cmd =
13. new SQLiteCommand(cmdStr, con);
14. 
    
15. cmd.Parameters.Add(param);
16. 
    
17. SQLiteDataReader reader = cmd.ExecuteReader();
18. 
    
19. return reader;
20. }

public SQLiteDataReader selectClave(int clave) { SQLiteConnection con = new SQLiteConnection("Data Source=basedatos.bd");  SQLiteParameter param = new SQLiteParameter(); param.Value = clave;  string cmdStr = "SELECT campo2 FROM tabla1 WHERE campo1 = ?;";  SQLiteCommand cmd = new SQLiteCommand(cmdStr, con);  cmd.Parameters.Add(param);  SQLiteDataReader  reader = cmd.ExecuteReader();  return reader; } 

Inserción de datos

La ejecución de una operación INSERT simple será análoga a la consulta descrita en el apartado anterior, salvo que el método de ejecución será ahora ExecuteNonQuery() ya que no se devolverá ningún valor:

1. public void insertClave(string campo1, int campo2)
2. {
3. SQLiteConnection con =
4. new SQLiteConnection("Data Source=basedatos.bd");
5. 
   
6. SQLiteParameter param1 = new SQLiteParameter();
7. Param1.Value = campo1;
8. 
   
9. SQLiteParameter param2 = new SQLiteParameter();
10. Param2.Value = campo2;
11. 
    
12. string cmdStr =
13. "INSERT INTO tabla1 (campo1, campo2) VALUES (?,?);";
14. 
    
15. SQLiteCommand cmd =
16. new SQLiteCommand(cmdStr, con);
17. 
    
18. cmd.Parameters.Add(param1);
19. cmd.Parameters.Add(param2);
20. 
    
21. SQLiteDataReader reader = cmd.ExecuteNonQuery();
22. 
    
23. return reader;
24. }

public void insertClave(string campo1, int campo2) { SQLiteConnection con = new SQLiteConnection("Data Source=basedatos.bd");  SQLiteParameter param1 = new SQLiteParameter(); Param1.Value = campo1;  SQLiteParameter param2 = new SQLiteParameter(); Param2.Value = campo2;  string cmdStr = "INSERT INTO tabla1 (campo1, campo2) VALUES (?,?);";  SQLiteCommand cmd = new SQLiteCommand(cmdStr, con);  cmd.Parameters.Add(param1); cmd.Parameters.Add(param2);  SQLiteDataReader reader = cmd.ExecuteNonQuery();  return reader; } 

Si en nuestra tabla existe un campo autonumérico y queremos recuperar su valor después de ejecutar la operación de INSERT, tendremos que modificar la instrucción ejecutada para añadir al final una llamada a la función last_insert_rowid() de SQLite y cambiar el método de ejecución por ExecuteScalar() para recuperar el valor devuelto:

1. string cmdStr =
2. "INSERT INTO tabla1 (campo1, campo2) VALUES (?,?);" +
3. "SELECT last_insert_rowid();";
4. 
   
5. ...
6. 
   
7. int res = Convert.ToInt32(cmd.ExecuteScalar());

string cmdStr = "INSERT INTO tabla1 (campo1, campo2) VALUES (?,?);" + "SELECT last_insert_rowid();";  ...  int res = Convert.ToInt32(cmd.ExecuteScalar()); 

Transacciones en SQLite

Tal y como aconsejan en la ayuda del propio proveedor de ADO.NET para SQLite, en caso de ejecutar varias operaciones sobre una base de datos es recomendable hacerlas dentro de una misma transacción, algo que aumentará muchísimo el rendimiento de la base de datos y por tanto reducirá considerablemente el tiempo necesario para ejecutar las operaciones. Para comenzar una transacción bastará con llamar al método BeginTransaction() del objeto SQLiteConnection una vez creada la conexión, y para finalizar la transacción haciendo COMMIT o ROLLBACK de todas las operaciones llamaremos a los métodos Commit() o Rollback() respectivamente:

1. SQLiteConnection con =
2. new SQLiteConnection("Data Source=basedatos.bd");
3. 
   
4. SQLiteTransaction tran = con.BeginTransaction();
5. 
   
6. ...
7. 
   
8. tran.Commit();

SQLiteConnection con = new SQLiteConnection("Data Source=basedatos.bd");  SQLiteTransaction tran = con.BeginTransaction();  ...  tran.Commit(); 

Espero que este pequeño tutorial de SQLite sobre .NET les sea de ayuda.

Fuente: sgoliver.net