.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="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
安装的依赖包,右边部分为项目整体目录结构。
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
页面显示如下:
LiteDB & Sqlite
对应的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-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
文件将显示如下结果:
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 接口性能测试(准备篇)
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、在 W…...

2024 年,新程序员如何与AI共赢!!
🌷🍁 博主猫头虎 带您 Go to New World.✨🍁 🦄 博客首页——猫头虎的博客🎐 🐳《面试题大全专栏》 文章图文并茂🦕生动形象🦖简单易学!欢迎大家来踩踩~🌺 &a…...

Debian 系统镜像下载
最近在看一些网络相关的文章需要用到 debian 11.x 的系统网上找了好多都发下载,在官网看一下 有个 11.8 的版本我无法下载,提示被最新的 debian-12.4.0 所代替,于是找到了这个链接 Index of /cdimage/unofficial/non-free/cd-including-fi…...

数据结构和算法(全)
1.了解数据结构和算法 1.1 二分查找 二分查找(Binary Search)是一种在有序数组中查找特定元素的搜索算法。它的基本思想是将数组分成两半,然后比较目标值与中间元素的大小关系,从而确定应该在左半部分还是右半部分继续查找。这个…...
Vue项目中WebSocket封装
WEBSOCKET 封装引入初始化使用 封装 utils下建立WebSocketManager.js class WebSocketManager {constructor() {this.url null;this.websocket null;this.isConnected false;this.listeners {onopen: [],onmessage: [],onclose: [],onerror: [],};this.reconnectionOptio…...

018 OpenCV 人脸检测
目录 一、环境 二、分类器原理 2.1、概述 2.2、工作原理 三、人脸检测代码 一、环境 本文使用环境为: Windows10Python 3.9.17opencv-python 4.8.0.74 二、分类器原理 CascadeClassifier是OpenCV(开源计算机视觉库)中的一个强大的类…...
Etcd实战(一)-部署etcd集群
1 概述 etcd是一个高可用的分布式键值存储系统,是CoreOS(现在隶属于Red Hat)公司开发的一个开源项目。它提供了一个简单的接口来存储和检索键值对数据,并使用Raft协议实现了分布式一致性。etcd广泛应用于Docker、Kubernetes等分布…...
Python绘制一个简单的圣诞树
在Python中,你可以使用基本的打印语句和循环来绘制一个简单的圣诞树。以下是一个例子: def draw_christmas_tree(height):for i in range(height):print( * (height - i - 1) +...

【CANoe】CANoe中使用RS232
文章目录 1、CANoe中自带示例2、示例讲解2.1CANoe自带Port A和Port B通讯2.2CANoe自带Port A和串口助手通讯 1、CANoe中自带示例 我使用的事CANoe12,RS232路径如下: C:\Users\Public\Documents\Vector\CANoe\Sample Configurations 12.0.75\IO_HIL\RS23…...

Springboot内置Tomcat线程数优化
Springboot内置Tomcat线程数优化 # 等待队列长度,默认100。队列也做缓冲池用,但也不能无限长,不但消耗内存,而且出队入队也消耗CPU server.tomcat.accept-count1000 # 最大工作线程数,默认200。(4核8g内存…...
vue+django 开发环境跨域前后端联调配置
vue环境是127.0.0.1:8080,django环境是127.0.0.1:8000 要解决url相对路径和Axios跨域权限问题。 注意:程序发起了一个 POST 请求,但请求的 URL 没有以斜杠结尾。Django 默认设置是无法执行重定向到带斜杠 URL的。例如:url http:/…...

Apache+mod_jk模块代理Tomcat容器
一、背景介绍 最近在看Tomcat运行架构原理, 正好遇到了AJP协议(Apache JServ Protocol). 顺道来研究下这个AJP协议和具体使用方法. 百度百科是这么描述AJP协议的: AJP(Apache JServ Protocol)是定向包协议。因为性能原因,使用二进制格式来传输…...

Nginx访问FTP服务器文件的时效性/安全校验
背景 FTP文件服务器在我们日常开发中经常使用,在项目中我们经常把FTP文件下载到内存中,然后转为base64给前端进行展示。如果excel中也需要导出图片,数据量大的情况下会直接返回一个后端的开放接口地址,然后在项目中对接口的参数进…...
【VSCode】自定义配置
VSCode自定义配置 Visual Studio Code (VSCode) 是一个强大的开源代码编辑器,支持丰富的自定义配置。下面是一些常见的自定义配置选项,你可以根据个人喜好和工作流程进行调整: 1. 主题和配色方案: 在 “settings.json” 中设置:…...

SpringBoot整合Kafka (一)
📑前言 本文主要讲了SpringBoot整合Kafka文章⛺️ 🎬作者简介:大家好,我是青衿🥇 ☁️博客首页:CSDN主页放风讲故事 🌄每日一句:努力一点,优秀一点 目录 文章目录 &…...

随机分词与tokenizer(BPE->BBPE->Wordpiece->Unigram->sentencepiece->bytepiece)
0 tokenizer综述 根据不同的切分粒度可以把tokenizer分为: 基于词的切分,基于字的切分和基于subword的切分。 基于subword的切分是目前的主流切分方式。subword的切分包括: BPE(/BBPE), WordPiece 和 Unigram三种分词模型。其中WordPiece可以认为是一种特殊的BPE。完…...

成都工业学院Web技术基础(WEB)实验四:CSS3布局应用
写在前面 1、基于2022级计算机大类实验指导书 2、代码仅提供参考,前端变化比较大,按照要求,只能做到像,不能做到一模一样 3、图片和文字仅为示例,需要自行替换 4、如果代码不满足你的要求,请寻求其他的…...

TikTok科技趋势:平台如何引领数字社交革命?
TikTok作为一款颠覆性的短视频应用,不仅改变了用户的娱乐方式,更在数字社交领域引领了一场革命。本文将深入探讨TikTok在科技趋势方面的引领作用,分析其在数字社交革命中的关键角色,以及通过技术创新如何不断满足用户需求…...

【上海大学数字逻辑实验报告】六、时序电路
一、 实验目的 掌握同步二进制计数器和移位寄存器的原理。学会用分立元件构成2位同步二进制加计数器。学会在Quartus II上设计单向移位寄存器。学会在Quartus II上设计环形计数器。 二、 实验原理 同步计数器是指计数器中的各触发器的时钟脉冲输入端连接在一起,接…...
docker版zerotier-planet服务端搭建
1:ZeroTier 介绍2:为什么要自建PLANET 服务器3:开始安装 3.1:准备条件 3.1.1 安装git3.1.2 安装docker3.1.3 启动docker3.2:下载项目源码3.3:执行安装脚本3.4 下载 planet 文件3.5 新建网络 3.5.1 创建网络4.客户端配置 4.1 Windows 配置 4.2 加入网络4.2 Linux 客户端4.…...

Linux应用开发之网络套接字编程(实例篇)
服务端与客户端单连接 服务端代码 #include <sys/socket.h> #include <sys/types.h> #include <netinet/in.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <arpa/inet.h> #include <pthread.h> …...

Redis相关知识总结(缓存雪崩,缓存穿透,缓存击穿,Redis实现分布式锁,如何保持数据库和缓存一致)
文章目录 1.什么是Redis?2.为什么要使用redis作为mysql的缓存?3.什么是缓存雪崩、缓存穿透、缓存击穿?3.1缓存雪崩3.1.1 大量缓存同时过期3.1.2 Redis宕机 3.2 缓存击穿3.3 缓存穿透3.4 总结 4. 数据库和缓存如何保持一致性5. Redis实现分布式…...

大型活动交通拥堵治理的视觉算法应用
大型活动下智慧交通的视觉分析应用 一、背景与挑战 大型活动(如演唱会、马拉松赛事、高考中考等)期间,城市交通面临瞬时人流车流激增、传统摄像头模糊、交通拥堵识别滞后等问题。以演唱会为例,暖城商圈曾因观众集中离场导致周边…...

2021-03-15 iview一些问题
1.iview 在使用tree组件时,发现没有set类的方法,只有get,那么要改变tree值,只能遍历treeData,递归修改treeData的checked,发现无法更改,原因在于check模式下,子元素的勾选状态跟父节…...
【论文笔记】若干矿井粉尘检测算法概述
总的来说,传统机器学习、传统机器学习与深度学习的结合、LSTM等算法所需要的数据集来源于矿井传感器测量的粉尘浓度,通过建立回归模型来预测未来矿井的粉尘浓度。传统机器学习算法性能易受数据中极端值的影响。YOLO等计算机视觉算法所需要的数据集来源于…...

NLP学习路线图(二十三):长短期记忆网络(LSTM)
在自然语言处理(NLP)领域,我们时刻面临着处理序列数据的核心挑战。无论是理解句子的结构、分析文本的情感,还是实现语言的翻译,都需要模型能够捕捉词语之间依时序产生的复杂依赖关系。传统的神经网络结构在处理这种序列依赖时显得力不从心,而循环神经网络(RNN) 曾被视为…...

selenium学习实战【Python爬虫】
selenium学习实战【Python爬虫】 文章目录 selenium学习实战【Python爬虫】一、声明二、学习目标三、安装依赖3.1 安装selenium库3.2 安装浏览器驱动3.2.1 查看Edge版本3.2.2 驱动安装 四、代码讲解4.1 配置浏览器4.2 加载更多4.3 寻找内容4.4 完整代码 五、报告文件爬取5.1 提…...

C++使用 new 来创建动态数组
问题: 不能使用变量定义数组大小 原因: 这是因为数组在内存中是连续存储的,编译器需要在编译阶段就确定数组的大小,以便正确地分配内存空间。如果允许使用变量来定义数组的大小,那么编译器就无法在编译时确定数组的大…...
Fabric V2.5 通用溯源系统——增加图片上传与下载功能
fabric-trace项目在发布一年后,部署量已突破1000次,为支持更多场景,现新增支持图片信息上链,本文对图片上传、下载功能代码进行梳理,包含智能合约、后端、前端部分。 一、智能合约修改 为了增加图片信息上链溯源,需要对底层数据结构进行修改,在此对智能合约中的农产品数…...

深度学习水论文:mamba+图像增强
🧀当前视觉领域对高效长序列建模需求激增,对Mamba图像增强这方向的研究自然也逐渐火热。原因在于其高效长程建模,以及动态计算优势,在图像质量提升和细节恢复方面有难以替代的作用。 🧀因此短时间内,就有不…...