SQLite Database with .NET Example

SQLite is a file-based relational database management system that is lightweight and suitable for small Web applications, Development and Testing applications, and Mobile applications like iOS and Android apps. It is a serverless and self-contained Database that is also well-suited for .NET applications.

To use SQLite in the .NET application, you need to install the "System.Data.SQLite" package from the Nuget package or run the below command in the terminal.

Install-Package System.Data.SQLite

 SQLiteConnection class is used to make connection strings, create SQLite DB files, handle queries, and manage connections from the SQLite database engine. Here, we will take the Create, update, read, delete (CURD) operation example code with C#.

using System;
using System.Data.SQLite;

class Program
{
    static void Main()
    {
        // Create a new database file
        SQLiteConnection.CreateFile("ExampleDatabase.sqlite");

        // Connect to the database
        using (var connection = new SQLiteConnection("Data Source=ExampleDatabase.sqlite;Version=3;"))
        {
            connection.Open();

            // Create a table
            string createTableQuery = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Name TEXT, Age INTEGER)";
            using (var command = new SQLiteCommand(createTableQuery, connection))
            {
                command.ExecuteNonQuery();
            }

            // Insert data
            string insertQuery = "INSERT INTO Users (Name, Age) VALUES ('aaa', 15)";
            using (var command = new SQLiteCommand(insertQuery, connection))
            {
                command.ExecuteNonQuery();
            }

            // Read data
            string selectQuery = "SELECT * FROM Users";
            using (var command = new SQLiteCommand(selectQuery, connection))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"Id: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]}");
                }
            }

            // Update data
            string updateQuery = "UPDATE Users SET Age = 31 WHERE Name = 'Alice'";
            using (var command = new SQLiteCommand(updateQuery, connection))
            {
                command.ExecuteNonQuery();
            }

            // Delete data
            string deleteQuery = "DELETE FROM Users WHERE Name = 'Alice'";
            using (var command = new SQLiteCommand(deleteQuery, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}

Once after executing the code, the SQLite file will be created in the build location. Using online SQLite Viewer SQLite database and table able to view in this Link