SqlSugar的基本应用（雪花ID，数据审计，CodeFirst）

 

public class SqlSugarHelper //不能是泛型类
    {

        //多库情况下使用说明：
        //如果是固定多库可以传 new SqlSugarScope(List<ConnectionConfig>,db=>{}) 文档：多租户
        //如果是不固定多库 可以看文档Saas分库 
        //用单例模式
        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
        {
            DbType = SqlSugar.DbType.Oracle,
        
            ConnectionString = "Data Source=192.168.1111.222/XXXX;User ID=XXX;Password=XXX;",
            IsAutoCloseConnection = true, //不设成true要手动close

        },


      db =>
      {

          //(A)全局生效配置点，一般AOP和程序启动的配置扔这里面 ，所有上下文生效
          //调试SQL事件，可以删掉
          db.Aop.OnLogExecuting = (sql, pars) =>
          {

              //获取原生SQL推荐 5.1.4.63  性能OK
              Console.WriteLine(UtilMethods.GetNativeSql(sql, pars));

              //获取无参数化SQL 对性能有影响，特别大的SQL参数多的，调试使用
              //Console.WriteLine(UtilMethods.GetSqlString(DbType.SqlServer,sql,pars))

          };

          db.Aop.OnError = (exp) =>
          {
              //获取原生SQL推荐 5.1.4.63  性能OK
              var s = UtilMethods.GetNativeSql(exp.Sql, (SugarParameter[])exp.Parametres);

              LogManage.Logger.SaveLog("错误SQL：" + s);

          };

          db.Aop.OnDiffLogEvent = it =>
          {
              //操作前记录  包含： 字段描述 列名 值 表名 表描述
              var editBeforeData = it.BeforeData;//插入Before为null，之前还没进库
                                                 //操作后记录   包含： 字段描述 列名 值  表名 表描述
              var editAfterData = it.AfterData;
              var sql = it.Sql;
              var parameter = it.Parameters;
              var data = it.BusinessData;//这边会显示你传进来的对象
              var time = it.Time;
              var diffType = it.DiffType;//enum insert 、update and delete  

              LogManage.Logger.SaveLog("数据审计：" + getDiff(it).ToJson());

              //Write logic
          };

          db.Aop.OnLogExecuted = (sql, p) =>
          {
              //执行时间超过1秒
              if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
              {
                  //代码CS文件名
                  var fileName = db.Ado.SqlStackTrace.FirstFileName;
                  //代码行数
                  var fileLine = db.Ado.SqlStackTrace.FirstLine;
                  //方法名
                  var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                  //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息

                  var s = UtilMethods.GetNativeSql(sql, p);

                  LogManage.Logger.SaveLog("执行超过1秒SQL：" + s);
              }
              //相当于EF的 PrintToMiniProfiler
          };



          //多个配置就写下面
          //db.Ado.IsDisableMasterSlaveSeparation=true;

          //注意多租户 有几个设置几个
          //db.GetConnection(i).Aop
      });



        /// <summary>
        /// 比较两个数据对象的修改内容
        /// </summary>
        /// <param name="beforeData"></param>
        /// <param name="afterData"></param>
        /// <returns></returns>
        public static diffLog getDiff(DiffLogModel it)
        {
            List<DiffLogTableInfo> beforeData = it.BeforeData;
            List<DiffLogTableInfo> afterData = it.AfterData;
            string mianID = null;
            if (beforeData != null)
            {
                var keyCoulumn = beforeData[0].Columns.FirstOrDefault(p => p.IsPrimaryKey == true);
                if (keyCoulumn != null)
                {
                    mianID = keyCoulumn.Value.ToString();
                }
            }
            else if (afterData != null)
            {
                var keyCoulumn = afterData[0].Columns.FirstOrDefault(p => p.IsPrimaryKey == true);
                if (keyCoulumn != null)
                {
                    mianID = keyCoulumn.Value.ToString();
                }
            }

            StringBuilder sb = new StringBuilder();
            sb.AppendLine("更改类型：" + it.DiffType);
            if (beforeData != null && afterData != null)
            {
                var befroeColumns = beforeData[0].Columns;
                var afterCloums = afterData[0].Columns;
                foreach (var item in befroeColumns)
                {
                    if (IgnoreColumns.Contains(item.ColumnName.ToLower()))
                        continue;
                    var afterItem = afterCloums.FirstOrDefault(p => p.ColumnName == item.ColumnName && !p.Value.Equals(item.Value));
                    if (afterItem != null)
                    {
                        sb.Append($"[字段:{item.ColumnDescription},修改前:{item.Value}，修改后:{afterItem.Value}]");
                    }
                }
            }
            if (it.DiffType == DiffType.delete)
            {
                sb.Append(" 删除前数据：" + beforeData.ToJson());
            }

            return new diffLog { ID = mianID, diffData = sb.ToString() };

        }

        public class diffLog
        {
            public string ID { get; set; }
            public string diffData { get; set; }
        }

        /// <summary>
        /// 忽略的字段
        /// </summary>
        public static readonly List<string> IgnoreColumns = new List<string>()
        {
            "enable",
            "updatetime",
            "updateuserid",
            "createtime",
            "createuserid",
        };

private void FmSqlSugar_Load(object sender, EventArgs e)
        {
            var d = SqlSugarHelper.Db.GetDate();

            StaticConfig.CompleteInsertableFunc =
            StaticConfig.CompleteUpdateableFunc =
            StaticConfig.CompleteDeleteableFunc = it => //it是具体的对象Updateable<T>等是个object
            {

                //反射的方法可能多个就需要用GetMethods().Where
                var method = it.GetType().GetMethod("EnableDiffLogEvent");
                method.Invoke(it, new object[] { null });

                //技巧：
                //可以定义一个接口只要是这个接口的才走这个逻辑
                //if(db.GetType().GenericTypeArguments[0].GetInterfaces().Any(it=>it==typeof(IDiff))
                //可以根据类型写if
                //if(x.GetType().GenericTypeArguments[0] = typeof(Order)) {   }
            };



            btnAdo_Click(null, null);

        }

        private void btnAdo_Click(object sender, EventArgs e)
        {
            DataTable tb = SqlSugarHelper.Db.Ado.GetDataTable("select * from test_part");

            //this.dataGridView1.DataSource = tb;

            var LST = SqlSugarHelper.Db.Queryable<PatientInfo>().Where(t => t.Name == "A" || t.Name == "B" || t.ID > 1).ToList();

            this.dataGridView1.DataSource = LST;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable tb = new DataTable();
            tb.Columns.Add("sort", typeof(int));
            tb.Columns.Add("ID", typeof(long));

            for (int i = 0; i < 1000; i++)
            {
                //雪花ID
                var id = SnowFlakeSingle.Instance.NextId();
                tb.Rows.Add(new object[] { i + 1, id });
            }

            this.dataGridView1.DataSource = tb;

        }

        private void button2_Click(object sender, EventArgs e)
        {
            //code first
            SqlSugarHelper.Db.CodeFirst.InitTables<PatientInfo>();

            //插入
            PatientInfo pat = new PatientInfo()
            {
                Name = "A",
                Sex = "男",
                Age = "18岁",
                DeptName = "内科2",
                WardName = "内科病区2",
                BedLabel = "13",
                Description = "W",
                AdwDate = "2"
            };

            //返回雪花ID 主键是LONG
            var id = SqlSugarHelper.Db.Insertable<PatientInfo>(pat).ExecuteReturnSnowflakeId();


        }

        private void button3_Click(object sender, EventArgs e)
        {
            //修改审计
            var pat = this.dataGridView1.CurrentRow.DataBoundItem as PatientInfo;
            pat.Name = pat.Name + DateTime.Now.ToString("yyyyMMddHHmmss");

            SqlSugarHelper.Db.Updateable<PatientInfo>(pat).ExecuteCommand();

            //.EnableDiffLogEvent("删除患者")
            SqlSugarHelper.Db.Deleteable<PatientInfo>(pat).ExecuteCommand();

            var id = SqlSugarHelper.Db.Insertable<PatientInfo>(pat).ExecuteReturnSnowflakeId();


        }