SqlSugar的使用日常

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

        }

    }

}

// 连接 MySQL 数据库，添加数据库上下文

builder.Services.AddSqlsugarSetup(builder.Configuration, "MySQLConnection");

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

}