TianSong
528 lines
20 KiB
C#
528 lines
20 KiB
C#
using Microsoft.Data.Sqlite;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Reflection;
|
||
using System.Text;
|
||
using System.Threading.Tasks;
|
||
using XP.Common.Configs;
|
||
using XP.Common.Database.Interfaces;
|
||
using XP.Common.Database.Models;
|
||
using XP.Common.Helpers;
|
||
using XP.Common.Logging.Interfaces;
|
||
|
||
using CustomDbTransaction = XP.Common.Database.Interfaces.IDbTransaction;
|
||
|
||
namespace XP.Common.Database.Implementations
|
||
{
|
||
/// <summary>
|
||
/// SQLite 核心操作实现（适配IDbContext）
|
||
/// </summary>
|
||
public class SqliteContext : IDbContext
|
||
{
|
||
private readonly SqliteConfig _config;
|
||
private readonly ILoggerService _logger;
|
||
private SqliteConnection? _connection;
|
||
private bool _isDisposed = false;
|
||
|
||
/// <summary>
|
||
/// 构造函数（DI注入配置+日志）
|
||
/// </summary>
|
||
public SqliteContext(SqliteConfig config, ILoggerService logger)
|
||
{
|
||
_config = config ?? throw new ArgumentNullException(nameof(config));
|
||
_logger = logger.ForModule("Sqlite.Context");
|
||
|
||
// 自动创建数据库目录
|
||
if (_config.CreateIfNotExists)
|
||
{
|
||
var dbDir = Path.GetDirectoryName(_config.DbFilePath);
|
||
if (!string.IsNullOrEmpty(dbDir) && !Directory.Exists(dbDir))
|
||
{
|
||
Directory.CreateDirectory(dbDir);
|
||
_logger.Info("自动创建数据库目录：{DirPath}", dbDir);
|
||
}
|
||
}
|
||
}
|
||
|
||
#region 连接管理
|
||
public IDbExecuteResult OpenConnection()
|
||
{
|
||
try
|
||
{
|
||
if (_connection != null && _connection.State == ConnectionState.Open)
|
||
{
|
||
return DbExecuteResult.Success("连接已打开");
|
||
}
|
||
|
||
_connection = new SqliteConnection(_config.GetConnectionString());
|
||
_connection.Open();
|
||
_logger.Debug("SQLite连接已打开：{DbPath}", _config.DbFilePath);
|
||
|
||
// 启用WAL模式提升性能
|
||
if (_config.EnableWalMode)
|
||
{
|
||
using var cmd = new SqliteCommand("PRAGMA journal_mode=WAL;", _connection);
|
||
cmd.ExecuteNonQuery();
|
||
_logger.Debug("SQLite已启用WAL模式");
|
||
}
|
||
|
||
return DbExecuteResult.Success("连接打开成功");
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQLite连接打开失败：{DbPath}", _config.DbFilePath);
|
||
return DbExecuteResult.Fail("连接打开失败", ex);
|
||
}
|
||
}
|
||
|
||
public async Task<IDbExecuteResult> OpenConnectionAsync()
|
||
{
|
||
try
|
||
{
|
||
if (_connection != null && _connection.State == ConnectionState.Open)
|
||
{
|
||
return DbExecuteResult.Success("连接已打开");
|
||
}
|
||
|
||
_connection = new SqliteConnection(_config.GetConnectionString());
|
||
await _connection.OpenAsync();
|
||
_logger.Debug("SQLite连接已异步打开：{DbPath}", _config.DbFilePath);
|
||
|
||
if (_config.EnableWalMode)
|
||
{
|
||
using var cmd = new SqliteCommand("PRAGMA journal_mode=WAL;", _connection);
|
||
await cmd.ExecuteNonQueryAsync();
|
||
}
|
||
|
||
return DbExecuteResult.Success("连接异步打开成功");
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQLite连接异步打开失败：{DbPath}", _config.DbFilePath);
|
||
return DbExecuteResult.Fail("连接异步打开失败", ex);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 获取有效连接（自动打开）
|
||
/// </summary>
|
||
private SqliteConnection GetValidConnection()
|
||
{
|
||
if (_connection == null || _connection.State != ConnectionState.Open)
|
||
{
|
||
var result = OpenConnection();
|
||
if (!result.IsSuccess)
|
||
{
|
||
throw new InvalidOperationException($"获取SQLite连接失败：{result.Message}");
|
||
}
|
||
}
|
||
return _connection!;
|
||
}
|
||
#endregion
|
||
|
||
#region 事务管理
|
||
public (IDbExecuteResult Result, CustomDbTransaction? Transaction) BeginTransaction()
|
||
{
|
||
try
|
||
{
|
||
var conn = GetValidConnection();
|
||
// 系统的SqliteTransaction
|
||
var innerSystemTrans = conn.BeginTransaction();
|
||
// 封装为自定义的SqliteTransaction
|
||
var customTrans = new SqliteTransaction(innerSystemTrans, _logger);
|
||
_logger.Debug("SQLite事务已开始");
|
||
// 返回：自定义IDbTransaction（解决返回类型不匹配）
|
||
return (DbExecuteResult.Success("事务开始成功"), customTrans);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQLite事务开始失败");
|
||
return (DbExecuteResult.Fail("事务开始失败", ex), null);
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 通用SQL执行
|
||
public IDbExecuteResult ExecuteNonQuery(string sql, Dictionary<string, object>? parameters = null)
|
||
{
|
||
try
|
||
{
|
||
LogSql(sql, parameters);
|
||
using var cmd = CreateCommand(sql, parameters);
|
||
var rowsAffected = cmd.ExecuteNonQuery();
|
||
_logger.Debug("SQL执行成功，影响行数：{Rows}", rowsAffected);
|
||
return DbExecuteResult.Success("执行成功", rowsAffected);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL执行失败：{Sql}", sql);
|
||
return DbExecuteResult.Fail("执行失败", ex);
|
||
}
|
||
}
|
||
|
||
public async Task<IDbExecuteResult> ExecuteNonQueryAsync(string sql, Dictionary<string, object>? parameters = null)
|
||
{
|
||
try
|
||
{
|
||
LogSql(sql, parameters);
|
||
using var cmd = CreateCommand(sql, parameters);
|
||
var rowsAffected = await cmd.ExecuteNonQueryAsync();
|
||
_logger.Debug("SQL异步执行成功，影响行数：{Rows}", rowsAffected);
|
||
return DbExecuteResult.Success("异步执行成功", (int)rowsAffected);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL异步执行失败：{Sql}", sql);
|
||
return DbExecuteResult.Fail("异步执行失败", ex);
|
||
}
|
||
}
|
||
|
||
public (IDbExecuteResult Result, T? Value) ExecuteScalar<T>(string sql, Dictionary<string, object>? parameters = null)
|
||
{
|
||
try
|
||
{
|
||
LogSql(sql, parameters);
|
||
using var cmd = CreateCommand(sql, parameters);
|
||
var resultObj = cmd.ExecuteScalar();
|
||
T? value = default;
|
||
if (resultObj != null && resultObj != DBNull.Value)
|
||
{
|
||
if (resultObj is T directValue)
|
||
{
|
||
value = directValue;
|
||
}
|
||
else
|
||
{
|
||
// 类型不匹配时安全转换，处理值类型/引用类型
|
||
value = (T)Convert.ChangeType(resultObj, Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T));
|
||
}
|
||
}
|
||
var logValue = value ?? (object)"null";
|
||
_logger.Debug("SQL标量查询成功，返回值：{Value}", logValue);
|
||
return (DbExecuteResult.Success("标量查询成功"), value);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL标量查询失败：{Sql}", sql);
|
||
return (DbExecuteResult.Fail("标量查询失败", ex), default);
|
||
}
|
||
}
|
||
|
||
public async Task<(IDbExecuteResult Result, T? Value)> ExecuteScalarAsync<T>(string sql, Dictionary<string, object>? parameters = null)
|
||
{
|
||
try
|
||
{
|
||
LogSql(sql, parameters);
|
||
using var cmd = CreateCommand(sql, parameters);
|
||
var resultObj = await cmd.ExecuteScalarAsync();
|
||
T? value = default;
|
||
if (resultObj != null && resultObj != DBNull.Value)
|
||
{
|
||
if (resultObj is T directValue)
|
||
{
|
||
value = directValue;
|
||
}
|
||
else
|
||
{
|
||
// 类型不匹配时安全转换，处理值类型/引用类型
|
||
value = (T)Convert.ChangeType(resultObj, Nullable.GetUnderlyingType(typeof(T)) ?? typeof(T));
|
||
}
|
||
}
|
||
var logValue = value ?? (object)"null";
|
||
_logger.Debug("SQL异步标量查询成功，返回值：{Value}", logValue);
|
||
return (DbExecuteResult.Success("异步标量查询成功"), value);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL异步标量查询失败：{Sql}", sql);
|
||
return (DbExecuteResult.Fail("异步标量查询失败", ex), default);
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 查询结果映射
|
||
public (IDbExecuteResult Result, DataTable? Data) ExecuteDataTable(string sql, Dictionary<string, object>? parameters = null)
|
||
{
|
||
try
|
||
{
|
||
LogSql(sql, parameters);
|
||
using var cmd = CreateCommand(sql, parameters);
|
||
using var reader = cmd.ExecuteReader();
|
||
|
||
var dt = new DataTable();
|
||
// 添加列
|
||
for (int i = 0; i < reader.FieldCount; i++)
|
||
{
|
||
dt.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
|
||
}
|
||
// 添加行
|
||
while (reader.Read())
|
||
{
|
||
var row = dt.NewRow();
|
||
for (int i = 0; i < reader.FieldCount; i++)
|
||
{
|
||
row[i] = reader.IsDBNull(i) ? DBNull.Value : reader.GetValue(i);
|
||
}
|
||
dt.Rows.Add(row);
|
||
}
|
||
|
||
_logger.Debug("SQL查询成功，返回DataTable行数：{Rows}", dt.Rows.Count);
|
||
return (DbExecuteResult.Success("查询成功"), dt);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL查询DataTable失败：{Sql}", sql);
|
||
return (DbExecuteResult.Fail("查询失败", ex), null);
|
||
}
|
||
}
|
||
|
||
public async Task<(IDbExecuteResult Result, DataTable? Data)> ExecuteDataTableAsync(string sql, Dictionary<string, object>? parameters = null)
|
||
{
|
||
try
|
||
{
|
||
LogSql(sql, parameters);
|
||
using var cmd = CreateCommand(sql, parameters);
|
||
using var reader = await cmd.ExecuteReaderAsync();
|
||
|
||
var dt = new DataTable();
|
||
// 添加列
|
||
for (int i = 0; i < reader.FieldCount; i++)
|
||
{
|
||
dt.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
|
||
}
|
||
// 添加行（异步读取）
|
||
while (await reader.ReadAsync())
|
||
{
|
||
var row = dt.NewRow();
|
||
for (int i = 0; i < reader.FieldCount; i++)
|
||
{
|
||
row[i] = await reader.IsDBNullAsync(i) ? DBNull.Value : reader.GetValue(i);
|
||
}
|
||
dt.Rows.Add(row);
|
||
}
|
||
_logger.Debug("SQL异步查询成功，返回DataTable行数：{Rows}", dt.Rows.Count);
|
||
return (DbExecuteResult.Success("异步查询成功"), dt);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL异步查询DataTable失败：{Sql}", sql);
|
||
return (DbExecuteResult.Fail("异步查询失败", ex), null);
|
||
}
|
||
}
|
||
|
||
public (IDbExecuteResult Result, List<T> Data) QueryList<T>(string sql, Dictionary<string, object>? parameters = null) where T : new()
|
||
{
|
||
try
|
||
{
|
||
var (result, dt) = ExecuteDataTable(sql, parameters);
|
||
if (!result.IsSuccess || dt == null)
|
||
{
|
||
return (result, new List<T>());
|
||
}
|
||
|
||
var list = MapDataTableToEntityList<T>(dt);
|
||
return (DbExecuteResult.Success("实体列表查询成功"), list);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL查询实体列表失败：{Sql}", sql);
|
||
return (DbExecuteResult.Fail("实体列表查询失败", ex), new List<T>());
|
||
}
|
||
}
|
||
|
||
public async Task<(IDbExecuteResult Result, List<T> Data)> QueryListAsync<T>(string sql, Dictionary<string, object>? parameters = null) where T : new()
|
||
{
|
||
try
|
||
{
|
||
var (result, dt) = await ExecuteDataTableAsync(sql, parameters);
|
||
if (!result.IsSuccess || dt == null)
|
||
{
|
||
return (result, new List<T>());
|
||
}
|
||
|
||
var list = MapDataTableToEntityList<T>(dt);
|
||
return (DbExecuteResult.Success("异步实体列表查询成功"), list);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL异步查询实体列表失败：{Sql}", sql);
|
||
return (DbExecuteResult.Fail("异步实体列表查询失败", ex), new List<T>());
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 分页查询
|
||
public (IDbExecuteResult Result, PaginationResponse<T> Data) QueryPaged<T>(string sql, PaginationRequest pagination, Dictionary<string, object>? parameters = null) where T : new()
|
||
{
|
||
try
|
||
{
|
||
// 1. 查询总条数
|
||
var countSql = $"SELECT COUNT(*) FROM ({sql}) AS TotalCountQuery";
|
||
var (countResult, totalCount) = ExecuteScalar<int>(countSql, parameters);
|
||
if (!countResult.IsSuccess)
|
||
{
|
||
return (countResult, new PaginationResponse<T>());
|
||
}
|
||
|
||
// 2. 构建分页SQL（SQLite分页：LIMIT/OFFSET）
|
||
var offset = (pagination.PageIndex - 1) * pagination.PageSize;
|
||
var pagedSql = $"{sql} {(!string.IsNullOrEmpty(pagination.OrderBy) ? $"ORDER BY {pagination.OrderBy}" : "")} LIMIT {pagination.PageSize} OFFSET {offset}";
|
||
|
||
// 3. 查询当前页数据
|
||
var (dataResult, list) = QueryList<T>(pagedSql, parameters);
|
||
if (!dataResult.IsSuccess)
|
||
{
|
||
return (dataResult, new PaginationResponse<T>());
|
||
}
|
||
|
||
// 4. 封装分页结果
|
||
var pagedResponse = new PaginationResponse<T>
|
||
{
|
||
PageIndex = pagination.PageIndex,
|
||
PageSize = pagination.PageSize,
|
||
TotalCount = totalCount,
|
||
Data = list
|
||
};
|
||
|
||
_logger.Debug("SQL分页查询成功：页码{Page}，条数{Size}，总条数{Total}",
|
||
pagination.PageIndex, pagination.PageSize, totalCount);
|
||
return (DbExecuteResult.Success("分页查询成功"), pagedResponse);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL分页查询失败：{Sql}", sql);
|
||
return (DbExecuteResult.Fail("分页查询失败", ex), new PaginationResponse<T>());
|
||
}
|
||
}
|
||
|
||
public async Task<(IDbExecuteResult Result, PaginationResponse<T> Data)> QueryPagedAsync<T>(string sql, PaginationRequest pagination, Dictionary<string, object>? parameters = null) where T : new()
|
||
{
|
||
try
|
||
{
|
||
// 1. 查询总条数
|
||
var countSql = $"SELECT COUNT(*) FROM ({sql}) AS TotalCountQuery";
|
||
var (countResult, totalCount) = await ExecuteScalarAsync<int>(countSql, parameters);
|
||
if (!countResult.IsSuccess)
|
||
{
|
||
return (countResult, new PaginationResponse<T>());
|
||
}
|
||
|
||
// 2. 构建分页SQL
|
||
var offset = (pagination.PageIndex - 1) * pagination.PageSize;
|
||
var pagedSql = $"{sql} {(!string.IsNullOrEmpty(pagination.OrderBy) ? $"ORDER BY {pagination.OrderBy}" : "")} LIMIT {pagination.PageSize} OFFSET {offset}";
|
||
|
||
// 3. 查询当前页数据
|
||
var (dataResult, list) = await QueryListAsync<T>(pagedSql, parameters);
|
||
if (!dataResult.IsSuccess)
|
||
{
|
||
return (dataResult, new PaginationResponse<T>());
|
||
}
|
||
|
||
// 4. 封装分页结果
|
||
var pagedResponse = new PaginationResponse<T>
|
||
{
|
||
PageIndex = pagination.PageIndex,
|
||
PageSize = pagination.PageSize,
|
||
TotalCount = totalCount,
|
||
Data = list
|
||
};
|
||
|
||
_logger.Debug("SQL异步分页查询成功：页码{Page}，条数{Size}，总条数{Total}",
|
||
pagination.PageIndex, pagination.PageSize, totalCount);
|
||
return (DbExecuteResult.Success("异步分页查询成功"), pagedResponse);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
_logger.Error(ex, "SQL异步分页查询失败：{Sql}", sql);
|
||
return (DbExecuteResult.Fail("异步分页查询失败", ex), new PaginationResponse<T>());
|
||
}
|
||
}
|
||
#endregion
|
||
|
||
#region 私有辅助方法
|
||
/// <summary>
|
||
/// 创建SQLite命令（带参数）
|
||
/// </summary>
|
||
private SqliteCommand CreateCommand(string sql, Dictionary<string, object>? parameters = null)
|
||
{
|
||
var cmd = new SqliteCommand(sql, GetValidConnection());
|
||
cmd.CommandTimeout = _config.ConnectionTimeout;
|
||
|
||
// 添加参数（防SQL注入）
|
||
if (parameters != null && parameters.Count > 0)
|
||
{
|
||
foreach (var (key, value) in parameters)
|
||
{
|
||
var paramValue = value ?? DBNull.Value;
|
||
cmd.Parameters.AddWithValue($"@{key}", paramValue);
|
||
}
|
||
}
|
||
|
||
return cmd;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 记录SQL日志（调试模式）
|
||
/// </summary>
|
||
private void LogSql(string sql, Dictionary<string, object>? parameters = null)
|
||
{
|
||
if (!_config.EnableSqlLogging) return;
|
||
|
||
var paramStr = parameters == null
|
||
? "无参数"
|
||
: string.Join(", ", parameters.Select(kv => $"{kv.Key}={kv.Value}"));
|
||
_logger.Debug("执行SQL：{Sql} | 参数：{Params}", sql, paramStr);
|
||
}
|
||
|
||
/// <summary>
|
||
/// DataTable映射为实体列表
|
||
/// </summary>
|
||
private List<T> MapDataTableToEntityList<T>(DataTable dt) where T : new()
|
||
{
|
||
var list = new List<T>();
|
||
var props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
|
||
|
||
foreach (DataRow row in dt.Rows)
|
||
{
|
||
var entity = new T();
|
||
foreach (var prop in props)
|
||
{
|
||
if (dt.Columns.Contains(prop.Name) && row[prop.Name] != DBNull.Value)
|
||
{
|
||
var value = Convert.ChangeType(row[prop.Name], prop.PropertyType);
|
||
prop.SetValue(entity, value);
|
||
}
|
||
}
|
||
list.Add(entity);
|
||
}
|
||
|
||
return list;
|
||
}
|
||
#endregion
|
||
|
||
#region 资源释放
|
||
public void Dispose()
|
||
{
|
||
if (_isDisposed) return;
|
||
|
||
// 关闭连接
|
||
if (_connection != null)
|
||
{
|
||
if (_connection.State == ConnectionState.Open)
|
||
{
|
||
_connection.Close();
|
||
_logger.Debug("SQLite连接已关闭（Dispose）");
|
||
}
|
||
_connection.Dispose();
|
||
}
|
||
|
||
_isDisposed = true;
|
||
_logger.Debug("SQLiteContext资源已释放");
|
||
}
|
||
#endregion
|
||
}
|
||
}
|