热门标签
热门文章
- 1UE5 C++ 插件开发 0.专栏介绍_ue插件开发
- 2更改unity中Spine2D动画的颜色_unity spine换色
- 3小程序左右滑动切换页面_新手uniapp框架纯手写微信小程序开发左侧滑动菜单
- 4【MySQL】MySQL内置函数--日期函数/字符串函数/数学函数/其他相关函数
- 5python学习教程（十四）range和xrange_python rage
- 6docker容器管理平台—portainer_portainer终端控制台
- 7vue3+ElementPlus后台管理搭建
- 8python sift 特征匹配 图片相似度_sift相似度比对
- 9图像各通道特征提取_图像的通道特征提取
- 10Python爬虫学习笔记（二）：网页的信息_网站爬虫网页信息一般包括哪些信息
当前位置: article > 正文
.NET 8 编写 LiteDB vs SQLite 数据库 CRUD 接口性能测试（准备篇）
作者：你好赵伟 | 2024-02-24 22:37:31
赞
踩
litedb
项目准备
此处还是以默认的 WeatherForecast（天气预报） 的数据为例，分别对两种类型的数据库做相应的 crud 操作，并对比测试性能。
1、.net cli 创建项目
这里我们使用的 .net8 版本，.net cli 创建 WebAppDbTest 项目，执行命令如下：
dotnet new webapi -o WebAppDbTest --no-https -f net8.0
- 1
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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
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 构造函数 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();
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
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
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
- 186
- 187
- 188
- 189
- 190
- 191
- 192
- 193
- 194
- 195
- 196
- 197
- 198
- 199
- 200
- 201
- 202
- 203
- 204
- 205
- 206
- 207
- 208
- 209
- 210
- 211
- 212
- 213
- 214
- 215
- 216
- 217
- 218
- 219
- 220
- 221
- 222
- 223
- 224
- 225
- 226
- 227
- 228
- 229
- 230
- 231
- 232
- 233
- 234
- 235
- 236
- 237
- 238
- 239
- 240
- 241
- 242
- 243
- 244
- 245
- 246
- 247
- 248
- 249
- 250
- 251
- 252
- 253
- 254
- 255
- 256
- 257
- 258
- 259
- 260
- 261
- 262
- 263
- 264
- 265
- 266
- 267
- 268
- 269
- 270
- 271
- 272
- 273
- 274
- 275
- 276
- 277
- 278
- 279
- 280
- 281
- 282
- 283
- 284
- 285
- 286
- 287
- 288
- 289
- 290
- 291
- 292
- 293
- 294
- 295
- 296
- 297
- 298
- 299
- 300
- 301
- 302
- 303
- 304
- 305
- 306
- 307
- 308
- 309
- 310
- 311
- 312
- 313
- 314
- 315
- 316
- 317
- 318
- 319
- 320
- 321
- 322
- 323
- 324
- 325
- 326
- 327
- 328
- 329
- 330
- 331
- 332
- 333
- 334
- 335
- 336
- 337
- 338
- 339
- 340
- 341
- 342
- 343
- 344
- 345
- 346
- 347
- 348
- 349
- 350
- 351
- 352
- 353
- 354
- 355
- 356
- 357
- 358
- 359
- 360
- 361
- 362
- 363
- 364
- 365
- 366
- 367
- 368
- 369
- 370
- 371
- 372
- 373
- 374
- 375
- 376
- 377
- 378
- 379
- 380
- 381
- 382
- 383
- 384
- 385
- 386
- 387
- 388
- 389
- 390
- 391
- 392
- 393
- 394
- 395
- 396
- 397
- 398
- 399
- 400
- 401
- 402
- 403
- 404
- 405
- 406
- 407
- 408
- 409
- 410
- 411
- 412
- 413
- 414
- 415
- 416
- 417
- 418
- 419
- 420
- 421
- 422
- 423
- 424
- 425
- 426
- 427
- 428
- 429
- 430
- 431
- 432
- 433
- 434
- 435
- 436
- 437
- 438
- 439
- 440
- 441
- 442
- 443
- 444
- 445
- 446
- 447
- 448
- 449
- 450
- 451
- 452
- 453
- 454
- 455
- 456
- 457
- 458
- 459
- 460
- 461
- 462
- 463
- 464
- 465
- 466
- 467
- 468
- 469
- 470
- 471
- 472
- 473
- 474
- 475
- 476
- 477
- 478
- 479
- 480
- 481
- 482
- 483
- 484
- 485
- 486
- 487
- 488
- 489
- 490
- 491
- 492
- 493
- 494
- 495
- 496
- 497
- 498
- 499
- 500
- 501
- 502
- 503
- 504
- 505
- 506
- 507
- 508
- 509
- 510
- 511
- 512
- 513
- 514
- 515
- 516
- 517
- 518
- 519
- 520
- 521
- 522
- 523
- 524
- 525
- 526
- 527
- 528
- 529
- 530
- 531
- 532
- 533
- 534
- 535
- 536
- 537
- 538
- 539
- 540
- 541
- 542
- 543
- 544
- 545
- 546
- 547
- 548
- 549
- 550
- 551
- 552
- 553
- 554
- 555
- 556
- 557
- 558
- 559
- 560
- 561
- 562
- 563
- 564
- 565
- 566
- 567
- 568
- 569
- 570
- 571
- 572
- 573
- 574
- 575
- 576
- 577
- 578
- 579
- 580
- 581
- 582
- 583
- 584
- 585
- 586
- 587
- 588
- 589
- 590
- 591
- 592
- 593
- 594
- 595
- 596
- 597
- 598
- 599
- 600
- 601
- 602
- 603
- 604
- 605
- 606
- 607
- 608
- 609
- 610
- 611
- 612
- 613
- 614
- 615
- 616
- 617
- 618
- 619
- 620
- 621
- 622
- 623
- 624
- 625
- 626
- 627
- 628
- 629
- 630
- 631
- 632
- 633
- 634
- 635
- 636
- 637
- 638
- 639
- 640
- 641
- 642
- 643
- 644
- 645
- 646
- 647
- 648
- 649
- 650
- 651
- 652
- 653
- 654
- 655
- 656
- 657
- 658
- 659
- 660
- 661
- 662
- 663
- 664
- 665
- 666
- 667
- 668
- 669
- 670
- 671
- 672
- 673
- 674
- 675
- 676
- 677
- 678
- 679
- 680
- 681
- 682
- 683
- 684
- 685
- 686
- 687
- 688
- 689
- 690
- 691
- 692
- 693
- 694
- 695
- 696
- 697
- 698
- 699
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();
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
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"
}
]
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
相关文件代码此处就不再详细说明，感兴趣的可自行查看项目地址：
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
- 1
查看 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>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
此时在当前目录下，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")
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
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
- 1
- 2
基本 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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
说明：
litedb数据库和数据集无需创建，当不存在时执行crud代码会自动创建。
好了先到这里，我们就把测试项目准备好了，关于接口测试性能对比，下篇再续，敬请观看。
推荐阅读
相关标签
