From Beginner to Advanced: A Complete Guide to CRUD Operations in SQLite with C#

RSDevX
3 min readOct 22, 2024

--

In modern application development for example in WinForms or WPF, managing data efficiently is crucial. One of the most popular lightweight database systems is SQLite, known for its simplicity and ease of use.

C# SQLite Database complete Tutorial for beginners

Original article: Complete SQLite Database Tutorial using C# and ADO.NET

Full Source codes for the C# SQLite Database Tutorial can be found here

In this article, we will explore how to perform basic CRUD (Create, Read, Update, Delete) operations on an SQLite database using C#.This guide will cover everything from setting up your project to executing SQL commands, making it an essential resource for developers looking to work with SQLite in C#.

We will start by guiding you through the process of setting up your C# project to interact with SQLite, ensuring that you have all the necessary tools and libraries at your disposal. This includes installing the SQLite NuGet package and configuring your environment to support database operations

Create a SQLite database using C#

To begin, let’s create a simple SQLite database and a table for our operations. The following code snippet demonstrates how to create a database and a table named Customers.

string connectionString = "Data Source=customers.db;Version=3;";

using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();

string sqlCreateTable = @"CREATE TABLE IF NOT EXISTS Customers (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT,
Age INTEGER,
Email TEXT)";
using (var command = new SQLiteCommand(sqlCreateTable, connection))
{
command.ExecuteNonQuery();
}

In this section, we will cover how to insert new records into the database. You will learn about the importance of using parameterized queries to safeguard against SQL injection and ensure data integrity.

using (var connection = new SQLiteConnection("Data Source=customers.db;Version=3;"))
{
connection.Open();
string sqlInsert = "INSERT INTO Customers (Name, Age, Email) VALUES (@Name, @Age, @Email)";

using (var command = new SQLiteCommand(sqlInsert, connection))
{
command.Parameters.AddWithValue("@Name", name);
command.Parameters.AddWithValue("@Age", age);
command.Parameters.AddWithValue("@Email", email);
command.ExecuteNonQuery();
}

Read data from SQLite database

Here, we will focus on retrieving data from the database. You will see how to execute SELECT statements and utilize data readers to fetch and display records. This is a crucial aspect of data management, allowing applications to present relevant information to users effectively.

using (var connection = new SQLiteConnection("Data Source=customers.db;Version=3;"))
{
connection.Open();
string sqlSelect = "SELECT * FROM Customers";

using (var command = new SQLiteCommand(sqlSelect, connection))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
int age = reader.GetInt32(2);
string email = reader.GetString(3);
Console.WriteLine($"Id: {id}, Name: {name}, Age: {age}, Email: {email}");
}

Update a record in a SQLite database

Updating records is a common requirement in many applications. We will demonstrate how to modify existing entries in the database and ensure that your application maintains accurate and up-to-date information.

connection.Open();
string sqlUpdate = "UPDATE Customers SET Name = @Name, Age = @Age, Email = @Email WHERE Id = @Id";

using (var command = new SQLiteCommand(sqlUpdate, connection))
{
command.Parameters.AddWithValue("@Name", name);
command.Parameters.AddWithValue("@Age", age);
command.Parameters.AddWithValue("@Email", email);
command.Parameters.AddWithValue("@Id", id);
command.ExecuteNonQuery();
}

Delete a Row inside SQLite database

Finally, we will explore how to safely remove records from the database. Understanding how to perform delete operations is essential for data management, allowing you to maintain a clean and organized database.

connection.Open();
string sqlDelete = "DELETE FROM Customers WHERE Id = @Id";

using (var command = new SQLiteCommand(sqlDelete, connection))
{
command.Parameters.AddWithValue("@Id", id);
command.ExecuteNonQuery();
}

--

--

No responses yet