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