Creating simple CRUD using SQLite in C# C# with Example

First of all we need to add SQLite support to our application. There are two ways of doing that 
Download DLL suiting your system from SQLite download page and then add to the project manually 
Add SQLite dependency via NuGet 
We'll do it the second way 
First open the NuGet menu 
and search for System.Data.SQLite, select it and hit Install 
Installation can also be done from Package Manager Console with 
PM> Install-Package System.Data.SQLite 
Or for only core features 
PM> Install-Package System.Data.SQLite.Core 
 

That's it for the download, so we can go right into coding. 
First create a simple SQLite database with this table and add it as a file to the project 
CREATE TABLE User( 
Id INTEGER PRIMARY KEY AUTOINCREMENT, 
FirstName TEXT NOT NULL, 
LastName TEXT NOT NULL 
); 
Also do not forget to set the Copy to Output Directory property of the file to Copy if newer of Copy always, 
based on your needs 
Create a class called User, which will be the base entity for our database 
private class User 
{ 
public string FirstName { get; set; } 
public string Lastname { get; set; } 
} 
We'll write two methods for query execution, first one for inserting, updating or removing from database 
private int ExecuteWrite(string query, Dictionary args) 
{ 
int numberOfRowsAffected; 
//setup the connection to the database 
using (var con = new SQLiteConnection("Data Source=test.db")) 
{ 
con.Open(); 
//open a new command 
using (var cmd = new SQLiteCommand(query, con)) 
{ 
//set the arguments given in the query 
foreach (var pair in args) 
{ 
cmd.Parameters.AddWithValue(pair.Key, pair.Value); 
} 
//execute the query and get the number of row affected 
numberOfRowsAffected = cmd.ExecuteNonQuery(); 
} 
return numberOfRowsAffected; 
 

} 
} 
and the second one for reading from database 
private DataTable Execute(string query) 
{ 
if (string.IsNullOrEmpty(query.Trim())) 
return null; 
using (var con = new SQLiteConnection("Data Source=test.db")) 
{ 
con.Open(); 
using (var cmd = new SQLiteCommand(query, con)) 
{ 
foreach (KeyValuePair entry in args) 
{ 
cmd.Parameters.AddWithValue(entry.Key, entry.Value); 
} 
var da = new SQLiteDataAdapter(cmd); 
var dt = new DataTable(); 
da.Fill(dt); 
da.Dispose(); 
return dt; 
} 
} 
} 
Now lets get into our CRUD methods 
Adding user 
private int AddUser(User user) 
{ 
const string query = "INSERT INTO User(FirstName, LastName) VALUES(@firstName, @lastName)"; 
//here we are setting the parameter values that will be actually 
//replaced in the query in Execute method 
var args = new Dictionary 
{ 
{"@firstName", user.FirstName}, 
{"@lastName", user.Lastname} 
}; 
return ExecuteWrite(query, args); 
} 
Editing user 
private int EditUser(User user) 
{ 
const string query = "UPDATE User SET FirstName = @firstName, LastName = @lastName WHERE Id = 
@id"; 
//here we are setting the parameter values that will be actually 
//replaced in the query in Execute method 
var args = new Dictionary 
 

{ 
{"@id", user.Id}, 
{"@firstName", user.FirstName}, 
{"@lastName", user.Lastname} 
}; 
return ExecuteWrite(query, args); 
} 
Deleting user 
private int DeleteUser(User user) 
{ 
const string query = "Delete from User WHERE Id = @id"; 
//here we are setting the parameter values that will be actually 
//replaced in the query in Execute method 
var args = new Dictionary 
{ 
{"@id", user.Id} 
}; 
return ExecuteWrite(query, args); 
} 
Getting user by Id 
private User GetUserById(int id) 
{ 
var query = "SELECT * FROM User WHERE Id = @id"; 
var args = new Dictionary 
{ 
{"@id", id} 
}; 
DataTable dt = ExecuteRead(query, args); 
if (dt == null || dt.Rows.Count == 0) 
{ 
return null; 
} 
var user = new User 
{ 
Id = Convert.ToInt32(dt.Rows[0]["Id"]), 
FirstName = Convert.ToString(dt.Rows[0]["FirstName"]), 
Lastname = Convert.ToString(dt.Rows[0]["LastName"]) 
}; 
return user; 
}