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<User> GetAllUsers() { List<User> users = new List<User>(); 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进行数据库操作。
该文章在 2024/8/1 9:01:56 编辑过
400 186 1886
