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

2023-12-12 08:44
文章标签 接口 性能 编写 数据库 vs 测试 准备 net sqlite crud litedb

本文主要是介绍.NET 8 编写 LiteDB vs SQLite 数据库 CRUD 接口性能测试（准备篇），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

WebAppDbTest 项目准备

  • 项目准备
    • 1、.net cli 创建项目
    • 2、nuget 包引用和项目结构
      • 2.1、项目添加相关 nuget 包
      • 2.2、WebAppDbTest 项目结构
    • 3、项目代码说明
      • 3.1、CSharp/C# 类文件说明
      • 3.2、json 配置文件说明
    • 4、项目运行预览
  • 数据库 .db 文件准备
    • 1、创建 SQLite 数据库
      • 1.1、在 Windows 上安装 SQLite
      • 1.2、创建 SQLite 数据库
    • 2、创建 LiteDB 数据库
      • 2.1、LiteDB.Shell
      • 2.2、创建 LiteDB 数据库

项目准备

此处还是以默认的 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="LiteDB.Async" Version="0.1.7" /><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 构造函数 DIprivate 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 crudTask<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 crudTask<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 构造函数 DIprivate 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;}#endregionprivate 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 CRUDpublic 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 CRUDpublic 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); //此时快照 itemt.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); 
#endregionvar app = builder.Build();#region Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{app.UseSwagger();app.UseSwaggerUI();
}app.UseAuthorization();
app.MapControllers(); 
#endregionapp.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
    sqlite-download

此处我是 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 definitionCREATE 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 collectionsList 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>.indexesList all indexes in this collection<filter> = <field> [=|>|>=|<|<=|!=|like|between] <jsonValue>Filter query syntax<filter> = (<filter> [and|or] <filter> [and|or] ...)Multi queries syntaxTry:> 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 代码会自动创建。

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

这篇关于.NET 8 编写 LiteDB vs SQLite 数据库 CRUD 接口性能测试（准备篇）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/483961。

相关文章

C# LiteDB处理时间序列数据的高性能解决方案

C# LiteDB处理时间序列数据的高性能解决方案

《C#LiteDB处理时间序列数据的高性能解决方案》LiteDB作为.NET生态下的轻量级嵌入式NoSQL数据库,一直是时间序列处理的优选方案,本文将为大家大家简单介绍一下LiteDB处理时间序列数... 目录为什么选择LiteDB处理时间序列数据第一章：LiteDB时间序列数据模型设计1.1 核心设计原则
阅读更多...
使用shardingsphere实现mysql数据库分片方式

使用shardingsphere实现mysql数据库分片方式

《使用shardingsphere实现mysql数据库分片方式》本文介绍如何使用ShardingSphere-JDBC在SpringBoot中实现MySQL水平分库,涵盖分片策略、路由算法及零侵入配置... 目录一、ShardingSphere 简介1.1 对比1.2 核心概念1.3 Sharding-Sp
阅读更多...
深度剖析SpringBoot日志性能提升的原因与解决

深度剖析SpringBoot日志性能提升的原因与解决

《深度剖析SpringBoot日志性能提升的原因与解决》日志记录本该是辅助工具,却为何成了性能瓶颈,SpringBoot如何用代码彻底破解日志导致的高延迟问题,感兴趣的小伙伴可以跟随小编一起学习一下... 目录前言第一章：日志性能陷阱的底层原理1.1 日志级别的“双刃剑”效应1.2 同步日志的“吞吐量杀手”
阅读更多...
Go语言连接MySQL数据库执行基本的增删改查

Go语言连接MySQL数据库执行基本的增删改查

《Go语言连接MySQL数据库执行基本的增删改查》在后端开发中,MySQL是最常用的关系型数据库之一,本文主要为大家详细介绍了如何使用Go连接MySQL数据库并执行基本的增删改查吧... 目录Go语言连接mysql数据库准备工作安装 MySQL 驱动代码实现运行结果注意事项Go语言执行基本的增删改查准备工作
阅读更多...
MySQL 数据库表操作完全指南:创建、读取、更新与删除实战

MySQL 数据库表操作完全指南:创建、读取、更新与删除实战

《MySQL数据库表操作完全指南:创建、读取、更新与删除实战》本文系统讲解MySQL表的增删查改（CURD）操作,涵盖创建、更新、查询、删除及插入查询结果,也是贯穿各类项目开发全流程的基础数据交互原... 目录mysql系列前言一、Create(创建)并插入数据1.1 单行数据 + 全列插入1.2 多行数据
阅读更多...
基于Python编写自动化邮件发送程序(进阶版)

基于Python编写自动化邮件发送程序(进阶版)

《基于Python编写自动化邮件发送程序(进阶版)》在数字化时代,自动化邮件发送功能已成为企业和个人提升工作效率的重要工具,本文将使用Python编写一个简单的自动化邮件发送程序,希望对大家有所帮助... 目录理解SMTP协议基础配置开发环境构建邮件发送函数核心逻辑实现完整发送流程添加附件支持功能实现htm
阅读更多...
MySQL 数据库表与查询操作实战案例

MySQL 数据库表与查询操作实战案例

《MySQL数据库表与查询操作实战案例》本文将通过实际案例,详细介绍MySQL中数据库表的设计、数据插入以及常用的查询操作,帮助初学者快速上手,感兴趣的朋友跟随小编一起看看吧... 目录mysql 数据库表操作与查询实战案例项目一：产品相关数据库设计与创建一、数据库及表结构设计二、数据库与表的创建项目二：员
阅读更多...
Go语言使用net/http构建一个RESTful API的示例代码

Go语言使用net/http构建一个RESTful API的示例代码

《Go语言使用net/http构建一个RESTfulAPI的示例代码》Go的标准库net/http提供了构建Web服务所需的强大功能,虽然众多第三方框架（如Gin、Echo）已经封装了很多功能,但... 目录引言一、什么是 RESTful API？二、实战目标：用户信息管理 API三、代码实现1. 用户数据
阅读更多...
在ASP.NET项目中如何使用C#生成二维码

在ASP.NET项目中如何使用C#生成二维码

《在ASP.NET项目中如何使用C#生成二维码》二维码（QRCode）已广泛应用于网址分享,支付链接等场景,本文将以ASP.NET为示例,演示如何实现输入文本/URL,生成二维码,在线显示与下载的完整... 目录创建前端页面（Index.cshtml）后端二维码生成逻辑（Index.cshtml.cs）总结
阅读更多...
Java慢查询排查与性能调优完整实战指南

Java慢查询排查与性能调优完整实战指南

《Java慢查询排查与性能调优完整实战指南》Java调优是一个广泛的话题,它涵盖了代码优化、内存管理、并发处理等多个方面,：本文主要介绍Java慢查询排查与性能调优的相关资料,文中通过代码介绍的非... 目录1. 事故全景：从告警到定位1.1 事故时间线1.2 关键指标异常1.3 排查工具链2. 深度剖析：
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2