Installing Microsoft.Data.Sqlite
Add the NuGet package: right-click the project → Manage NuGet Packages → search for Microsoft.Data.Sqlite and install it.
Creating a Database and Table
Connect with a SqliteConnection, create a command, and execute SQL using ExecuteNonQuery().
Create DB
CreateDB.cs
using Microsoft.Data.Sqlite;
const string ConnStr = "Data Source=app.db";
using var conn = new SqliteConnection(ConnStr);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = @"
CREATE TABLE IF NOT EXISTS Students (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
Grade REAL NOT NULL,
Enrolled DATE NOT NULL
)";
cmd.ExecuteNonQuery();
Console.WriteLine("Database created.");Inserting & Querying Data
Always use parameterised queries to prevent SQL injection.
CRUD
CRUD.cs
// Insert
using (var conn = new SqliteConnection(ConnStr))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO Students (Name, Grade, Enrolled) VALUES ($name, $grade, $enrolled)";
cmd.Parameters.AddWithValue("$name", "Alice");
cmd.Parameters.AddWithValue("$grade", 92.5);
cmd.Parameters.AddWithValue("$enrolled", DateTime.Today.ToString("yyyy-MM-dd"));
cmd.ExecuteNonQuery();
}
// Query
using (var conn = new SqliteConnection(ConnStr))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM Students ORDER BY Grade DESC";
using var reader = cmd.ExecuteReader();
while (reader.Read())
Console.WriteLine($"{reader["Name"]}: {reader["Grade"]}");
}