Database test

I haven’t decided on what database I will be using to host my project. It could be PostgreSQL, MySQL/MariaDB or SQL Server Express Edition on Linux. But those all are a bit cumbersome when you are developing on different machines. Therefore I wanted to see if I could use SQLite for the first steps of development.

I created a very small console application to test this out.

Create a new console application

Very straight forward from the command line.

mkdir test_dotnet_sqlite
cd test_dotnet_sqlite
dotnet new console

Create a simple test database

In my Notepad++ Snippets Plugin repository I have a SQLite file to create a simple table and inserts a number of rows. This contains the languages supported by Notepad++.

The CREATE TABLE command is very simple:

CREATE TABLE Languages (
    LangID INTEGER PRIMARY KEY NOT NULL,
    Name TEXT NOT NULL
);

This command creates the test.sqlite file I want to use for this test.

sqlite3 test.sqlite -init ../NppSnippets/misc/Languages.sql

Add the NuGet packages

To let .NET communicate with a SQLite database I added the System.Data.SQLite.Core NuGet package.

And to make database communication a bit easier a ORM like Dapper can be very handy. I decided against Entity Framework because I want to write my own query’s. Call me old fashioned. And I have used Dapper before on other projects at the office, so Dapper is a logic choice at this moment.

This all results in adding this ItemGroup to test_dotnet_sqlite.csproj.

<ItemGroup>
  <PackageReference Include="Dapper" Version="2.0.30" />
  <PackageReference Include="System.Data.SQLite.Core" Version="1.0.112" />
</ItemGroup>

The code

With all the prerequisites in place, I can start hacking the very simple test. Since it is such a simple test I didn’t bother putting the model, interface and repository in separate files.

using System;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using Dapper;

namespace test_dotnet_sqlite
{
    // Model to store a language. Contains all the fields in the tables.
    public class Language
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public interface ILanguageRepository
    {
        public Language GetLanguage(long id);
    }

    public class SqliteRepostitory: ILanguageRepository
    {
        // For this test a semi hard-coded path will do
        // But still be multi platform aware, so no string concatenations
        public static string DbFile => Path.Combine(Environment.CurrentDirectory, "test.sqlite");

        public static SQLiteConnection DbConnection()
        {
            // For SQLite the connection string is very simple
            return new SQLiteConnection("Data Source=" + DbFile);
        }

        public Language GetLanguage(long id)
        {
            // It there a database?
            if (!File.Exists(DbFile))
                return null;

            // Retrieve the language based on the LangID
            using (var cnn = DbConnection())
            {
                cnn.Open();
                var result = cnn.Query<Language>(@"SELECT LangID, Name FROM Languages WHERE LangID = @id", new { id }).FirstOrDefault();
                return result;
            }
        }
    }

    public class Program
    {
        static void Main()
        {
            // Create an instance of the repository
            var repo = new SqliteRepostitory();

            // Get the language from the repository and output the result
            var lang = repo.GetLanguage(0);
            Console.WriteLine(lang.Name);
        }
    }
}

The program works as expected and the code was really straigth forward. I need to pay attention when I create the repostitories to use dependency injection and to keep in mind that the database engine will change.

Conclusion

This all means that for the first stages of the development I will start using a SQLite database. Since much of the fixtures are known now I will start with creating a .sql file that can create a database and start from there.

13 Dec 2019