// hzysql 基础Install-Package HzySql -Version 1.0.4.3// hzysql 表达式分析核心（如果要自己扩展 ado 安装此包）Install-Package HzySql.Core -Version 1.0.4.3// mysql 和 sqlserver 全量包 (如果要 使用 sqlserver,mysql 安装此包)Install-Package HzySql.DapperExtend -Version 1.0.4.3// dapper 扩展 包Install-Package HzySql.Dapper -Version 1.0.4.3// sqlserver 包 (如果要 使用 sqlserver 安装此包)Install-Package HzySql.Dapper.SqlServer -Version 1.0.4.3// mysql 包 (如果要 使用 mysql 安装此包)Install-Package HzySql.Dapper.MySql -Version 1.0.4.3

//采用原始 rownumber 分页//var db = new HzySqlSqlServerDapper("Server=.;Database=HzyAdminDB;User ID=sa;Password=123456;MultipleActiveResultSets=true", Core.SqlServer.PagingMode.ROW_NUMBER);//如果事 2008 以上数据库 则使用一下实例对象IHzySql db = new HzySqlSqlServerDapper("Server=.;Database=HzyAdminDB;User ID=sa;Password=123456;MultipleActiveResultSets=true");#region Aop 拦截器 db.UseAopExecuteSqlBeforeCall((sqlCodeContextBase, sqlString) =>{    Console.WriteLine($"执行sql执行之前拦截：\r\n{sqlString}\r\n");});db.UseAopCacheFieldInfoCall((propertyInfo, fieldInfo, type) =>{    if (fieldInfo.Remarks == "用户名称")    {        Console.WriteLine($"缓存表信息之前拦截：拦截到字段备注为 用户名称得属性\r\n{fieldInfo.Remarks},字段名称:{fieldInfo.FieldName},列名:{fieldInfo.ColumnName}\r\n");    }});db.UseAopInsertBeforeCall(() =>{    Console.WriteLine($"执行Insert执行之前拦截!");});db.UseAopUpdateBeforeCall(() =>{    Console.WriteLine($"执行Update执行之前拦截!");});db.UseAopExceptionCall(ex =>{    Console.WriteLine($"异常:{ex.Message}!");});#endregion#region 如果数据 null 需要返回一个 空实例 开关测试HzySqlExtend.ReturnInstance = true;//开启返回空实例   默认关闭var isNull = await db.Query<Sys_AppLog>().FirstAsync();if (isNull != null) Console.WriteLine($"他是一个空实例对象!");#endregion#region 去重 \ 分页var sql11 = db.Query<Member>().Select(w => w.t1).OrderBy(w => w.t1.Member_CreateTime).Distinct().TakePage(1, 20, out int ToalCount).ToSql();Console.WriteLine($"row number 分页状态 分页有去重：\r\n{sql11} 总数：{ToalCount}\r\n");var sql12 = db.Query<Member>().Select(w => w.t1).OrderBy(w => w.t1.Member_CreateTime).TakePage(1, 20).ToSql();Console.WriteLine($"row number 分页状态 分页无去重：\r\n{sql12}\r\n");var sql13 = db.Query<Member>()    .Join<Sys_User>(w => w.t1.Member_UserID == w.t2.User_ID)    .Select(w => w.t1)    .OrderBy(w => w.t1.Member_CreateTime)    .TakePage(1, 20)    .ToSql();Console.WriteLine($"row number 分页状态 分页无去重：\r\n{sql13}\r\n");#endregion#region 插入var model = new Member();model.Member_Name = "hzysql";model.Member_Phone = "18510912266";var insertContext = db.Insert(model);Console.WriteLine($"插入：\r\n{insertContext.ToSql()}\r\n");var id = insertContext.Save();//var id = insertContext.SaveAsync();Console.WriteLine($"插入 >> 存入数据库成功 ：\r\n{id}\r\n");var id1 = insertContext.Save<Guid>();//var id = insertContext.SaveAsync();Console.WriteLine($"插入 >> 存入数据库成功 id1 ：\r\n{id1}\r\n");var memberById = db.FindById<Member>(id1);Console.WriteLine($"插入 >> 存入数据库成功 后 根据 Id 查询出结果 ：\r\n{memberById.Member_Name}\r\n");#endregion#region 修改model = new Member();model.Member_Name = "hzysql6666666666666";model.Member_Phone = "185106767676";var updateContext = db.Update(model)    .Where(w => w.t1.Member_ID == Guid.Empty);Console.WriteLine($"修改1：\r\n{updateContext.ToSql()}\r\n");//updateContext = db.Update(() => new Member{    Member_Name = "hzysql888888888888888"}).Where(w => w.t1.Member_ID == Guid.Empty);Console.WriteLine($"修改2：\r\n{updateContext.ToSql()}\r\n");#endregion#region 删除var deleteContext = db.Delete<Member>()    .Where(w => w.t1.Member_Name == "hzy");var deleteContextSqlCodeContext = deleteContext.ToSqlContext();Console.WriteLine($"删除：\r\n{deleteContext.ToSql()}\r\n");#endregion#region 事务//======== commit 1//try//{//    db.AdoProvider.BeginTransaction();//    model = new Member();//    model.Member_Name = "hzysqlCommit";//    model.Member_Phone = "18510912266";//    model.Member_ID = db.Insert(model).Save<Guid>();//    if (model.Member_ID != Guid.Empty) throw new Exception("事务回滚测试!!");//    db.Update<Member>(t => new Member//    {//        Member_Name = "欧里给Async"//    }, w => w.t1.Member_ID == model.Member_ID)//    .Save();//    db.AdoProvider.Commit();//}//catch (Exception)//{//    db.AdoProvider.Rollback();//}//======== commit 2try{    db.AdoProvider.BeginTransaction();    model = new Member();    model.Member_Name = "hzysqlCommit";    model.Member_Phone = "18510912266";    model.Member_ID = await db.Insert(model).SaveAsync<Guid>();    //if (model.Member_ID != Guid.Empty) throw new Exception("事务回滚测试!!");    await db.Update<Member>(t => new Member    {        Member_Name = "欧里给Async"    })    .Where(w => w.t1.Member_ID == model.Member_ID)    .SaveAsync();    db.AdoProvider.Commit();}catch (Exception ex){    db.AdoProvider.Rollback();    Console.WriteLine($"异常:{ex.Message}!");}var timer = db.Query<Member>().ToSql(out string sqlCode).Max(w => w.t1.Member_CreateTime);#endregion#region 查询 、 Likevar Member_Name = string.Empty;var selectContext = db.Query<Member>()    .Where(w => w.t1.Member_Name.Contains("6666"), !string.IsNullOrWhiteSpace(Member_Name)) // like %value% 后面bool参数代表什么情况下添加这条where    .Where(w => w.t1.Member_Name.StartsWith("6666"))// like value%    .Where(w => w.t1.Member_Name.EndsWith("6666"))// like %value    .Where(w => w.Like(w.t1.Member_Name, "777"))// like %value%    .Where(w => w.LikeStart(w.t1.Member_Name, "777"))// like value%    .Where(w => w.LikeEnd(w.t1.Member_Name, "777"))// like %value    .Where(w => w.t1.Member_Name == ("6666") && w.t1.Member_Phone == "185106767676")    .OrderBy(w => w.t1.Member_ID)    .Top(20)    .Distinct();Console.WriteLine($"Where >> Like ：\r\n{selectContext.ToSql()}\r\n");#endregion#region Invar guids = new List<Guid>() { Guid.NewGuid(), Guid.NewGuid(), Guid.NewGuid() };var inContext = db.Query<Member>()    .Where(w => guids.Contains(w.t1.Member_ID));Console.WriteLine($"Where >> In ：\r\n{inContext.ToSql()}\r\n");var inContext1 = db.Query<Member>()    .Where(w => w.In(w.t1.Member_ID, guids.ToArray()));Console.WriteLine($"Where >> In ：\r\n{inContext1.ToSql()}\r\n");var inContext2 = db.Query<Member>()    .Where(w => w.t1.Member_Name == "999")    .OrderBy(w => w.t1.Member_ID);Console.WriteLine($"Where >> 为子查询准备的一条语句 ：\r\n{inContext2.ToSql()}\r\n");var inContext3 = db.Query<Member>()    .Where(w => w.In(w.t1.Member_Name, inContext2.ToSqlContext()));Console.WriteLine($"Where >> In 子查询 ：\r\n{inContext3.ToSql()}\r\n");#endregion#region NotInvar inContext4 = db.Query<Member>()    .Where(w => !guids.Contains(w.t1.Member_ID));Console.WriteLine($"Where >> Not In ：\r\n{inContext4.ToSql()}\r\n");var inContext5 = db.Query<Member>()    .Where(w => w.NotIn(w.t1.Member_ID, guids.ToArray()));Console.WriteLine($"Where >> Not In ：\r\n{inContext5.ToSql()}\r\n");var inContext6 = db.Query<Member>()    .Where(w => w.t1.Member_Name == "999")    .OrderBy(w => w.t1.Member_ID);Console.WriteLine($"Where >> 为子查询准备的一条语句 ：\r\n{inContext6.ToSql()}\r\n");var inContext7 = db.Query<Member>()    .Where(w => w.NotIn(w.t1.Member_Name, inContext2.ToSqlContext()));Console.WriteLine($"Where >> Not In 子查询 ：\r\n{inContext7.ToSql()}\r\n");#endregion#region Sql 函数var sqlStrContext = db.Query<Member>()    .Where(w => w.HzySql($"{nameof(w.t1.Member_Name)}=5555555555555555555555555555"));Console.WriteLine($"Where >> Sql 返回布尔型 ：\r\n{sqlStrContext.ToSql()}\r\n");var sqlStrContext1 = db.Query<Member>()    .Where(w => w.HzySql<string>($"Convert(varchar(50),{nameof(w.t1.Member_Name)})") == "123");Console.WriteLine($"Where >> Sql 自定义返回类型 ：\r\n{sqlStrContext1.ToSql()}\r\n");var sqlStrContext2 = db.Query<Member>()    .Where(w => w.HzySql($"{nameof(w.t1.Member_Name)}=5555555555555555555555555555"))    .Select(w => new { w.t1.Member_Name, 创建时间 = w.HzySql($"convert(datetime,{nameof(w.t1.Member_CreateTime)})") });Console.WriteLine($"Where >> Sql 返回布尔型 ：\r\n{sqlStrContext2.ToSql()}\r\n");#endregion#region UNIONvar unionSql = db.Query<Member>().Where(w => w.t1.Member_ID == Guid.Empty).Select(w => w.t1).ToSqlContext(out ISqlContext sqlCodeContext);var unionSql1 = db.Query<Member>().Where(w => w.t1.Member_ID == Guid.Empty).Select(w => w.t1).Union(sqlCodeContext);Console.WriteLine($"Where >> UNION ：\r\n{unionSql1.Code}\r\n");var unionSql2 = db.Query<Member>().Where(w => w.t1.Member_ID == Guid.Empty).Select(w => w.t1).UnionAll(sqlCodeContext);Console.WriteLine($"Where >> UNION ALL ：\r\n{unionSql2.Code}\r\n");#endregion#region Joinvar joinSql = db.Query<Member>().Join<Sys_User>(w => w.t1.Member_UserID == w.t2.User_ID).Where(w => w.t2.User_Name == "hzy").Select(w => w.t1);Console.WriteLine($"Where >> Join ：\r\n{joinSql.ToSql()}\r\n");#endregion#region 分页var queryTakePage = db.Query<Member>().OrderBy(w => w.t1.Member_CreateTime).TakePage(1, 20, out int Counts);Console.WriteLine($"TakePage >> 分页 ：\r\n{queryTakePage.ToSql()},总数：{Counts}\r\n");var queryTakePage1 = db.Query<Member>().OrderBy(w => w.t1.Member_CreateTime).TakePage(1, 20);var names = queryTakePage1.ToSqlContext().GetTableNames();Console.WriteLine($"TakePage >> 分页 ：\r\n{queryTakePage1.ToSql()}\r\n");#endregion#region Case When Thenvar CaseWhenThenSql = db.Query<Member>()    .Select(w => new    {        w.t1.Member_ID,        HzySql = w.Case()        .When(() => w.t1.Member_Name == "1", "男")        .When(() => w.t1.Member_Name == "2", "女")        .Else("不男不女")        .End(nameof(w.t1.Member_Name)),        w.t1.Member_CreateTime    });Console.WriteLine($"Where >> Case When Then ：\r\n{CaseWhenThenSql.ToSql()}\r\n");#endregion#region 批量语句操作 batchvar sqlCodeContextBatchList = new List<ISqlContext>();for (int i = 0; i < 5; i++){    var model1 = new Member();    model1.Member_ID = Guid.NewGuid();    model1.Member_Name = "batch";    db.Insert(model1).ToSqlContext(sqlCodeContextBatchList);    db.Update<Member>(table => new Member { Member_Name = "我以前是 batch 现在被修改了！" })        .Where(w => w.t1.Member_ID == model1.Member_ID)        .ToSqlContext(sqlCodeContextBatchList);}var count = db.ExecuteBatch(sqlCodeContextBatchList);if (count == sqlCodeContextBatchList.Count)    Console.WriteLine($"Batch >> 批量操作成功! 语句数量：{sqlCodeContextBatchList.Count} 受影响行数 ：{count}\r\n");else    Console.WriteLine($"Batch >> 批量操作失败! 语句数量：{sqlCodeContextBatchList.Count} 受影响行数 ：{count}\r\n");#endregion#region 返回 元组 Tuple(string name, string phone) tuple = db.Query<Member>()    .Select(w => new { name = w.t1.Member_Name, phone = w.t1.Member_Phone })    .First<(string, string)>();Console.WriteLine($"Where >> First 元组 ：\r\n{tuple.name}，{tuple.phone}\r\n");List<(string name, string phone)> tupleList = db.Query<Member>()    .Select(w => new { name = w.t1.Member_Name, phone = w.t1.Member_Phone })    .ToList<(string, string)>();var stringToList = "";foreach (var item in tupleList) stringToList += $"Where >> ToList[{tupleList.IndexOf(item) + 1}] 元组 ：\r\n{item.name}，{item.phone}\r\n";Console.WriteLine(stringToList);#endregion#region 悲观锁var userList = db.Query<Sys_User>().LockWith().ToList();#endregion#region Adovar dataPar = new List<DataParameter>();dataPar.Add(new DataParameter("member_id", Guid.NewGuid()));var memberList = db.AdoProvider.Query<Member>("select * from member where member_id=@member_id", dataPar);//非参数化示例memberList = db.AdoProvider.Query<Member>("select * from member where member_name='hzy' ", null);//db.AdoProvider.Execute("insert into ....", null);//还有很多方法 自行查看 AdoProvider 对象#endregion#region OrderBy、GroupBy、Having//OrderBydb.Query<Member> ().OrderBy (w => w.t1.Member_Num);db.Query<Member> ().OrderByDesc (w => w.t1.Member_CreateTime);db.Query<Member> ().OrderBy (w => new { w.t1.Member_Num, desc = w.t1.Member_CreateTime });//GroupBydb.Query<Member> ().GroupBy (w => w.t1.Member_ID);db.Query<Member> ().GroupBy (w => new { w.t1.Member_ID, w.t1.Member_Name });//Havingdb.Query<Member> ().Having (w => w.t1.Member_Birthday >= DateTime.Now);db.Query<Member> ().Having (w => w.HzySql ("Member_Birthday >= DateTime.Now"));#endregion