As part of developing a new application using the SQLite database I need to perform some standard database checks when the application starts. Firstly I need to check that the application’s database exists and then either create the database if it doesn’t exist, or, if the database does exist, check that the tables in the SQLite database contain the columns expected by the app.
Checking whether the Database Exists
Checking that the SQLite database exists is easy, I can simply use code like:
if (!File.Exists(mPathName))
{
i.e. I can use the normal System.IO methods in .Net to check whether the database is present.
Creating a SQLite Database
Creating a SQLite database is also straightforward in SQLite. I can create the database file as follows:
// Create the database file.
SQLiteConnection.CreateFile(mPathName);
// Open a connection to the database.
using (SQLiteConnection Conn = new SQLiteConnection("Data Source = " + mPathName))
{
Conn.Open();
// Create the required tables. In this example, I'm creating a Customers table with 3 fields - ID, UNIQUEID and DATAFIELD.
String SQL = "CREATE TABLE Customers (ID INTEGER PRIMARY KEY, UNIQUEID VARCHAR(30), DATAFIELD VARCHAR(100))";
using (SQLiteCommand Command = new SQLiteCommand(SQL, Conn))
{
Command.ExecuteNonQuery();
}
Conn.Close();
}
This is all pretty standard SQL and ADO.Net.
Checking if Columns Exist in the SQLite Database
Checking whether columns exist was a little harder to work out for SQLite. Some other databases I’ve used has special methods to get the tables, columns and their attributes. SQLite didn’t have a class to enable me to do this quite so obviously but after some research and experimentation, I found the following code worked:
// Open a connection to the database.
using (SQLiteConnection Conn = new SQLiteConnection("Data Source = " + mPathName))
{
Conn.Open();
// Get the schema for the columns in the database.
DataTable ColsTable = Conn.GetSchema("Columns");
// Query the columns schema using SQL statements to work out if the required columns exist.
bool IDExists = ColsTable.Select("COLUMN_NAME='ID' AND TABLE_NAME='Customers'").Length != 0;
bool UNIQUEIDExists = ColsTable.Select("COLUMN_NAME='UNIQUEID' AND TABLE_NAME='Customers'").Length != 0;
bool ElephantExists = ColsTable.Select("COLUMN_NAME='ELEPHANT' AND TABLE_NAME='Customers'").Length != 0;
Conn.Close();
}
A statement like
ColsTable.Select("COLUMN_NAME='ID' AND TABLE_NAME='Customers'")
returns an array of DataRows. If the column doesn’t exist an array of length 0 will be returned, hence the .Length != 0 check.
Adding a Column if it doesn’t Exist in the SQLite Database
Adding a column to a SQLite database can be performed using standard SQL statements.
String SQL = "ALTER TABLE Customers ADD COLUMN ELEPHANT VARCHAR(100)";
using (SQLiteCommand Command = new SQLiteCommand(SQL, Conn))
{
Command.ExecuteNonQuery();
}
It turns out that performing these basic operations on a SQLite database is pretty straightforward when you know how. One thing I haven’t bothered to find out yet is to determine whether a column has the correct attributes, e.g. my example ELEPHANT column may change from a 100 character to a 200 character wide column between different versions of the application. However, I’ve never had a good reason for doing such a database update in the past. I’m also relying on the SQLite feature where the database will store any sort of data of almost any length in any column.
For more SQLite tips, see: