How to Create, Connect, and Use a SQLite Database with .NET

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:

Serverless: SQLite runs directly inside the application without a separate database server.
Zero configuration: There is no server installation or database administration required for a basic setup.
Lightweight: The database engine has a small footprint and is suitable for resource-constrained environments.
Self-contained: The complete database can be stored in a single file.
Transactional: SQLite supports ACID transactions to help maintain data integrity.
Cross-platform: SQLite databases can be used across operating systems and supported application platforms.
Public domain: SQLite's source code is released into the public domain.

When Should You Use SQLite?

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

Common scenarios include:

Mobile applications
Desktop applications
Embedded systems and IoT devices
Browser and application storage
Local development and testing
Prototypes and small applications
Applications that need a portable database file

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:

Id is the primary key and is automatically generated.
Name stores the student's name.
Email stores the student's email address.

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:

No separate database server is required.
Setup and configuration are minimal.
The database can be stored in a single file.
It supports SQL and relational database features.
It supports transactions and ACID properties.
The database file is portable across supported platforms.
It is suitable for applications with relatively modest database requirements.

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:

Desktop applications
Mobile applications
Embedded applications
IoT devices
Local caching and offline data
Small applications
Automated tests
Prototypes

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.