NTDLS.SqliteDapperWrapper

What it is. A simple interface to a SQLite database wrapped in Dapper. Each call opens the connection, executes, then closes and disposes it. Beyond plain queries it adds the things that are annoying in raw SQLite: executing embedded .sql scripts, passing complex multi-value parameters as temporary tables, and attaching a second database file for inner-database joins.

When to reach for it. You are building on local/embedded SQLite and want Dapper's ergonomics plus transactional Ephemeral blocks without managing SqliteConnection lifetimes yourself. Skip it if you only need one-off scalar queries; Microsoft.Data.Sqlite directly is lighter weight.

Getting started

Point an instance at your database file:

public static SqliteManagedInstance MyConnection { get; } = new("Data Source=.\\databaseFile.db");

Execute inline SQL or an embedded script resource, with optional parameters:

MyConnection.Execute("DROP TABLE IF EXISTS Test");
MyConnection.Execute("CreateTestTable.sql"); // Embedded resource in the project

for (int i = 0; i < 100; i++)
{
    var param = new { Name = $"Name #{i}", Description = Guid.NewGuid().ToString() };
    MyConnection.Execute("INSERT INTO Test (Name, Description) VALUES (@Name, @Description)", param);
}

Multi-step work runs inside Ephemeral, which keeps one connection open for the lambda - perfect for transactions and for passing a list of values into a query via a temp table:

MyConnection.Ephemeral(o =>
{
    using var tx = o.BeginTransaction();

    try
    {
        List<int> ids = [10, 20, 30, 40, 50];

        // Creates a temp table holding the values; dropped when disposed.
        using var idTemp = o.CreateTempTableFrom("id_temp", ids);

        var results = o.Query<TestModel>(
            "SELECT * FROM Test INNER JOIN id_temp ON id_temp.Value = Test.Id");

        tx.Commit();
    }
    catch
    {
        tx.Rollback();
        throw;
    }
});

To join across two database files, attach the other one inside the same block:

MyConnection.Ephemeral(o =>
{
    using var otherDatabase = o.Attach("otherDatabase.db", "MyOtherDatabase");

    var results = o.Query<JoinedModel>(
        "SELECT A.Id, A.Name AS NameA, B.Name AS NameB " +
        "FROM Test AS A INNER JOIN MyOtherDatabase.Test AS B ON A.Id = B.Id");
});

NuGet: NTDLS.SqliteDapperWrapper