代码拉取完成,页面将自动刷新
拉姆达表达式 转 Sql 语句 可自定义 Ado
DbFrame源码 请查看分支 DbFrameOld:https://gitee.com/hzy6/HzySql/tree/DbFrameOld/
1、可直接使用 解析 sql 功能
2、可自行扩展自己喜欢的 Ado
Nuget包 》可nuget搜索 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
使用项目例子:https://gitee.com/hzy6/HZY.AdminSpa
//采用原始 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
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。
1. 开源生态
2. 协作、人、软件
3. 评估模型