Connecting and Using SQLite Databases from C#: A Practical Guide
There are two primary methods for connecting to SQLite databases in C#:
-
ADO.NET (System.Data.SQLite):
- This is the most common approach, providing a familiar interface for database interactions.
-
Dapper:
- A lightweight object mapper (ORM) that simplifies database interactions.
Using SQLite in C#
Once connected, you can perform various operations on your SQLite database:
-
Creating Tables:
string sql = @"CREATE TABLE Customers ( CustomerID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Email TEXT UNIQUE )"; using (var cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); }
-
Inserting Data:
string sql = @"INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)"; using (var cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddWithValue("@Name", "John Doe"); cmd.Parameters.AddWithValue("@Email", "[email protected]"); cmd.ExecuteNonQuery(); }
-
string sql = @"SELECT * FROM Customers WHERE Name = @Name"; using (var cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddWithValue("@Name", "John Doe"); using (var reader = cmd.ExecuteReader()) { if (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); string email = reader.GetString(2); // Process retrieved data } } }
-
Updating Data:
string sql = @"UPDATE Customers SET Email = @NewEmail WHERE CustomerID = @ID"; using (var cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddWithValue("@NewEmail", "[email protected]"); cmd.Parameters.AddWithValue("@ID", 1); // Replace with actual ID cmd.ExecuteNonQuery(); }
-
string sql = @"DELETE FROM Customers WHERE CustomerID = @ID"; using (var cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddWithValue("@ID", 1); // Replace with actual ID cmd.ExecuteNonQuery(); }
Choosing the Right Approach
- ADO.NET offers a more granular control over database interactions but requires more code.
- Dapper provides a simpler syntax and object mapping capabilities, making it suitable for rapid development.
using System.Data.SQLite;
public class SqliteExampleADO
{
private const string ConnectionString = @"Data Source=mydatabase.db";
public void CreateCustomersTable()
{
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
string sql = @"CREATE TABLE Customers (
CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
Email TEXT UNIQUE
)";
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public void InsertCustomer(string name, string email)
{
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
string sql = @"INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Email", email);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public List<Customer> GetCustomers()
{
List<Customer> customers = new List<Customer>();
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
string sql = @"SELECT * FROM Customers";
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
string email = reader.GetString(2);
customers.Add(new Customer { CustomerID = id, Name = name, Email = email });
}
}
}
conn.Close();
}
return customers;
}
}
public class Customer
{
public int CustomerID { get; set; }
public string Name { get; set; }
public string Email { get; set; }
}
Dapper Example:
using Dapper;
using System.Data.SQLite;
public class SqliteExampleDapper
{
private const string ConnectionString = @"Data Source=mydatabase.db";
public void CreateCustomersTable()
{
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
string sql = @"CREATE TABLE IF NOT EXISTS Customers (
CustomerID INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
Email TEXT UNIQUE
)";
conn.Execute(sql);
}
}
public void InsertCustomer(string name, string email)
{
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
string sql = @"INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
conn.Execute(sql, new { Name = name, Email = email });
}
}
public List<Customer> GetCustomers()
{
using (var conn = new SQLiteConnection(ConnectionString))
{
conn.Open();
string sql = @"SELECT * FROM Customers";
return conn.Query<Customer>(sql).ToList();
}
}
}
public class Customer
{
public int CustomerID { get; set; }
public string Name { get; set; }
public string Email { get; set; }
}
-
Entity Framework Core (EF Core):
- If your project already utilizes EF Core for data access, you can leverage it for SQLite as well.
- Requires installing the
Microsoft.EntityFrameworkCore.Sqlite
NuGet package. - Offers a higher-level abstraction for database interactions, mapping C# classes to database tables.
-
Dapper.Contrib:
- An extension library for Dapper that provides additional functionality, including:
- Automatic object mapping from database results.
- Transaction management.
- Command caching.
- Useful if you want the simplicity of Dapper with some additional features.
- Requires installing the
Dapper.Contrib
NuGet package.
- An extension library for Dapper that provides additional functionality, including:
-
SQLitePCL.raw:
- A low-level, managed code interface directly interacting with the native SQLite library.
- Offers maximum performance and control but requires more development effort.
- Suitable for advanced scenarios where raw performance is critical.
- Requires building the library from source or using a pre-built package.
- EF Core: If your project already uses EF Core and you value a high-level abstraction.
- Dapper.Contrib: If you're using Dapper and want some additional features like automatic object mapping.
- SQLitePCL.raw: For experienced developers needing maximum performance and control over SQLite interactions.
c# sqlite