In C#, storing and retrieving data through the SQLite database is a common task. This article will introduce how to use SQLite in C# and provide corresponding example code.

SQLite is a lightweight, embedded relational database widely used in mobile applications, desktop applications, and embedded systems. In this article, we will cover how to use SQLite in C# with example code.

Preparation

Install SQLite Library

In C# projects, we typically use the System.Data.SQLite library to interact with SQLite databases. You can install this library through the NuGet Package Manager. Commonly used packages include System.Data.SQLite and Microsoft.Data.Sqlite. This article will use Microsoft.Data.Sqlite. In Visual Studio, right-click your project, select "Manage NuGet Packages," then search for and install Microsoft.Data.Sqlite.

Create SQLite Database

If you do not have an SQLite database, you can create a database file using SQLite tools (such as DB Browser for SQLite) or dynamically create one in code.

Example Code

Below is a complete example demonstrating how to create tables, insert data, query data, and update data using SQLite in C#.

1. Create SQLite Connection

First, we need to create a SQLite connection.

using System;
using Microsoft.Data.Sqlite;

class Program
{
    private static string connectionString = "Data Source=mydatabase.db";

    static void Main(string[] args)
    {
        using (var connection = new SqliteConnection(connectionString))
        {
            connection.Open();

            // Perform database operations here
            CreateTable(connection);
            InsertData(connection);
            QueryData(connection);
            UpdateData(connection);
        }
    }

    // Other database operation methods...
}

2. Create Table

Next, we create a table. For example, we will create a table named Users that contains Id and Name fields.

static void CreateTable(SqliteConnection connection)
{
    string sql = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Name TEXT)";

    using (var command = new SqliteCommand(sql, connection))
    {
        command.ExecuteNonQuery();
        Console.WriteLine("Table 'Users' created.");
    }
}

3. Insert Data

Insert some data into the Users table.

static void InsertData(SqliteConnection connection)
{
    string sql = "INSERT INTO Users (Name) VALUES (@Name)";

    using (var command = new SqliteCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@Name", "Alice");
        command.ExecuteNonQuery();

        command.Parameters.AddWithValue("@Name", "Bob");
        command.ExecuteNonQuery();

        Console.WriteLine("Data inserted into 'Users'.");
    }
}

4. Query Data

Query data from the Users table and output it.

static void QueryData(SqliteConnection connection)
{
    string sql = "SELECT Id, Name FROM Users";

    using (var command = new SqliteCommand(sql, connection))
    {
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"Id: {reader["Id"]}, Name: {reader["Name"]}");
            }
        }
    }
}

5. Update Data

Update data in the Users table.

static void UpdateData(SqliteConnection connection)
{
    string sql = "UPDATE Users SET Name = @NewName WHERE Id = @Id";

    using (var command = new SqliteCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@NewName", "Charlie");
        command.Parameters.AddWithValue("@Id", 1);
        command.ExecuteNonQuery();

        Console.WriteLine("Data updated in 'Users'.");
    }
}

Complete Code

Here’s a summary of the complete code:

using System;using Microsoft.Data.Sqlite;class Program{    private static string connectionString = "Data Source=mydatabase.db";    static void Main(string[] args)    {        using (var connection = new SqliteConnection(connectionString))        {            connection.Open();            CreateTable(connection);            InsertData(connection);            QueryData(connection);            UpdateData(connection);            QueryData(connection);  // Query again to see updated results        }    }    static void CreateTable(SqliteConnection connection)    {        string sql = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Name TEXT)";        using (var command = new SqliteCommand(sql, connection))        {            command.ExecuteNonQuery();            Console.WriteLine("Table 'Users' created.");        }    }    static void InsertData(SqliteConnection connection)    {        string sql = "INSERT INTO Users (Name) VALUES (@Name)";        using (var command = new SqliteCommand(sql, connection))        {            command.Parameters.AddWithValue("@Name", "Alice");            command.ExecuteNonQuery();            command.Parameters.AddWithValue("@Name", "Bob");            command.ExecuteNonQuery();            Console.WriteLine("Data inserted into 'Users'.");        }    }    static void QueryData(SqliteConnection connection)    {        string sql = "SELECT Id, Name FROM Users";        using (var command = new SqliteCommand(sql, connection))        {            using (var reader = command.ExecuteReader())            {                while (reader.Read())                {                    Console.WriteLine($"Id: {reader["Id"]}, Name: {reader["Name"]}");                }            }        }    }    static void UpdateData(SqliteConnection connection)    {        string sql = "UPDATE Users SET Name = @NewName WHERE Id = @Id";        using (var command = new SqliteCommand(sql, connection))        {            command.Parameters.AddWithValue("@NewName", "Charlie");            command.Parameters.AddWithValue("@Id", 1);            command.ExecuteNonQuery();            Console.WriteLine("Data updated in 'Users'.");        }    }}

Conclusion

Through this article, we learned how to perform basic CRUD operations using SQLite in C#. With the Microsoft.Data.Sqlite library, it is easy to create connections, execute SQL commands, and query and process results. The lightweight nature of SQLite makes it very suitable for embedded and local storage applications. We hope this article is helpful, and we encourage you to try using SQLite in your projects!