Introduction

SQLite is a lightweight, serverless, self-contained relational database engine. Unlike traditional client-server databases, SQLite does not require a separate database server. The complete database is stored in a single file, which makes it useful for applications that need local data storage with minimal configuration.

In this article, we will learn how to use SQLite with C# by building a simple console application. We will create a SQLite database, create a table, insert records, retrieve data, and display the results in the console.

By the end of this article, you will understand how SQLite works in a C# application and when it is appropriate to use it.

Why Use SQLite?

SQLite is particularly useful when an application needs a local relational database without the overhead of managing a separate database server.

Some of its important characteristics include:

When Should You Use SQLite?

SQLite works well for applications that primarily need local or embedded data storage.

Common scenarios include:

However, SQLite is not intended to replace every client-server database. Applications requiring extensive concurrent writes, centralized user management, or distributed database infrastructure may be better served by databases such as SQL Server, PostgreSQL, or MySQL.

Create a C# Project

For this example, we will create a .NET console application.

Open a terminal and run:

dotnet new console -n SQLiteDemo
cd SQLiteDemo

This creates a new console application named SQLiteDemo.

Install the SQLite Package

For this example, we will use the Microsoft.Data.Sqlite ADO.NET provider.

Install the package using the following command:

dotnet add package Microsoft.Data.Sqlite

After installation, the application can communicate with a SQLite database using C#.

Create a SQLite Database

SQLite does not require a database server to be created.

The following code creates or opens a database file named students.db:

using Microsoft.Data.Sqlite;

string connectionString = "Data Source=students.db";

using var connection = new SqliteConnection(connectionString);

connection.Open();

Console.WriteLine("SQLite database connection established.");

The Data Source=students.db portion specifies the SQLite database file.

If the file does not exist, SQLite creates it when the connection is opened.

Understanding the Connection

The following line creates the connection:

using var connection = new SqliteConnection(connectionString);

The Open() method then establishes the connection:

connection.Open();

Because the connection is declared with using, it is automatically disposed when it is no longer required.

Create a Table

After opening the database, we can create a Students table.

string createTableSql = """
    CREATE TABLE IF NOT EXISTS Students
    (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        Name TEXT NOT NULL,
        Email TEXT NOT NULL
    );
    """;

using var createTableCommand = new SqliteCommand(createTableSql, connection);

createTableCommand.ExecuteNonQuery();

Console.WriteLine("Students table created.");

The CREATE TABLE IF NOT EXISTS statement ensures that the table is created only when it does not already exist.

The table contains three columns:

Insert Data into SQLite

Now that the table exists, we can insert student records.

string insertSql = """
    INSERT INTO Students (Name, Email)
    VALUES ($name, $email);
    """;

using var insertCommand = new SqliteCommand(insertSql, connection);

insertCommand.Parameters.AddWithValue("$name", "John Doe");
insertCommand.Parameters.AddWithValue("$email", "[email protected]");

insertCommand.ExecuteNonQuery();

Console.WriteLine("Student record inserted.");

Parameters are used instead of directly concatenating values into the SQL statement. This is a safer approach for handling application input.

We can insert another record by creating another command:

using var secondInsertCommand = new SqliteCommand(insertSql, connection);

secondInsertCommand.Parameters.AddWithValue("$name", "Sarah Smith");
secondInsertCommand.Parameters.AddWithValue("$email", "[email protected]");

secondInsertCommand.ExecuteNonQuery();

Console.WriteLine("Second student record inserted.");

Retrieve Data from SQLite

We can now retrieve the records using a SELECT query.

string selectSql = """
    SELECT Id, Name, Email
    FROM Students;
    """;

using var selectCommand = new SqliteCommand(selectSql, connection);

using var reader = selectCommand.ExecuteReader();

Console.WriteLine();
Console.WriteLine("Students:");

while (reader.Read())
{
    Console.WriteLine(
        $"Id: {reader["Id"]}, " +
        $"Name: {reader["Name"]}, " +
        $"Email: {reader["Email"]}");
}

The ExecuteReader() method executes the query and returns a data reader.

The while loop reads each returned row and displays its values.

Complete C# Example

The complete program can now be combined into a single file:

using Microsoft.Data.Sqlite;

string connectionString = "Data Source=students.db";

using var connection = new SqliteConnection(connectionString);

connection.Open();

Console.WriteLine("SQLite database connection established.");

string createTableSql = """
    CREATE TABLE IF NOT EXISTS Students
    (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        Name TEXT NOT NULL,
        Email TEXT NOT NULL
    );
    """;

using var createTableCommand = new SqliteCommand(createTableSql, connection);

createTableCommand.ExecuteNonQuery();

Console.WriteLine("Students table created.");

string insertSql = """
    INSERT INTO Students (Name, Email)
    VALUES ($name, $email);
    """;

using var insertCommand = new SqliteCommand(insertSql, connection);

insertCommand.Parameters.AddWithValue("$name", "John Doe");
insertCommand.Parameters.AddWithValue("$email", "[email protected]");

insertCommand.ExecuteNonQuery();

Console.WriteLine("Student record inserted.");

using var secondInsertCommand = new SqliteCommand(insertSql, connection);

secondInsertCommand.Parameters.AddWithValue("$name", "Sarah Smith");
secondInsertCommand.Parameters.AddWithValue("$email", "[email protected]");

secondInsertCommand.ExecuteNonQuery();

Console.WriteLine("Second student record inserted.");

string selectSql = """
    SELECT Id, Name, Email
    FROM Students;
    """;

using var selectCommand = new SqliteCommand(selectSql, connection);

using var reader = selectCommand.ExecuteReader();

Console.WriteLine();
Console.WriteLine("Students:");

while (reader.Read())
{
    Console.WriteLine(
        $"Id: {reader["Id"]}, " +
        $"Name: {reader["Name"]}, " +
        $"Email: {reader["Email"]}");
}

Run the Application

Save the code in Program.cs and run the application:

dotnet run

The application creates the students.db file in the project directory if it does not already exist.

Output

A possible output is:

SQLite database connection established.
Students table created.
Student record inserted.
Second student record inserted.

Students:
Id: 1, Name: John Doe, Email: [email protected]
Id: 2, Name: Sarah Smith, Email: [email protected]

The exact IDs can differ if the database already contains records from an earlier execution.

Understanding the SQLite Database File

After running the application, a file named students.db is created in the application's working directory.

Unlike a client-server database where data is managed by a separate database service, the SQLite database is contained in this file.

This makes it convenient to copy, back up, or use as application-local storage.

SQLite Transactions

SQLite supports transactions, which are useful when multiple database operations must succeed or fail as a unit.

For example:

using var transaction = connection.BeginTransaction();

try
{
    using var command = connection.CreateCommand();

    command.Transaction = transaction;
    command.CommandText = """
        INSERT INTO Students (Name, Email)
        VALUES ($name, $email);
        """;

    command.Parameters.AddWithValue("$name", "Michael Brown");
    command.Parameters.AddWithValue("$email", "[email protected]");

    command.ExecuteNonQuery();

    transaction.Commit();

    Console.WriteLine("Transaction completed successfully.");
}
catch
{
    transaction.Rollback();
    Console.WriteLine("Transaction rolled back.");
}

The transaction allows the application to commit the operation only when it completes successfully. If an error occurs, the changes can be rolled back.

Advantages of SQLite

SQLite provides several advantages for local and embedded applications:

Limitations of SQLite

SQLite also has limitations that should be considered before choosing it for an application.

Write Concurrency

SQLite allows multiple readers, but write access is serialized at the database level. Applications with heavy concurrent write workloads may therefore be better suited to a client-server database.

No Built-In Server-Side User Management

SQLite is embedded in the application and does not provide the same server-level user, role, and permission management model commonly found in client-server database systems.

Dynamic Type System

SQLite uses a dynamic type system with type affinity rather than enforcing column types in exactly the same way as many traditional relational database systems. Applications should therefore validate data appropriately.

Large Distributed Workloads

Applications requiring distributed database infrastructure, very high traffic, or extensive concurrent writes may be better candidates for a client-server RDBMS such as PostgreSQL, MySQL, or SQL Server.

SQLite vs Client-Server Databases

Feature

SQLite

Client-Server Database

Database server

Not required

Required

Configuration

Minimal

Usually more extensive

Storage

Typically a database file

Managed by database server

Local application storage

Excellent

Usually unnecessary

Concurrent writes

Limited compared with server databases

Designed for higher concurrency

Administration

Minimal

Database administration required

Distributed workloads

Limited

Better suited

Embedded applications

Excellent

Usually more infrastructure than needed

When Should You Choose SQLite?

SQLite is a strong option when an application needs a lightweight local relational database and does not require a separate database server.

It can be a good fit for:

For applications with many concurrent users, heavy write workloads, centralized database administration, or distributed data requirements, a client-server database may be more appropriate.

Conclusion

SQLite provides a simple way to add relational database functionality to a C# application without installing or managing a separate database server.

In this example, we created a SQLite database, created a Students table, inserted records, retrieved the data, and displayed the results using C#. We also looked at transactions, advantages, limitations, and scenarios where SQLite is appropriate.

The main benefit of SQLite is its simplicity. For applications that need reliable local data storage with minimal infrastructure, SQLite can be an effective choice. For applications requiring high write concurrency, centralized administration, or distributed database capabilities, a client-server database should be considered instead.