话说C程序员人手一个ORM
话说C#程序员人手一个ORM,确实没有必要再写ORM了,不过我的ORM并不是新的,是从DBHelper演化过来的,算是DBHelper魔改版。
目前流行的ORM有EF、Dapper、SqlSugar、FreeSql、Chloe等,有经验的程序员总是在这些ORM基础上或者在DBHelper基础上再封装一套,再加上自己写ORM的,可谓人手一套ORM。可能是因为在框架当中,编写ORM,入门相对简单吧,但是做好很难。
主要是自己用的,如果别人要用的话,建议从gitee或github上拉源码下来,源码在手好控制,源码相对简单,有经验的程序员不难看懂,可以自己调试修改扩展,Lambda表达式是新支持的,如果Lambda hold不住,就使用原生SQL。
跟主流ORM相比还是比较欠缺的,它只是一个DBHelper。 源码地址(VS2015、.NET Framework 4.5.2):
https://gitee.com/s0611163/DBHelper
https://github.com/0611163/DBHelper .NET Core 5 版本源码地址(VS2019、.NET 5):
https://gitee.com/s0611163/DBHelperCore 性能测试
DBHelper SqlSugar EntityFramework 增删改查 性能对比测试
https://www.cnblogs.com/s0611163/p/15814051.html
为什么每个主流ORM都说性能比EF好,都说EF性能不好?而我的测试结果显示EF性能挺好的,为了防止有缓存,每项测试前,重启程序再测,性能还是一样的。批量添加和修改,别的ORM是快很多,但它使用了别的专门的方法。
该ORM介绍及使用示例如下: DBHelper简介
一款轻量级ORM,查询使用原生SQL,查询结果映射到实体类,增删改支持实体类,支持Oracle、MSSQL、MySQL、SQLite等多种数据库,有配套Model生成器,方便自己扩展以支持更多数据库 特点支持Oracle、MSSQL、MySQL、SQLite四种数据库 方便扩展以支持更多关系数据库 有配套的Model生成器 insert、update、delete操作无需写SQL 查询使用原生SQL 查询结果通过映射转成实体类或实体类集合 支持参数化查询,通过SqlString类提供非常方便的参数化查询 支持连接多个数据源 单表查询、单表分页查询、简单的联表分页查询支持Lambda表达式 支持原生SQL和Lambda表达式混写 优点代码实现比较简单,有经验的程序员容易掌控代码,自己修改和扩展 查询使用原生SQL 缺点联表查询对Lambda表达式的支持比较弱 复杂查询不支持Lambda表达式 建议单表查询可以使用Lambda表达式 联表查询以及复杂查询建议使用原生SQL或原生SQL和Lambda表达式混写 示例定义数据库对象public class DBHelper { #region 变量 private static ISessionHelper _sessionHelper = new SessionHelper(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString(), DBType.MySQL); #endregion #region 获取 ISession /// /// 获取 ISession /// public static ISession GetSession() { return _sessionHelper.GetSession(); } #endregion #region 获取 ISession (异步) /// /// 获取 ISession (异步) /// public static async Task GetSessionAsync() { return await _sessionHelper.GetSessionAsync(); } #endregion } 使用Model生成器生成实体类实体类放在Models文件夹中 扩展实体类放在ExtModels文件夹中 实体类和扩展实体类使用partial修饰,实际上是一个类,放在不同的文件中 如果需要添加自定义属性,请修改ExtModels,不要修改Models 实体类示例/// /// 订单表 /// [Serializable] [DBTable("bs_order")] public partial class BsOrder { /// /// 主键 /// [DBKey] [DBField] public string Id { get; set; } /// /// 订单时间 /// [DBField("order_time")] public DateTime OrderTime { get; set; } /// /// 订单金额 /// [DBField] public decimal? Amount { get; set; } /// /// 下单用户 /// [DBField("order_userid")] public long OrderUserid { get; set; } /// /// 订单状态(0草稿 1已下单 2已付款 3已发货 4完成) /// [DBField] public int Status { get; set; } /// /// 备注 /// [DBField] public string Remark { get; set; } /// /// 创建者ID /// [DBField("create_userid")] public string CreateUserid { get; set; } /// /// 创建时间 /// [DBField("create_time")] public DateTime CreateTime { get; set; } /// /// 更新者ID /// [DBField("update_userid")] public string UpdateUserid { get; set; } /// /// 更新时间 /// [DBField("update_time")] public DateTime? UpdateTime { get; set; } } 修改扩展实体类修改扩展实体类,添加自定义属性 下面的扩展实体类中,查询时OrderUserRealName会被自动填充,查询SQL:select t.*, u.real_name as OrderUserRealName from ...... DetailList不会被自动填充,需要手动查询 扩展实体类示例/// /// 订单表 /// public partial class BsOrder { /// /// 订单明细集合 /// public List DetailList { get; set; } /// /// 下单用户姓名 /// public string OrderUserRealName { get; set; } /// /// 下单用户名 /// public string OrderUserName { get; set; } } 添加public void Insert(SysUser info) { using (var session = DBHelper.GetSession()) { session.Insert(info); } } 批量添加public void Insert(List list) { using (var session = DBHelper.GetSession()) { session.Insert(list); } } 修改public void Update(SysUser info) { using (var session = DBHelper.GetSession()) { session.Update(info); } } 批量修改public void Update(List list) { using (var session = DBHelper.GetSession()) { session.Update(list); } } 删除public void Delete(string id) { using (var session = DBHelper.GetSession()) { session.DeleteById(id); } } 条件删除using (var session = DBHelper.GetSession()) { session.DeleteByCondition(string.Format("id>=12")); } 查询单个记录public SysUser Get(string id) { using (var session = DBHelper.GetSession()) { return session.FindById(id); } } using (var session = DBHelper.GetSession()) { return session.FindBySql("select * from sys_user"); } 简单查询using (var session = DBHelper.GetSession()) { string sql = "select * from CARINFO_MERGE"; List result = session.FindListBySql(sql); } 条件查询public List GetList(int? status, string remark, DateTime? startTime, DateTime? endTime) { using (var session = DBHelper.GetSession()) { SqlString sql = session.CreateSqlString(@" select t.*, u.real_name as OrderUserRealName from bs_order t left join sys_user u on t.order_userid=u.id where 1=1"); sql.AppendIf(status.HasValue, " and t.status=@status", status); sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like concat("%",@remark,"%")", remark); sql.AppendIf(startTime.HasValue, " and t.order_time>=STR_TO_DATE(@startTime, "%Y-%m-%d %H:%i:%s") ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); sql.AppendIf(endTime.HasValue, " and t.order_time<=STR_TO_DATE(@endTime, "%Y-%m-%d %H:%i:%s") ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); sql.Append(" order by t.order_time desc, t.id asc "); List list = session.FindListBySql(sql.SQL, sql.Params); return list; } } 分页查询public List GetListPage(ref PageModel pageModel, int? status, string remark, DateTime? startTime, DateTime? endTime) { using (var session = DBHelper.GetSession()) { SqlString sql = session.CreateSqlString(@" select t.*, u.real_name as OrderUserRealName from bs_order t left join sys_user u on t.order_userid=u.id where 1=1"); sql.AppendIf(status.HasValue, " and t.status=@status", status); sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like concat("%",@remark,"%")", remark); sql.AppendIf(startTime.HasValue, " and t.order_time>=STR_TO_DATE(@startTime, "%Y-%m-%d %H:%i:%s") ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); sql.AppendIf(endTime.HasValue, " and t.order_time<=STR_TO_DATE(@endTime, "%Y-%m-%d %H:%i:%s") ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); string orderby = " order by t.order_time desc, t.id asc "; pageModel = session.FindPageBySql(sql.SQL, orderby, pageModel.PageSize, pageModel.CurrentPage, sql.Params); return pageModel.GetResult(); } } 事务public string Insert(BsOrder order, List detailList) { using (var session = DBHelper.GetSession()) { try { session.BeginTransaction(); order.Id = Guid.NewGuid().ToString("N"); order.CreateTime = DateTime.Now; decimal amount = 0; foreach (BsOrderDetail detail in detailList) { detail.Id = Guid.NewGuid().ToString("N"); detail.OrderId = order.Id; detail.CreateTime = DateTime.Now; amount += detail.Price * detail.Quantity; session.Insert(detail); } order.Amount = amount; session.Insert(order); session.CommitTransaction(); return order.Id; } catch (Exception ex) { session.RollbackTransaction(); Console.WriteLine(ex.Message + "r " + ex.StackTrace); throw ex; } } } 异步查询public async Task> GetListPageAsync(PageModel pageModel, int? status, string remark, DateTime? startTime, DateTime? endTime) { using (var session = await DBHelper.GetSessionAsync()) { SqlString sql = session.CreateSqlString(@" select t.*, u.real_name as OrderUserRealName from bs_order t left join sys_user u on t.order_userid=u.id where 1=1"); sql.AppendIf(status.HasValue, " and t.status=@status", status); sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like concat("%",@remark,"%")", remark); sql.AppendIf(startTime.HasValue, " and t.order_time>=STR_TO_DATE(@startTime, "%Y-%m-%d %H:%i:%s") ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); sql.AppendIf(endTime.HasValue, " and t.order_time<=STR_TO_DATE(@endTime, "%Y-%m-%d %H:%i:%s") ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss")); string orderby = " order by t.order_time desc, t.id asc "; pageModel = await session.FindPageBySqlAsync(sql.SQL, orderby, pageModel.PageSize, pageModel.CurrentPage, sql.Params); return pageModel.GetResult(); } } 条件查询(使用 ForContains、ForStartsWith、ForEndsWith、ForDateTime、ForList 等辅助方法)public List GetListExt(int? status, string remark, DateTime? startTime, DateTime? endTime, string ids) { using (var session = DBHelper.GetSession()) { SqlString sql = session.CreateSqlString(@" select t.*, u.real_name as OrderUserRealName from bs_order t left join sys_user u on t.order_userid=u.id where 1=1"); sql.AppendIf(status.HasValue, " and t.status=@status", status); sql.AppendIf(!string.IsNullOrWhiteSpace(remark), " and t.remark like @remark", sql.ForContains(remark)); sql.AppendIf(startTime.HasValue, " and t.order_time >= @startTime ", sql.ForDateTime(startTime.Value)); sql.AppendIf(endTime.HasValue, " and t.order_time <= @endTime ", sql.ForDateTime(endTime.Value)); sql.Append(" and t.id in @ids ", sql.ForList(ids.Split(",").ToList())); sql.Append(" order by t.order_time desc, t.id asc "); List list = session.FindListBySql(sql.SQL, sql.Params); return list; } } 使用Lambda表达式单表查询
单表分页查询使用ToPageList替换ToList即可 public void TestQueryByLambda6() { using (var session = DBHelper.GetSession()) { SqlString sql = session.CreateSqlString(); string remark = "测试"; List list = sql.Query() .WhereIf(!string.IsNullOrWhiteSpace(remark), t => t.Remark.Contains(remark) && t.CreateTime < DateTime.Now && t.CreateUserid == "10") .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id) .ToList(); foreach (BsOrder item in list) { Console.WriteLine(ModelToStringUtil.ToString(item)); } } } 使用Lambda表达式联表分页查询(简单的联表查询,复杂情况请使用原生SQL或原生SQL和Lambda表达式混写)public void TestQueryByLambda7() { using (var session = DBHelper.GetSession()) { SqlString sql = session.CreateSqlString(); int total; List idsNotIn = new List() { "100007", "100008", "100009" }; List list = sql.Query() .Select(u => u.UserName, t => t.OrderUserName) .Select(u => u.RealName, t => t.OrderUserRealName) .LeftJoin((t, u) => t.OrderUserid == u.Id) .LeftJoin((t, d) => t.Id == d.OrderId) .Where((t, u, d) => t.Remark.Contains("订单") && u.CreateUserid == "1" && d.GoodsName != null) .WhereIf(true, t => t.Remark.Contains("测试")) .WhereIf(true, t => !idsNotIn.Contains(t.Id)) .WhereIf(true, u => u.CreateUserid == "1") .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id) .ToPageList(1, 20, out total); foreach (BsOrder item in list) { Console.WriteLine(ModelToStringUtil.ToString(item)); } } } 原生SQL和Lambda表达式混写public void TestQueryByLambda9() { using (var session = DBHelper.GetSession()) { SqlString sql = session.CreateSqlString(@" select t.*, u.real_name as OrderUserRealName from bs_order t left join sys_user u on t.order_userid=u.id where 1=1"); List list = sql.Where(t => t.Status == int.Parse("0") && t.Status == new BsOrder().Status && t.Remark.Contains("订单") && t.Remark != null && t.OrderTime >= new DateTime(2010, 1, 1) && t.OrderTime <= DateTime.Now.AddDays(1)) .WhereIf(true, u => u.CreateTime < DateTime.Now) .OrderByDescending(t => t.OrderTime).OrderBy(t => t.Id) .ToList(); foreach (BsOrder item in list) { Console.WriteLine(ModelToStringUtil.ToString(item)); } } }