Forem: Chris

Getting started with SQLite using C#/Microsoft Visual Studio

Chris — Sat, 08 Nov 2025 10:59:06 +0000

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!

Security flaw: Greenshot

Chris — Wed, 13 Sep 2023 06:24:03 +0000

I've always used the handy application Greenshot extensively for taking screenshots, but I've recently been alerted that it contains a potential security vulnerability. Worse, the app itself is no longer under development, so an official fix isn't expected. It's open source so maybe the community will fork it and bring out a new version, but for now, I've sadly uninstalled it. Farewell, friend, you've served me well! Snipping tool is just not the same :(

My implementation of RedBlob's hex grid

Chris — Wed, 13 Sep 2023 05:59:19 +0000

For a game I'm making, I wanted to create a hex grid based map for navigation instead of a square X/Y grid. Hex-based maps have some advantages and disadvantages over square ones, but frankly, it's mainly because I just love hexagons. Of course I could just use Unity which has support for a hex grid out of the box, but where's the fun in that! I wanted to build it myself.

It involves a lot more maths but thankfully there's a truly excellent guide available at Red Blob Games that was a massive help. I wrote out the basic functions in C# and used WPF to render them, and got a proof of concept working - a small rectangular map with each hex's Q/R/S co-ordinates tagged for bughunting purposes:

Not much to look at now, but it's a start! As you can see, I've gone with flat-topped hexes instead of pointy topped, but I may change that. Unlike the way the RedBlob author does it, each time I instantiate a hex, the algorithm places each of the hex's edges into a HashSet, and then when I render the map, it draws each edge instead of each hex. This way it loops over a smaller data structure, as there's no duplicates, so it should render faster. That's the theory anyway! I'm definitely going to test it so I can confirm or deny.

Some of the lines are a little glitchy (a sin I've hidden in my screenshot by increasing the line thickness) which is probably to do with int/float conversion. I suspect the following code snippets are to blame, which is used to calculate the precise position of each hex, and where their corners lie:

public readonly Point Size;
public readonly Point Origin;
readonly double F0 = (Math.Sqrt(3) / 2);
readonly double F1 = Math.Sqrt(3);
const double F2 = (2 / 3);
const double F3 = (-1 / 3);
readonly double F4 = (Math.Sqrt(3) / 3);

public Point HexToPixel(Hex hex)
{
    double x = Size.X * (hex.Q * 1.5);
    double y = Size.Y * (F0 * hex.Q + F1 * hex.R);
    return new Point((int)(Math.Round(x)) + Origin.X, (int)(Math.Round(y)) + Origin.Y);
}

public Point HexCornerOffset(int corner)
{
    double angle = Math.PI * 2 * -corner / 6;
    double x = Size.X * Math.Cos(angle);
    double y = Size.Y * Math.Sin(angle);
    return new Point((int)(Math.Round(x)), (int)(Math.Round(y)));
}

public List<Point> Corners(Hex hex)
{
    List<Point> result = new();
    Point centre = HexToPixel(hex);
    for (int i = 0; i < 6; i++)
    {
        Point offset = HexCornerOffset(i);
        result.Add(new Point(centre.X + offset.X, centre.Y + offset.Y));
    }
    return result;
}

I tried initially using PointF instead of Point types for some of these to handle the floating point values, but I had some issues with it. I think I need to go back to that plan so I convert the floats to ints as late as possible rather than early on, which should make them more precise.

I'm planning on uploading all of the code once it's all cleaned up nice and is a more presentable mini-application.

The ideal password - proven with maths

Chris — Sun, 10 Sep 2023 12:50:24 +0000

I've recently moved all my passwords from lastpass to Bitwarden (other password managers are available), which is a good app as you can sync across multiple devices without upgrading to premium (though their premium features are worthwhile too - no, I'm not sponsored by them…). At the same time I've updated all my logins that relied on duplicate passwords, where I've been lazy and put in a standard, easy-to-remember thing.

(Side tip: has your account ever been hacked? check haveibeenpwned.com to find out. Yes, I have been pwned :( )

Another philosophy which has fallen by the wayside is the idea of frequently changing passwords. This seems like a good idea in theory but in practice means that people often write their passwords down because they can't remember them all, which is also a bad idea for what's probably obvious reasons. Adding 2-factor/multifactor authentication where available is of course also worthwhile.

A difficult-to-hack password is one of a certain minimum length, and/or uses a large character set - both of these make brute forcing harder. But a good password (for humans) is also one that's easy to remember. This can be visualised thus (behold my incredible MSPaint skills):

Password managers are great as they can randomly generate passwords for you - this way they only need to satisfy the first criteria. Changing them now and then, as necessary might still be worthwhile. But of course, you still need a login to the password manager itself. This is even more critical, as if this gets hacked, someone has all your logins! (MFA absolutely essential here!)

For this password, I recommend using a website (such as this) to generate something that is both easy to remember and difficult to brute-force, purely based on the length. Set it to generate four random words and then string them together in any way you like. You can write this down for a short while until it's locked in your brain, but destroy it as soon as it's memorised. Dictionary words can sometimes be a bad idea for passwords, but as long as there's enough of them (and they're not easy to guess by anyone who knows enough about you), it's secure enough, thanks to the following maths...

The English language contains about a million words, but let's reduce it to the 170,000 in common use, just to keep our assumptions low. Brute-forcing a single dictionary word - let's say all in lower caps to keep it straightforward - would therefore take my computer (just an ordinary laptop, not a particularly fancy rig) - 30 seconds in the worst case scenario, using a not-particularly-good algorithm:

Ah, but what if we randomised uppercase and lowercase characters? Sure, that makes it harder. Now we need to check each word, and randomly assign an uppercase or lowercase character to each letter of each word, for every attempt. It makes sense to capitalise the first letter first (or early on at least) as that's how most people do it, so you're more likely to get the correct answer faster. Then you need to work through the word in a comprehensive fashion to obtain every possible permutation. Since each character here has two states (uppercase or lowercase) this effectively makes it a binary problem, which is something computer scientists are very familiar with. You can create a binary string of the same length of the word, and use that to generate the selection of uppercase/lowercase alternatives, incrementing the binary by 1 each attempt. So for an eight-letter word (let's say "computer"), the binary 0000 0000 is initially produced, putting all characters lowercase. It then gets increased by 1, producing 0000 0001 ("Computer"). 0000 0010 produces "cOmputer". Then 0000 0011 => "COmputer". And so on. For an eight letter word, this means 2^8, or 256 attempts. The 'average' word in the English language is 4.7 letters - let's round that to 5 - so 2^5 = 64 attempts. 64 × 170 000 = 10 800 000 attempts to brute force. Let's give that a try:

My PC ran this in 52ms. That's a single run on a single word, so running that 170,000 times might take just over two and a half hours (no, I didn't try this!). That's not too long for an even slightly determined attacker. Again, that's only in the worst case scenario (from the hacker's perspective!) that the required result is the very last combination checked. And we can increase the complexity of the problem by introducing numbers and special characters, but we can _also _ increase the efficiency of the brute force algorithm. My quick-and-dirty approach to this involves triple-nested loops, giving a complexity of O(n^3), which is pretty bad. I might have a go at creating a better one at some point, just for the attempt. (You could also, for example, sort it by words most commonly used in passwords, and include words you think the person is most likely to use based on any personal information you can glean… hackers are sneaky.)

You might notice one thing; that the longer the word is, the harder it is (more work required) to hack. If you take the original method - a single lowercase word - and make it longer - with multiple words - you're exploring the set of all such possible word combinations, instead of the set of all possible substitutions for each letter of a single word. This is an enormous set. For two words, this produces 170,000 ^ 2 possibilities (assuming duplicates), which is 28.9 billion. For 3 words: 170,000 ^ 3 = 4,913,000,000,000,000; that's 4.9 quadrillion, or 4.9 × 10^15 in scientific notation. For 4 words, the number is (rounding) 8.4 × 10^20.

That’s a Very Big Number indeed. The number of grains of sand on all the beaches and deserts on Earth is about 1.7 × 10^18, and there are conservatively about 1 * 10^22 stars in the observable universe, so it's somewhere between those two Very Big Numbers. My attempt above took 30 seconds to try 170,000 words, so a dictionary attack that could create four-word combinations at a similar rate (and this is an apples-to-oranges comparison, granted, but it gives you an idea) would take 4.7 billion years to run all the possibilities. (Check my maths - I'm not an expert: 170 000 / 30 = 5,667; (8.4 × 10^20) / 5667 = 1.5 × 10^17 seconds; divide that by 3.154 × 10^7 to get 4.7Bn.)

It's easy to remember, hard to guess, and good luck brute forcing it.

An over-reliance on IntelliSense...

Chris — Sun, 10 Sep 2023 07:23:40 +0000

I recently spent ages trawling through my codebase, completely unable to understand why my build was crashing with null reference error at a certain point. Eventually I realised what it was (it always seems so obvious when it hits you); I had tabbed through an IntelliSense suggestion without properly reading it, and set a for loop to break with a completely inappropriate (but similarly-named) variable.

IntelliSense is an absolutely invaluable productivity tool for me but lesson learned to handle it with care! If it's causing bugs that have to be investigated & fixed, it's (at worst) slowing me down rather speeding up development. Taking the time to double-check the suggestions are absolutely worth the few extra seconds it takes!

Rubber duck debugging

Chris — Sat, 09 Sep 2023 07:07:41 +0000

Have you ever had a problem with your code you just can't figure out, so you sit down with someone else and ask for their help? And you walk them through your code and what it's supposed to do... and then suddenly, you see your error, clear as day, and feel like a bit of a fool!

I recently reminded myself of this wonderful effect and how to hack it to make it work for you; Rubber Duck Debugging. Save yourself the embarrassment by asking for help from, and explaining your code to, a handy rubber duck. Its gentle, calm demeanour will unerringly guide you towards the problem and allow you to fix it yourself.

Rubber Duck Debugging, saving lives since 1999.

Intro...

Chris — Sat, 09 Sep 2023 06:49:48 +0000

Hi - my name's Chris, I'm an Operations Engineer based in Bristol, UK.

I'm passionate about writing code and everything tech/science.

I plan on using this blog to track updates on my own coding journey, share my thoughts, some useful stuff, and anything else that catches my eye along the way.

xkcd: Blogging

xkcd.com

I will also use numerous psychological tricks to curate only the most well-informed, cultured, and downright handsome of readers. So welcome - you're already in the right place!

Hope to see you around!

-C