SqlSugar的基本应用（雪花ID，数据审计，CodeFirst）
原创
©著作权归作者所有：来自51CTO博客作者超级帅虫虫的原创作品，请联系作者获取转载授权，否则将追究法律责任
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 => == "A" || == "B" || > 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;
pa = pa + 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();
}
