SQLite 是一个轻量级的嵌入式数据库，它适用于小型到中型的应用场景。SQLite在C#中的应用非常广泛，支持跨平台开发，并且非常易于使用。下面我们将详细介绍如何在C#中使用SQLite，并提供详细的例子。
特点
- 轻量级：SQLite 是一个嵌入式数据库，不需要独立的服务器进程，它是一个C库，可以集成到主程序中。
- 跨平台：SQLite 支持包括Windows、Linux、macOS在内的多个平台。
- 自包含：SQLite 的数据库是单个文件，非常适合移动应用和其他需要便携性的应用。
- 丰富的功能：支持事务、跨多个表的复杂查询、视图、触发器等。
环境配置
- 安装SQLite NuGet包
dotnet add package System.Data.SQLite.Core
基础操作示例
1. 创建并连接SQLite数据库
首先，我们需要创建一个SQLite数据库，并连接到该数据库。
private void btnCreateDb_Click(object sender, EventArgs e)
{
string connectionString = "Data Source=demo.db;Version=3;";
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
MessageBox.Show("SQLite database connection open.");
}
}
2. 创建表
创建一个名为 users 的表，用于存储用户信息。
private void btnCreateTable_Click(object sender, EventArgs e)
{
string connectionString = "Data Source=demo.db;Version=3;";
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)";
using (var command = new SQLiteCommand(sql, connection))
{
command.ExecuteNonQuery();
MessageBox.Show("Table 'users' created.");
}
}
}
3. 插入数据
往 users 表中插入用户数据。
private void btnInsert_Click(object sender, EventArgs e)
{
string connectionString = "Data Source=demo.db;Version=3;";
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = "INSERT INTO users (name, age) VALUES ('Alice', 30)";
using (var command = new SQLiteCommand(sql, connection))
{
command.ExecuteNonQuery();
MessageBox.Show("Data inserted into 'users' table.");
}
}
}
4. 查询数据
从 users 表中查询数据，并打印输出。
private void btnQuery_Click(object sender, EventArgs e)
{
string connectionString = "Data Source=demo.db;Version=3;";
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = "SELECT * FROM users";
using (var command = new SQLiteCommand(sql, connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
MessageBox.Show($"ID: {reader["id"]}, Name: {reader["name"]}, Age: {reader["age"]}");
}
}
}
}
}
5. 更新数据
更新 users 表中的数据。
using System;
using System.Data.SQLite;
class Program
{
static void Main()
{
string connectionString = "Data Source=demo.db;Version=3;";
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = "UPDATE users SET age = 31 WHERE name = 'Alice'";
using (var command = new SQLiteCommand(sql, connection))
{
command.ExecuteNonQuery();
Console.WriteLine("Data updated in 'users' table.");
}
}
}
}
6. 删除数据
从 users 表中删除数据。
using System;
using System.Data.SQLite;
class Program
{
static void Main()
{
string connectionString = "Data Source=demo.db;Version=3;";
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
string sql = "DELETE FROM users WHERE name = 'Alice'";
using (var command = new SQLiteCommand(sql, connection))
{
command.ExecuteNonQuery();
Console.WriteLine("Data deleted from 'users' table.");
}
}
}
}
一个简单仓储类
用户模型类
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
我们创建一个用户仓储类，包含基本的CRUD（Create, Read, Update, Delete）操作：
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AppSqlLite
{
public class UserRepository
{
private string _connectionString;
public UserRepository(string connectionString)
{
_connectionString = connectionString;
InitializeDatabase();
}
private void InitializeDatabase()
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
string sql = "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)";
using (var command = new SQLiteCommand(sql, connection))
{
command.ExecuteNonQuery();
}
}
}
public void AddUser(User user)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
string sql = "INSERT INTO users (name, age) VALUES (@name, @age)";
using (var command = new SQLiteCommand(sql, connection))
{
command.Parameters.AddWithValue("@name", user.Name);
command.Parameters.AddWithValue("@age", user.Age);
command.ExecuteNonQuery();
}
}
}
public User GetUser(int id)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
string sql = "SELECT * FROM users WHERE id = @id";
using (var command = new SQLiteCommand(sql, connection))
{
command.Parameters.AddWithValue("@id", id);
using (SQLiteDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
return new User
{
Id = Convert.ToInt32(reader["id"]),
Name = reader["name"].ToString(),
Age = Convert.ToInt32(reader["age"])
};
}
}
}
}
return null;
}
public List GetAllUsers()
{
List users = new List();
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
string sql = "SELECT * FROM users";
using (var command = new SQLiteCommand(sql, connection))
{
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
users.Add(new User
{
Id = Convert.ToInt32(reader["id"]),
Name = reader["name"].ToString(),
Age = Convert.ToInt32(reader["age"])
});
}
}
}
}
return users;
}
public void UpdateUser(User user)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
string sql = "UPDATE users SET name = @name, age = @age WHERE id = @id";
using (var command = new SQLiteCommand(sql, connection))
{
command.Parameters.AddWithValue("@name", user.Name);
command.Parameters.AddWithValue("@age", user.Age);
command.Parameters.AddWithValue("@id", user.Id);
command.ExecuteNonQuery();
}
}
}
public void DeleteUser(int id)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
string sql = "DELETE FROM users WHERE id = @id";
using (var command = new SQLiteCommand(sql, connection))
{
command.Parameters.AddWithValue("@id", id);
command.ExecuteNonQuery();
}
}
}
}
}
private void btnRepository_Click(object sender, EventArgs e)
{
string connectionString = "Data Source=demo.db;Version=3;";
UserRepository userRepository = new UserRepository(connectionString);
// 添加用户
User user = new User { Name = "Alice", Age = 30 };
userRepository.AddUser(user);
MessageBox.Show("User added.");
// 获取用户
User retrievedUser = userRepository.GetUser(1);
MessageBox.Show($"Retrieved User: ID={retrievedUser.Id}, Name={retrievedUser.Name}, Age={retrievedUser.Age}");
// 更新用户
retrievedUser.Age = 31;
userRepository.UpdateUser(retrievedUser);
MessageBox.Show("User updated.");
// 获取所有用户
var users = userRepository.GetAllUsers();
MessageBox.Show("All users:");
foreach (var u in users)
{
MessageBox.Show($"ID={u.Id}, Name={u.Name}, Age={u.Age}");
}
// 删除用户
userRepository.DeleteUser(1);
MessageBox.Show("User deleted.");
}
结论
本文介绍了如何在C#中使用SQLite以及一些常见的数据库操作。SQLite 是一个强大的嵌入式数据库，适用于各种应用场景。希望通过本文的介绍，您能够轻松地在C#项目中使用SQLite进行数据库操作。