sqlite sugar 使用 scope 插入后立马查询异常报错

[shuangqingqiang]

System.ArgumentNullException: SafeHandle cannot be null. (Parameter 'pHandle')
at System.StubHelpers.StubHelpers.SafeHandleAddRef(SafeHandle pHandle, Boolean& success)
at SQLitePCL.SQLite3Provider_e_sqlite3.NativeMethods.sqlite3_prepare_v2(sqlite3 db, Byte* pSql, Int32 nBytes, IntPtr& stmt, Byte*& ptrRemain)
at SQLitePCL.SQLite3Provider_e_sqlite3.SQLitePCL.ISQLite3Provider.sqlite3_prepare_v2(sqlite3 db, ReadOnlySpan1 sql, IntPtr& stm, ReadOnlySpan1& tail)
at SQLitePCL.raw.sqlite3_prepare_v2(sqlite3 db, ReadOnlySpan1 sql, sqlite3_stmt& stmt, ReadOnlySpan1& tail)
at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
at SqlSugar.AdoProvider.GetDataReaderAsync(String sql, SugarParameter[] parameters)
at SqlSugar.QueryableProvider1.GetDataAsync[TResult](KeyValuePair2 sqlObj)
at SqlSugar.QueryableProvider1._ToListAsync[TResult]() at SqlSugar.QueryableProvider1.ToPageListAsync(Int32 pageIndex, Int32 pageSize, RefAsync1 totalNumber) at Weight.Services.WeightService.GetWeighsByLicensePlateInTimeStateRange(RefAsync1 totalCount, Int32 type, Int32 state, String licensePlate, DateTime startTime, DateTime endTime, Int32 pageIndex, Int32 pageSize, Int32 source) in C:\Users\24524\Desktop\scale\Weight\Services\WeightService.cs:line 430 public async Task<List> GetWeighsByLicensePlateInTimeStateRange(RefAsync totalCount, int type, int state, string licensePlate,
DateTime startTime, DateTime endTime, int pageIndex, int pageSize, int source = 0)
{
try
{
// 1. 处理时间范围，确保覆盖全天
var beginDate = startTime.Date; // 00:00:00
var endDate = endTime.Date.AddDays(1).AddSeconds(-1); // 23:59:59

        // 2. 清理查询参数
        var cleanedLicensePlate = (licensePlate).Trim();

        // 3. 构建基础查询
        var query = _db.Queryable<WeightModel>()
            .Where(w => w.CreateTime >= beginDate && w.CreateTime <= endDate&& w.IsCancel==0)
             .WhereIF(source == 1, w => w.WeightSource == 3)
            .WhereIF(!string.IsNullOrEmpty(cleanedLicensePlate), w => w.LicensePlate.Contains(cleanedLicensePlate));


        switch (type)
        {
            case 1: // 收料
                query = query.Where(w => w.WeightType == 1);
                break;
            case 2: // 发料
                query = query.Where(w => w.WeightType == 2);
                break;
        }

        // 4. 根据状态过滤逻辑
        switch (state)
        {
            case 1: // 未出场
                query = query.Where(w => w.IsOut == 0);
                break;
            case 2: // 已出场
                query = query.Where(w => w.IsOut == 1);
                break;
        }

        // 5. 执行分页查询
        // ToPageListAsync 会自动填充 totalCount 的值
        var result = await query
            .OrderByDescending(w => w.CreateTime) // 通常分页需要排序，建议按时间倒序
            .ToPageListAsync(pageIndex, pageSize, totalCount);

        Log.Information("分页查询车牌 [{LicensePlate}] 状态:{State}, 第{Idx}页, 共{Total}条",
            cleanedLicensePlate, state, pageIndex, totalCount.Value);

        return result;
    }
    catch (Exception ex)
    {
        Log.Error(ex, "分页查询车牌 {LicensePlate} 称重记录失败", licensePlate);
        totalCount.Value = 0;
        return [];
    }
}  using System;

using System.IO;
using System.Linq;
using Serilog;
using SqlSugar;
using Weight.Config;
using Weight.Models;
using YamlDotNet.Serialization;
using YamlDotNet.Serialization.NamingConventions;

namespace Weight.Services;

public interface ISugarService
{
string? ConnectionString { get; }
DbType? DbType { get; }
SqlSugarScope? SqlSugarScope { get; }
void InitializeDatabaseTables();
}

public class SugarService : ISugarService, IDisposable
{
private readonly string _configFilePath;
private readonly IDeserializer _deserializer;
private readonly ISerializer _serializer;

private readonly BaseConfig _baseConfig;


public string? ConnectionString { get; private set; }
public DbType? DbType { get; private set; }
public SqlSugarScope? SqlSugarScope { get; private set; }

public SugarService(string configFilePath = "data/database.yaml")
{
    _configFilePath = configFilePath;

    _baseConfig = BaseConfig.Load();

    _deserializer = new DeserializerBuilder()
        .WithNamingConvention(CamelCaseNamingConvention.Instance)
        .IgnoreUnmatchedProperties()
        .Build();

    _serializer = new SerializerBuilder()
        .WithNamingConvention(CamelCaseNamingConvention.Instance)
        .Build();

    // InitializeDatabaseConfig();
    // InitializeDatabaseTables();
}



// Add this explicit initialization method
public void Initialize()
{
    InitializeDatabaseConfig();
    InitializeDatabaseTables();
}


public void InitializeDatabaseTables()
{
    try
    {
        if (SqlSugarScope == null)
        {
            Log.Error("SqlSugarClient 未初始化，无法创建数据库表");
            return;
        }

        SqlSugarScope.CodeFirst.InitTables(
            typeof(AccountModel),
            typeof(CarManagerModel),
            typeof(MaterialCategoryModel),
            typeof(CoefficientModel),
            typeof(ContractModel),
            typeof(GoodModel),
            typeof(HardwareModel),
            typeof(ImageModel),
            typeof(InternalOrganizationModel),
            typeof(MaterialModel),
            typeof(MerchantModel),
            typeof(PublicKeyModel),
            typeof(ReportModel),
            typeof(StorageModel),
            typeof(UnAttendanceModel),
            typeof(UnattendanceImageModel),
            typeof(UnAttendanceDriverModel),
            typeof(UsePositionModel),
            typeof(UserModel),
            typeof(WeightModel),
            typeof(WeightTypeModel),
            typeof(MonthBatchNumModel),
            typeof(TareWeightModel),
            typeof(TareImageModel),
            typeof(SyncLogModel),
            typeof(MaterialChangeLogModel)
        );

        Log.Information("数据库表初始化完成");
    }
    catch (Exception ex)
    {
        Log.Error(ex, "数据库表初始化失败");
    }
}

private void InitializeDatabaseConfig()
{
    try
    {
        DatabaseConfig config;
        // 1. 加载配置
        if (File.Exists(_configFilePath))
        {
            var yamlContent = File.ReadAllText(_configFilePath);
            config = _deserializer.Deserialize<DatabaseConfig>(yamlContent);
        }
        else
        {
            config = CreateDefaultConfig();
            SaveConfig(config);
        }

        ConnectionString = config.ConnectionString;
        DbType = config.DbType;

        if (!string.IsNullOrEmpty(ConnectionString) && DbType.HasValue)
        {
            // 2. 初始化 SqlSugar
            SqlSugarScope = CreateSqlSugarScope(ConnectionString, DbType.Value);
        
            // 3. 【新增】精准定位并打印数据库物理路径
            try
            {
                // 使用 SqliteConnectionStringBuilder 解析连接字符串
                var builder = new Microsoft.Data.Sqlite.SqliteConnectionStringBuilder(ConnectionString);
                var dbDataSource = builder.DataSource;
            
                // 将相对路径或文件名转换为绝对物理路径
                var absolutePath = Path.GetFullPath(dbDataSource);
            
                Log.Information("==========================================");
                Log.Information("数据库初始化成功");
                Log.Information("物理文件路径: {DbPath}", absolutePath);
                Log.Information("文件是否存在: {0}", File.Exists(absolutePath) ? "是" : "否 (首次写入时创建)");
                Log.Information("==========================================");
            }
            catch (Exception ex)
            {
                Log.Warning("无法解析数据库文件的物理路径: {Message}", ex.Message);
            }
        }
        else
        {
            ShowErrorAndExit("数据库配置不完整", "请检查 database.yaml 文件。");
        }
    }
    catch (Exception ex)
    {
        Log.Error(ex, "初始化数据库配置失败");
        ShowErrorAndExit("数据库配置初始化失败", ex.Message);
    }
}

private SqlSugarScope CreateSqlSugarScope(string connectionString, DbType dbType)
{
    return new SqlSugarScope(new ConnectionConfig()
        {
            ConnectionString = connectionString,
            DbType = dbType,
            IsAutoCloseConnection = true,
            InitKeyType = InitKeyType.Attribute
        },
        db =>
        {
            // --- 【核心修复】针对 SQLite 开启 WAL 模式 ---
            if (dbType == SqlSugar.DbType.Sqlite)
            {
                // 开启 WAL 模式：允许多个读取者和一个写入者并发，彻底解决文件锁导致的连接崩溃
                db.Ado.ExecuteCommand("PRAGMA journal_mode=WAL;");
                // 设置同步模式为 NORMAL：在 WAL 模式下更安全且性能更好
                db.Ado.ExecuteCommand("PRAGMA synchronous=NORMAL;");
            }
            // ------------------------------------------

            // 1. 记录 SQL 和 参数 (执行前)
            db.Aop.OnLogExecuting = (sql, pars) =>
            {

                try
                {
                    Log.Debug("SQL: {Sql}", sql);

                    if (pars != null && pars.Length > 0)
                    {
                        var paramInfo = string.Join(", ",
                            pars.Select(p => $"{p.ParameterName}={p.Value ?? "NULL"}"));
                        Log.Debug("Parameters: {Params}", paramInfo);
                    }
                }
                catch
                {
                    
                }

            };

            // 2. 记录执行时间和慢查询 (执行后)
            db.Aop.OnLogExecuted = (sql, pars) =>
            {
                var sqlTimeCost = db.Ado.SqlExecutionTime.TotalMilliseconds;
                if (sqlTimeCost > _baseConfig.SlowSqlThreshold)
                {
                    var sqlFormat = UtilMethods.GetSqlString(db.CurrentConnectionConfig.DbType, sql, pars);
                    Log.Warning("【慢查询警告】执行时间: {Time:F2}ms | SQL: {Sql}", sqlTimeCost, sqlFormat);
                }
            };

            db.Aop.OnError = (exp) => { Log.Error(exp, "SQL执行错误"); };

            ConfigureDatabaseSpecificSettings(db, dbType);
        });
}

private void ConfigureDatabaseSpecificSettings(ISqlSugarClient db, DbType dbType)
{
    if (dbType == SqlSugar.DbType.Sqlite)
    {
        db.CodeFirst.SetStringDefaultLength(200);
    }
}

private DatabaseConfig CreateDefaultConfig()
{
    var appDataPath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
    var appDirectory = Path.Combine(appDataPath, "WeightApp", "Data");
    Directory.CreateDirectory(appDirectory);
    var dbPath = Path.Combine(appDirectory, "weight.db");

    return new DatabaseConfig
    {
        ConnectionString = $"Data Source={dbPath};Pooling=true;Cache=Shared;Mode=ReadWrite;Default Timeout=30;",
        DbType = SqlSugar.DbType.Sqlite
    };
}

private void SaveConfig(DatabaseConfig config)
{
    var directory = Path.GetDirectoryName(_configFilePath);
    if (!string.IsNullOrEmpty(directory)) Directory.CreateDirectory(directory);
    File.WriteAllText(_configFilePath, _serializer.Serialize(config));
}

private void ShowErrorAndExit(string title, string message)
{
    Log.Error("{Title}: {Message}", title, message);
    Environment.Exit(1);
}

public void Dispose()
{
    SqlSugarScope?.Dispose();
}

}

public class DatabaseConfig
{
public string ConnectionString { get; set; } = string.Empty;
public DbType DbType { get; set; } = DbType.Sqlite;
} 使用依赖注入 scope 不应该是 多线程安全的吗

[DotNetNext]

单例 SCOPE并不能完全线程安全，如果用到异步方法需要注意用法。

[DotNetNext]

SqlSugarScope 模式 （性能中上）
只需要看下面3条就行了，100%覆盖所有场景

排查1：异步方法wait存在漏写， 一个不能少 。解决方案：找到漏掉的await 建议补上遗漏的await，如果不想补就用db.CopyNew()。

排查2：使用await 返回值必须有Task，不能是Void 这种（表达式中异步只能是Func不能是Action ,比如List.Forech就是Action要改普通forech) 建议改代码，如果不想修改 db改成db.CopyNew()解决

排查3：Task.WhenAll 、 ParallelAsync 和 第三方定时任务库 Job （backgroundservice也算） 使用 db.CopyNew()

方式1: 强制刷新AsyncLocal实现线程安全 点击查看 （优点支持仓储和封装代码）

方式2：db.CopyNew()强制线程安全，缺点不适合仓储和封装代码

CopyNew只有定时任务和Task.WhenAll 第3项必要需要加其他只要代码写对就不需要加

CopyNew要当前业务方法用，不能赋值全局变量，避免写到公用方法（会破坏事务会有问题）

CopyNew如果要用事务看3.1

多租户要用db.CopyNew().GetConnection方法（不能用GetConnectionScope）