C# 开发 SQLite 插件

C# 开发 SQLite 插件

使用 System.Data.SQLite 库

System.Data.SQLite 是一个开源的 ADO.NET 提供程序，支持 SQLite 数据库操作。

安装库：

Install-Package System.Data.SQLite

基本操作示例：

using System.Data.SQLite;

// 创建或连接数据库
SQLiteConnection connection = new SQLiteConnection("Data Source=database.db;Version=3;");
connection.Open();

// 创建表
string createTableQuery = "CREATE TABLE IF NOT EXISTS Users (Id INTEGER PRIMARY KEY, Name TEXT)";
SQLiteCommand createTableCommand = new SQLiteCommand(createTableQuery, connection);
createTableCommand.ExecuteNonQuery();

// 插入数据
string insertQuery = "INSERT INTO Users (Name) VALUES (@Name)";
SQLiteCommand insertCommand = new SQLiteCommand(insertQuery, connection);
insertCommand.Parameters.AddWithValue("@Name", "John Doe");
insertCommand.ExecuteNonQuery();

// 查询数据
string selectQuery = "SELECT * FROM Users";
SQLiteCommand selectCommand = new SQLiteCommand(selectQuery, connection);
SQLiteDataReader reader = selectCommand.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Name"]}");
}

connection.Close();

使用 Entity Framework Core

Entity Framework Core 支持 SQLite，提供更高级的 ORM 功能。

安装库：

Install-Package Microsoft.EntityFrameworkCore.Sqlite

定义模型和上下文：

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=database.db");
    }
}

基本操作示例：

using (var context = new AppDbContext())
{
    // 添加数据
    context.Users.Add(new User { Name = "Jane Doe" });
    context.SaveChanges();

    // 查询数据
    var users = context.Users.ToList();
    foreach (var user in users)
    {
        Console.WriteLine($"ID: {user.Id}, Name: {user.Name}");
    }
}

插件开发建议

封装 SQLite 操作为独立类库，便于重用和分发。示例插件结构：

public class SQLitePlugin
{
    private readonly string _connectionString;

    public SQLitePlugin(string databasePath)
    {
        _connectionString = $"Data Source={databasePath};Version=3;";
    }

    public void ExecuteNonQuery(string query, Dictionary<string, object> parameters = null)
    {
        using (var connection = new SQLiteConnection(_connectionString))
        {
            connection.Open();
            var command = new SQLiteCommand(query, connection);

            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.AddWithValue(param.Key, param.Value);
                }
            }

            command.ExecuteNonQuery();
        }
    }

    public List<Dictionary<string, object>> ExecuteReader(string query, Dictionary<string, object> parameters = null)
    {
        var results = new List<Dictionary<string, object>>();

        using (var connection = new SQLiteConnection(_connectionString))
        {
            connection.Open();
            var command = new SQLiteCommand(query, connection);

            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    command.Parameters.AddWithValue(param.Key, param.Value);
                }
            }

            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    var row = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row[reader.GetName(i)] = reader.GetValue(i);
                    }
                    results.Add(row);
                }
            }
        }

        return results;
    }
}

性能优化

使用事务处理批量操作：

using (var transaction = connection.BeginTransaction())
{
    try
    {
        // 执行多个命令
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

启用 WAL 模式提高并发性能：

SQLiteConnection connection = new SQLiteConnection("Data Source=database.db;Version=3;Journal Mode=WAL;");

注意事项

SQLite 数据库文件路径应使用绝对路径，避免相对路径导致的文件位置不确定问题。在多线程环境中，每个线程应使用独立的 SQLiteConnection 实例。