SQLite 是一個輕量級的嵌入式數(shù)據庫，它適用于小型到中型的應用場景。SQLite在C#中的應用非常廣泛，支持跨平臺開發(fā)，并且非常易于使用。下面我們將詳細介紹如何在C#中使用SQLite，并提供詳細的例子。
特點
輕量級：SQLite 是一個嵌入式數(shù)據庫，不需要獨立的服務器進程，它是一個C庫，可以集成到主程序中。
跨平臺：SQLite 支持包括Windows、Linux、macOS在內的多個平臺。
自包含：SQLite 的數(shù)據庫是單個文件，非常適合移動應用和其他需要便攜性的應用。
豐富的功能：支持事務、跨多個表的復雜查詢、視圖、觸發(fā)器等。
環(huán)境配置
安裝SQLite NuGet包
dotnet add package System.Data.SQLite.Core
基礎操作示例
1. 創(chuàng)建并連接SQLite數(shù)據庫
首先，我們需要創(chuàng)建一個SQLite數(shù)據庫，并連接到該數(shù)據庫。
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. 創(chuàng)建表
創(chuàng)建一個名為 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. 插入數(shù)據
往 users 表中插入用戶數(shù)據。
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. 查詢數(shù)據
從 users 表中查詢數(shù)據，并打印輸出。
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. 更新數(shù)據
更新 users 表中的數(shù)據。
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. 刪除數(shù)據
從 users 表中刪除數(shù)據。
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; }}
我們創(chuàng)建一個用戶倉儲類，包含基本的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以及一些常見的數(shù)據庫操作。SQLite 是一個強大的嵌入式數(shù)據庫，適用于各種應用場景。希望通過本文的介紹，您能夠輕松地在C#項目中使用SQLite進行數(shù)據庫操作。
該文章在 2024/8/1 9:01:56 編輯過
400 186 1886
