.NET 8 编写 LiteDB vs SQLite 数据库 CRUD 接口性能测试（准备篇）

2023-12-16

WebAppDbTest 项目准备

项目准备
数据库 .db 文件准备

项目准备

此处还是以默认的 WeatherForecast （天气预报）的数据为例，分别对两种类型的数据库做相应的 crud 操作，并对比测试性能。

1、.net cli 创建项目

这里我们使用的 .net8 版本， .net cli 创建 WebAppDbTest 项目，执行命令如下：

dotnet new webapi -o WebAppDbTest --no-https -f net8.0

2、nuget 包引用和项目结构

2.1、项目添加相关 nuget 包

  <ItemGroup>
    <PackageReference Include="FreeSql" Version="3.2.805" />
    <PackageReference Include="FreeSql.Provider.Sqlite" Version="3.2.805" />
    <PackageReference Include="FreeSql.Repository" Version="3.2.805" />
    <PackageReference Include="LiteDB.Async" Version="0.1.7" />
    <PackageReference Include="Mapster.Async" Version="2.0.1" />
    <PackageReference Include="Serilog.AspNetCore" Version="8.0.0" />
    <PackageReference Include="Serilog.Sinks.LiteDB" Version="1.0.29" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.5.0" />
  </ItemGroup>

2.2、WebAppDbTest 项目结构

左边部分为 Nuget 安装的依赖包，右边部分为项目整体目录结构。

WebAppDbTest

3、项目代码说明

3.1、CSharp/C# 类文件说明

1、控制器类（ Controllers ）

LiteDbController.cs ，针对 LiteDb 数据库的 CRUD 方法；
SqliteController.cs ，针对 SQLite 数据库的 CRUD 方法；
WeatherForecastController.cs （项目默认的类）；

2、模型类（ Models ）

ActionExecTime.cs ，记录方法执行时间；
AppLogs.cs ，记录日志信息；
WeatherForecast.cs ，天气预报数据模型；

3、服务类（ Services ）

AppLogsServices.cs ，提供日志写入相关方法；

using System.Text.Json;
using WebAppDbTest.Models;

namespace WebAppDbTest.Services;

/// <summary>
/// 接口规范定义
/// </summary>
public interface IAppLogsServices
{
    /// <summary>
    /// 写入日志信息
    /// </summary>
    /// <param name="logs"></param>
    /// <param name="logLevel"></param>
    /// <returns></returns>
    Task WriteLogAsync(AppLogs logs, LogLevel logLevel = LogLevel.Information);

    /// <summary>
    /// 模型数据序列化json字符串
    /// </summary>
    /// <typeparam name="TData"></typeparam>
    /// <param name="data"></param>
    /// <returns></returns>
    Task<string> JsonSerializeAsync<TData>(TData data);
}

/// <summary>
/// 接口规范实现
/// </summary>
public class AppLogsServices : IAppLogsServices
{
    #region 构造函数 DI
    private readonly ILogger<AppLogsServices> _logger;

    public AppLogsServices(ILogger<AppLogsServices> logger)
    {
        _logger = logger;
    }
    #endregion

    /// <summary>
    /// 写入日志信息
    /// </summary>
    /// <param name="logs"></param>
    /// <param name="logLevel"></param>
    /// <returns></returns>
    public async Task WriteLogAsync(AppLogs logs, LogLevel logLevel = LogLevel.Information)
    {
        logs.LogLevel = logLevel;
        string jsonLogs = await JsonSerializeAsync(logs);

        switch (logLevel)
        {
            case LogLevel.Trace:
                _logger.LogTrace(jsonLogs);
                break;
            case LogLevel.Debug:
                _logger.LogDebug(jsonLogs);
                break;
            case LogLevel.Information:
                _logger.LogInformation(jsonLogs);
                break;
            case LogLevel.Warning:
                _logger.LogWarning(jsonLogs);
                break;
            case LogLevel.Error:
                _logger.LogError(jsonLogs);
                break;
            case LogLevel.Critical:
                _logger.LogCritical(jsonLogs);
                break;
            case LogLevel.None:
                _logger.LogInformation(jsonLogs);
                break;
            default:
                _logger.LogInformation(jsonLogs);
                break;
        }
    }

    /// <summary>
    /// json 序列化
    /// </summary>
    /// <typeparam name="TData"></typeparam>
    /// <param name="data"></param>
    /// <returns></returns>
    public async Task<string> JsonSerializeAsync<TData>(TData data)
    {
        var options = new JsonSerializerOptions
        {
            PropertyNameCaseInsensitive = true
        };
        await using var stream = new MemoryStream();
        await JsonSerializer.SerializeAsync(stream, data, options);
        stream.Position = 0;
        using var reader = new StreamReader(stream);
        return await reader.ReadToEndAsync();
    }
}

WeatherForecastServices.cs ，模拟天气预报的数据；

using LiteDB;
using LiteDB.Async;
using Mapster;
using System.Diagnostics;
using System.Linq.Expressions;
using WebAppDbTest.Models;

namespace WebAppDbTest.Services;

/// <summary>
/// 天气预报接口规范定义
/// </summary>
public interface IWeatherForecastServices
{
    /// <summary>
    /// 获取天气预报概要
    /// </summary>
    /// <returns></returns>
    string GetSummarie();

    /// <summary>
    /// 获取天气预报列表
    /// </summary>
    /// <param name="count"></param>
    /// <returns></returns>
    IEnumerable<WeatherForecast> GetWeatherForecasts(int count);

    #region about litedb crud
    Task<Guid> LiteDbAddSingleAsync<T>(string collectioName, T t);

    Task<int> LiteDbAddBulkAsync<T>(string collectioName, IEnumerable<T> list);

    Task<T> LiteDbGetSingleAsync<T>(string collectioName, Guid id);

    Task<IEnumerable<T>> LiteDbGetAllAsync<T>(string collectioName);

    Task<bool> LiteDbUpdateSingleAsync<T>(string collectioName, T t);

    Task<int> LiteDbUpdateBulkAsync<T>(string collectioName, IEnumerable<T> list);

    Task<bool> LiteDbDeleteSingleAsync<T>(string collectioName, Guid id);

    Task<int> LiteDbDeleteBulkAsync<T>(string collectioName, Expression<Func<T, bool>> predicate);
    #endregion

    #region about sqlite crud
    Task<Guid> SqliteAddSingleAsync<T>(T t) where T : BaseEntity;

    Task<int> SqliteAddBulkAsync<T>(IEnumerable<T> list) where T : BaseEntity;

    Task<T> SqliteGetSingleAsync<T>(Guid id) where T : BaseEntity;

    Task<IEnumerable<T>> SqliteGetAllAsync<T>() where T : BaseEntity;

    Task<bool> SqliteUpdateSingleAsync<T>(T t) where T : BaseEntity, new();

    Task<int> SqliteUpdateBulkAsync<T>(IEnumerable<T> list) where T : BaseEntity, new();

    Task<bool> SqliteDeleteSingleAsync<T>(Guid id) where T : BaseEntity;

    Task<int> SqliteDeleteBulkAsync<T>(List<Guid> ids) where T : BaseEntity;
    #endregion
}

/// <summary>
/// 天气预报接口规范实现，模拟天气预报的数据
/// </summary>
public class WeatherForecastServices : IWeatherForecastServices
{
    #region 构造函数 DI
    private readonly IAppLogsServices _logger;
    private readonly IConfiguration _configuration;
    private readonly IFreeSql _freeSql;
    private readonly IWebHostEnvironment _webHostEnvironment;

    public WeatherForecastServices(IAppLogsServices logger,
        IConfiguration configuration,
        IFreeSql freeSql,
        IWebHostEnvironment webHostEnvironment)
    {
        _logger = logger;
        _configuration = configuration;
        _freeSql = freeSql;
        _webHostEnvironment = webHostEnvironment;
    }
    #endregion

    #region 模拟数据
    /// <summary>
    /// 模拟天气情况摘要数据列表
    /// </summary>
    private static readonly string[] Summaries = new[]
    {
        "Freezing", "Bracing", "Chilly", "Cool", "Mild", "Warm", "Balmy", "Hot", "Sweltering", "Scorching"
    };

    public string GetSummarie() => Summaries[Random.Shared.Next(Summaries.Length)];

    public IEnumerable<WeatherForecast> GetWeatherForecasts(int count)
    {
        if (count <= 0 || count > 1000) count = 1000;

        /** 等效代码如下
        return Enumerable.Range(1, count).Select(index => {
            int temperatureC = Random.Shared.Next(-20, 55);
            var wf = new WeatherForecast
            {
                Id = Guid.NewGuid(),
                //Date = DateOnly.FromDateTime(DateTime.Now.AddDays(index)),
                Date = DateTime.Now.AddDays(index),
                TemperatureC = temperatureC,
                TemperatureF = 32 + (int)(temperatureC / 0.5556),
                Summary = GetSummarie()
            };
            return wf;
        }).ToArray();
        */

        return Enumerable.Range(1, count).Select(index => GetWeatherForecast(index)).ToArray();
    }

    private WeatherForecast GetWeatherForecast(int index) 
    {
        int temperatureC = Random.Shared.Next(-20, 55);
        var wf = new WeatherForecast
        {
            Id = Guid.NewGuid(),
            Date = DateTime.Now.AddDays(index),
            TemperatureC = temperatureC,
            TemperatureF = 32 + (int)(temperatureC / 0.5556),
            Summary = GetSummarie()
        };
        return wf;
    }

    #endregion

    private enum DbFileType { LiteDB, SQLite };

    private string GetConnString(int index, DbFileType dbFileType = DbFileType.LiteDB) 
    {
        string? dbFile = _configuration.GetSection($"DbConfig:{index}:DbFilePath").Value;
        string filePath = Path.Combine(_webHostEnvironment.ContentRootPath, dbFile);

        string dbConnString = string.Empty;
        switch (dbFileType)
        {
            case DbFileType.LiteDB:
                dbConnString = $"Filename={ filePath };Connection=shared;Password=123456";
                break;
            case DbFileType.SQLite:
                dbConnString = $"Data Source={ filePath };Version=3;Pooling=False;Max Pool Size=100";
                break;
            default:
                dbConnString = $"Filename={ filePath };Connection=shared;Password=123456";
                break;
        }

        return dbConnString;
    }

    private static readonly Stopwatch _sw = new();

    /// <summary>
    /// 记录信息
    /// </summary>
    /// <param name="ts">方法执行耗时，单位：毫秒/ms</param>
    /// <param name="appLogs"></param>
    /// <returns></returns>
    private async Task LiteDbWraiteInfoAsync(ActionExecInfo actionExecInfo, AppLogs appLogs)
    {
        // 记录操作方法执行的时间
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<ActionExecInfo>(nameof(ActionExecInfo));
        var item = await collection.InsertAsync(actionExecInfo);
        appLogs.ActionExecInfoId = item.AsGuid;

        // 记录日志
        await _logger.WriteLogAsync(appLogs);
    }

    #region About LiteDb CRUD
    public async Task<Guid> LiteDbAddSingleAsync<T>(string collectioName, T t)
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        var item = await collection.InsertAsync(t);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "AddSingle",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "AddSingle",
            ItemCount = 1,
            OperationInfo = $"[AddSingle] ==> 插入数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return item.AsGuid;
    }

    public async Task<int> LiteDbAddBulkAsync<T>(string collectioName, IEnumerable<T> list)
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个表和 MongoDB 一样的
        var collection = db.GetCollection<T>(collectioName);
        int rcount = await collection.InsertBulkAsync(list);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "AddBulk",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "AddBulk",
            ItemCount = 1,
            OperationInfo = $"[AddBulk] ==> 插入数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }

    public async Task<T> LiteDbGetSingleAsync<T>(string collectioName, Guid id) 
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        var result = await collection.FindByIdAsync(id); // 下面代码等效
        // var item = await collection.FindOneAsync(x => x.Id == id);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "GetSingle",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "GetSingle",
            ItemCount = 1,
            OperationInfo = $"[GetSingle] ==> 查询数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return result;
    }

    public async Task<IEnumerable<T>> LiteDbGetAllAsync<T>(string collectioName)
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        var result = await collection.FindAllAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "GetAll",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "GetAll",
            ItemCount = result.Count(),
            OperationInfo = $"[GetAll] ==> 查询数据:{result.Count()}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return result;
    }

    public async Task<bool> LiteDbUpdateSingleAsync<T>(string collectioName, T t) 
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        bool isOk = await collection.UpdateAsync(t);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "UpdateSingle",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "UpdateSingle",
            ItemCount = 1,
            OperationInfo = $"[UpdateSingle] ==> 更新数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return isOk;
    }

    public async Task<int> LiteDbUpdateBulkAsync<T>(string collectioName, IEnumerable<T> list)
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        int rcount = await collection.UpdateAsync(list);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "UpdateBulk",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "UpdateBulk",
            ItemCount = rcount,
            OperationInfo = $"[UpdateBulk] ==> 更新数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }

    public async Task<bool> LiteDbDeleteSingleAsync<T>(string collectioName, Guid id) 
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        bool isOk = await collection.DeleteAsync(id);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "DeleteSingle",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "DeleteSingle",
            ItemCount = 1,
            OperationInfo = $"[DeleteSingle] ==> 删除数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return isOk;
    }

    public static BsonValue Serialize(Guid id) => new BsonDocument(new Dictionary<string, BsonValue>
    {
        {"_id", id }
    });

    public static Guid Deserialize(BsonValue bsonValue)
    {
        var id = bsonValue["_id"].AsGuid;
        return id;
    }

    public async Task<int> LiteDbDeleteBulkAsync<T>(string collectioName, Expression<Func<T, bool>> predicate) 
    {
        _sw.Start();
        string connectionString = GetConnString(0);
        //打开数据库，如果不存在会自动创建。
        using var db = new LiteDatabaseAsync(connectionString);
        //打开一个集合和 MongoDB 一样的，类似关系数据库的表。
        var collection = db.GetCollection<T>(collectioName);
        //int rcount = await collection.DeleteAllAsync();
        int rcount = await collection.DeleteManyAsync(predicate);
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "DeleteBulk",
            ExecTime = ts,
            Database = "litedb"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "DeleteBulk",
            ItemCount = rcount,
            OperationInfo = $"[DeleteBulk] ==> 删除数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;

    }
    #endregion


    #region About SQLite CRUD
    public async Task<Guid> SqliteAddSingleAsync<T>(T t) where T : BaseEntity
    {
        _sw.Start();
        var rcount = await _freeSql.Insert(t).ExecuteAffrowsAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "AddSingle",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "AddSingle",
            ItemCount = rcount,
            OperationInfo = $"[AddSingle] ==> 插入数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return t.Id;
    }

    public async Task<int> SqliteAddBulkAsync<T>(IEnumerable<T> list) where T : BaseEntity
    {
        _sw.Start();
        int rcount = await _freeSql.Insert(list).ExecuteAffrowsAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "AddBulk",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "AddBulk",
            ItemCount = 1,
            OperationInfo = $"[AddBulk] ==> 插入数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }

    public async Task<T> SqliteGetSingleAsync<T>(Guid id) where T : BaseEntity
    {
        _sw.Start();
        var result = await _freeSql.Select<T>().Where(x => x.Id == id).FirstAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "GetSingle",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "GetSingle",
            ItemCount = 1,
            OperationInfo = $"[GetSingle] ==> 查询数据:1条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return result;
    }

    public async Task<IEnumerable<T>> SqliteGetAllAsync<T>() where T : BaseEntity
    {
        _sw.Start();
        var result = await _freeSql.Select<T>().ToListAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "GetAll",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "GetAll",
            ItemCount = result.Count(),
            OperationInfo = $"[GetAll] ==> 查询数据:{result.Count()}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return result;
    }

    public async Task<bool> SqliteUpdateSingleAsync<T>(T t) where T : BaseEntity, new()
    {
        _sw.Start();

        // 推荐快照模式
        var repo = _freeSql.GetRepository<T>();
        var item = new T { Id = t.Id };
        repo.Attach(item); //此时快照 item
        t.Adapt(item);

        //bool isOk = ReferenceEquals(item, t);
        int rcount = await repo.UpdateAsync(item); //对比快照时的变化

        // 传统模式
        // int rcount = await _freeSql.Update<T>().SetSource(t).IgnoreColumns(a => new { a.Id }).ExecuteAffrowsAsync();

        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "UpdateSingle",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "UpdateSingle",
            ItemCount = rcount,
            OperationInfo = $"[UpdateSingle] ==> 更新数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount > 0;
    }

    public async Task<int> SqliteUpdateBulkAsync<T>(IEnumerable<T> list) where T : BaseEntity, new()
    {
        _sw.Start();

        // 推荐快照模式
        var repo = _freeSql.GetRepository<T>();
        var items = list.Select(x => new T{ Id = x.Id });
        repo.Attach(items); //此时快照 item
        //list.Adapt(items);
        items = list;
        bool isOk = ReferenceEquals(items, list);
        int rcount = await repo.UpdateAsync(items); //对比快照时的变化

        // 传统模式
        //int rcount = await _freeSql.Update<T>().SetSource(list).IgnoreColumns(a => new { a.Id }).ExecuteAffrowsAsync();

        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "UpdateBulk",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "UpdateBulk",
            ItemCount = rcount,
            OperationInfo = $"[UpdateBulk] ==> 更新数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }

    public async Task<bool> SqliteDeleteSingleAsync<T>(Guid id) where T : BaseEntity
    {
        _sw.Start();
        int rcount = await _freeSql.Delete<T>().Where(x => x.Id == id).ExecuteAffrowsAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "DeleteSingle",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "DeleteSingle",
            ItemCount = rcount,
            OperationInfo = $"[DeleteSingle] ==> 删除数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount > 0;
    }

    public async Task<int> SqliteDeleteBulkAsync<T>(List<Guid> ids) where T : BaseEntity
    {
        _sw.Start();
        int rcount = await _freeSql.Delete<T>(ids.ToArray()).ExecuteAffrowsAsync();
        _sw.Stop();
        TimeSpan ts = _sw.Elapsed;

        // 记录操作方法执行的时间
        var actionExecInfo = new ActionExecInfo
        {
            ActionName = "DeleteBulk",
            ExecTime = ts,
            Database = "sqlite"
        };

        // 记录日志
        var appLogs = new AppLogs
        {
            Label = "DeleteBulk",
            ItemCount = rcount,
            OperationInfo = $"[DeleteBulk] ==> 删除数据:{rcount}条，耗时：{ts.TotalMilliseconds}ms."
        };
        await LiteDbWraiteInfoAsync(actionExecInfo, appLogs);

        return rcount;
    }
    #endregion
}

4、程序入口类

Program.cs

using Serilog;
using WebAppDbTest.Services;

var builder = WebApplication.CreateBuilder(args);

//const string OUTPUT_TEMPLATE = "{Timestamp:yyyy-MM-dd HH:mm:ss.fff} <{ThreadId}> [{Level:u3}] {Message:lj}{NewLine}{Exception}";
const string OUTPUT_TEMPLATE = "{Timestamp:yyyy-MM-dd HH:mm:ss.fff} [{Level:u3}] {Message:lj}{NewLine}{Exception}";
char b = Path.DirectorySeparatorChar; // 符号 

// creates custom collection `applog`
Log.Logger = new LoggerConfiguration()
    .MinimumLevel.Information()
    .Enrich.FromLogContext()
    .CreateLogger();

#region Host
builder.Host.ConfigureAppConfiguration((context, config) => {
    string configPath = $"{context.HostingEnvironment.ContentRootPath}{b}AppData{b}Configuration";
    config.SetBasePath(configPath)
      .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
      .AddJsonFile($"appsettings.{context.HostingEnvironment.EnvironmentName}.json", optional: true, reloadOnChange: true)
      .AddEnvironmentVariables();
}).UseSerilog((context, logger) => {
    string liteDbPath = Path.Combine(context.HostingEnvironment.ContentRootPath, $"AppData{b}DataBase{b}LiteDbLogs.db");
    logger.WriteTo.LiteDB(liteDbPath, logCollectionName: "applog");
    logger.WriteTo.Console(outputTemplate: OUTPUT_TEMPLATE);
});
// .UseSerilog(Log.Logger, dispose: true);
#endregion

#region Add services to the container.
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();

// 注册 AppLogsServices
builder.Services.AddScoped<IAppLogsServices, AppLogsServices>();
// 注册 WeatherForecastServices
builder.Services.AddScoped<IWeatherForecastServices, WeatherForecastServices>();

// 注入 Sqlite 类型的 IFreeSql 
//string sqlitePath = $"AppData{b}DataBase{b}SQLiteTest.db";
string sqlitePath = builder.Configuration.GetSection("DbConfig:1:DbFilePath").Value;
string connStr = $"Data Source={Path.Combine(builder.Environment.ContentRootPath, sqlitePath)};Version=3;Pooling=False;Max Pool Size=100";
// Log.Logger.Information(connStr);

IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(dataType: FreeSql.DataType.Sqlite, connectionString: connStr)
    .UseAutoSyncStructure(false) //自动同步实体结构【开发环境必备】，FreeSql不会扫描程序集，只有CRUD时才会生成表。
    //.UseMonitorCommand(cmd => Console.Write(cmd.CommandText)) 
    .UseMonitorCommand(cmd => Log.Logger.Information(cmd.CommandText))
    .Build(); //请务必定义成 Singleton 单例模式
builder.Services.AddSingleton(fsql); 
#endregion

var app = builder.Build();

#region Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseAuthorization();
app.MapControllers(); 
#endregion

app.Run();

3.2、json 配置文件说明

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "DbConfig": [
    {
      "DbType": "LiteDB",
      "DbFilePath": "AppData\\DataBase\\LiteDbTest.db"
    },
    {
      "DbType": "SQLite",
      "DbFilePath": "AppData\\DataBase\\SqliteTest.db"
    }
  ]
}

相关文件代码此处就不再详细说明，感兴趣的可自行查看项目地址：

WebAppDbTest ， https://gitee.com/dolayout/sample/tree/master/code/Sample.WebAppDbTest

4、项目运行预览

启动 WebAppDbTest ， swagger 页面显示如下：

webapp

LiteDB & Sqlite 对应的 CRUD 方法：

webapp-dbtest-crud

数据库 .db 文件准备

1、创建 SQLite 数据库

请访问 SQLite 下载页面，从 Windows 区下载预编译的二进制文件。

1.1、在 Windows 上安装 SQLite

SQLite 下载， https://www.sqlite.org/download.html

此处我是 Windows 11 x64 环境，下载文件分别如下：

sqlite-dll-win-x64-3440200.zip
sqlite-tools-win-x64-3440200.zip

把下载文件拷贝到 D 盘并解压文件，如下所示：

sqlite

文件夹默认文件说明：

sqlite-dll-win-x64-3440200 文件夹默认包含： sqlite3.def 和 sqlite3.dll 文件；
sqlite-tools-win-x64-3440200 文件夹默认包含： sqldiff.exe 、 sqlite3.exe 和 sqlite3_analyzer.exe 文件；

可以把 D:\sqlite\sqlite-tools-win-x64-3440200 添加到 PATH 环境变量，最后在命令提示符下，使用 sqlite3 命令，此处我就不添加环境变量了，直接双击 sqlite.exe 文件将显示如下结果：

sqlite-tools-win-x64

1.2、创建 SQLite 数据库

依据终端提示信息，输入命令创建数据库 SQLiteTest.db 文件，执行如下：

sqlite> .open SQLiteTest.db

查看 sqlite 更多命令帮助信息：

SQLite version 3.44.2 2023-11-24 11:41:44 (UTF-16 console I/O)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open SQLiteTest.db
sqlite> PRAGMA key = '123456';
sqlite> .help
.archive ...             Manage SQL archives
.auth ON|OFF             Show authorizer callbacks
.backup ?DB? FILE        Backup DB (default "main") to FILE
.bail on|off             Stop after hitting an error.  Default OFF
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output since .testcase does not match
.clone NEWDB             Clone data into NEWDB from the existing database
.connection [close] [#]  Open or close an auxiliary database connection
.crnl on|off             Translate \n to \r\n.  Default ON
.databases               List names and files of attached databases
.dbconfig ?op? ?val?     List or change sqlite3_db_config() options
.dbinfo ?DB?             Show status information about the database
.dump ?OBJECTS?          Render database content as SQL
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.explain ?on|off|auto?   Change the EXPLAIN formatting mode.  Default: auto
.filectrl CMD ...        Run various sqlite3_file_control() operations
.fullschema ?--indent?   Show schema and the content of sqlite_stat tables
.headers on|off          Turn display of headers on or off
.help ?-all? ?PATTERN?   Show help text for PATTERN
.import FILE TABLE       Import data from FILE into TABLE
.indexes ?TABLE?         Show names of indexes
.limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
.lint OPTIONS            Report potential schema issues.
.load FILE ?ENTRY?       Load an extension library
.log FILE|on|off         Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?OPTIONS?     Set output mode
.nonce STRING            Suspend safe mode for one command if nonce matches
.nullvalue STRING        Use STRING in place of NULL values
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE is omitted
.parameter CMD ...       Manage SQL parameter bindings
.print STRING...         Print literal STRING
.progress N              Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE    Replace the standard prompts
.quit                    Stop interpreting input stream, exit if primary.
.read FILE               Read input from FILE or command output
.recover                 Recover as much data as possible from corrupt db.
.restore ?DB? FILE       Restore content of DB (default "main") from FILE
.save ?OPTIONS? FILE     Write database to FILE (an alias for .backup ...)
.scanstats on|off|est    Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN?        Show the CREATE statements matching PATTERN
.separator COL ?ROW?     Change the column and row separators
.session ?NAME? CMD ...  Create or control sessions
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a system shell
.tables ?TABLE?          List names of tables matching LIKE pattern TABLE
.timeout MS              Try opening locked tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.version                 Show source, library and compiler versions
.vfsinfo ?AUX?           Information about the top-level VFS
.vfslist                 List all available VFSes
.vfsname ?AUX?           Print the name of the VFS stack
.width NUM1 NUM2 ...     Set minimum column widths for columnar output
sqlite>

此时在当前目录下， SQLite 的数据库文件 SQLiteTest.db 文件就创建好了。

接下来使用 dbeaver-ce 工具连接数据库文件测试：

连接测试

sqlite 数据表脚本：

-- WeatherForecast definition

CREATE TABLE "WeatherForecast" (  
  "Id" CHARACTER(36) NOT NULL, 
  "Date" TEXT NOT NULL, 
  "TemperatureC" INTEGER NOT NULL, 
  "TemperatureF" INTEGER NOT NULL, 
  "Summary" NVARCHAR(255), 
  PRIMARY KEY ("Id")
);

2、创建 LiteDB 数据库

2.1、LiteDB.Shell

LiteDB 项目包含一个简单的控制台应用程序 ( LiteDB.Shell.exe )，可用于查看、更新以及测试你的数据，在处理你的数据库时非常有用。

LiteDB.Shell 项目地址， https://github.com/mustakimali/LiteDB.Shell.NetCore

2.2、创建 LiteDB 数据库

使用 LiteDB.Shell 创建数据库，执行如下命令：

> open <filename>|<connectionString>
    Open/Crete a new database

基本 Shell 命令，尝试使用 help full 执行所有命令：

Basic Shell Commands - try `help full` for all commands
=======================================================
> open <filename>|<connectionString>
    Open/Crete a new database

> show collections
    List all collections inside database

> db.<collection>.insert <jsonDoc>
    Insert a new document into collection

> db.<collection>.update <jsonDoc>
    Update a document inside collection

> db.<collection>.delete <filter>
    Delete documents using a filter clausule (see find)

> db.<collection>.find <filter> [skip N][limit N]
    Show filtered documents based on index search

> db.<collection>.count <filter>
    Show count rows according query filter

> db.<collection>.ensureIndex <field> [true|{options}]
    Create a new index document field. For unique key, use true

> db.<collection>.indexes
    List all indexes in this collection

<filter> = <field> [=|>|>=|<|<=|!=|like|between] <jsonValue>
    Filter query syntax

<filter> = (<filter> [and|or] <filter> [and|or] ...)
    Multi queries syntax

Try:
 > db.customers.insert { _id:1, name:"John Doe", age: 37 }
 > db.customers.ensureIndex name
 > db.customers.find name like "John"
 > db.customers.find name like "John" and _id between [0, 100] limit 10

说明： litedb 数据库和数据集无需创建，当不存在时执行 crud 代码会自动创建。

好了先到这里，我们就把测试项目准备好了，关于接口测试性能对比，下篇再续，敬请观看。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

DB数据库

NET Core 跨平台

数据库

NET

SQLite