SqlSugar的使用日常

这两天从FreeSql转SqlSugar，记录一些日常

添加引用

<PackageReference Include="MySql.Data" Version="8.0.28" />
<PackageReference Include="SqlSugarCore" Version="5.0.5.4" />

上下文类

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using SqlSugar;

namespace DataService.DbContext
{
    /// <summary>
    /// 数据库上下文类
    /// </summary>
    public static class SqlSugarContext
    {
        /// <summary>
        /// 获取连接实例
        /// </summary>
        /// <param name="services"></param>
        /// <param name="configuration"></param>
        public static void AddSqlsugarSetup(this IServiceCollection services, IConfiguration configuration, string dbName)
        {
            // SqlSugarScope在此处使用AOP无效
            SqlSugarScope sqlSugar = new SqlSugarScope(new ConnectionConfig()
            {
                ConnectionString = configuration.GetConnectionString(dbName),
                DbType = DbType.MySql,// 切换数据库类型
                IsAutoCloseConnection = true,// 自动释放数据务，如果存在事务，在事务结束后释放
                InitKeyType = InitKeyType.Attribute// 从实体特性中读取主键自增列信息
            });
            #region AOP
            // SQL执行前
            //sqlSugar.Aop.OnLogExecuting = (sql, pars) =>
            //{
            //    List<Dictionary<string, object>> result = new List<Dictionary<string, object>>();
            //    foreach (var row in pars)
            //    {
            //        Dictionary<string, object> value = new Dictionary<string, object>();
            //        value.Add(row.ParameterName, row.Value);
            //        result.Add(value);
            //    }
            //    Console.WriteLine("{0}|SQL执行语句：{1}，参数：{2}", DateTime.Now, sql, JsonConvert.SerializeObject(result));
            //};
            // SQL执行完
            //sqlSugar.Aop.OnLogExecuted = (sql, pars) =>
            //{
            //    // 执行完了可以输出SQL执行时间 (OnLogExecutedDelegate)
            //    Console.WriteLine("{0}|SQL执行时间：{1}", DateTime.Now, sqlSugar.Ado.SqlExecutionTime.ToString());
            //};
            #endregion
            services.AddSingleton<ISqlSugarClient>(sqlSugar);// 这边是SqlSugarScope用AddSingleton
        }
    }
}

注入SqlSugar

//连接MySQL数据库，添加数据库上下文
builder.Services.AddSqlsugarSetup(builder.Configuration, "MySQLConnection");

appsettings.json添加本地数据库连接字符串

"ConnectionStrings": {
    "MySQLConnection": "server=127.0.0.1;uid=root;pwd=123456;port=3306;database=world;SslMode=None"
  }

今天本打算写一个树形结构的菜单层级处理，看到SqlSugar官方文档有自带的方法ToTree

简单使用一下，以下是数据库结构

实体类数据代码

using System.Collections.Generic;
using SqlSugar;

namespace DataModel.Table
{
    /// <summary>
    /// 菜单表
    /// </summary>
    [SugarTable("menuinfo")]
    public partial class MenuInfo
    {
        /// <summary>
        /// 主键
        /// </summary>
        [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public int Id { get; set; }

        /// <summary>
        /// 菜单名称
        /// </summary>
        public string MenuName { get; set; }

        /// <summary>
        /// 父级Id
        /// </summary>
        public int? ParentId { get; set; }

        /// <summary>
        /// 不验证数据库，做树形结构使用
        /// </summary>
        [SqlSugar.SugarColumn(IsIgnore = true)]
        public List<MenuInfo> Child { get; set; }
    }
}

示例代码

private readonly ISqlSugarClient _dbContext;
public HomeDataService(ISqlSugarClient dbContext)
{
    _dbContext = dbContext;
}
public void SqlSugarTest()
{
    //SqlSugar自带树形结构
    Stopwatch sw = new Stopwatch();
    sw.Start();
    var tree = _dbContext.Queryable<MenuInfo>().ToTree(s => s.Child, s => s.ParentId, 0);
    string json = JsonConvert.SerializeObject(tree);
    sw.Stop();
    //Linq自带Foreach实现递归遍历树形结构
    Stopwatch sw2 = new Stopwatch();
    sw2.Start();
    var list = _dbContext.Queryable<MenuInfo>().ToList();
    list.ForEach(s => s.Child = list.Where(x => x.ParentId == s.Id).ToList());
    var tree2 = list.Count > 0 ? list.Where(s => s.ParentId == list.OrderBy(s => s.ParentId).ToList().FirstOrDefault().ParentId).ToList() : null;
    string json2 = JsonConvert.SerializeObject(tree2);
    sw2.Stop();
    Console.WriteLine("SqlSugar耗时：{0}ms，数据：{1}", sw.ElapsedTicks / (decimal)Stopwatch.Frequency * 1000, json);//SqlSugar
    Console.WriteLine("Linq Foreach耗时：{0}ms，数据：{1}", sw2.ElapsedTicks / (decimal)Stopwatch.Frequency * 1000, json2);//Linq Foreach
}

运行五次并打印日志

SqlSugar耗时：14.0168000ms
Linq Foreach耗时：7.8033000ms
SqlSugar耗时：1.6285000ms
Linq Foreach耗时：1.0115000ms
SqlSugar耗时：0.8813000ms
Linq Foreach耗时：0.7391000ms
SqlSugar耗时：4.4206000ms
Linq Foreach耗时：4.1635000ms
SqlSugar耗时：1.015000ms
Linq Foreach耗时：0.8329000ms

结论：SqlSugar这个ToTree方法处理树形结构很方便，但性能优化不如原生Foreach