---
title: "Use SQLite with ADO.NET in C#: DbConnection, DbCommand and parameters"
url: https://recursiveai.org/onelite/cookbook/sqlite-ado-net-csharp/
description: "Use a SQLite database through ADO.NET in C#: DbConnection, DbCommand, named parameters, DbDataReader and ExecuteScalar with OneLite’s RecursiveAI.OneLite.Data provider, async, no native library. Real output, checked with the sqlite3 shell."
version: "0.2.0-preview.2"
---

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

`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.

```shell
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`:

```csharp
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", "ada@example.com"), ("Grace", null),
                       ("Linus", "linus@example.org") };
    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 ada@example.com
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:

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

```text
1|Ada|ada@example.com
2|Grace|NULL
3|Linus|linus@example.org
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](https://recursiveai.org/onelite/docs/data/) · [Transactions](https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/) · [All recipes](https://recursiveai.org/onelite/cookbook/)