SQLite and Dapper ORM

This morning I tried to read a SQLite database for the first time in at least 3 years. I remember the last time I tried to integrate SQLite into a C# project with Entity Framework 6 and designer support I just about went mad. It took hours and hours of searching and bumbling around to eventually get the right combination of references and config values. I even restored a backup of an old project that used SQLite, and I couldn't get it working again, probably due to missing dependent installs.

This time I went looking for a simpler way, with minimal dependencies and simpler code. Remembering a hint from someone several weeks ago in the ozdotnet forum I searched for "SQLite Dapper" and came upon StackExchange/dapper-dot-net. This lightweight ORM has a tiny footprint of a single DLL and one class that adds extension methods to IDbConnection, so it's mostly database agnostic. The readme.md on the main page contains a neat summary of how to perform basic database and query operations. The code is so terse that some of it seems "magical" and you need to read the samples carefully to see the pattern.

As a sanity check I attempted to read the Songs table in a MediaMonkey db3 database file. I made a POCO class which is a subset of the dozens of table columns.

public class Song
{
  public long ID { get; set; }
  public string Album{ get; set; }
  public string SongTitle { get; set; }
}

Add Nuget packages Dapper and System.Data.SQLite.Core to your project. The following code enumerates all the Songs table rows.

using (var connect = new SQLiteConnection(@"Data Source=M:\Monkey.db3"))
{
  connect.Open();
  foreach (var song in connect.Query<Song>("Select ID,SongTitle,Album from Songs", buffered: false))
  {
    Info($"{song.ID} {song.SongTitle} - {song.Album}");
  }
}

Dapper is a long way from being a full ORM, but you can still easily perform all of the typically needed database operations in a strongly typed way. You can even do eager loads over joins, a bit like the Include extension method used in Entity Framework queries (using a curious syntax).

Best of all, you only need two small Nuget packages, there is no need to put anything in a config file, you can learn to use it in minutes and the code is brief.

Orthogonal Programming