使用SQLite：C#.NET 中 CRUD 操作示例
4.97/5 (14投票s)
带有一个 DEMO WPF 应用程序 (WinNotif)
引言
SQLite 是某些需要**无服务器、零配置、事务性（关系数据库*）、单个数据文件**的应用程序的完美解决方案。
(*) 需要手动启用 外键约束
sqlite> PRAGMA foreign_keys = ON;
在这篇文章中，我将向您展示如何使用 System.Data.SQLite，这是一个用于 SQLite 的 ADO.NET 包装器。
此外，我还想向您介绍一个使用 SQLite 的程序。
获取 System.Data.SQLite 包
使用 Visual Studio 的 NuGet 包管理器，搜索“SQLite”。
如果您计划使用 Entity Framework 或 LINQ，请选择您需要的。如果您想同时使用它们，请安装`System.Data.SQLite`。但如果您只使用“传统风格”的 SQL 查询语法，只需安装**核心**版本，这就是我使用的版本。Visual Studio 将自动为您添加引用。现在我们可以编写代码来查询数据库了！
介绍 DEMO（WinNotif 程序）
WinNotif 是一个在屏幕上显示引语的程序。引语来自单个 sqlite 数据库文件。每个引语都有属性，例如：作者来源、语言和文本内容。`Author`、`Language` 和 `Quote` 是三个模型（C# 类）。
语言模型定义如下：
public class Language
{
int _id;
string _langTitle;
public int Id
{
get { return _id; }
set { _id = value; }
}
public string LangTitle
{
get { return _langTitle; }
set { _langTitle = value; }
}
}
数据库架构
数据类型
请注意，SQLite 数据类型 与 SQL Server 或其他数据库系统不同。文本（`string`、`varchar`）和数字（`integer`、`double`）很常见，可以根据我们的需要轻松转换。我发现日期时间有点不同。为了避免将来在不同平台（.NET、Java、PHP）上可能出现的问题（头痛），我选择不使用 SQLite 的内置日期和时间函数。
有三个主要表对应于我们的模型：`Language`、`Author` 和 `Quote` 表。
(*) **注意**：我建议您**千万不要**犹豫在数据库表上设置约束。它可以强制执行数据的完整性，并且只会让您的数据库更好。
语言表
CREATE TABLE Language (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
LangTitle TEXT NOT NULL UNIQUE CHECK (LangTitle <> '')
)
作者表
CREATE TABLE Author (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
Name TEXT NOT NULL UNIQUE,
Nationality TEXT CHECK (Nationality <> ''),
BirthDay INTEGER CHECK (BirthDay > 0 AND BirthDay < 32),
BirthMonth INTEGER CHECK (BirthMonth > 0 AND BirthMonth < 13),
BirthYear INTEGER CHECK (BirthYear > 0 AND BirthYear < 5000)
)
引语表
CREATE TABLE Quote (
Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
Content TEXT NOT NULL UNIQUE CHECK (Content <> ''),
AuthorId INTEGER NOT NULL DEFAULT '1' REFERENCES Author (Id) _
ON DELETE RESTRICT ON UPDATE RESTRICT,
LanguageId INTEGER NOT NULL DEFAULT '1' _
REFERENCES Language (Id) ON DELETE RESTRICT ON UPDATE RESTRICT)
如您所见，外键约束通过 `Quote` 的 `LanguageId` 和 `Language` 的 `Id` 列建立了 `Quote` 和 `Language` 表之间的关系。换句话说，`Quote` 表的 `LanguageId`（它是外键）列映射到 `Language` 表的 `Id`（它是主键）列。`Quote` 和 `Author` 表也是如此。`Language` 和 `Author` 表称为父表。`Quote` 称为子表。
因此，您不能向 `Quote`（子）表添加语言 ID 在 `Language`（父）表中不存在的新引语。您不能更新当前有效的引语，使其语言 ID 不对应于 `Language` 表中的一行。只要该行仍然引用 `Quote`（子）表中的一行，您就不能删除 `Language`（父）表中的一行。
插入您的虚拟数据，或从上面的链接下载示例数据库文件。现在我们可以编写 C#.NET 代码来执行 CRUD（创建、读取、更新和删除）操作了。
示例代码
务必首先添加指令：`using System.Data.SQLite;`。
连接字符串
string connectionString = @"Data Source=PATH_TO_DB_FILE\...\file.ABC;
Version=3; FailIfMissing=True; Foreign Keys=True;";
(*) 非常重要的一点是：显式添加 `Foreign Keys=True;` 选项，因为 SQLite 不会自动强制执行外键约束。否则，您为保持数据有效性和完整性所做的所有努力都将白费！！
(*) `PATH_TO_DB_FILE\...\file.ABC` 可以是数据库文件的绝对路径或相对路径。
检索数据
从 `Language` 表检索所有语言。如果传递的语言 ID 为 `0`，则选择全部。
public static List<language> GetLanguages(int langId)
{
List<language> langs = new List<language>();
try
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
string sql = "SELECT * FROM Language WHERE Id = " + langId;
if (langId == 0)
{
sql = "SELECT * FROM Language";
}
using (SQLiteCommand cmd = new SQLiteCommand(sql, conn))
{
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Language la = new Language();
la.LangTitle = reader["LangTitle"].ToString();
la.Id = Int32.Parse(reader["Id"].ToString());
langs.Add(la);
}
}
}
conn.Close();
}
}
catch (SQLiteException e)
{
...
}
return langs;
}
更新数据
现在，我们使用参数化查询来提高数据库操作的安全性。
public static int UpdateLang(int id, string newLangTitle)
{
int result = -1;
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "UPDATE Language "
+ "SET LangTitle = @Lang "
+ "WHERE Id = @Id";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Lang", newLangTitle);
cmd.Parameters.AddWithValue("@Id", id);
try
{
result = cmd.ExecuteNonQuery();
}
catch (SQLiteException)
{
...
}
}
conn.Close();
}
return result;
}
如果结果大于 `-1`，则表示 `update` 操作影响的行数。
插入数据
与 `update` 操作非常相似
public static int AddLang(string langTitle)
{
int result = -1;
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "INSERT INTO Language(LangTitle) VALUES (@Lang)";
cmd.Prepare();
cmd.Parameters.AddWithValue("@Lang", langTitle);
try
{
result = cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
...
}
}
conn.Close();
}
return result;
}
删除数据
public static int DeleteLang(int id)
{
int result = -1;
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "DELETE FROM Language WHERE Id = @I";
cmd.Prepare();
cmd.Parameters.AddWithValue("@I", id);
try
{
result = cmd.ExecuteNonQuery();
}
catch (SQLiteException e)
{
...
}
}
conn.Close();
}
return result;
}
更新引语
与更新语言类似的示例，只是参数多一些
public static int UpdateQuote(int id, string newContent, int newAuthId, int newLangId)
{
int result = -1;
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(conn))
{
cmd.CommandText = "UPDATE Quote "
+ "SET Content = @C, AuthorId = @A, LanguageId = @L "
+ "WHERE Id = @I";
cmd.Prepare();
cmd.Parameters.AddWithValue("@C", newContent);
cmd.Parameters.AddWithValue("@A", newAuthId);
cmd.Parameters.AddWithValue("@L", newLangId);
cmd.Parameters.AddWithValue("@I", id);
try
{
result = cmd.ExecuteNonQuery();
}
catch (SQLiteException)
{
...
}
}
conn.Close();
}
return result;
}
结论
SQLite 对于嵌入式设备和像这样的独立应用程序来说是一个非常好的选择。无需配置和管理数据库服务器。我们只需要一个单个数据文件。
欢迎尝试我的最新应用程序，WinNotif v.1。该程序正在开发中。稍后将添加更多功能。您的意见将非常有帮助。