C#搭配Dapper玩转SQLite3：从零开始的数据库操作实战（附避坑指南）

C#搭配Dapper玩转SQLite3：从零开始的数据库操作实战（附避坑指南）

在.NET生态中，SQLite以其轻量、零配置和单文件存储的特性，成为了本地数据存储、原型开发和小型应用的首选。而Dapper，作为一款由Stack Overflow团队打造的“微型ORM”，以其接近原生ADO.NET的性能和极简的API，深受追求效率的开发者的喜爱。当C#、Dapper与SQLite三者结合，便能构建出既高效又简洁的数据访问层。然而，看似简单的组合背后，从环境配置、连接管理到性能调优，每一步都可能藏着让新手甚至中级开发者“踩坑”的细节。本文旨在为你铺平这条道路，不仅提供清晰的实战步骤，更会聚焦于那些官方文档未曾详述的“陷阱”与“优化技巧”，让你在项目中游刃有余。

1. 环境搭建与项目初始化

万事开头难，一个正确的开发环境是成功的一半。对于C#项目操作SQLite，第一步往往就充满了选择。

1.1 选择正确的SQLite驱动

这是新手最容易掉进的第一个坑。在NuGet仓库中搜索“SQLite”，你会找到多个包，其中最常见的是 System.Data.SQLite 和 Microsoft.Data.Sqlite。它们背后是不同的维护团队和实现方式。

注意：Dapper本身不依赖任何特定的数据库驱动，它只依赖于 IDbConnection 接口。因此，你的选择决定了底层连接的具体实现。

为了清晰地展示两者的主要区别，我整理了以下对比表格，这能帮助你根据项目情况做出决策：

在本文的实战中，我们将选择 System.Data.SQLite.Core，因为它开箱即用，避免了部署时缺少原生库的麻烦。使用Visual Studio或dotnet CLI安装以下必要的NuGet包：

dotnet add package System.Data.SQLite.Core
dotnet add package Dapper

安装后，请务必在代码文件顶部引用正确的命名空间：

using System.Data.SQLite; // 关键！不是 Microsoft.Data.Sqlite
using Dapper;

1.2 初始化数据库与表结构

SQLite数据库就是一个文件。我们首先需要创建它并定义表结构。这里不依赖任何图形化工具，纯粹用代码完成，以体现可重复性。

创建一个简单的 Student 模型类：

namespace SQLiteDemo.Model
{
    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Major { get; set; }
    }
}

接下来，编写一个数据库初始化工具类。这个类不仅创建数据库文件，还会检查并创建表。这里引入一个最佳实践：将连接字符串集中管理。

using System.Data;
using System.IO;

namespace SQLiteDemo
{
    public static class DatabaseBootstrapper
    {
        // 使用相对路径，增强项目可移植性
        private static readonly string DbFilePath = Path.Combine(Directory.GetCurrentDirectory(), "Data", "school.db");
        public static readonly string ConnectionString = $"Data Source={DbFilePath};Version=3;";

        public static void Initialize()
        {
            // 确保数据目录存在
            var dataDir = Path.GetDirectoryName(DbFilePath);
            if (!Directory.Exists(dataDir))
            {
                Directory.CreateDirectory(dataDir);
            }

            // 如果数据库文件不存在，SQLiteConnection会在Open时自动创建它。
            // 但我们还需要创建表。
            if (!File.Exists(DbFilePath))
            {
                using (var cnn = new SQLiteConnection(ConnectionString))
                {
                    cnn.Open();
                    // 使用参数化查询创建表，避免SQL注入（虽然这里不是用户输入）
                    cnn.Execute(@"
                        CREATE TABLE IF NOT EXISTS Student (
                            Id INTEGER PRIMARY KEY AUTOINCREMENT,
                            Name TEXT NOT NULL,
                            Age INTEGER NOT NULL,
                            Major TEXT
                        )");
                }
                Console.WriteLine($"数据库已创建于：{DbFilePath}");
            }
        }
    }
}

在程序入口（如 Main 方法）首先调用 DatabaseBootstrapper.Initialize()，即可完成基础的准备工作。

2. 使用Dapper进行CRUD核心操作

环境就绪后，我们来深入Dapper的核心：执行查询和命令。Dapper的魅力在于，它将繁琐的数据读取和对象映射简化成了几个扩展方法。

2.1 查询操作：从简单到复杂

基础查询：使用 Query<T> 方法。这是最常用的方法，它将查询结果映射到强类型对象列表。

public class StudentRepository
{
    private readonly string _connString;

    public StudentRepository(string connString)
    {
        _connString = connString;
    }

    public IEnumerable<Student> GetAllStudents()
    {
        using (IDbConnection cnn = new SQLiteConnection(_connString))
        {
            // Query<T> 直接返回IEnumerable<T>，无需手动映射字段
            return cnn.Query<Student>("SELECT * FROM Student");
        }
    }

    public Student GetStudentById(int id)
    {
        using (var cnn = new SQLiteConnection(_connString))
        {
            // 使用参数化查询，防止SQL注入。Dapper会自动处理参数对象。
            return cnn.QueryFirstOrDefault<Student>(
                "SELECT * FROM Student WHERE Id = @Id",
                new { Id = id } // 匿名对象作为参数
            );
        }
    }
}

多结果集查询：有时一次调用需要返回多个查询结果。Dapper的 QueryMultiple 方法可以优雅地处理。

假设我们想同时获取学生总数和所有学生列表：

public (int totalCount, IEnumerable<Student> students) GetStudentsWithCount()
{
    using (var cnn = new SQLiteConnection(_connString))
    {
        using (var multi = cnn.QueryMultiple(
            "SELECT COUNT(*) FROM Student; SELECT * FROM Student;"))
        {
            var totalCount = multi.ReadSingle<int>();
            var students = multi.Read<Student>();
            return (totalCount, students);
        }
    }
}

2.2 增删改操作：Execute的力量

对于INSERT, UPDATE, DELETE操作，我们使用 Execute 方法，它返回受影响的行数。

public int AddStudent(Student student)
{
    using (var cnn = new SQLiteConnection(_connString))
    {
        // SQLite 使用 `last_insert_rowid()` 获取自增ID。
        // 这里执行插入，并返回影响的行数（通常为1）。
        string sql = @"
            INSERT INTO Student (Name, Age, Major)
            VALUES (@Name, @Age, @Major);";

        return cnn.Execute(sql, student); // 直接将对象作为参数，属性名与SQL参数名匹配
    }
}

public bool UpdateStudent(Student student)
{
    using (var cnn = new SQLiteConnection(_connString))
    {
        string sql = @"
            UPDATE Student
            SET Name = @Name, Age = @Age, Major = @Major
            WHERE Id = @Id";
        int rowsAffected = cnn.Execute(sql, student);
        return rowsAffected > 0; // 返回是否成功更新
    }
}

批量操作：Dapper通过 Execute 方法原生支持批量操作，性能远超在循环中执行单条语句。

public int AddStudentsBatch(IEnumerable<Student> students)
{
    using (var cnn = new SQLiteConnection(_connString))
    {
        string sql = @"INSERT INTO Student (Name, Age, Major) VALUES (@Name, @Age, @Major)";
        // 传入一个对象集合，Dapper会高效地执行批量插入
        return cnn.Execute(sql, students);
    }
}

3. 进阶技巧与性能优化

掌握了基本CRUD后，我们来看看如何让代码更健壮、性能更优。

3.1 连接管理与事务

虽然上面的例子都在方法内使用 using 语句管理连接，这是正确的。但在Web应用（如ASP.NET Core）中，更推荐使用依赖注入（DI）来管理连接的生命周期（Scoped或Transient）。这里我们关注另一个重点：事务。

确保一系列操作要么全部成功，要么全部回滚，事务是关键。Dapper与 IDbTransaction 配合非常简单。

public bool TransferMajor(int fromStudentId, int toStudentId, string newMajor)
{
    using (var cnn = new SQLiteConnection(_connString))
    {
        cnn.Open();
        // 开始一个事务
        using (var transaction = cnn.BeginTransaction())
        {
            try
            {
                string updateSql = "UPDATE Student SET Major = @Major WHERE Id = @Id";
                // 在同一个事务中执行多个操作
                cnn.Execute(updateSql, new { Id = fromStudentId, Major = newMajor }, transaction);
                cnn.Execute(updateSql, new { Id = toStudentId, Major = newMajor }, transaction);

                // 模拟一个可能失败的操作
                // if (someCondition) throw new Exception("模拟失败");

                transaction.Commit(); // 提交事务
                return true;
            }
            catch
            {
                transaction.Rollback(); // 回滚事务，所有更改撤销
                // 记录日志
                return false;
            }
        }
    }
}

3.2 异步操作

在现代应用中，异步编程不可或缺。Dapper全面支持异步方法，后缀为 Async。

public async Task<IEnumerable<Student>> GetAllStudentsAsync()
{
    using (var cnn = new SQLiteConnection(_connString))
    {
        // 使用 QueryAsync 替代 Query
        return await cnn.QueryAsync<Student>("SELECT * FROM Student");
    }
}

public async Task<int> AddStudentAsync(Student student)
{
    using (var cnn = new SQLiteConnection(_connString))
    {
        string sql = @"INSERT INTO Student (Name, Age, Major) VALUES (@Name, @Age, @Major)";
        return await cnn.ExecuteAsync(sql, student);
    }
}

使用异步方法可以避免阻塞调用线程，显著提升应用程序的响应能力和吞吐量，特别是在I/O密集型的数据库操作中。

3.3 查询性能优化建议

• 只查询需要的字段：避免使用 SELECT *，明确列出所需字段。这能减少网络传输和数据映射的开销。
• 合理使用索引：对于 WHERE、ORDER BY、JOIN 中频繁使用的列，在SQLite中创建索引。虽然Dapper不负责这部分，但却是整体性能的基石。

  CREATE INDEX IF NOT EXISTS idx_student_name ON Student (Name);
  

• 利用SQLite的编译语句（Prepared Statement）：Dapper的参数化查询底层会利用这一点，对重复执行的SQL语句进行编译缓存，提升速度。
• 分页查询：对于大量数据，务必使用分页。SQLite使用 LIMIT 和 OFFSET 子句。

  public IEnumerable<Student> GetStudentsPaged(int pageNumber, int pageSize)
  {
      using (var cnn = new SQLiteConnection(_connString))
      {
          string sql = @"SELECT * FROM Student ORDER BY Id LIMIT @PageSize OFFSET @Offset";
          return cnn.Query<Student>(sql, new {
              PageSize = pageSize,
              Offset = (pageNumber - 1) * pageSize
          });
      }
  }
  

4. 实战避坑指南与疑难解答

这一部分汇集了我在多个项目中实际遇到的“坑”，希望你能提前避开。

4.1 连接字符串与文件锁

坑点1：数据库文件被锁定 当多个线程或进程同时访问同一个SQLite数据库文件，并且其中一个执行写操作时，可能会遇到“database is locked”错误。

解决方案：

• 确保写操作被妥善地序列化（例如使用锁或队列）。
• 在连接字符串中尝试添加 Pooling=False;。默认情况下，System.Data.SQLite 可能启用连接池，这在某些并发场景下可能导致问题。
• 对于高并发写场景，SQLite可能不是最佳选择，考虑使用客户端-服务器型数据库。

坑点2：相对路径的陷阱 在开发时使用相对路径（如 "Data Source=test.db"）可能没问题，但当应用程序发布或工作目录改变时，数据库文件可能“消失”。

解决方案：

• 使用绝对路径，或基于应用程序基目录（AppDomain.CurrentDomain.BaseDirectory）构造路径，如我们之前在 DatabaseBootstrapper 类中所做的那样。

4.2 类型映射与空值处理

坑点3：Dapper映射时字段为空（NULL） 如果数据库表字段允许为NULL，而对应的C#属性是值类型（如 int），映射时会抛出异常。

解决方案：

• 将模型属性改为可空类型（如 int?）。
• 或者在查询SQL中使用 COALESCE 函数提供默认值：SELECT Id, Name, COALESCE(Age, 0) AS Age ...。

坑点4：自定义类型映射 有时数据库中的字段格式（如存储JSON的TEXT字段）需要映射到复杂的C#对象。

解决方案： Dapper支持通过 SqlMapper.SetTypeMap 或实现 TypeHandler 接口来进行自定义类型处理。例如，处理JSON：

using Dapper;
using System.Data;
using Newtonsoft.Json;

public class JsonTypeHandler<T> : SqlMapper.TypeHandler<T>
{
    public override void SetValue(IDbDataParameter parameter, T value)
    {
        parameter.Value = JsonConvert.SerializeObject(value);
    }

    public override T Parse(object value)
    {
        return JsonConvert.DeserializeObject<T>(value.ToString());
    }
}

// 在程序启动时注册
SqlMapper.AddTypeHandler(new JsonTypeHandler<Dictionary<string, object>>());

4.3 与Entity Framework Core共存的注意事项

在有些项目中，你可能想在同一应用内同时使用Dapper（用于复杂、高性能查询）和EF Core（用于快速建模和简单CRUD）。两者可以共存，但需要注意：

• 驱动冲突：EF Core通常使用 Microsoft.Data.Sqlite，而本文使用的是 System.Data.SQLite。两者可能不兼容，尤其是在处理连接和事务时。建议统一使用其中一个驱动栈。如果必须混用，需极其小心地管理各自的连接实例，避免共享。
• 连接管理：切勿将一个驱动创建的连接交给另一个驱动使用。它们应完全独立。

4.4 调试与日志

虽然Dapper很轻量，但有时你需要知道它最终生成了什么SQL。可以设置 System.Data.SQLite 的日志输出，或者使用像 MiniProfiler 这样的工具，它集成了对Dapper的查询分析功能，能直观地看到每条查询的执行时间和参数，对于性能调优和问题排查有巨大帮助。

安装MiniProfiler后，配置大致如下：

// 通常放在Startup或Program中
services.AddMiniProfiler(options =>
{
    options.RouteBasePath = "/profiler";
    // 跟踪SQLite连接
    (options.Storage as MemoryCacheStorage).CacheDuration = TimeSpan.FromMinutes(10);
}).AddEntityFramework(); // 如果需要跟踪EF Core

在开发环境中，访问 /profiler 页面就能看到所有数据库操作的详细报告。

走完这趟从环境搭建到进阶优化的旅程，你会发现C#、Dapper和SQLite的组合就像一把精心调校的瑞士军刀，轻便、锋利且可靠。核心在于理解每个工具的特性：SQLite的轻量文件存储、Dapper的极简映射哲学，以及C#强大的类型系统。我自己的经验是，在中小型项目、需要快速原型验证、或是作为分布式缓存之外的本地持久化层时，这个技术栈总能带来惊喜。最后一个小建议：将你的数据访问代码进行充分的单元测试，模拟 IDbConnection 接口，这能确保业务逻辑的纯净性，并让你在未来的重构或驱动更换中更加从容。