SQLite CRUD Tutorials in C#: A Step-by-Step Guide

SQLite is a lightweight, serverless, and self-contained relational database management system, and C# is a versatile programming language. In this tutorial, we’ll explore the basics of performing CRUD (Create, Read, Update, Delete) operations in SQLite using C#. We’ll cover the following steps:

Setting Up Your Environment:
- Installing SQLite.
- Setting up your C# development environment.
Connecting to SQLite:
- Creating a connection to your SQLite database.
Creating a Table:
- Writing C# code to create a table in your SQLite database.
Inserting Data:
- Demonstrating how to insert data into the table.
Querying Data:
- Retrieving data from the table.
Updating Data:
- Modifying existing records in the table.
Deleting Data:
- Deleting records from the table.

1. Setting Up Your Environment

Installing SQLite

SQLite is a self-contained database system, so you don’t need to install it separately. You can use it as a library within your C# project.

Setting Up Your C# Development Environment

Install Visual Studio or Visual Studio Code, and ensure you have the .NET SDK installed.

2. Connecting to SQLite

To connect to your SQLite database from a C# application, you can use the System.Data.SQLite library. Install it using NuGet Package Manager or the .NET CLI:

dotnet add package System.Data.SQLite

Now, let’s create a connection to your SQLite database:

using System;
using System.Data;
using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=mydatabase.db;Version=3;";
        SQLiteConnection connection = new SQLiteConnection(connectionString);

        try
        {
            connection.Open();
            Console.WriteLine("Connected to SQLite Database!");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        finally
        {
            connection.Close();
        }
    }
}

Replace mydatabase.db with the path to your SQLite database file.

3. Creating a Table

Let’s create a simple users table in your SQLite database:

string createTableSql = "CREATE TABLE IF NOT EXISTS users (" +
    "id INTEGER PRIMARY KEY AUTOINCREMENT," +
    "name TEXT NOT NULL," +
    "email TEXT NOT NULL)";
SQLiteCommand createTableCommand = new SQLiteCommand(createTableSql, connection);

try
{
    connection.Open();
    createTableCommand.ExecuteNonQuery();
    Console.WriteLine("Table created!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code creates a table named users with columns id, name, and email.

4. Inserting Data

Now, let’s insert a user into the users table:

string insertSql = "INSERT INTO users (name, email) VALUES (@name, @email)";
SQLiteCommand insertCommand = new SQLiteCommand(insertSql, connection);

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

try
{
    connection.Open();
    int rowsAffected = insertCommand.ExecuteNonQuery();
    Console.WriteLine($"Inserted {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code inserts a user with the name “John Doe” and email “[email protected]” into the users table.

5. Querying Data

Let’s retrieve data from the users table:

string query = "SELECT * FROM users";
SQLiteCommand queryCommand = new SQLiteCommand(query, connection);

try
{
    connection.Open();
    SQLiteDataReader reader = queryCommand.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}, Email: {reader["email"]}");
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code queries and displays all records in the users table.

6. Updating Data

Let’s update a user’s email address:

string updateSql = "UPDATE users SET email = @newEmail WHERE name = @name";
SQLiteCommand updateCommand = new SQLiteCommand(updateSql, connection);

// Parameters
updateCommand.Parameters.AddWithValue("@newEmail", "[email protected]");
updateCommand.Parameters.AddWithValue("@name", "John Doe");

try
{
    connection.Open();
    int rowsAffected = updateCommand.ExecuteNonQuery();
    Console.WriteLine($"Updated {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code updates the email address of the user with the name “John Doe” in the users table.

7. Deleting Data

Let’s delete a user from the users table:

string deleteSql = "DELETE FROM users WHERE name = @name";
SQLiteCommand deleteCommand = new SQLiteCommand(deleteSql, connection);

// Parameter
deleteCommand.Parameters.AddWithValue("@name", "John Doe");

try
{
    connection.Open();
    int rowsAffected = deleteCommand.ExecuteNonQuery();
    Console.WriteLine($"Deleted {rowsAffected}

 row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code deletes the user with the name “John Doe” from the users table.

With these CRUD operations, you have a solid foundation for working with SQLite in your C# applications. SQLite’s simplicity and portability make it suitable for various application scenarios, especially when you need a lightweight, embedded database.