SQLite C# 入门与实践：从基础到最佳实践

简介

在当今的数据驱动世界中，数据存储和管理对于各种应用程序至关重要。SQLite 作为一款轻量级、无服务器的关系型数据库，因其简单易用、占用资源少等特点，在许多场景中被广泛应用，尤其是在移动应用、桌面应用以及小型项目中。C# 作为一种强大的编程语言，提供了丰富的库和工具来与 SQLite 进行交互。本文将深入探讨 SQLite C# 的基础概念、使用方法、常见实践以及最佳实践，帮助读者全面掌握并高效运用这一技术组合。

目录

  1. 基础概念
    • SQLite 简介
    • C# 与 SQLite 的交互方式
  2. 使用方法
    • 安装 SQLite 相关库
    • 连接到 SQLite 数据库
    • 创建表
    • 插入数据
    • 查询数据
    • 更新数据
    • 删除数据
  3. 常见实践
    • 数据访问层的设计
    • 事务处理
    • 数据加密
  4. 最佳实践
    • 性能优化
    • 错误处理与日志记录
    • 数据库版本管理
  5. 小结
  6. 参考资料

基础概念

SQLite 简介

SQLite 是一个开源的嵌入式关系型数据库管理系统。它将整个数据库存储在一个单一的文件中，支持标准的 SQL 查询语言。SQLite 无需安装额外的服务器进程，非常适合在资源受限的环境中使用，例如移动设备、小型桌面应用等。它具有以下特点：

  • 轻量级：体积小，运行时占用资源少。
  • 自包含：无需外部依赖，数据库存储在单个文件中。
  • 跨平台：支持多种操作系统，包括 Windows、Linux 和 macOS。

C# 与 SQLite 的交互方式

在 C# 中，有多种方式可以与 SQLite 进行交互。最常用的是使用 System.Data.SQLite 库，它提供了一组类和方法来连接到 SQLite 数据库、执行 SQL 语句以及管理数据。此外，Entity Framework Core 也支持 SQLite，提供了更高级的对象关系映射（ORM）功能，使得开发人员可以使用面向对象的方式来操作数据库，而无需编写大量的 SQL 语句。

使用方法

安装 SQLite 相关库

在使用 SQLite 与 C# 之前，需要安装相关的库。如果使用 Visual Studio，可以通过 NuGet 包管理器来安装 System.Data.SQLite 库。具体步骤如下：

  1. 打开 Visual Studio 项目。
  2. 右键点击项目，选择“管理 NuGet 包”。
  3. 在 NuGet 包管理器中，搜索“System.Data.SQLite”，选择合适的版本并点击“安装”。

连接到 SQLite 数据库

连接到 SQLite 数据库是使用 SQLite 的第一步。以下是一个简单的示例代码：

using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=myDatabase.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Connected to SQLite database successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error connecting to database: {ex.Message}");
            }
        }
    }
}

在上述代码中，我们创建了一个 SQLiteConnection 对象，并使用连接字符串指定了数据库文件的路径。然后调用 Open 方法打开数据库连接。

创建表

创建表可以通过执行 SQL 的 CREATE TABLE 语句来实现。以下是一个示例：

using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=myDatabase.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string createTableQuery = @"
                CREATE TABLE IF NOT EXISTS Employees (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name TEXT NOT NULL,
                    Age INTEGER,
                    Salary REAL
                )
            ";
            using (SQLiteCommand command = new SQLiteCommand(createTableQuery, connection))
            {
                try
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine("Table created successfully.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error creating table: {ex.Message}");
                }
            }
        }
    }
}

在上述代码中，我们使用 SQLiteCommand 对象执行了 CREATE TABLE 语句，创建了一个名为 Employees 的表。

插入数据

插入数据可以使用 INSERT INTO 语句。以下是一个示例：

using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=myDatabase.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string insertQuery = @"
                INSERT INTO Employees (Name, Age, Salary)
                VALUES (@Name, @Age, @Salary)
            ";
            using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@Name", "John Doe");
                command.Parameters.AddWithValue("@Age", 30);
                command.Parameters.AddWithValue("@Salary", 5000.0);
                try
                {
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"{rowsAffected} row(s) inserted successfully.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error inserting data: {ex.Message}");
                }
            }
        }
    }
}

在上述代码中，我们使用 SQLiteCommand 对象执行了 INSERT INTO 语句，并通过参数化查询的方式传递了要插入的数据，以防止 SQL 注入攻击。

查询数据

查询数据可以使用 SELECT 语句。以下是一个示例：

using System.Data.SQLite;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=myDatabase.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string selectQuery = "SELECT * FROM Employees";
            using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        int age = reader.GetInt32(2);
                        double salary = reader.GetDouble(3);
                        Console.WriteLine($"Id: {id}, Name: {name}, Age: {age}, Salary: {salary}");
                    }
                }
            }
        }
    }
}

在上述代码中，我们使用 SQLiteCommand 对象执行了 SELECT 语句，并通过 SQLiteDataReader 对象读取查询结果。

更新数据

更新数据可以使用 UPDATE 语句。以下是一个示例：

using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=myDatabase.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string updateQuery = @"
                UPDATE Employees
                SET Salary = @Salary
                WHERE Id = @Id
            ";
            using (SQLiteCommand command = new SQLiteCommand(updateQuery, connection))
            {
                command.Parameters.AddWithValue("@Salary", 5500.0);
                command.Parameters.AddWithValue("@Id", 1);
                try
                {
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"{rowsAffected} row(s) updated successfully.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error updating data: {ex.Message}");
                }
            }
        }
    }
}

在上述代码中，我们使用 SQLiteCommand 对象执行了 UPDATE 语句，更新了 Employees 表中 Id 为 1 的记录的 Salary 字段。

删除数据

删除数据可以使用 DELETE FROM 语句。以下是一个示例：

using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=myDatabase.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string deleteQuery = @"
                DELETE FROM Employees
                WHERE Id = @Id
            ";
            using (SQLiteCommand command = new SQLiteCommand(deleteQuery, connection))
            {
                command.Parameters.AddWithValue("@Id", 1);
                try
                {
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"{rowsAffected} row(s) deleted successfully.");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error deleting data: {ex.Message}");
                }
            }
        }
    }
}

在上述代码中，我们使用 SQLiteCommand 对象执行了 DELETE FROM 语句，删除了 Employees 表中 Id 为 1 的记录。

常见实践

数据访问层的设计

为了提高代码的可维护性和可测试性，通常会将数据库访问逻辑封装在一个独立的数据访问层（DAL）中。DAL 提供了一组方法来执行各种数据库操作，例如插入、查询、更新和删除。以下是一个简单的数据访问层示例：

using System.Data.SQLite;
using System;

class EmployeeDAL
{
    private string connectionString;

    public EmployeeDAL(string connectionString)
    {
        this.connectionString = connectionString;
    }

    public void InsertEmployee(string name, int age, double salary)
    {
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            string insertQuery = @"
                INSERT INTO Employees (Name, Age, Salary)
                VALUES (@Name, @Age, @Salary)
            ";
            using (SQLiteCommand command = new SQLiteCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@Name", name);
                command.Parameters.AddWithValue("@Age", age);
                command.Parameters.AddWithValue("@Salary", salary);
                command.ExecuteNonQuery();
            }
        }
    }

    // 其他数据库操作方法，如查询、更新和删除
}

在上述代码中，EmployeeDAL 类封装了插入员工数据的逻辑。通过这种方式，业务逻辑层可以依赖于 DAL 来进行数据库操作，而无需关心底层的数据库连接和 SQL 语句。

事务处理

事务是一组不可分割的数据库操作序列，要么全部执行成功，要么全部失败。在 SQLite 中，可以使用 BeginTransaction、Commit 和 Rollback 方法来实现事务处理。以下是一个示例：

using System.Data.SQLite;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=myDatabase.db;Version=3;";
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            connection.Open();
            SQLiteTransaction transaction = connection.BeginTransaction();
            try
            {
                string insertQuery1 = @"
                    INSERT INTO Employees (Name, Age, Salary)
                    VALUES ('Alice', 25, 4500.0)
                ";
                string insertQuery2 = @"
                    INSERT INTO Employees (Name, Age, Salary)
                    VALUES ('Bob', 28, 4800.0)
                ";
                using (SQLiteCommand command1 = new SQLiteCommand(insertQuery1, connection))
                {
                    command1.ExecuteNonQuery();
                }
                using (SQLiteCommand command2 = new SQLiteCommand(insertQuery2, connection))
                {
                    command2.ExecuteNonQuery();
                }
                transaction.Commit();
                Console.WriteLine("Transactions committed successfully.");
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                Console.WriteLine($"Error occurred, transactions rolled back: {ex.Message}");
            }
        }
    }
}

在上述代码中，我们使用 BeginTransaction 方法开始一个事务，然后执行两个插入操作。如果所有操作都成功，调用 Commit 方法提交事务；如果发生异常，调用 Rollback 方法回滚事务。

数据加密

在某些场景下，需要对 SQLite 数据库中的数据进行加密，以保护敏感信息。SQLite 本身支持加密扩展，例如 SQLite Encryption Extension（SEE）。可以通过以下步骤实现数据加密：

  1. 下载并安装 SQLite Encryption Extension 库。
  2. 在连接字符串中指定加密密钥。例如：
string connectionString = "Data Source=myDatabase.db;Version=3;Password=mySecretPassword;";
  1. 使用支持加密的 SQLite 驱动程序进行连接和操作。

最佳实践

性能优化

  • 使用参数化查询：避免直接拼接 SQL 语句，使用参数化查询可以防止 SQL 注入攻击，并且有助于数据库引擎进行查询优化。
  • 批量操作：如果需要插入或更新大量数据，考虑使用批量操作，例如 SQLiteBulkCopy 类（在某些版本的 System.Data.SQLite 库中支持），可以显著提高性能。
  • 索引优化：为经常查询的列创建索引，可以加快查询速度。但要注意索引过多会增加数据库的存储空间和维护成本。

错误处理与日志记录

  • 详细的错误处理：在执行数据库操作时，捕获并处理可能出现的异常。记录异常信息，包括异常类型、消息和堆栈跟踪，以便于调试和排查问题。
  • 日志记录：使用日志框架（如 NLog 或 Serilog）记录数据库操作的关键信息，例如连接时间、查询执行时间、影响的行数等。这有助于监控数据库的使用情况和性能。

数据库版本管理

  • 使用数据库迁移工具：如 Flyway 或 EF Core Migrations，用于管理数据库的版本和模式变更。这些工具可以帮助开发人员在不同环境中保持数据库的一致性，并记录数据库的变更历史。

小结

本文全面介绍了 SQLite C# 的基础概念、使用方法、常见实践以及最佳实践。通过学习这些内容，读者可以掌握如何在 C# 应用程序中使用 SQLite 进行数据存储和管理，并且能够运用最佳实践来优化性能、提高代码质量和确保数据的安全性。希望本文能帮助读者在实际项目中更加高效地使用 SQLite C# 技术组合。

参考资料