C# SQLite Tutorial: Microsoft.Data.Sqlite With Tested Examples

Ninad Pathak

October 7, 2026

C# has two SQLite packages with nearly the same name, Microsoft.Data.Sqlite and System.Data.SQLite, and their class names are hard to keep apart. In this article I’ll walk you through Microsoft’s package from an empty console app to transactions, with the output from a real run.

TLDR

Add the package with dotnet add package Microsoft.Data.Sqlite.
Open a database with new SqliteConnection("Data Source=orders.db"). The file is created if it’s missing.
Run statements with ExecuteNonQuery() and read rows with ExecuteReader().
Pass values as parameters ($name), never by joining strings.
Wrap many inserts in BeginTransaction() and Commit(). A transaction disposed without Commit() rolls back.

What is Microsoft.Data.Sqlite and what will we build?

“Microsoft.Data.Sqlite is a lightweight ADO.NET provider for SQLite”, and the Entity Framework Core provider for SQLite is built on top of it (Microsoft Learn: Microsoft.Data.Sqlite overview). It ships its own SQLite library, so you don’t install SQLite separately.

The other package, System.Data.SQLite, is older. Microsoft’s comparison says it was created in 2005 and taken over by the SQLite team in 2010 (Microsoft Learn: comparison to System.Data.SQLite). Its classes are spelled SQLiteConnection and SQLiteCommand. This guide uses Microsoft’s package, whose classes are SqliteConnection and SqliteCommand.

	Microsoft.Data.Sqlite	System.Data.SQLite
Maintained by	Microsoft (.NET team)	The SQLite team
Connection class	`SqliteConnection`	`SQLiteConnection`
Used by	EF Core’s SQLite provider	Older .NET Framework apps

To show you the whole flow, I will build a small console app that stores orders in orders.db.

Prerequisites

Requirement	Version tested
.NET SDK	10.0.401
Microsoft.Data.Sqlite	10.0.12 (bundles SQLite 3.53.3)

Building the orders app

The steps below are sections of one Program.cs, shown in full at the end of this section.

Step 1: Create the project and add the package

dotnet new console creates the project. Then add the package from inside the OrdersApp folder:

$ dotnet new console -o OrdersApp
The template "Console App" was created successfully.
$ dotnet add package Microsoft.Data.Sqlite
PackageReference for package 'Microsoft.Data.Sqlite' version '10.0.12' added to file '/src/OrdersApp/OrdersApp.csproj'.

Step 2: Connect and create a table

Data Source=orders.db opens the file in the current folder and creates it if it doesn’t exist. ExecuteNonQuery() runs statements that don’t return rows, like CREATE TABLE.

Step 3: Insert rows with parameters and a transaction

Microsoft’s docs say parameters “are used to protect against SQL injection attacks”. Their names can start with : or @ as well as $ (Microsoft Learn: parameters). The program reuses one command and changes the parameter values for each row, all inside one transaction.

Step 4: Query rows with a reader

ExecuteReader() returns a SqliteDataReader. Call Read() until it returns false. Read each column by position with a typed getter such as GetString().

Step 5: Update and delete

ExecuteNonQuery() returns the number of rows changed, which is handy for checking an update hit something.

Step 6: Roll back a failed transaction

The last block inserts two rows in a transaction. The second one breaks a NOT NULL constraint, so Commit() never runs and the first insert is rolled back too.

The complete Program.cs:

using Microsoft.Data.Sqlite;

// Opens orders.db, creating it if it doesn't exist.
using var connection = new SqliteConnection("Data Source=orders.db");
connection.Open();

// Step 2: create a table
var create = connection.CreateCommand();
create.CommandText = """
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY,
        product TEXT NOT NULL,
        quantity INTEGER NOT NULL,
        price REAL NOT NULL
    )
    """;
create.ExecuteNonQuery();

// Step 3: insert rows with parameters, inside one transaction
using (var transaction = connection.BeginTransaction())
{
    var insert = connection.CreateCommand();
    insert.CommandText = "INSERT INTO orders (product, quantity, price) VALUES ($product, $quantity, $price)";
    var product = insert.Parameters.Add("$product", SqliteType.Text);
    var quantity = insert.Parameters.Add("$quantity", SqliteType.Integer);
    var price = insert.Parameters.Add("$price", SqliteType.Real);
    foreach (var (p, q, pr) in new[] { ("Keyboard", 2, 49.99), ("Mouse", 5, 19.50), ("Monitor", 1, 229.00) })
    {
        product.Value = p; quantity.Value = q; price.Value = pr;
        insert.ExecuteNonQuery();
    }
    transaction.Commit();
}

var lastId = connection.CreateCommand();
lastId.CommandText = "SELECT last_insert_rowid()";
Console.WriteLine($"Last inserted id: {lastId.ExecuteScalar()}");

// Step 4: query with a reader
var select = connection.CreateCommand();
select.CommandText = "SELECT id, product, quantity, price FROM orders WHERE quantity >= $min ORDER BY id";
select.Parameters.AddWithValue("$min", 1);
using (var reader = select.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine($"{reader.GetInt64(0)} {reader.GetString(1),-8} x{reader.GetInt32(2)} @ {reader.GetDouble(3):F2}");
    }
}

// Step 5: update and delete
var update = connection.CreateCommand();
update.CommandText = "UPDATE orders SET quantity = quantity + 1 WHERE product = $product";
update.Parameters.AddWithValue("$product", "Mouse");
Console.WriteLine($"Rows updated: {update.ExecuteNonQuery()}");

var delete = connection.CreateCommand();
delete.CommandText = "DELETE FROM orders WHERE price > $limit";
delete.Parameters.AddWithValue("$limit", 200);
Console.WriteLine($"Rows deleted: {delete.ExecuteNonQuery()}");

var total = connection.CreateCommand();
total.CommandText = "SELECT count(*), sum(quantity * price) FROM orders";
using (var r = total.ExecuteReader())
{
    r.Read();
    Console.WriteLine($"{r.GetInt64(0)} orders, total {r.GetDouble(1):F2}");
}

// Step 6: a failed transaction rolls back
try
{
    using var tx = connection.BeginTransaction();
    var bad = connection.CreateCommand();
    bad.CommandText = "INSERT INTO orders (product, quantity, price) VALUES ('Cable', 3, 4.99); INSERT INTO orders (product, quantity) VALUES ('Dock', 1);";
    bad.ExecuteNonQuery();
    tx.Commit();
}
catch (SqliteException ex)
{
    Console.WriteLine($"SqliteException {ex.SqliteErrorCode}: {ex.Message}");
}
var count = connection.CreateCommand();
count.CommandText = "SELECT count(*) FROM orders WHERE product = 'Cable'";
Console.WriteLine($"Cable rows after rollback: {count.ExecuteScalar()}");

var version = connection.CreateCommand();
version.CommandText = "SELECT sqlite_version()";
Console.WriteLine($"SQLite version: {version.ExecuteScalar()}");

Running it

Run the app with dotnet run:

$ dotnet run
Last inserted id: 3
1 Keyboard x2 @ 49.99
2 Mouse    x5 @ 19.50
3 Monitor  x1 @ 229.00
Rows updated: 1
Rows deleted: 1
2 orders, total 216.98
SqliteException 19: SQLite Error 19: 'NOT NULL constraint failed: orders.price'.
Cable rows after rollback: 0
SQLite version: 3.53.3

Run it again, and the three inserts are added to the same file. These are the first four lines of the second run:

$ dotnet run
Last inserted id: 5
1 Keyboard x2 @ 49.99
2 Mouse    x6 @ 19.50
3 Keyboard x2 @ 49.99

The new rows got ids 3 to 5. The Monitor row (id 3) was deleted in the first run, and without AUTOINCREMENT SQLite can reuse the highest deleted id. See SQLite AUTOINCREMENT.

Common problems

SQLite Error 1: 'no such table: orders': the app opened a different orders.db than you expect, because a relative path depends on the working directory. Use a full path in Data Source.
SQLite Error 19: 'NOT NULL constraint failed …': a constraint rejected the row, as in Step 6. SqliteException.SqliteErrorCode holds the number.

SQLiteConnection not found: that’s the System.Data.SQLite spelling. With Microsoft.Data.Sqlite it’s SqliteConnection.
database is locked: another connection is writing. See database is locked.

The second error came up in the run above. The others weren’t reproduced here.

Conclusion

For C#, Microsoft.Data.Sqlite is all you need. Open a file with SqliteConnection, and use commands with parameters for everything else. Use transactions for batches of writes. If you’d rather work with classes than SQL, EF Core’s SQLite provider sits on top of the same library. See SQLite with Entity Framework.