Dapper连接四大数据库实战一份代码搞定SQL Server、MySQL、SQLite和Access含连接字符串避坑指南在.NET生态中Dapper作为一款轻量级ORM工具因其接近原生ADO.NET的性能和简洁的API设计成为许多开发者在中小型项目中的首选。特别是在需要同时支持多种数据库的场景下Dapper的灵活性显得尤为珍贵。本文将带你深入实战构建一个能够无缝切换SQL Server、MySQL、SQLite和Access的Dapper数据访问层。1. 多数据库支持架构设计实现多数据库支持的核心在于抽象和封装。我们需要设计一个统一的接口来屏蔽不同数据库的差异让业务代码无需关心底层使用的是哪种数据库。这种设计模式通常被称为数据库工厂模式。关键设计原则使用IDbConnection作为统一接口将数据库特定的实现细节封装在工厂类中通过配置而非硬编码决定使用哪种数据库确保线程安全和高性能让我们先来看一个基础实现框架public interface IDatabaseFactory { IDbConnection CreateConnection(); IDbConnection CreateConnection(string connectionString); } public class DatabaseFactory : IDatabaseFactory { private readonly DatabaseConfig _config; public DatabaseFactory(DatabaseConfig config) { _config config; } public IDbConnection CreateConnection() { return CreateConnection(_config.ConnectionString); } public IDbConnection CreateConnection(string connectionString) { IDbConnection connection _config.DatabaseType switch { DatabaseType.SqlServer new SqlConnection(connectionString), DatabaseType.MySql new MySqlConnection(connectionString), DatabaseType.Sqlite new SQLiteConnection(connectionString), DatabaseType.Access new OleDbConnection(connectionString), _ throw new NotSupportedException($不支持的数据库类型: {_config.DatabaseType}) }; connection.Open(); return connection; } }2. 连接字符串的标准化与安全配置连接字符串是数据库连接的核心也是最容易出问题的地方。我们需要解决两个关键问题如何安全地管理连接字符串以及如何处理不同数据库连接字符串的差异。2.1 安全配置最佳实践绝对避免的做法在代码中硬编码连接字符串将连接字符串提交到版本控制系统使用明文存储敏感信息推荐的安全实践使用.NET的配置系统appsettings.json 用户机密对生产环境连接字符串使用Azure Key Vault等安全存储为不同环境使用不同的配置// appsettings.Development.json { Database: { Type: Sqlite, ConnectionString: Data Source./dev.db } }2.2 各数据库连接字符串详解不同数据库的连接字符串格式差异很大下面是四种数据库的标准连接字符串模板数据库类型基本格式关键参数说明SQL ServerServer服务器;Database数据库;User Id用户;Password密码;TrustServerCertificate用于本地开发MySQLServer服务器;Database数据库;Uid用户;Pwd密码;AllowPublicKeyRetrieval在某些版本需要SQLiteData Source文件路径;Foreign KeysTrue启用外键约束AccessProviderMicrosoft.ACE.OLEDB.12.0;Data Source文件路径;Jet OLEDB:Database Password用于加密数据库常见连接问题解决方案SQL Server连接超时// 增加连接超时时间单位秒 Server.;DatabaseTest;Integrated SecurityTrue;Connect Timeout30;MySQL SSL问题// 禁用SSL验证仅开发环境 Serverlocalhost;Databasetest;Uidroot;Pwd123456;SslModenone;Access数据库锁定// 独占模式打开 ProviderMicrosoft.ACE.OLEDB.12.0;Data SourceC:\\data.accdb;ModeShare Exclusive;3. 数据库特定问题的处理每种数据库都有其独特的特性和潜在问题我们需要在代码中妥善处理这些差异。3.1 SQL Server的特殊处理SQL Server在连接字符串和参数化查询方面有一些特殊要求// 使用SQL Server时的最佳实践 var parameters new DynamicParameters(); parameters.Add(id, id, DbType.Int32); parameters.Add(name, name, DbType.String, size: 50); var result connection.QueryUser( SELECT * FROM Users WHERE Id id AND Name name, parameters);注意SQL Server对参数类型和大小非常敏感明确指定DbType和size可以避免隐式转换带来的性能问题。3.2 MySQL的编码问题MySQL默认使用latin1编码处理中文时需要特别注意// 确保连接字符串指定UTF-8编码 Serverlocalhost;Databasetest;Uidroot;Pwd123456;Charsetutf8mb4; // 创建表时指定字符集 connection.Execute(CREATE TABLE IF NOT EXISTS Users ( Id INT PRIMARY KEY, Name VARCHAR(50) CHARACTER SET utf8mb4 ));3.3 SQLite的并发限制SQLite是文件型数据库对并发写入有严格限制提示SQLite同一时间只允许一个写入操作在高并发场景下需要考虑使用读写锁或迁移到客户端-服务器型数据库。// 使用读写锁保护SQLite操作 private static readonly object _lock new object(); public void SafeWriteOperation() { lock (_lock) { using var connection _factory.CreateConnection(); connection.Execute(INSERT INTO Logs (...) VALUES (...)); } }3.4 Access数据库的兼容性问题Access数据库.mdb/.accdb在使用时有许多兼容性陷阱驱动问题32位/64位系统需要匹配的驱动新版本使用Microsoft.ACE.OLEDB.12.0旧版本使用Microsoft.Jet.OLEDB.4.0SQL方言差异Access不支持LIMIT关键字使用TOP代替日期函数与SQL Server不同// Access分页查询的替代方案 var users connection.QueryUser( SELECT TOP 10 * FROM Users WHERE Id NOT IN ( SELECT TOP 20 Id FROM Users ORDER BY CreateDate ) ORDER BY CreateDate);4. 高级应用动态SQL与多数据库兼容在支持多种数据库时最大的挑战是如何处理不同数据库的SQL方言差异。以下是几种解决方案4.1 使用Dapper的DynamicParametersDynamicParameters可以帮助我们构建数据库无关的参数化查询public IEnumerableUser SearchUsers(string name, int? age) { var sql new StringBuilder(SELECT * FROM Users WHERE 11); var parameters new DynamicParameters(); if (!string.IsNullOrEmpty(name)) { sql.Append( AND Name LIKE name); parameters.Add(name, $%{name}%); } if (age.HasValue) { sql.Append( AND Age age); parameters.Add(age, age.Value); } using var connection _factory.CreateConnection(); return connection.QueryUser(sql.ToString(), parameters); }4.2 数据库特定SQL的运行时选择对于必须使用数据库特定功能的场景可以在运行时根据数据库类型选择不同的SQLpublic string GetPagedUsersSql(int page, int pageSize) { return _databaseType switch { DatabaseType.SqlServer $SELECT * FROM Users ORDER BY Id OFFSET {(page-1)*pageSize} ROWS FETCH NEXT {pageSize} ROWS ONLY, DatabaseType.MySql $SELECT * FROM Users ORDER BY Id LIMIT {(page-1)*pageSize}, {pageSize}, DatabaseType.Sqlite $SELECT * FROM Users ORDER BY Id LIMIT {pageSize} OFFSET {(page-1)*pageSize}, DatabaseType.Access $SELECT TOP {pageSize} * FROM Users WHERE Id NOT IN ( SELECT TOP {(page-1)*pageSize} Id FROM Users ORDER BY Id ) ORDER BY Id, _ throw new NotSupportedException() }; }4.3 使用Dapper.Contrib的多数据库支持Dapper.Contrib提供了简单的CRUD操作但在多数据库环境下需要注意自增主键的处理方式不同表名和列名的引用方式不同批量插入的语法差异// 通用的Contrib操作示例 public long AddUser(User user) { using var connection _factory.CreateConnection(); // SQLite需要单独处理自增ID if (_databaseType DatabaseType.Sqlite) { var id connection.ExecuteScalarlong( INSERT INTO Users (...) VALUES (...); SELECT last_insert_rowid();); return id; } return connection.Insert(user); }5. 性能优化与调试技巧在多数据库环境下性能优化需要考虑不同数据库的特性。以下是一些通用和特定于数据库的优化建议5.1 连接池配置每种数据库都有自己的连接池实现需要分别优化数据库类型关键连接池参数推荐值SQL ServerMax Pool Size100-200MySQLMaximumPoolSize100-200SQLitePoolingfalse (文件数据库不需要)AccessN/A不适用// SQL Server连接字符串中的连接池配置 Server.;DatabaseTest;Integrated SecurityTrue;Max Pool Size100;Connection Timeout30;5.2 批量操作优化Dapper本身对批量操作的支持有限但可以通过一些技巧提高性能// 使用表值参数(SQL Server特有) public void BulkInsertUsers(IEnumerableUser users) { if (_databaseType ! DatabaseType.SqlServer) throw new NotSupportedException(仅SQL Server支持此操作); using var connection _factory.CreateConnection() as SqlConnection; var table new DataTable(); // 构建DataTable... connection.Execute( INSERT INTO Users SELECT * FROM tvp, new { tvp table.AsTableValuedParameter(UserTableType) }); }5.3 调试与日志记录在多数据库环境下统一的日志记录非常重要public class DapperLogger { private readonly ILogger _logger; public DapperLogger(ILogger logger) { _logger logger; SqlMapper.AddTypeHandler(new LoggingTypeHandler(_logger)); } private class LoggingTypeHandler : SqlMapper.ITypeHandler { private readonly ILogger _logger; public LoggingTypeHandler(ILogger logger) { _logger logger; } public object Parse(Type destinationType, object value) { _logger.LogDebug($从数据库读取: {value} {destinationType}); return Convert.ChangeType(value, destinationType); } public void SetValue(IDbDataParameter parameter, object value) { _logger.LogDebug($向数据库写入: {value} {parameter.ParameterName}); parameter.Value value ?? DBNull.Value; } } }6. 实战完整的多数据库仓储实现让我们将这些知识点整合到一个完整的仓储实现中public class UserRepository : IUserRepository { private readonly IDatabaseFactory _factory; private readonly DatabaseType _databaseType; public UserRepository(IDatabaseFactory factory) { _factory factory; _databaseType factory.DatabaseType; } public User GetById(int id) { using var connection _factory.CreateConnection(); return connection.QueryFirstOrDefaultUser( SELECT * FROM Users WHERE Id id, new { id }); } public IEnumerableUser Search(string keyword, int page, int pageSize) { var sql _databaseType switch { DatabaseType.SqlServer SELECT * FROM Users WHERE Name LIKE keyword OR Email LIKE keyword ORDER BY Id OFFSET offset ROWS FETCH NEXT pageSize ROWS ONLY, _ SELECT * FROM Users WHERE Name LIKE keyword OR Email LIKE keyword ORDER BY Id LIMIT pageSize OFFSET offset }; using var connection _factory.CreateConnection(); return connection.QueryUser(sql, new { keyword $%{keyword}%, offset (page - 1) * pageSize, pageSize }); } public long Add(User user) { using var connection _factory.CreateConnection(); if (_databaseType DatabaseType.Sqlite) { var sql INSERT INTO Users (Name, Email, CreateDate) VALUES (Name, Email, CreateDate); SELECT last_insert_rowid();; return connection.ExecuteScalarlong(sql, user); } return connection.Insert(user); } // 其他CRUD操作... }这个实现展示了如何处理不同数据库的SQL差异同时保持接口的统一性。在实际项目中你可以根据需要扩展更多的数据库特定优化。