Use SQLite with ADO.NET in C#: DbConnection, DbCommand and parameters · onelite cookbook

RecursiveAI.OneLite.Data is an ADO.NET provider: OneLiteConnection is a DbConnection, so code written against DbConnection, DbCommand, DbParameter and DbDataReader runs on a SQLite file with no native library.

Terminal

dotnet new console -o AdoNetSqlite && cd AdoNetSqlite
dotnet add package RecursiveAI.OneLite.Data --version 0.2.0-preview.2

RecursiveAI.OneLite.Data brings the core package, RecursiveAI.OneLite, with it. Put this in Program.cs and run dotnet run:

using System.Data.Common;
using RecursiveAI.OneLite.Data;
 
await using DbConnection conn = new OneLiteConnection("Data Source=contacts.db");
await conn.OpenAsync();
 
await using (DbCommand create = conn.CreateCommand())
{
    create.CommandText = """
        create table if not exists contacts
          (id integer primary key, name text not null, email text)
        """;
    await create.ExecuteNonQueryAsync();
}
 
await using (DbCommand insert = conn.CreateCommand())
{
    insert.CommandText = "insert into contacts (name, email) values (@name, @email)";
    DbParameter name = insert.CreateParameter(), email = insert.CreateParameter();
    name.ParameterName = "@name";
    email.ParameterName = "@email";
    insert.Parameters.Add(name);
    insert.Parameters.Add(email);
 
    var rows = new[] { ("Ada", "[email protected]"), ("Grace", null),
                       ("Linus", "[email protected]") };
    foreach ((string n, string? e) in rows)
    {
        name.Value = n;
        email.Value = (object?)e ?? DBNull.Value;   // null is DBNull.Value in ADO.NET
        Console.WriteLine($"inserted {await insert.ExecuteNonQueryAsync()} row");
    }
}
 
await using (DbCommand query = conn.CreateCommand())
{
    query.CommandText =
        "select id, name, email from contacts where name like @p order by id";
    query.Parameters.Add(new OneLiteParameter("@p", "%a%"));
    await using DbDataReader r = await query.ExecuteReaderAsync();
    while (await r.ReadAsync())
        Console.WriteLine($"{r.GetInt64(0)} {r.GetString(1)} "
                        + (r.IsDBNull(2) ? "(no email)" : r.GetString(2)));
}
 
await using (DbCommand count = conn.CreateCommand())
{
    count.CommandText = "select count(*) from contacts";
    Console.WriteLine($"{(long)(await count.ExecuteScalarAsync())!} contacts");
}

Text

inserted 1 row
inserted 1 row
inserted 1 row
1 Ada [email protected]
2 Grace (no email)
3 contacts

Journal mode. OneLiteConnection keeps SQLite’s default, a rollback journal (delete), as the check below shows. pragma journal_mode=wal switches a file to WAL; the mode is stored in the file, so later connections open it in WAL mode too.
Parameters. @x, :x and $x in the SQL match a parameter named x, with or without its prefix. A plain ? takes the parameter at its position. A null Value throws: use DBNull.Value.
Types. GetValue returns long, double, string, byte[] or DBNull. Guid binds as upper-case text, DateTime as yyyy-MM-dd HH:mm:ss.FFFFFFF, decimal as invariant text.
Real async. Every …Async method awaits the storage layer; over a fully cached database it completes synchronously.
Connection string. Data Source is a path or :memory:. Mode=ReadOnly opens read-only, Mode=ReadWrite requires the file to exist, and Default Timeout is the seconds to wait for a lock (default 30). Pooling is accepted and ignored.
In the project’s words, “Dapper and other DbConnection consumers work unchanged”.

Check it with sqlite3

The sqlite3 shell (3.53.4, from sqlite.org) reads the file the provider wrote, in rollback-journal mode, with the NULL email intact:

Terminal

sqlite3 contacts.db "select id, name, ifnull(email, 'NULL') from contacts" \
  "pragma journal_mode" "pragma integrity_check"

Text

1|Ada|[email protected]
2|Grace|NULL
3|Linus|[email protected]
delete
ok

Limits

A preview: APIs may change between previews.
It is OneLite’s own provider. Microsoft.Data.Sqlite and EF Core can’t run on OneLite yet: in the README’s words, “the Microsoft.Data.Sqlite/EF Core adapter” is “still to come before 1.0”.
GetSchema and CommandType.StoredProcedure are not supported; GetSchemaTable is minimal. GetFieldType describes the current value’s storage class, not the declared column type.
Cancel() does nothing: cancel an …Async call with its token.

ADO.NET provider docs · Transactions · All recipes