This post is a short demo of using ADO.NET to insert and query data against a SQLite database.

The source code is available on GitHub here.

Context

The example data are achievements - anything that can be quantified and occurs on a certain date:

public sealed class Achievement
{
    public long Id { get; set; }
    public AchievementClass AchievementClass { get; set; }
    public DateOnly CompletedDate { get; set; }
    public int Quantity { get; set; }
}

Different kinds of achievements are identified by their name and a unit for interpreting quantities:

public sealed class AchievementClass
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Unit { get; set; }
}

Implementation

Step 1 - Configure

The first commit configures the project and creates the database upon repository initialisation.

SQLite databases reside on the local file system. The snippet below places the database inside the application data special folder (on MacOS called "Application Support"), which is the standard location for storing application-specific data and configuration files. This way, one does not need to worry about differences between operating systems or how to use relative paths.

It also uses the environment name as part of the path. This ensures that development and production databases are different. The environment is configured by setting the DOTNET_ENVIRONMENT environment variable to Deveploment.

public Repository(IHostEnvironment hostEnvironment)
{
	string dataSource = Path.Combine(
		Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
		"AdoDotNetDemo",
		hostEnvironment.EnvironmentName,
		"data.db");
	Directory.CreateDirectory(Path.GetDirectoryName(dataSource)!);
	_connectionString = $"Data Source={dataSource}";
}

When the code is run, it creates the relevant folders:

Step 2 - Ping

The second commit implements the ping method:

await using SqliteConnection connection = new(_connectionString);
await using SqliteCommand command = connection.CreateCommand();
command.CommandText = "SELECT 1";
await connection.OpenAsync();
Object? output = await command.ExecuteScalarAsync();
return output != null && (long)output == 1;

Every method instantiates its own connection.

By default, integer values have 64 bits, i.e. they correspond to the long data type in C#. In particular, casting output to int fails.

The using declaration ensures that the connection and the command are disposed at the end of the method.

At this point, running the code also creates the data.db file:

Step 3 - Setup

The third commit sets up the Achievement and AchievementClass tables, using the following query:

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS AchievementClass (
  Id INTEGER PRIMARY KEY AUTOINCREMENT,
  Name TEXT NOT NULL,
  Unit TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS Achievement (
  Id INTEGER PRIMARY KEY AUTOINCREMENT,
  AchievementClassId INTEGER NOT NULL,
  CompletedDate TEXT NOT NULL,
  Quantity INTEGER NOT NULL,
  RecordedDate TEXT NOT NULL,
  FOREIGN KEY (AchievementClassId) REFERENCES AchievementClass(Id)
);

COMMIT;

Step 4 - Insert

The fourth commit inserts achievement classes:

await using SqliteConnection connection = new(_connectionString);
await using SqliteCommand command = connection.CreateCommand();
command.CommandText = """
					  INSERT INTO AchievementClass (Name, Unit) VALUES (@Name, @Unit);
					  SELECT last_insert_rowid() AS Id;
					  """;
command.Parameters.AddWithValue("@Name", achievementClass.Name);
command.Parameters.AddWithValue("@Unit", achievementClass.Unit);
await connection.OpenAsync();
object? output = await command.ExecuteScalarAsync();
if (output is null) return null;
achievementClass.Id = (long)output;
return achievementClass;

and achievements:

await using SqliteConnection connection = new(_connectionString);
await using SqliteCommand command = connection.CreateCommand();
command.CommandText = """
					  INSERT INTO Achievement (AchievementClassId, CompletedDate, Quantity, RecordedDate)
					  VALUES (@AchievementClassId, @CompletedDate, @Quantity, datetime('now'));
					  SELECT last_insert_rowid() AS Id;
					  """;
command.Parameters.AddWithValue("@AchievementClassId", achievement.AchievementClass.Id);
command.Parameters.AddWithValue("@CompletedDate", achievement.CompletedDate);
command.Parameters.AddWithValue("@Quantity", achievement.Quantity);
await connection.OpenAsync();
object? output = await command.ExecuteScalarAsync();
if (output is null) return null;
achievement.Id = (long)output;
return achievement;

Both inserts are nearly identical in their implementations.

There is no need to insert primary keys because they are automatically created through auto-increment.

The function last_insert_rowid() is a function specific to SQLite (rather than SQL in general). It does exactly what it says, i.e. returns the id of the last inserted row.

Step 5 - Query

The fifth commit queries all achievements for a specific class, which is achieved by filtering on AchievementClassId:

await using SqliteConnection connection = new(_connectionString);
await using SqliteCommand command = connection.CreateCommand();
command.CommandText = """
					  SELECT Id, CompletedDate, Quantity
					  FROM Achievement
					  WHERE AchievementClassId = @AchievementClassId
					  """;
command.Parameters.AddWithValue("@AchievementClassId", achievementClass.Id);
await connection.OpenAsync();
SqliteDataReader reader = await command.ExecuteReaderAsync();
List<Achievement> achievements = new();
while (await reader.ReadAsync())
{
	long id = reader.GetInt64(reader.GetOrdinal("Id"));
	DateTime completedDate = reader.GetDateTime(reader.GetOrdinal("CompletedDate"));
	DateOnly completedDateOnly = DateOnly.FromDateTime(completedDate);
	int quantity = reader.GetInt32(reader.GetOrdinal("Quantity"));
	Achievement achievement = new()
	{
		Id = id,
		AchievementClass = achievementClass,
		CompletedDate = completedDateOnly,
		Quantity = quantity,
	};
	achievements.Add(achievement);
}

return achievements.ToArray();

Running the program results in the following output:

Can access database: True
Completed setup: True
Inserted achievement class: AchievementClass { Id: 1, Name: Drink water, Unit: Glass }
Successfully inserted achievements
Querying achievements
Found achievement: Achievement { Id: 1, Class: Drink water, Quantity: 2 Unit: Glass }
Found achievement: Achievement { Id: 2, Class: Drink water, Quantity: 1 Unit: Glass }
Found achievement: Achievement { Id: 3, Class: Drink water, Quantity: 1 Unit: Glass }