HzySql

// 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 2
try
{
    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 查询 、 Like
var 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 In

var 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 NotIn

var 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 UNION

var 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 Join

var 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 Then

var 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 批量语句操作 batch
var 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 Ado

var 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
//OrderBy
db.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 });
//GroupBy
db.Query<Member> ().GroupBy (w => w.t1.Member_ID);
db.Query<Member> ().GroupBy (w => new { w.t1.Member_ID, w.t1.Member_Name });
//Having
db.Query<Member> ().Having (w => w.t1.Member_Birthday >= DateTime.Now);
db.Query<Member> ().Having (w => w.HzySql ("Member_Birthday >= DateTime.Now"));
#endregion