Many of us are probably pretty familiar with SQL and the relational database concept in general, so it likely needs no introduction from me. Here I’m going to walk through the process I went through in setting up SQLite for a lightweight desktop app, using Visual Studio.

What is SQLite?

SQL databases are often connected to over a network so that data can be stored and accessed for, say, a web app. But it doesn’t have to be that way – SQLite allows you to use a locally-held database, and is widely used for desktop applications, embedded software, and more. sqlite.org reckons there are over 1 trillion SQLite databases in active use. You probably use one a dozen times a day without realising. It’s lightweight, secure, fast, cross-platform, and extremely widespread.

Installing the packages

The first step is to install the packages in your application. As always, this can be done on the command line or with NuGet. The two we want are Microsoft.Data.Sqlite and SQLitePCLRaw.bundle_e_sqlite3. NuGet is quick and easy, so I tend to do it that way.

The packages installed via NuGet. Don’t worry too much about the particular project – I’m doing it on a WPF app, but if you’re just experimenting you can use a command line project, for example.

The first is the official ADO.NET provider from Microsoft, and provides classes we’re going to need like SqliteConnection. It’s the ‘glue’ between VS/C# and the underlying engine. Which, speaking of, is provided by the second package, and is the compiled C-code and essential libraries.

Creating the database

Next, the database we’re going to use will need to be created. I’m going to do this with DB Browser for SQLite. Once installed, just go to ‘New Database’, browse to the location you want to create it at (just a handy place within the project folder is fine), choose a name and hit Save. An empty DB is created.

Connecting to the database

So, with that done, let’s dive into some simple code. The first step is to define a connection string. I’ll create this within a static class in my project library:

Microsoft recommend an absolute filepath rather than a relative one - in a professional app it would more likely be held in %appdata%, but this will do for now.

We can then use this connection string to open the database from within the code. Using Batteries.init() will automatically setup and configure SQLite for us. Then we can use new SqliteConnection(ConnectionString) and connection.Open() to commence operations.

At this point, we’re ready to access the database and start playing with data! I’m going to 1) create a table and 2) populate it with some data. For this, I’ll reproduce the ‘damage table’ from the GURPS tabletop roleplaying system. The general process for this is to declare the SQL commands as strings, and then execute them in-code using provided method calls.

Table data

This isn’t going to be a deep-dive into the syntax and concepts of SQL/SQLite, but the important bits here are CREATE TABLE, followed by the column definitions. I’ve created my primary key here as text, but if you define it as INTEGER (providing there’s only one key column) then it will auto-increment the value on each row. I prefer to use strings unless there’s a compelling reason to use ints (like if I need to perform mathematical operations on them), however, so that’s what I’ve done here.

Then there’s INSERT INTO to create the table data. Thankfully this multi-row syntax is now valid in SQLite (previously it involved an ugly-looking workaround). By declaring OR IGNORE, I can tell it to silently skip any rows that already exist (based on the UNIQUE constraint), rather than crashing out with an exception. The only other thing to note is that I didn’t need to explicitly declare my column tables in this case (since I’m not inserting selected column data, but rather populating each and every one) – but again, I just prefer to as a style choice.

A quick note on composing the data if you find yourself in this situation – you can enter it using a spreadsheet app, save it as a CSV and then open with Notepad++. You can then use the various keystrokes and shortcuts that Notepad++ provides to quickly get it into the required format. Saves a lot of tedious typing-out.

For more depth on the syntax of SQLite I recommend sqlitetutorial.net or sqlite.org.

Verifying the data

As long as you call this class and method (in my case Data.InitialiseDatabase();) at an appropriate place in the project, hitting *Build * should then run all this, and create your table along with all its data. You can then inspect it using DB Browser. Just go to Open Database, browse to and select the DB to open, then to Browse Data and select the table from the combobox selector:

Success! Of course, there’s more to say about this – some of the code can be improved, and there’s the matter of accessing the data itself so you can use it in your project. All of which will be covered in a later post - but this is enough to get started!

If you spot any errors in my work, run into any problems yourself, or see other things that can be improved, please let me know below. And I’ll see you for the next one!