Introduction to SQLite in C#
SQLite is a lightweight, serverless, self-contained SQL database engine that's perfect for:
- Desktop applications
- Mobile apps
- Small to medium web applications
- Embedded systems
- Prototyping and development
Setting Up SQLite in a C# Project
- 1. Install Required Packages
First, add these NuGet packages to your project:
Install-Package System.Data.SQLite.Core
Install-Package Microsoft.EntityFrameworkCore.Sqliteusing System.Data.SQLite;
public class DatabaseHelper
{
private const string ConnectionString = "Data Source=mydatabase.db;Version=3;";
public SQLiteConnection CreateConnection()
{
SQLiteConnection connection = new SQLiteConnection(ConnectionString);
connection.Open();
return connection;
}
}Creating Tables
public void CreateTables()
{
using (var connection = CreateConnection())
{
string createTableQuery = @"
CREATE TABLE IF NOT EXISTS Users (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
FirstName TEXT NOT NULL,
LastName TEXT NOT NULL,
Email TEXT UNIQUE NOT NULL,
CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
)";
SQLiteCommand command = new SQLiteCommand(createTableQuery, connection);
command.ExecuteNonQuery();
}
}CRUD Operations
- 1. Create (Insert) Data
public void AddUser(User user)
{
using (var connection = CreateConnection())
{
string insertQuery = @"
INSERT INTO Users (FirstName, LastName, Email)
VALUES (@firstName, @lastName, @email)";
SQLiteCommand command = new SQLiteCommand(insertQuery, connection);
command.Parameters.AddWithValue("@firstName", user.FirstName);
command.Parameters.AddWithValue("@lastName", user.LastName);
command.Parameters.AddWithValue("@email", user.Email);
command.ExecuteNonQuery();
}
}
public class User
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
public DateTime CreatedAt { get; set; }
}public List<User> GetAllUsers()
{
List<User> users = new List<User>();
using (var connection = CreateConnection())
{
string selectQuery = "SELECT * FROM Users";
SQLiteCommand command = new SQLiteCommand(selectQuery, connection);
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
users.Add(new User
{
Id = Convert.ToInt32(reader["Id"]),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString(),
CreatedAt = Convert.ToDateTime(reader["CreatedAt"])
});
}
}
}
return users;
}
public User GetUserById(int id)
{
using (var connection = CreateConnection())
{
string selectQuery = "SELECT * FROM Users WHERE Id = @id";
SQLiteCommand command = new SQLiteCommand(selectQuery, connection);
command.Parameters.AddWithValue("@id", id);
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return new User
{
Id = Convert.ToInt32(reader["Id"]),
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString(),
CreatedAt = Convert.ToDateTime(reader["CreatedAt"])
};
}
}
}
return null;
}public void UpdateUser(User user)
{
using (var connection = CreateConnection())
{
string updateQuery = @"
UPDATE Users
SET FirstName = @firstName,
LastName = @lastName,
Email = @email
WHERE Id = @id";
SQLiteCommand command = new SQLiteCommand(updateQuery, connection);
command.Parameters.AddWithValue("@firstName", user.FirstName);
command.Parameters.AddWithValue("@lastName", user.LastName);
command.Parameters.AddWithValue("@email", user.Email);
command.Parameters.AddWithValue("@id", user.Id);
command.ExecuteNonQuery();
}
}public void DeleteUser(int id)
{
using (var connection = CreateConnection())
{
string deleteQuery = "DELETE FROM Users WHERE Id = @id";
SQLiteCommand command = new SQLiteCommand(deleteQuery, connection);
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
}
}Using Entity Framework Core with SQLite
- 1. Set Up DbContext
using Microsoft.EntityFrameworkCore;
public class AppDbContext : DbContext
{
public DbSet<User> Users { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Data Source=mydatabase.db");
}
}public class UserService
{
private readonly AppDbContext _context;
public UserService(AppDbContext context)
{
_context = context;
}
// Create
public void AddUser(User user)
{
_context.Users.Add(user);
_context.SaveChanges();
}
// Read
public List<User> GetAllUsers()
{
return _context.Users.ToList();
}
public User GetUserById(int id)
{
return _context.Users.Find(id);
}
// Update
public void UpdateUser(User user)
{
_context.Users.Update(user);
_context.SaveChanges();
}
// Delete
public void DeleteUser(int id)
{
var user = _context.Users.Find(id);
if (user != null)
{
_context.Users.Remove(user);
_context.SaveChanges();
}
}
}Advanced Topics
1. Database Migrations with EF Core
- Install the EF Core tools:
dotnet tool install --global dotnet-efdotnet ef migrations add InitialCreate
dotnet ef database update2. Transactions
public void PerformTransaction()
{
using (var connection = CreateConnection())
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
// Multiple operations
SQLiteCommand command1 = new SQLiteCommand("INSERT INTO Users...", connection, transaction);
command1.ExecuteNonQuery();
SQLiteCommand command2 = new SQLiteCommand("UPDATE Users...", connection, transaction);
command2.ExecuteNonQuery();
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}3. Parameterized Queries (Security)
- Always use parameters to prevent SQL injection:
public User GetUserByEmail(string email)
{
using (var connection = CreateConnection())
{
string query = "SELECT * FROM Users WHERE Email = @email";
SQLiteCommand command = new SQLiteCommand(query, connection);
command.Parameters.AddWithValue("@email", email);
// Execute query...
}
}Complete Example Application
using System;
using System.Collections.Generic;
using System.Data.SQLite;
class Program
{
static void Main()
{
var dbHelper = new DatabaseHelper();
dbHelper.CreateTables();
// Create
dbHelper.AddUser(new User
{
FirstName = "John",
LastName = "Doe",
Email = "john@example.com"
});
// Read
var users = dbHelper.GetAllUsers();
Console.WriteLine("All Users:");
foreach (var user in users)
{
Console.WriteLine($"{user.Id}: {user.FirstName} {user.LastName}");
}
// Update
var userToUpdate = users[0];
userToUpdate.FirstName = "UpdatedName";
dbHelper.UpdateUser(userToUpdate);
// Delete
dbHelper.DeleteUser(userToUpdate.Id);
}
}SQLite with C# provides a powerful yet lightweight database solution for various applications. Whether you choose the traditional ADO.NET approach or the more modern Entity Framework Core, you have all the tools needed to implement robust CRUD operations.
Key takeaways:
- SQLite is serverless and requires no configuration
- Both direct SQL and ORM approaches are available
- Proper connection and transaction management are crucial
- Security should always be a priority
- EF Core simplifies many database operations while maintaining flexibility