在 C#/VB.Net 中使用 SQLite
4.93/5 (90投票s)
关于如何使用 SQLite 和 C#/.Net 高效管理约 100,000 条记录的快速指南。
 |
目录
引言
PC、服务器和笔记本电脑在内存和处理能力方面的最新进步，使得在日常应用程序中处理和显示复杂数据结构的需求日益普遍。本文将为一种广为人知且广泛使用的开源数据库 SQLite 提供 C#/.Net 101 风格的介绍（请注意，拼写不是 SQLLite 或 SQL Light）。
SQLite 数据库引擎可用于多种操作系统（Android、IOS、Linux、Windows）、编程语言，并可作为内存引擎或本地文件数据库引擎使用。该引擎无需额外设置，因为它实现为一组 DLL，这些 DLL 会被引用到给定的 VS 项目中。
使用 SQLite 等嵌入式数据库引擎，使我们能够高效地处理复杂的数据结构，而无需
- 在实现复杂结构（例如，附加索引或
- 为运行专用数据库服务器而进行额外的设置/维护/安全工作）时重新发明轮子。
嵌入式数据库的应用包括（但不限于）
- 以最佳格式（应用程序文件格式）存储和检索数据结构
- 在不使用附加服务器的情况下实时计算复杂分析
SQLite 默认限制
一次只能有 2 个线程访问文件数据库，因此在使用完数据库后务必关闭它，因为潜在的挂起线程可能会阻止后续对 Open() 的调用。我们可以通过使用正确的日志模式来解决此默认限制，正如我们在下面的“从多个线程访问 SQLite 数据库”部分中所述。
为向后兼容性考虑，外键的强制执行默认情况下是关闭的。如何强制执行外键将在“强制执行外键”部分中显示。
先决条件
可以通过 NuGet 使用 SQLite 数据库引擎。只需创建一个新的 VS 项目（例如：控制台项目），然后搜索 System.Data.SQLite 包。安装该包即可开始编码。您还可以从以下位置手动下载二进制文件：
- http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki 或
- https://nuget.net.cn/packages/System.Data.SQLite/（将 nuget 文件重命名为 zip 并解压）
并在项目中添加静态引用。
背景
我创建了一系列关于“高级 WPF TreeView”的文章，并收到了一些反馈，内容如下：
如何将 TreeView 的数据存储或检索到/从文件中？
为了以应用方式回答这个问题，我将侧面介绍 SQLite 的基础知识，稍后将在另一篇文章中构建它，该文章将解释如何在关系数据库引擎中存储树状结构数据（另请参阅此处是 XML 解决方案）。
附加工具
关系数据库系统通常包含某种（基于文本的）SQL 查询应用程序（例如：SQL Management Studio 或 VS for SQL Server）。SQLite 生态系统包含大量此类客户端查询应用程序。例如，有一个用于 Visual Studio 的 SQLite / SQL Server Compact Toolbox 插件，可用于编写 SQL 查询或查看数据库的当前数据定义。
另一方面，我是一个开源的忠实粉丝，因此出于这个和其他原因，我更喜欢 FireFox 的 SQLite Manager 插件，因为它可以在所有平台上使用，并且不会更改我的 VS 设置。
因此，您可以使用这些工具中的任何一个来检查数据库，无论您执行以下哪个步骤。
Using the Code
本教程中的示例应用程序是简单的 WPF 项目，具有文本输出。代码位于 MainWindow.xaml.cs 文件中（除非另有说明）。
一个“Hello World”SQLite 数据库
示例项目中的 SQLite 代码本身就能说明问题，但对于不熟悉关系数据库系统的人来说，可能会提出一些疑问。因此，这里有一些代码片段可以解释基础知识：
创建数据库（文件）
您可以使用以下代码片段创建 SQLite 数据库文件：
// create a new database connection:
SQLiteConnection sqlite_conn =
new SQLiteConnection("Data Source=database.sqlite;Version=3;");
// open the connection:
SQLiteCommand sqlite_conn.Open();
Dim sqlite_conn As SQLiteConnection
' create a new database connection:
sqlite_conn = New SQLiteConnection("Data Source=database.sqlite;Version=3;")
' open the connection:
sqlite_conn.Open()
第一行创建一个 SQLiteConnection 对象，其构造函数接受一个 连接字符串作为参数。此语句将在 Debug/bin 或 Release/bin 下的当前目录中创建一个名为 database.sqlite 的文件。SQLite 的最低必需版本是 3。
// create a new database connection:
SQLiteConnection sqlite_conn =
new SQLiteConnection("Data Source=:memory:;Version=3;");
// open the connection:
SQLiteCommand sqlite_conn.Open();
Dim sqlite_conn As SQLiteConnection
' create a new database connection:
sqlite_conn = New SQLiteConnection("Data Source=:memory:;Version=3;")
' open the connection:
sqlite_conn.Open()
上述语句在内存中创建了一个 SQLite 数据库。每个内存数据库实例都是唯一的，并在连接关闭时消失。
在 SQLite 文件中创建表
下面的语句创建一个新的基于文件的数据库并在其中创建一个表（SQLite 系统默认不会重新创建表。如果执行该语句两次，您将收到相应的异常）。
// create a new database connection:
SQLiteConnection sqlite_conn =
new SQLiteConnection("Data Source=database.sqlite;Version=3;");
// open the connection:
SQLiteCommand sqlite_conn.Open();
SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
// Let the SQLiteCommand object know our SQL-Query:
sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));";
// Now lets execute the SQL ;-)
sqlite_cmd.ExecuteNonQuery();
Dim sqlite_conn As SQLiteConnection
Dim sqlite_cmd As SQLiteCommand
' create a new database connection:
sqlite_conn = New SQLiteConnection("Data Source=:memory:;Version=3;")
' open the connection:
sqlite_conn.Open()
sqlite_cmd = sqlite_conn.CreateCommand()
' Let the SQLiteCommand object know our SQL-Query:
sqlite_cmd.CommandText = "CREATE TABLE test (id integer primary key, text varchar(100));"
' Now lets execute the SQL ;-)
sqlite_cmd.ExecuteNonQuery()
您可以使用最后两行对 SQLite 系统执行几乎任何 SQL 操作。通常，您会使用 ExecuteNonQuery() 方法对数据字典执行操作（创建、删除等），而其他方法，例如 ExecuteReader()，可用于检索 CommandText 属性中定义的该查询的（扩展）结果。
使用下面的创建表语句仅在首次创建表。否则，每次插入或选择数据时都会重用该表（请参阅 01_SQLite_tut.zip）。
sqlite_cmd.CommandText =
@"CREATE TABLE IF NOT EXISTS
[Mytable] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[NAME] NVARCHAR(2048) NULL)";
sqlite_cmd.CommandText =
@"CREATE TABLE IF NOT EXISTS
[Mytable] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[NAME] NVARCHAR(2048) NULL)"
向表中插入记录
这是一个“Hello World”示例，它将 2 个值写入现有表中的 1 条记录：
SQLiteConnection sqlite_conn =
new SQLiteConnection("Data Source=:memory:;Version=3;New=True;");
SQLiteCommand sqlite_conn.Open();
SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Hello World');";
sqlite_cmd.ExecuteNonQuery();
Dim sqlite_conn As SQLiteConnection
Dim sqlite_cmd As SQLiteCommand
' create a new database connection:
sqlite_conn = New SQLiteConnection("Data Source=:memory:;Version=3;")
' open the connection:
sqlite_conn.Open()
sqlite_cmd = sqlite_conn.CreateCommand()
sqlite_cmd.CommandText = "INSERT INTO test (id, text) VALUES (1, 'Hello World');"
sqlite_cmd.ExecuteNonQuery()
从表中读取记录
这是一个“Hello World”示例，它从现有表中读取具有 2 个值的记录：
SQLiteConnection sqlite_conn; // Database Connection Object
SQLiteCommand sqlite_cmd; // Database Command Object
SQLiteDataReader sqlite_datareader; // Data Reader Object
sqlite_conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;New=True;");
sqlite_conn.Open();
sqlite_cmd = sqlite_conn.CreateCommand();
sqlite_cmd.CommandText = "SELECT * FROM test";
sqlite_datareader = sqlite_cmd.ExecuteReader();
// The SQLiteDataReader allows us to run through each row per loop
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
// Print out the content of the text field:
// System.Console.WriteLine("DEBUG Output: '" + sqlite_datareader["text"] + "'");
object idReader = sqlite_datareader.GetValue(0);
string textReader = sqlite_datareader.GetString(1);
OutputTextBox.Text += idReader + " '" + textReader +"' "+ "\n";
}
Dim sqlite_conn As SQLiteConnection
Dim sqlite_cmd As SQLiteCommand
Dim sqlite_datareader As SQLiteDataReader
sqlite_conn = New SQLiteConnection("Data Source=database.sqlite;Version=3;New=True;")
sqlite_conn.Open()
sqlite_cmd = sqlite_conn.CreateCommand()
sqlite_cmd.CommandText = "SELECT * FROM test"
sqlite_datareader = sqlite_cmd.ExecuteReader()
' The SQLiteDataReader allows us to run through each row per loop
While (sqlite_datareader.Read()) ' Read() returns true if there is still a result line to read
Begin
' Print out the content of the text field:
' Console.WriteLine("DEBUG Output: '" + sqlite_datareader["text"] + "'")
Dim idReader as object
Dim textReader as String
idReader = sqlite_datareader.GetValue(0)
textReader = sqlite_datareader.GetString(1)
OutputTextBox.Text = OutputTextBox.Text + idReader + " '" + textReader +"' "+ Environment.NewLine
End
上述代码示例中的 while 循环将执行直到查询用完结果数据 - 或者，如果查询无法满足任何结果，则永远不会执行。
上面显示的 GetValue(1) 方法返回一个 .Net object 值，可以使用反射将其转换为其他类型。您也可以使用基于字段索引的语法来获得相同的结果：sqlite_datareader["text"]。
bool GetBoolean(int i);
byte GetByte(int i);
char GetChar(int i);
DateTime GetDateTime(int i);
decimal GetDecimal(int i);
double GetDouble(int i);
float GetFloat(int i);
Guid GetGuid(int i);
short GetInt16(int i);
int GetInt32(int i);
long GetInt64(int i);
Function GetBoolean(int i) as Boolean
Function GetByte(int i) as Byte
Function GetChar(int i) as Char
Function GetDateTime(int i) as DateTime
Function GetDecimal(int i) as Decimal
Function GetDouble(int i) as Double
Function GetFloat(int i) as Single
Function GetGuid(int i) as Guid
Function GetInt16(int i) as Short
Function GetInt32(int i) as Integer
Function GetInt64(int i) as Long
您要么知道 SQLiteDataReader 列的正确数据类型（因为您刚刚创建了它），要么可以使用下面的方法以确定性的方式确定给定列的正确数据类型。
// Retrieves the name of the back-end data-type of the column
string GetDataTypeName(int i);
// Returns the .NET type of a given column
Type GetFieldType(int i);
// Retrieves the name of the column
string GetName(int i);
// Retrieves the i of a column, given its name
int GetOrdinal(string name);
// Returns the original name of the specified column.
string GetOriginalName(int i);
' Retrieves the name of the back-end data-type of the column
Function GetDataTypeName(ByVal i as Integer) as String
' Returns the .NET type of a given column
Function GetFieldType(ByVal i as Integer) as Type
' Retrieves the name of the column
Function GetName(ByVal i as Integer) as String
' Retrieves the i of a column, given its name
Function GetOrdinal(ByVal name as String) as Integer
' Returns the original name of the specified column.
Function GetOriginalName(ByVal i as Integer) as String
一个 SQLite 包装类
C#/.Net 应用程序通常有其自身的配置要求和架构模式。本节讨论可能值得在第一个软件版本中进行包装的 SQLite 属性和方法。
我们将简要回顾 Models.SQLiteDatabase 数据库包装类，该类隐藏了一些实现细节，例如数据库文件的名称。
private const string _DBfileName = "database.sqlite";
Private _DBFileName As String = "database.sqlite"
Models.SQLiteDatabase 类还具有用于错误日志记录和状态显示的自定义属性。
public Exception Exception { get; set; }
public string Status { get; private set; }
public string ExtendendStatus{ get; set; }
Public Property Exception As Exception
Public Property Status As String
Public Property ExtendendStatus As String
覆盖现有数据库文件
数据库类还隐藏了数据库连接的技术建立方式，以及在数据库连接时是否会重新创建 SQLite 数据库文件。
private void ConstructConnection(bool overwriteFile = false)
{
if (overwriteFile == true)
{
if (System.IO.File.Exists(@".\" + _DBfileName) == true)
{
// Overwrites a file if it is already there
SQLiteConnection.CreateFile(_DBfileName);
}
}
_Connection = new SQLiteConnection("Data Source=" + _DBfileName);
Status = "Database is open.";
}
Private Sub ConstructConnection(ByVal Optional overWriteFile As Boolean = False)
Dim connectString As SQLiteConnectionStringBuilder = New SQLiteConnectionStringBuilder()
connectString.DataSource = DBFileNamePath
connectString.ForeignKeys = EnforceForeignKeys
connectString.JournalMode = GetJournalMode()
_Connection = New SQLiteConnection(connectString.ToString())
If System.IO.File.Exists(DBFileNamePath) = False Then
' Overwrites a file if it Is already there
SQLiteConnection.CreateFile(DBFileNamePath)
Status = "Created New Database."
Else
If overWriteFile = False Then
Status = "Using exsiting Database."
Else
' Overwrites a file if it Is already there
SQLiteConnection.CreateFile(DBFileNamePath)
End If
End If
End Sub
使用 SQLiteConnectionStringBuilder 构建连接字符串
SQLite 客户端应用程序需要将连接字符串格式化才能打开到 SQLite 数据库的连接，如前面部分所述。SQLite API 提供了一个 SQLiteConnectionStringBuilder 类来实现这一目的。请考虑以下代码片段：
SQLiteConnectionStringBuilder connectString = new SQLiteConnectionStringBuilder();
connectString.DataSource = "databasefile.sqlite";
connectString.ForeignKeys = true;
connectString.JournalMode = SQLiteJournalModeEnum.Wal;
System.Console.WriteLine(connectString.ToString());
SQLiteConnection sqlite_conn = new SQLiteConnection(connectString.ToString());
SQLiteCommand sqlite_conn.Open();
Dim connectString as SQLiteConnectionStringBuilder
connectString = new SQLiteConnectionStringBuilder()
connectString.DataSource = "databasefile.sqlite"
connectString.ForeignKeys = true
connectString.JournalMode = SQLiteJournalModeEnum.Wal
System.Console.WriteLine(connectString.ToString())
Dim sqlite_conn as SQLiteConnection
sqlite_conn = new SQLiteConnection(connectString.ToString());
Dim sqlite_conn as SQLiteCommand
sqlite_conn.Open()
上述代码片段的输出是：“data source=.\database.sqlite;foreign keys=True;journal mode=Wal”，这是 SQLiteConnection 对象所需的字符串。使用 SQLiteConnectionStringBuilder 的结果通常更具可读性，并且在未来的版本中更易于维护，因为 SQLite 应该最清楚连接字符串的正确格式。
Pragma User Version
SQLite 数据库文件规范具有用户版本属性，可以通过非 SQL 专有语句进行读取或设置，如下所示：
pragma user_version;或pragma user_version = 1;
user_version; 属性可供客户端软件使用，以确定给定数据文件是否为最新版本，以及在文件版本似乎过旧或过新时如何优雅地处理它。
public long UserVersion()
{
using (SQLiteCommand cmd = new SQLiteCommand(_Connection))
{
cmd.CommandText = "pragma user_version;";
return (long)cmd.ExecuteScalar();
}
}
public long UserVersionIncrease()
{
long version = UserVersion();
using (SQLiteCommand cmd = new SQLiteCommand(_Connection))
{
cmd.CommandText = string.Format("pragma user_version = {0};"
, version + 1);
cmd.ExecuteNonQuery();
}
return UserVersion();
}
Public Function UserVersion() As Long
Using cmd As SQLiteCommand = New SQLiteCommand(_Connection)
cmd.CommandText = "pragma user_version;"
Return CLng(cmd.ExecuteScalar())
End Using
End Function
Public Function UserVersionIncrease() As Long
Dim version = UserVersion()
Using cmd As SQLiteCommand = New SQLiteCommand(_Connection)
cmd.CommandText = String.Format("pragma user_version = {0};", version + 1)
cmd.ExecuteNonQuery()
End Using
Return UserVersion()
End Function
02_SQLite_tut.zip 演示应用程序使用上述代码在每次插入数据时（重用现有数据库文件）写入新的用户版本。我们可以看到 SQLite 用户版本的默认值为0。
请查阅以下链接以了解有关 SQLite 中 pragma 语句的更多信息：
从多个线程访问 SQLite 数据库
SQLite 数据库系统如果被超过 1 个线程同时访问，将抛出错误。此限制仅是默认限制，可以消除，正如 Scott 在下面的论坛部分中所建议的那样。在这种情况下要设置的相应选项是：
引用"PRAGMA journal_mode = WAL"
这个 pragma 选项实际上是 SQLite 引擎中的一个字符串。但在演示中，它被建模为一个枚举。
public enum JournalMode
{
DELETE = 0,
TRUNCATE = 1,
PERSIST = 2,
MEMORY = 3,
WAL = 4,
OFF = 5
}
Public Enum JournalMode
DELETE = 0
TRUNCATE = 1
PERSIST = 2
MEMORY = 3
WAL = 4
OFF = 5
End Enum
您可以使用 SQLiteDatabase 包装类方法：
public void JournalMode(JournalMode journalMode)在活动连接上设置日志模式；
- 以及
public string JournalMode()方法来读取当前使用的日志模式。
每个 JournalMode pragma 选项都可以通过上述方法按数据库连接进行设置。也就是说，如果客户端在下次连接时未指定日志模式，SQLite 引擎将恢复其默认值（DELETE）。'WAL' pragma 选项是此规则的一个例外，它实际上是持久化的，并且在客户端下次连接时未指定日志模式的情况下被用作默认值。
您可以使用测试应用程序进行验证，如果您创建了一个新数据库文件（勾选“覆盖数据库”），然后在不勾选“设置 Pragma Journal Mode”选项的情况下进行下一次连接时，检查结果标签。
| 步骤 1 | 第二步 | |
| TRUNCATE |  |  |
| WAL |  |  |
还有一个替代的 WAL 模式解决方案，即在 连接字符串中指定此选项。
SQLiteConnectionStringBuilder connectString = new SQLiteConnectionStringBuilder();
connectString.DataSource = "databasefile.sqlite";
connectString.JournalMode = SQLiteJournalModeEnum.Wal;
SQLiteConnection sqlite_conn = new SQLiteConnection(connectString.ToString());
sqlite_conn.Open();
Dim connectString as SQLiteConnectionStringBuilder
Dim sqlite_conn as SQLiteConnection
connectString = new SQLiteConnectionStringBuilder()
connectString.DataSource = "databasefile.sqlite"
connectString.JournalMode = SQLiteJournalModeEnum.Wal
sqlite_conn = new SQLiteConnection(connectString.ToString())
sqlite_conn.Open()
一个简单的应用程序，它想在启动时读取一个文件，并在应用程序生命周期结束时将更改的信息写回，则不需要这种多线程访问，但启用它应该有助于那些需要从多个线程访问多个表以实现复杂并行计算的应用程序。
强制执行外键
以下代码在 SQLite 数据库中创建了 2 个表：
string createQuery =
@"CREATE TABLE IF NOT EXISTS
[itemtype] (
[id] INTEGER NOT NULL PRIMARY KEY,
[name] VARCHAR(256) NOT NULL
)";
using (SQLiteCommand cmd = new SQLiteCommand(db.Connection))
{
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
}
createQuery =
@"CREATE TABLE IF NOT EXISTS
[solution] (
[id] INTEGER NOT NULL PRIMARY KEY,
[parent] INTEGER NOT NULL,
[level] INTEGER NOT NULL,
[name] VARCHAR(256) NOT NULL,
[itemtypeid] INTEGER NOT NULL,
FOREIGN KEY (itemtypeid) REFERENCES itemtype(id)
)";
using (SQLiteCommand cmd = new SQLiteCommand(db.Connection))
{
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
}
Dim createQuery As String = "CREATE TABLE IF NOT EXISTS
[itemtype] (
[id] INTEGER NOT NULL PRIMARY KEY,
[name] VARCHAR(256) NOT NULL
)"
Using cmd As SQLiteCommand = New SQLiteCommand(db.Connection)
cmd.CommandText = createQuery
cmd.ExecuteNonQuery()
End Using
createQuery =
@"CREATE TABLE IF NOT EXISTS
[solution] (
[id] INTEGER NOT NULL PRIMARY KEY,
[parent] INTEGER NOT NULL,
[level] INTEGER NOT NULL,
[name] VARCHAR(256) NOT NULL,
[itemtypeid] INTEGER NOT NULL,
FOREIGN KEY (itemtypeid) REFERENCES itemtype(id)
)"
Using cmd as SQLiteCommand = new SQLiteCommand(db.Connection)
cmd.CommandText = createQuery
cmd.ExecuteNonQuery()
End Using
上述 SQL 代码在 SQLite 数据库中创建了一个表约束。表约束在此情况下意味着 solution 表中的 itemtypeid 列仅限于 itemtype 表中 id 列的值。
数据库引擎通常通过在数据更改事务（提交事务）结束时存储的数据不一致时抛出异常来强制执行外键规则。但 SQLite 引擎默认不实现此行为，正如 Scott 在下面的论坛中暗示的那样。
SQLite 引擎允许您使用上述代码创建表，但默认不会抛出异常。
如果我们想要关于外键的异常“反馈”，我们需要在连接字符串中打开一个选项：
SQLiteConnectionStringBuilder connectString = new SQLiteConnectionStringBuilder();
connectString.DataSource = "databasefile.sqlite";
connectString.ForeignKeys = true;
SQLiteConnection sqlite_conn = new SQLiteConnection(connectString.ToString());
sqlite_conn.Open();
Dim connectString as SQLiteConnectionStringBuilder
Dim sqlite_conn as SQLiteConnection
connectString = new SQLiteConnectionStringBuilder()
connectString.DataSource = "databasefile.sqlite"
connectString.ForeignKeys = true
sqlite_conn = new SQLiteConnection(connectString.ToString())
sqlite_conn.Open()
本节中的上述代码片段设置并强制执行外键的使用。如果客户端应用程序尝试在 intemtypeid 列中插入值，并且该值无法在 itemtype 表的 id 列中引用，则数据库引擎将抛出异常（“FOREIGN KEY constraint failed”）。
将 (Pojo) 模型类与 SQLite 一起使用
DataRaeder 部分显示 SQLite 可以直接将其数据转换为类型安全 .Net 对象。反之亦然——将类型安全的 .Net 数据对象写入数据库——SQLite 也支持。
List<CategoryModel> values = new List<CategoryModel>();
values.Add(new CategoryModel(1,"ELECTRONICS"));
values.Add(new CategoryModel(2,"TELEVISIONS",1));
values.Add(new CategoryModel(3,"TUBE",2));
string query = "INSERT INTO category ([category_id],[name],[parent])VALUES(@category_id,@name,@parent)";
using (SQLiteCommand cmd = new SQLiteCommand(query, DB.Connection))
{
int result = 0;
using (var transaction = cmd.Connection.BeginTransaction())
{
foreach (var item in values)
{
cmd.Parameters.AddWithValue("@category_id", item.ID);
cmd.Parameters.AddWithValue("@name", item.Name);
cmd.Parameters.AddWithValue("@parent", item.Parent);
result += cmd.ExecuteNonQuery();
}
transaction.Commit();
}
}
Dim values As List(Of CategoryModel) = New List(Of CategoryModel)()
values.Add(new CategoryModel(1,"ELECTRONICS"))
values.Add(new CategoryModel(2,"TELEVISIONS",1))
values.Add(new CategoryModel(3,"TUBE",2))
Dim query as String
query = "INSERT INTO category ([category_id],[name],[parent])VALUES(@category_id,@name,@parent)"
Using cmd as SQLiteCommand = new SQLiteCommand(query, DB.Connection)
Dim result as Integer
result = 0
Using transaction = cmd.Connection.BeginTransaction()
For Each item In values
cmd.Parameters.AddWithValue("@category_id", item.ID)
cmd.Parameters.AddWithValue("@name", item.Name)
cmd.Parameters.AddWithValue("@parent", item.Parent)
result += cmd.ExecuteNonQuery()
Next
transaction.Commit()
End Using
End Using
上述语句创建了一个 CategoryModel 对象列表，并将该列表的内容写入名为 category 的数据库表中。
SQLite 系统默认对每个更改的记录（insert、update、delete）实现一次隐式事务开始和事务提交。如果您需要一次更改 100 条或更多记录，这种行为可能会显著减慢速度。这里可以进行的一个简单优化是增加一个涉及多条记录的事务。
上述示例代码实现了 BeginTransaction 和 transaction.Commit() 语句来更改默认事务行为。现在一个事务可以包含多条记录的更改，并确保多条记录的插入更有效率。
- 高效插入/更新记录
using (SQLiteCommand cmd = new SQLiteCommand(query, DB.Connection))
{
using (var transaction = cmd.Connection.BeginTransaction())
{
...
transaction.Commit();
}
}
Using cmd as SQLiteCommand = new SQLiteCommand(query, DB.Connection)
Using transaction = cmd.Connection.BeginTransaction()
...
transaction.Commit()
End Using
End Using
将关系数据转换为 XML
本节中的示例应用程序描述了一个可用于将 XML 文件写入存储在关系数据库中的数据的解决方案。此应用程序的背景是我想要一些有趣的示例数据来处理关于树结构和其他数据相关主题的文章系列。因此，我获得了一个小型数据转换工具，它可以根据 SQLLite 数据库输出 XML 数据。
SQLite 数据库是从 PL-SQL 语句生成的，这些语句源自一个关于 2012 年世界所有城市、地区和国家的数据项目：lokasyon.sql.gz。我将上面链接的文件转换为一个文件，该文件不包含原始帖子中的 Insert 和 Values 语句部分。从那里，我们使用以下代码将数据读取到 SQLite 数据库中（请参阅 ViewModels/AppViewModel.cs）。
string createQuery =
@"CREATE TABLE IF NOT EXISTS
[meta_location] (
[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[iso] VARCHAR(50) DEFAULT NULL,
[local_name] varchar(255) DEFAULT NULL,
[type] char(2) DEFAULT NULL,
[in_location] unsigned int DEFAULT NULL,
[geo_lat] double(18,11) DEFAULT NULL,
[geo_lng] double(18,11) DEFAULT NULL,
[db_id] varchar(50) DEFAULT NULL)";
using (SQLiteCommand cmd = new SQLiteCommand(db.Connection))
{
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
}
var cmdDeleteTable = new SQLiteCommand("delete from meta_location", db.Connection);
cmdDeleteTable.ExecuteNonQuery();
var lines = System.IO.File.ReadLines(@".\Resources\lokasyon.txt");
query = "INSERT INTO meta_location "
+ "([id], [iso], [local_name], [type], [in_location], [geo_lat], [geo_lng], [db_id])VALUES";
using (var transaction = db.Connection.BeginTransaction())
{
foreach (var line in lines) // Write data out to database
{
if (line.Trim().Length > 0) // Ignore empty lines
{
// Get rid of comma at the end of the line
var valueLine = line.Replace("),", ")");
// Adjust single quote escape from \' to SQLite ''
valueLine = valueLine.Replace("\\'", "''");
valueLine = valueLine.Replace("\\r", ""); // Get ride of these
valueLine = valueLine.Replace("\\n", "");
queryString = query + valueLine;
var cmdInsert = new SQLiteCommand(queryString, db.Connection);
cmdInsert.ExecuteNonQuery();
iLines++;
}
}
transaction.Commit();
}
Dim createQuery as String
Dim query as String
createQuery =
@"CREATE TABLE IF NOT EXISTS
[meta_location] (
[id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[iso] VARCHAR(50) DEFAULT NULL,
[local_name] varchar(255) DEFAULT NULL,
[type] char(2) DEFAULT NULL,
[in_location] unsigned int DEFAULT NULL,
[geo_lat] double(18,11) DEFAULT NULL,
[geo_lng] double(18,11) DEFAULT NULL,
[db_id] varchar(50) DEFAULT NULL)"
Using cmd as SQLiteCommand = new SQLiteCommand(db.Connection)
cmd.CommandText = createQuery
cmd.ExecuteNonQuery()
End Using
cmdDeleteTable = new SQLiteCommand("delete from meta_location", db.Connection)
cmdDeleteTable.ExecuteNonQuery()
lines = System.IO.File.ReadLines(@".\Resources\lokasyon.txt")
query = "INSERT INTO meta_location "
+ "([id], [iso], [local_name], [type], [in_location], [geo_lat], [geo_lng], [db_id])VALUES"
Using transaction = db.Connection.BeginTransaction()
For Each line in lines ' Write data out to database
if line.Trim().Length > 0 Then ' Ignore empty lines
' Get rid of comma at the end of the line
valueLine = line.Replace("),", ")")
' Adjust single quote escape from \' to SQLite ''
valueLine = valueLine.Replace("\\'", "''")
valueLine = valueLine.Replace("\\r", "") ' Get ride of these
valueLine = valueLine.Replace("\\n", "")
queryString = query + valueLine
cmdInsert = new SQLiteCommand(queryString, db.Connection)
cmdInsert.ExecuteNonQuery()
iLines = iLines + 1
End If
Next
transaction.Commit()
End Using
上面的代码片段逐行将输入文件读取到 lines 字符串集合中。然后处理 lines 集合以生成一个 query 字符串，该字符串执行 SQLite Insert 语句，该语句随后在上面显示的最后一个 foreach 循环中逐行执行。
为所有项目（城市、地区和国家）生成一个文件不是一个明智的选择，因为生成的文件会太大，而且并非每个项目都可能需要所有数据项。因此，每个数据级别（城市、地区和国家）都写入一个单独的 cs 文本文件，然后我将其包含在项目中的：Models/Generate*.cs。
这是从 SQLite 数据库生成的 C# 示例代码：
public static List<MetaLocationModel> Countries(List<MetaLocationModel> c)
{
...
c.Add(new MetaLocationModel(78, "GE", "Georgia", LocationType.Country, -1, 42, 43.5, "GE"));
c.Add(new MetaLocationModel(56, "DE", "Germany", LocationType.Country, -1, 51, 9, "DE"));
c.Add(new MetaLocationModel(81, "GH", "Ghana", LocationType.Country, -1, 8, -2, "GH"));
...
return c;
}
Public Shared Function Countries(ByRef par_countries As List(Of MetaLocationModel)) As List(Of MetaLocationModel)
...
par_countries.Add(New MetaLocationModel(78, "GE", "Georgia", LocationType.Country, -1, 42, 43.5, "GE"))
par_countries.Add(New MetaLocationModel(56, "DE", "Germany", LocationType.Country, -1, 51, 9, "DE"))
par_countries.Add(New MetaLocationModel(81, "GH", "Ghana", LocationType.Country, -1, 8, -2, "GH"))
...
Return par_regions
End Function
C# 代码需要一个名为 MetaLocationModel 的 PoJo 类。然后，MetaLocationModel 类和上面的集合可用于生成 XML 输出文件。
private void WriteCityXmlModels(string filename)
{
var items = new List<MetaLocationModel>();
items = GenerateCity.Items(items);
using (StreamWriter sw = new StreamWriter(filename))
{
using (TextWriter writer = TextWriter.Synchronized(sw))
{
new XmlSerializer(typeof(List<MetaLocationModel>)).Serialize(writer, items);
}
}
}
Private Sub WriteCityXmlModels(ByVal filename As String)
Dim items = New List(Of MetaLocationModel)()
items = GenerateCity.Items(items)
Using sw As StreamWriter = New StreamWriter(filename)
Using writer As TextWriter = TextWriter.Synchronized(sw)
Dim serialization As XmlSerializer = New XmlSerializer(GetType(List(Of MetaLocationModel)))
serialization.Serialize(writer, items)
End Using
End Using
End Sub
每个生成的 XML 文件有一个方法，最终有 3 个 XML 文件：
- bin\<Debug 或 Release>\Resources\countries.xml
- bin\<Debug 或 Release>\Resources\regions.xml
- bin\<Debug 或 Release>\Resources\cities.xml
上述数据模型简要概述了基于 iso 列的结构。iso 可以用作连接元素，因为国家的 iso 是地区 iso 的子字符串，而地区的 iso 是城市 iso 的子字符串。
结论
使用 SQLite 处理大型数据集（超过 100,000 条记录）非常简单、可靠且灵活，前提是您对关系数据库有基础的了解，而且这种嵌入式数据库系统也推荐给不擅长数据库的人使用，因为它的应用并不复杂，而且多掌握一项技术栈总是有益的。
文章结尾处的转换应用程序对于未来即将进行的数据中心评估来说是一个真正的加分项和良好的未来投资。如果您知道比这个 2012 年的数据更准确的城市、地区和国家数据项的免费数据源，请告诉我。
上面介绍的要点清楚地表明，如果项目预算有限且数据结构庞大，无法使用 XML 等标准技术以如此灵活高效的方式进行存储和检索，那么自定义（专有）文件格式可能会浪费时间。
SQLite 生态系统非常庞大，并且也提供 Java 或 Objectiv-C（在苹果电脑上）版本。结合现有的性能和稳定性，这是比不使用该系统更频繁使用它的（众多）原因之一。
参考文献
- SQLite 参考资料
- CodeProject - SQLite 助手 (C#)
- 世界城市数据库
StackOverflow 提示
lokasyon.sql.gz
历史
- 2017 年 11 月 1 日 更新了数据库包装类，以包含 Pragma Journal Mode（枚举）选项，并调整了 02_SQLite_tut.zp 以允许用户尝试此选项（在创建新数据库文件或重用现有数据库文件时），并在先决条件部分包含了额外的下载选项。
- 2017 年 11 月 28 日 调整了 02、03、04 中的数据库包装器，并添加了相应的章节。
- 2017 年 12 月 5 日 添加了 VB.Net 源代码下载。