Bases de datos portables: Utilizando SQLite en una aplicación .NET


English version here.

La mayor parte de las aplicaciones que desarrollamos en .NET de modo profesional suelen implicar la existencia de una base de datos, por norma SQL Server, Oracle, DB2 o MySQL. Sin embargo, algunas veces, pese a necesitar soporte de base de datos, resulta innecesario el montaje y mantenimiento de un gestor de base de datos, bien por portabilidad, bien por licencia, bien porque el volumen de datos y la complejidad de las tablas implicadas no sea muy complejo…

Vimos en artículos relacionados con Android que cada aplicación hace uso de una base de datos SQLite a nivel local. ¿Por qué no aplicar la misma filosofía a una aplicación .NET. Pues bien, es posible encapsular la base de datos en un fichero .db dentro de un directorio local del mismo modo que lo hacen las aplicaciones en Android. Veremos a continuación cómo instalar SQLite para .NET y realizar una pequeña aplicación que realice operaciones CRUD (Create, Retrieve, Update, Delete) sobre una entidad. A partir de ahí, será extrapolable a entidades más complejas.

Instalación de SQLite para .NET

El primer paso, por lo tanto, será descargar la biblioteca SQLite adaptada para .NET, que podremos descargar desde esta dirección.

Deberemos seleccionar la versión que mejor se adapte a nuestra aplicación, tanto por framework (2.0, 3.5, 4, 4.5…) como por arquitectura (32 ó 64 bits). En nuestro caso corremos un Visual Studio 2012 sobre una máquina x86 (32 bits), por lo que buscamos el enlace mostrado arriba. De los dos enlaces, seleccionamos aquel que es capaz de instalar los componentes para Visual Studio 2012 (el de arriba).

A continuación iniciaremos el proceso de instalación.

Seleccionaremos entonces los elementos de SQLite que deseamos instalar. En nuestro caso, marcaremos todas las casillas.

Creando el proyecto

Nuestra aplicación de prueba será simple: consistirá en una aplicación de escritorio donde codificaremos toda la lógica de acceso a datos. Por lo tanto, seleccionamos File > New Project… y seleccionaremos la opción Windows Forms Application.

De base necesitaremos añadir dos referencias: una de ellas será al componente System.Configuration para poder acceder a la cadena de conexión, y otra de ellas será a la DLL que implementa el acceso a SQLite. Haremos click derecho sobre nuestro proyecto y seleccionaremos Add Reference…

El primer elemento se encontrará en la sección Framework. Buscamos el ensamblado y marcamos la casilla correspondiente.

El segundo elemento hay que indicarlo a mano seleccionando la opción Browse. Buscaremos en el directorio donde instalamos previamente SQLite (por defecto, en C:\Program Files\System.Data.SQLite\2012\bin) y seleccionamos el ensamblado System.Data.SQLite.dll.

Creando la interfaz

Empezaremos la casa por el tejado añadiendo unos cuantos controles a nuestra aplicación. Mostraremos un DataGridView, tres labesl, tres cajas de texto y cuatro botones:

  • El DataGridView se encargará de mostrar el listado con los datos recuperados.
  • Las cajas de texto obtendrán los datos de un registro a insertar, modificar o eliminar.
  • Los botones se encargarán de obtener los datos, insertar/modificar, eliminar o salir de la aplicación.

Creando la base de datos

¡Un momento! tenemos el conector y la interfaz, pero… ¡Todavía no tenemos la base de datos! ¿Cómo la creamos? Una opción es la utilización de la herramienta SQLiteBrowser, que no es más que un pequeño gestor de bases de datos SQLite. Podemos descargar la herramienta desde aquí.

Una vez ejecutada la aplicación veremos que la interfaz es bastante intuitiva. Para crear una nueva base de datos pulsaremos sobre el primer icono de la izquierda, que representa un documento en blanco.

A continuación deberemos crear las tablas con sus respectivos campos. Sobra decir que SQLite es una base de datos mucho más limitada que SQL Server u Oracle, por lo que además de proporcionar menor funcionalidad, contiene un conjunto de restricciones que hay que satisfacer, como la existencia de una clave primaria de tipo entero en cada una de las tablas. Por lo tanto, crearemos una tabla con un campo UserId cuyo tipo será INTEGER PRIMARY KEY.

Añadiremos el resto de los campos de los que queremos disponer. Por ejemplo, Name para almacenar el nombre y Surname para el apellido. Una vez creados los campos, pulsaremos el botón Create y guardaremos la base de datos, que no será más que un fichero con la extensión .db.

Configurando la cadena de conexión

Al igual que ocurre con otras bases de datos, es posible utilizar una cadena de conexión para conectar a una base de datos SQLite. Para ello deberemos indicar los siguientes campos en nuestro fichero app.config:

  • Data Source: ruta del fichero que acabamos de crear.
  • Version: Versión de SQLite. Indicaremos la versión 3.
  • New: Indica si la base de datos existe o no.
  • Compress: Indica si se debe o no usar compresión con la base de datos.

El aspecto del fichero App.config tendrá el siguiente aspecto:


<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>

  <connectionStrings>
    <add name="db" connectionString="Data Source=clients.db;Version=3;New=False;Compress=True;"/>
  </connectionStrings>

</configuration>

Codificando la aplicación

Lo primero que deberemos hacer será indicar el using para utilizar SQLite y la creación de los atributos privados que se usarán en la aplicación.


     using System.Data.SQLite;

Los elementos que crearemos serán la cadena de conexión, la conexión y las cadenas de texto que implementarán las cuatro operaciones a realizar sobre la tabla. Nótese que lanzaremos consultas directamente sobre la base de datos a través de ADO.NET. SQLite permite también la utilización de Entity Framework, pero veremos en otro artículo cómo hacer esto.


        private String connectionString;
        private SQLiteConnection connection;

        private String SQLInsert = "INSERT INTO User(Name, Surname) VALUES(?, ?)";
        private String SQLUpdate = "UPDATE User SET Name = ?, Surname = ? where UserId = ?";
        private String SQLSelect = "SELECT * FROM User";
        private String SQLDelete = "DELETE FROM User WHERE UserId = ?";

Como observamos, las entradas de parámetro se codifican mediante el signo de interrogación, que serán sustituidos en tiempo de ejecución por los SQLiteParameter correspondientes. Iniciaremos la aplicación obteniendo la cadena de conexión del App.config y utilizándola para crear una nueva conexión.


        public Form1()
        {
            InitializeComponent();
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["db"].ConnectionString;
            connection = new SQLiteConnection(connectionString);
        }

Haremos doble click en el botón “Salir” y aplicaremos el siguiente código al botón, en el que cerraremos la conexión si se encuentra abierta y finalizaremos el programa.


        private void btnExit_Click(object sender, EventArgs e)
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();

            Application.Exit();
        }

Lo siguiente que haremos será hacer que, cuando seleccionemos una fila del DataGrid, los datos de éste se carguen en las cajas de texto. Para ello seleccionaremos el DataGrid, y en sus propiedades, haremos doble click sobre el evento RowEnter.

Añadiremos ahora el código necesario para pasar los datos de la fila a las cajas de texto.


        private void dataGrid_RowEnter(object sender, DataGridViewCellEventArgs e)
        {
            // Recuperamos ID, nombre y apellido de la fila
            int id = int.Parse(dataGrid.Rows[e.RowIndex].Cells[0].Value.ToString());
            String name = (String)dataGrid.Rows[e.RowIndex].Cells[1].Value;
            String surname = (String)dataGrid.Rows[e.RowIndex].Cells[2].Value;

            // Asignamos los valores a las cajas de texto
            txtId.Text = id.ToString();
            txtName.Text = name;
            txtSurname.Text = surname;
        }

Lo siguiente que haremos será codificar el proceso de búsqueda. Crearemos un método llamado search que cree una orden (SQLiteCommand) a partir de la cadena de texto con la sentencia SELECT y haga uso de un DataAdapter para rellenar un DataTable. Todo ello a partir de la conexión.

Debido a que la carga del grid provoca la posición del cursor en el primer elemento del DataGrid, eliminamos el handler del evento al comienzo del método y lo restauraremos al final.


        private void search()
        {
            // Eliminamos el handler del evento RowEnter para evitar que se dispare al
            // realizar la búsqueda
            dataGrid.RowEnter -= dataGrid_RowEnter;

            // Abrimos la conexión
            if (connection.State != ConnectionState.Open)
                connection.Open();

            // Creamos un SQLiteCommand y le asignamos la cadena de consulta
            SQLiteCommand command = connection.CreateCommand();
            command.CommandText = SQLSelect;

            // Creamos un nuevo DataTable y un DataAdapter a partir de la SELECT.
            // A continuación, rellenamos la tabla con el DataAdapter
            DataTable dt = new DataTable();
            SQLiteDataAdapter da = new SQLiteDataAdapter(command);
            da.Fill(dt);

            // Asignamos el DataTable al DataGrid y cerramos la conexión
            dataGrid.DataSource = dt;
            connection.Close();

            // Restauramos el handler del evento
            dataGrid.RowEnter += dataGrid_RowEnter;
        }

El método asociado al botón de búsqueda realizará una simple invocación a este método.


        private void btnSelect_Click(object sender, EventArgs e)
        {
            search();
        }

A continuación crearemos un método de limpieza, que borre el contenido de las cajas de texto. Será invocado cada vez que se realice una inserción, actualización o eliminación para dejar la aplicación preparada para la inserción de un nuevo registro.


        private void clean()
        {
            txtId.Text = String.Empty;
            txtName.Text = String.Empty;
            txtSurname.Text = String.Empty;
        }

Lo siguiente sera codificar el comportamiento del botón “Guardar”. Este botón realizará dos operaciones:

  • Si el campo de texto Id está vacío, creará un nuevo registro con los datos insertados en Nombre y Apellidos.
  • Si el campo de texto Id tiene algún dato, modificará el registro con los datos insertados en Nombre y Apellidos.

En este caso haremos uso de parámetros para evitar problemas como la inyección SQL.


        private void btnSave_Click(object sender, EventArgs e)
        {
            // Si la caja de texto está vacía, se tratará de una inserción
            if (String.IsNullOrEmpty(txtId.Text))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                // Creamos una orden con la cadena de texto con la sentencia INSERT
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = SQLInsert;

                // Añadimos los parámetros Name y Surname
                command.Parameters.AddWithValue("Name", txtName.Text);
                command.Parameters.AddWithValue("Surname", txtSurname.Text);

                // Ejecutamos la sentencia INSERT y cerramos la conexión
                command.ExecuteNonQuery();
                connection.Close();
            }
            else
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                // Creamos una orden con la cadena de texto con la sentencia UPDATE
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = SQLUpdate;

                // Añadimos los parámetros Name, Surname y UserId
                command.Parameters.AddWithValue("Name", txtName.Text);
                command.Parameters.AddWithValue("Surname", txtSurname.Text);
                command.Parameters.AddWithValue("UserId", int.Parse(txtId.Text));

                // Ejecutamos la sentencia UPDATE y cerramos la conexión
                command.ExecuteNonQuery();
                connection.Close();

            }
            clean();
            search();
        }

Finalmente, codificaremos el proceso de eliminación, que consistirá en más de lo mismo, pasando a la orden un parámetro con el ID a eliminar (previa comprobación de que existe en el campo txtId).


        private void btnDelete_Click(object sender, EventArgs e)
        {
            // Comprobamos que exista un ID seleccionado
            if (!String.IsNullOrEmpty(txtId.Text))
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();

                // Creamos la orden a partir de la cadena de texto DELETE
                SQLiteCommand command = connection.CreateCommand();
                command.CommandText = SQLDelete;

                // Añadimos como parámetro el ID
                command.Parameters.AddWithValue("UserId", int.Parse(txtId.Text));

                // Ejecutamos la sentencia SQL
                command.ExecuteNonQuery();
                connection.Close();

                clean();
                search();
            }
        }

 

Probando el programa

Ejecutaremos el programa pulsando F5 y rellenaremos los campos Nombre y Apellidos. A continuación, pulsaremos el botón Guardar, lo cual provocará la inserción del registro y se mostrará en el grid, limpiando además los campos de inserción.

Esta operación se realizará también pulsando el botón Buscar. Este será el resultado:

Si añadimos un segundo registro y luego lo seleccionamos, el campo ID tomará el valor del identificador del registro. Si modificamos sus valores en las cajas de texto y pulsamos Guardar, se modificará también en nuestra base de datos SQLite.

Finalmente, para eliminar un registro bastará con seleccionar la columna y pulsar el botón Eliminar.

Con esto habríamos cubierto las cuatro operaciones básicas sobre una base de datos, proporcionando una aplicación completamente portable e independiente de un gestor de bases de datos complejo.

Puedes descargarte el código de este ejemplo desde aquí.

Anuncios

15 comments

  1. Hola buen dia he intentado tu tutorial pero utilizando visual studio 2008 y he descargado la libreria apta para desarrollar en esta version del visual pero al momento de generar mi proyecto de instalacion para distribuir la aplicacion en otros equipo no me deja generar me marca la siguiente excepcion :
    El archivo System.Data.SQLite.dll que tiene como destino ‘AMD64’ no es compatible con la plataforma de destino ‘x86’ del proyecto
    necesito ayuda urgente por favorrr

  2. Diana, Solo tienes que descargar el otro paquete que esta en la página de descarga de SQLite para x86, eso pasa porque descargaste la versión de 64 bits y estas desarrollando una aplicación para 32 bits.

  3. La base de SQLite es su portabilidad, por eso el uso en las aplicaciones para dispositivos móviles basados en Android, justo como lo mencionas al principio del artículo, pero esa portabilidad sigue presente en una aplicación de VStudio? porque al parecer cuando haces las referencias “System.Configuration” y manualmente al archivo “.dll”, al parecer se hace un poco mas complejo el uso de la herramienta, podrías explicar un poco sobre eso? te lo agradecería

  4. Hola he hecho una aplicacion en mi trabajo basada en esta base de datos sqlilte la cual es perfecta por que la aplicacion que he realizdo esta en red y es muy portable.
    He creado un instalador y lo he podido instalar con exito en varias maquinas de mi empresa, sin embargo en un par de maquinas no ha habido forma de abrir mi aplicacion puesto que me lanza un error relacionado con la dll de sqlite para .net el cual es el siguiente:

    no se puede cargar el archivo o ensamblado system.data.sqlite ni una de sus dependencias, no se encuentra el módulo especificado.

  5. Si creo una aplicación de escritorio junto con ese tipo de base de datos empaquetada en un ejecutable, cómo se maneja la seguridad? por ejemplo que un usuario modifique el archivo manualmente si lo encuentra en Program Files, o que pueda ver la información?

  6. Hola y cual seria la ventaja de por ejemplo usar sql express, y teniendo tu base de datos local?

    Estoy usando Visual Studio 2013 te da la opción de crear una base de datos como creas cualquier archivo, Agregar elemento > Base de datos basada en servicio.

    Mi aplicación es interna la manejaría yo o alguien más pero no simultáneos, no en red, va a ser portatil por ahora pero más adelante (meses) por ahí la haga web app, solo por la disponibilidad en cualquier momento desde cualquier dispositivo no más que eso.

    Es una app, que consulta varias web, y según unos procesos muestra información en pantalla, es una tool chiquita, diseñada exclusivamente para eso.

  7. Buenas tardes,

    tengo un proyecto en visual basic 2010 y mi archivo app.config no lo veo por ningún lado, ademas que creo un proyecto nuevo aparece en el directorio y al guardar ya no tengo el archivo app.config en ese directorio.

    Si alguien me puede ayudar

    Gracias de antemano
    Un saludo.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s