1. 前言
在开发应用程序时，数据库操作是一个不可避免的环节。SQLite 是一个轻量级的关系数据库，它广泛应用于桌面应用程序和移动应用程序中。为了高效、规范地处理 SQLite 数据库的操作，我们需要一个统一的工具来管理这些操作。本文将介绍一个名为 SqliteHelper 的类，它能帮助我们简化 SQLite 数据库的操作，同时支持数据库的版本控制和迁移。
2. SQLiteHelper 类概述
SQLiteHelper 是一个用于操作 SQLite 数据库的 C# 类。它封装了对 SQLite 数据库的基本操作，包括执行查询、插入、更新和删除，同时提供了数据库版本控制和迁移功能。以下是 SQLiteHelper 类的主要功能：
- 统一的数据库操作接口：提供了简化的 API 来执行数据库操作。
- 数据库版本控制：通过维护一个
SchemaVersion表来跟踪数据库的版本，并执行必要的迁移。 - 自动迁移：根据当前版本执行数据库结构的升级，以适应应用程序的发展。
3. 实现细节
1. 添加sqlite依赖库
在dotnet 8.0中添加sqlite依赖库，主要包括 Microsoft.Data.Sqlite.Core 和 SQLitePCLRaw.bundle_e_sqlite3
<ItemGroup>
<PackageReference Include="Microsoft.Data.Sqlite.Core" Version="8.0.8" />
<PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.6" />
<PackageReference Include="SQLitePCLRaw.core" Version="2.1.6" />
<PackageReference Include="SQLitePCLRaw.lib.e_sqlite3" Version="2.1.6" />
<PackageReference Include="SQLitePCLRaw.provider.e_sqlite3" Version="2.1.6" />
<PackageReference Include="System.Memory" Version="4.5.3" />
</ItemGroup>
2. 数据库初始化
在 SQLiteHelper 的构造函数中，我们会初始化数据库并创建 SchemaVersion 表，如果它还不存在。这个表用于记录数据库的版本信息，这个版本信息决定了我们后续对数据库进行迁移的主要设计，所以这里非常重要：
using Microsoft.Data.Sqlite;
using SQLitePCL;
private void InitializeDatabase()
{
Batteries.Init();
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
var createVersionTableQuery = @"
CREATE TABLE IF NOT EXISTS SchemaVersion (
Version INTEGER PRIMARY KEY
);";
using (var command = new SQLiteCommand(createVersionTableQuery, connection))
{
command.ExecuteNonQuery();
}
// Perform database migrations if needed
int currentVersion = GetCurrentSchemaVersion(connection);
if (currentVersion < 1) MigrateToVersion1(connection);
//if (currentVersion < 2) MigrateToVersion2(connection);
//if (currentVersion < 3) MigrateToVersion3(connection);
}
}
private int GetCurrentSchemaVersion(SQLiteConnection connection)
{
var query = "SELECT Version FROM SchemaVersion ORDER BY Version DESC LIMIT 1;";
using (var command = new SQLiteCommand(query, connection))
{
var result = command.ExecuteScalar();
return result != null ? Convert.ToInt32(result) : 0;
}
}
private void SetSchemaVersion(SQLiteConnection connection, int version)
{
var query = "INSERT OR REPLACE INTO SchemaVersion (Version) VALUES (@Version);";
using (var command = new SQLiteCommand(query, connection))
{
command.Parameters.AddWithValue("@Version", version);
command.ExecuteNonQuery();
}
}
3. 版本控制和迁移
我们通过 GetCurrentSchemaVersion 方法获取当前数据库版本，并根据版本号调用相应的迁移方法。迁移方法负责执行数据库结构的更新。例如，以下代码展示了如何在版本 1 中创建一个用户表，在版本 2 中添加一个新列：
private void MigrateToVersion1(SQLiteConnection connection)
{
var createTableQuery = @"
CREATE TABLE IF NOT EXISTS Users (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
Age INTEGER NOT NULL
);";
using (var command = new SQLiteCommand(createTableQuery, connection))
{
command.ExecuteNonQuery();
}
SetSchemaVersion(connection, 1);
}
private void MigrateToVersion2(SQLiteConnection connection)
{
var alterTableQuery = "ALTER TABLE Users ADD COLUMN Email TEXT;";
using (var command = new SQLiteCommand(alterTableQuery, connection))
{
command.ExecuteNonQuery();
}
SetSchemaVersion(connection, 2);
}
private void MigrateToVersion3(SqliteConnection connection)
{
var alterTableQuery = "ALTER TABLE Users ADD COLUMN CreateTime INTEGER;";
using (var command = new SqliteCommand(alterTableQuery, connection))
{
command.ExecuteNonQuery();
}
SetSchemaVersion(connection, 3);
}
4. 数据库操作接口
SQLiteHelper 提供了几个方法来简化数据库操作，包括执行非查询语句、读取数据和执行标量查询：
public int ExecuteNonQuery(string query, params SqliteParameter[] parameters)
{
using (var command = new SqliteCommand(query, _connection))
{
command.Parameters.AddRange(parameters);
return command.ExecuteNonQuery();
}
}
public void ExecuteReader(string query, Action<SqliteDataReader> readerAction, params SqliteParameter[] parameters)
{
using (var command = new SqliteCommand(query, _connection))
{
command.Parameters.AddRange(parameters);
using (var reader = command.ExecuteReader())
{
readerAction(reader);
}
}
}
public object ExecuteScalar(string query, params SqliteParameter[] parameters)
{
using (var command = new SqliteCommand(query, _connection))
{
command.Parameters.AddRange(parameters);
return command.ExecuteScalar();
}
}
5. 打开、关闭和释放链接
using Microsoft.Data.Sqlite;
using SQLitePCL;
namespace ConsoleApp
{
public class SqliteHelper : IDisposable
{
private SqliteConnection _connection;
private readonly string _connectionString;
public SqliteHelper(string databaseFilePath)
{
_connectionString = $"Data Source={databaseFilePath};";
InitializeDatabase();
}
// 前面分别讲解的内容。。。
public void OpenConnection()
{
if (_connection == null)
{
_connection = new SqliteConnection(_connectionString);
}
if (_connection.State != System.Data.ConnectionState.Open)
{
_connection.Open();
}
}
public void CloseConnection()
{
if (_connection != null && _connection.State == System.Data.ConnectionState.Open)
{
_connection.Close();
}
}
public void Dispose()
{
CloseConnection();
_connection?.Dispose();
}
}
}
4. 使用示例
我们新增一个.Net 8.0控制台项目，添加一个SqliteDataService 类封装对sqliteHelper的调用。
using Microsoft.Data.Sqlite;
namespace ConsoleApp
{
internal class SqliteDataService: IDisposable
{
private readonly SqliteHelper _sqliteHelper;
public SqliteDataService(string dbfile)
{
_sqliteHelper = new SqliteHelper(dbfile);
_sqliteHelper.OpenConnection();
}
public void Dispose()
{
_sqliteHelper?.Dispose();
}
}
1. 第一次CRUD操作
现在，我们进行第一次CRUD操作，当前，我们的数据库中版本为0,我们需要执行第一次Migrate操作：
private void MigrateToVersion1(SqliteConnection connection)
{
var createTableQuery = @"
CREATE TABLE IF NOT EXISTS Users (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT NOT NULL,
Age INTEGER NOT NULL
);";
using (var command = new SqliteCommand(createTableQuery, connection))
{
command.ExecuteNonQuery();
}
SetSchemaVersion(connection, 1);
}
在SqliteDataService中编写CRUD逻辑：
public void Insert()
{
string insertQuery = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age);";
var id = _sqliteHelper.ExecuteNonQuery(insertQuery, new SqliteParameter("@Name", "张三"), new SqliteParameter("@Age", 25));
Console.WriteLine($"Insert ROWS: {id}");
}
public void Query1()
{
string selectQuery = "SELECT Id, Name, Age FROM Users;";
_sqliteHelper.ExecuteReader(selectQuery, (reader) =>
{
while (reader.Read())
{
Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]}");
}
});
}
public void Update()
{
string updateQuery = "UPDATE Users SET Age = @Age WHERE Name = @Name;";
var id = _sqliteHelper.ExecuteNonQuery(updateQuery, new SqliteParameter("@Name", "张三"), new SqliteParameter("@Age", 26));
Console.WriteLine($"UPDATE ROWS: {id}");
}
public void Delete()
{
string deleteQuery = "DELETE FROM Users WHERE Name = @Name;";
var id = _sqliteHelper.ExecuteNonQuery(deleteQuery, new SqliteParameter("@Name", "张三"));
Console.WriteLine($"DELETE ROWS: {id}");
}
在Program中实例化：
SqliteDataService sqliteData = new SqliteDataService("test.sqlite");
sqliteData.Insert();
sqliteData.Query1();
sqliteData.Update();
sqliteData.Query1();
sqliteData.Delete();
sqliteData.Query1();
sqliteData.Dispose();
Console.WriteLine("Hello, World!");
执行查看：
2. 第二次CRUD操作
现在，我们进行第二次CRUD操作，当前，我们的数据库中版本为1,我们需要执行一次 Migrate操作：在InitializeDatabase中添加代码
if (currentVersion < 2) MigrateToVersion2(connection);
同时添加迁移的内容，本次，我们就改变表结构，增加一个字段Email，同时改变版本号
private void MigrateToVersion2(SqliteConnection connection)
{
var alterTableQuery = "ALTER TABLE Users ADD COLUMN Email TEXT;";
using (var command = new SqliteCommand(alterTableQuery, connection))
{
command.ExecuteNonQuery();
}
SetSchemaVersion(connection, 2);
}
在SqliteDataService中编写CRUD逻辑：
public void Insert2()
{
string insertQuery = "INSERT INTO Users (Name, Age,Email) VALUES (@Name, @Age,@Email);";
var id = _sqliteHelper.ExecuteNonQuery(insertQuery,
new SqliteParameter("@Name", "张三2"),
new SqliteParameter("@Age", 29),
new SqliteParameter("@Email", "张三2@qq.com")
);
Console.WriteLine($"Insert ROWS: {id}");
}
public void Query2()
{
string selectQuery = "SELECT Id, Name, Age,Email FROM Users;";
_sqliteHelper.ExecuteReader(selectQuery, (reader) =>
{
while (reader.Read())
{
Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]},Email: {reader["Email"]}");
}
});
}
public void Update2()
{
string updateQuery = "UPDATE Users SET Age = @Age WHERE Name = @Name;";
var id = _sqliteHelper.ExecuteNonQuery(updateQuery, new SqliteParameter("@Name", "张三2"), new SqliteParameter("@Age", 30));
Console.WriteLine($"UPDATE ROWS: {id}");
}
//删除一个不存在的数据
public void Delete2()
{
string deleteQuery = "DELETE FROM Users WHERE Name = @Name;";
var id = _sqliteHelper.ExecuteNonQuery(deleteQuery, new SqliteParameter("@Name", "张三"));
Console.WriteLine($"DELETE ROWS: {id}");
}
在Program中实例化：
SqliteDataService sqliteData = new SqliteDataService("test.sqlite");
sqliteData.Insert2();
sqliteData.Query2();
sqliteData.Update2();
sqliteData.Query2();
sqliteData.Delete2();
sqliteData.Query2();
sqliteData.Dispose();
Console.WriteLine("Hello, World!");
执行结果可知
我们对比两次的结果，发现，Users表结构已成功的被改变
3. 第三次CRUD操作
由于看不出数据的变化，我们现在进行第三次数据库迁移。.当前，我们的数据库中版本为2
,我们需要执行一次 Migrate操作：在InitializeDatabase中添加代码
if (currentVersion < 3) MigrateToVersion3(connection);
同时添加迁移的内容，本次，我们就改变表结构，增加一个字段CreateTime，同时改变版本号为3
private void MigrateToVersion3(SqliteConnection connection)
{
var alterTableQuery = "ALTER TABLE Users ADD COLUMN CreateTime INTEGER;";
using (var command = new SqliteCommand(alterTableQuery, connection))
{
command.ExecuteNonQuery();
}
SetSchemaVersion(connection, 3);
}
在SqliteDataService中编写CRUD逻辑：
public void Insert3()
{
string insertQuery = "INSERT INTO Users (Name, Age,Email,CreateTime) VALUES (@Name, @Age,@Email,@CreateTime);";
var id = _sqliteHelper.ExecuteNonQuery(insertQuery,
new SqliteParameter("@Name", "张三"),
new SqliteParameter("@Age", 28),
new SqliteParameter("@Email", "张三@qq.com"),
new SqliteParameter("@CreateTime", DateTime.Now.ToFileTime())
);
Console.WriteLine($"Insert ROWS: {id}");
}
public void Query3()
{
string selectQuery = "SELECT Id, Name, Age,Email,CreateTime FROM Users;";
_sqliteHelper.ExecuteReader(selectQuery, (reader) =>
{
while (reader.Read())
{
if(reader["CreateTime"] != null && long.TryParse(reader["CreateTime"].ToString(), out long result))
{
Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]},Email: {reader["Email"]},CreateTime: {DateTime.FromFileTime(result)}");
}
else
{
Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}, Age: {reader["Age"]},Email: {reader["Email"]},CreateTime: {reader["CreateTime"]}");
}
}
});
}
public void Update3()
{
string updateQuery = "UPDATE Users SET Age = @Age WHERE Name = @Name;";
var id = _sqliteHelper.ExecuteNonQuery(updateQuery, new SqliteParameter("@Name", "张三3"), new SqliteParameter("@Age", 80));
Console.WriteLine($"UPDATE ROWS: {id}");
}
//删除一个不存在的数据
public void Delete3()
{
string deleteQuery = "DELETE FROM Users WHERE Name = @Name;";
var id = _sqliteHelper.ExecuteNonQuery(deleteQuery, new SqliteParameter("@Name", "张三"));
Console.WriteLine($"DELETE ROWS: {id}");
}
在Program中实例化：
SqliteDataService sqliteData = new SqliteDataService("test.sqlite");
sqliteData.Insert3();
sqliteData.Query3();
sqliteData.Update3();
sqliteData.Query3();
sqliteData.Delete3();
sqliteData.Query3();
sqliteData.Dispose();
Console.WriteLine("Hello, World!");
执行结果可知
我们对比两次的结果，发现，Users表结构已成功的被改变
数据库记录的版本也有了更新
5. 总结
SQLiteHelper 类通过封装常见的数据库操作和支持版本控制，使得处理 SQLite 数据库变得更加简洁和高效。它不仅提供了统一的操作接口，还自动处理了数据库的迁移问题，这对于不断变化的应用程序结构来说是非常重要的。无论是在桌面应用还是移动应用中，SQLiteHelper 都能帮助你简化数据库管理工作，提高开发效率。
6915
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
