System.Data.SQLite is a popular library that provides a lightweight, disk-based database that doesn’t require a separate server process. It is perfect for applications that need a simple, fast, and reliable database solution. This article will guide you on how to set up and use System.Data.SQLite in a Windows environment. We'll cover installation, basic usage, and provide practical examples to help you get started.

Examples:

Step 1: Install SQLite

First, download the SQLite tools for Windows from the official SQLite website (https://www.sqlite.org/download.html). Extract the downloaded ZIP file to a directory of your choice, for example, C:\sqlite.

Step 2: Install System.Data.SQLite via NuGet

To use System.Data.SQLite in a .NET project, you need to install the System.Data.SQLite package via NuGet. Open your project in Visual Studio and use the NuGet Package Manager Console to install the package:

Install-Package System.Data.SQLite

Step 3: Create a SQLite Database

You can create a SQLite database using the SQLite command-line tool or programmatically. Here, we'll create it programmatically using C#.

using System;
using System.Data.SQLite;

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

            string createTableQuery = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)";
            SQLiteCommand createTableCmd = new SQLiteCommand(createTableQuery, connection);
            createTableCmd.ExecuteNonQuery();

            string insertDataQuery = "INSERT INTO users (name, age) VALUES ('Alice', 30)";
            SQLiteCommand insertDataCmd = new SQLiteCommand(insertDataQuery, connection);
            insertDataCmd.ExecuteNonQuery();

            string selectDataQuery = "SELECT * FROM users";
            SQLiteCommand selectDataCmd = new SQLiteCommand(selectDataQuery, connection);
            SQLiteDataReader reader = selectDataCmd.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}, Age: {reader["age"]}");
            }
        }
    }
}

Step 4: Run the Program

To run the program, open a Command Prompt, navigate to your project directory, and execute the following commands:

csc Program.cs /r:System.Data.SQLite.dll
Program.exe

Step 5: Verify the Database

You can verify the contents of the database using the SQLite command-line tool:

C:\sqlite\sqlite3.exe sample.db
sqlite> SELECT * FROM users;

This should display the data inserted by your C# program.