首页 > 代码库 > 分享一个自己写的.Net的ORM工具
分享一个自己写的.Net的ORM工具
注册博客园帐号也有好几年了，之前注册帐号主要是为了看别人的文章下载东西的时候方便。从来没有写过什么博客，一直以为只要注册了帐号就可以写博客，最近用到了才发现还得申请一下，于是就申请了博客，算了也不扯这么多没用的了，直接进入主题吧！
网上开源的ORM工具也不少，开源中国上就有不少，很多都下载试用过，不过感觉用起来都不是很方便，园子里面也有不少人分享自己写的ORM工具，用过一个叫 MySoft.Data 的ORM工具，感觉里面的链式调用函数的写法不错，但是用起来感觉也有些不如意的地方，于是某个周末的晚上自己研究人家的代码就想着自己来实现一个ORM工具，又扯远了...
自己弄得这个ORM工具也不知道起什么名字好，于是就直接就叫 ORM，写这个的时候参考过 MySoft.Data、 NBearLite、PetaPoco ，整个设计的感觉不算太好，反正就是凭着自己的感觉来写的， 接口用的比较少，抽象类用到的比较多，支持多数据库，但是只在SqlServer和SQLite中测试过，其他数据库还没有测试，可以实现较复杂的查询，增删改、事务等操作，ORM没有经过系统的测试，也就是自己写些小例子测试一下，所以稳定性、性能方面不敢保证，建议大家先不要用到项目中去，大家可以下载代码自己改改、测试测试然后再在项目中使用。
下面说下用法吧，ORM 中所有类都用的一个命名空间就叫 ORM，只是在代码中对不能作用的类做了些分类放在不同文件夹，但都是一个命名空间。
首先要创建一个 IDbSession 对象：
<!--数据库连接字符串如下--><add name="connStr" connectionString="Data Source=.;Initial Catalog=dbName;Persist Security Info=True;User ID=sa;Password=******" providerName="System.Data.SqlClient"/>
IDbSession db = DbSession.New(‘connStr‘);
生成实体类：
namespace ORM.Demo.Model { using System; using ORM; public partial class SysUser2Role : TableEntity { private Guid? _SysUser2RoleOID; /// <summary> /// SysUser2RoleOID /// </summary> public Guid? SysUser2RoleOID { get { return _SysUser2RoleOID; } set { _SysUser2RoleOID = value; } } private static NormalField __SysUser2RoleOID = new NormalField("SysUser2Role", "SysUser2RoleOID"); /// <summary> /// SysUser2RoleOID /// </summary> [PrimaryKey()] public static NormalField SysUser2RoleOID_ { get { return __SysUser2RoleOID; } } private string _SysUserOID; /// <summary> /// SysUserOID /// </summary> public string SysUserOID { get { return _SysUserOID; } set { _SysUserOID = value; } } private static NormalField __SysUserOID = new NormalField("SysUser2Role", "SysUserOID"); /// <summary> /// SysUserOID /// </summary> public static NormalField SysUserOID_ { get { return __SysUserOID; } } private string _SysRoleOID; /// <summary> /// SysRoleOID /// </summary> public string SysRoleOID { get { return _SysRoleOID; } set { _SysRoleOID = value; } } private static NormalField __SysRoleOID = new NormalField("SysUser2Role", "SysRoleOID"); /// <summary> /// SysRoleOID /// </summary> public static NormalField SysRoleOID_ { get { return __SysRoleOID; } } private string _CreateUserOID; /// <summary> /// CreateUserOID /// </summary> public string CreateUserOID { get { return _CreateUserOID; } set { _CreateUserOID = value; } } private static NormalField __CreateUserOID = new NormalField("SysUser2Role", "CreateUserOID"); /// <summary> /// CreateUserOID /// </summary> public static NormalField CreateUserOID_ { get { return __CreateUserOID; } } private DateTime? _UpdateTime; /// <summary> /// UpdateTime /// </summary> public DateTime? UpdateTime { get { return _UpdateTime; } set { _UpdateTime = value; } } private static NormalField __UpdateTime = new NormalField("SysUser2Role", "UpdateTime"); /// <summary> /// UpdateTime /// </summary> public static NormalField UpdateTime_ { get { return __UpdateTime; } } private static FieldAll _All; public static FieldAll All { get { _All = _All ?? new FieldAll(typeof(SysUser2Role)); return _All; } } }}namespace ORM.Demo.Model { using System; using ORM; public partial class SysRole : TableEntity { private Guid? _SysRoleOID; /// <summary> /// SysRoleOID /// </summary> public Guid? SysRoleOID { get { return _SysRoleOID; } set { _SysRoleOID = value; } } private static NormalField __SysRoleOID = new NormalField("SysRole", "SysRoleOID"); /// <summary> /// SysRoleOID /// </summary> [PrimaryKey()] public static NormalField SysRoleOID_ { get { return __SysRoleOID; } } private string _RoleName; /// <summary> /// RoleName /// </summary> public string RoleName { get { return _RoleName; } set { _RoleName = value; } } private static NormalField __RoleName = new NormalField("SysRole", "RoleName"); /// <summary> /// RoleName /// </summary> public static NormalField RoleName_ { get { return __RoleName; } } private string _CreateUserOID; /// <summary> /// CreateUserOID /// </summary> public string CreateUserOID { get { return _CreateUserOID; } set { _CreateUserOID = value; } } private static NormalField __CreateUserOID = new NormalField("SysRole", "CreateUserOID"); /// <summary> /// CreateUserOID /// </summary> public static NormalField CreateUserOID_ { get { return __CreateUserOID; } } private string _Remark; /// <summary> /// Remark /// </summary> public string Remark { get { return _Remark; } set { _Remark = value; } } private static NormalField __Remark = new NormalField("SysRole", "Remark"); /// <summary> /// Remark /// </summary> public static NormalField Remark_ { get { return __Remark; } } private DateTime? _UpdateTime; /// <summary> /// UpdateTime /// </summary> public DateTime? UpdateTime { get { return _UpdateTime; } set { _UpdateTime = value; } } private static NormalField __UpdateTime = new NormalField("SysRole", "UpdateTime"); /// <summary> /// UpdateTime /// </summary> public static NormalField UpdateTime_ { get { return __UpdateTime; } } private static FieldAll _All; public static FieldAll All { get { _All = _All ?? new FieldAll(typeof(SysRole)); return _All; } } }}namespace ORM.Demo.Model { using System; using ORM; public partial class SysUser2Role : TableEntity { private Guid? _SysUser2RoleOID; /// <summary> /// SysUser2RoleOID /// </summary> public Guid? SysUser2RoleOID { get { return _SysUser2RoleOID; } set { _SysUser2RoleOID = value; } } private static NormalField __SysUser2RoleOID = new NormalField("SysUser2Role", "SysUser2RoleOID"); /// <summary> /// SysUser2RoleOID /// </summary> [PrimaryKey()] public static NormalField SysUser2RoleOID_ { get { return __SysUser2RoleOID; } } private string _SysUserOID; /// <summary> /// SysUserOID /// </summary> public string SysUserOID { get { return _SysUserOID; } set { _SysUserOID = value; } } private static NormalField __SysUserOID = new NormalField("SysUser2Role", "SysUserOID"); /// <summary> /// SysUserOID /// </summary> public static NormalField SysUserOID_ { get { return __SysUserOID; } } private string _SysRoleOID; /// <summary> /// SysRoleOID /// </summary> public string SysRoleOID { get { return _SysRoleOID; } set { _SysRoleOID = value; } } private static NormalField __SysRoleOID = new NormalField("SysUser2Role", "SysRoleOID"); /// <summary> /// SysRoleOID /// </summary> public static NormalField SysRoleOID_ { get { return __SysRoleOID; } } private string _CreateUserOID; /// <summary> /// CreateUserOID /// </summary> public string CreateUserOID { get { return _CreateUserOID; } set { _CreateUserOID = value; } } private static NormalField __CreateUserOID = new NormalField("SysUser2Role", "CreateUserOID"); /// <summary> /// CreateUserOID /// </summary> public static NormalField CreateUserOID_ { get { return __CreateUserOID; } } private DateTime? _UpdateTime; /// <summary> /// UpdateTime /// </summary> public DateTime? UpdateTime { get { return _UpdateTime; } set { _UpdateTime = value; } } private static NormalField __UpdateTime = new NormalField("SysUser2Role", "UpdateTime"); /// <summary> /// UpdateTime /// </summary> public static NormalField UpdateTime_ { get { return __UpdateTime; } } private static FieldAll _All; public static FieldAll All { get { _All = _All ?? new FieldAll(typeof(SysUser2Role)); return _All; } } }}
这个 IDbSession 最好每个数据库只实例化一个，然后用个静态变量保存就行了，上面三个类分别是 用户表、角色表、用户角色关系表生成的类，
下面是些查询的示例：
//可以生成一个SQL对象SelectSQL sql = db.SFrom<SysUser>().Where(SysUser.LoginName_.Like(‘%a%‘)).Select(SysUser.PersonName_).ToSQL();string str = sql.ToString();
/*
SELECT [SysUser].[PersonName] FROM [SysUser] WHERE [SysUser].[LoginName] LIKE ‘%a%‘
@P_7183594707124d339902badd64347c30: %a%
*/
//也可以直接执行查询生成 List<SysUser>List<SysUser> list = db.SFrom<SysUser>().Where(SysUser.LoginName.Like(‘%a%‘)).ToList<SysUser>();//也可以直接执行查询生成 DataTableDataTable tb = db.SFrom<SysUser>().Where(SysUser.LoginName.Like(‘%a%‘)).ToDataTable();
//多表分页查询
PageSQL sql = db.SFrom<SysUser>()
.InnerJoin<SysUser2Role>(SysUser.SysUserOID_ == SysUser2Role.SysUserOID_)
.InnerJoin<SysRole>(SysUser2Role.SysRoleOID_ == SysRole.SysRoleOID_)
.Where(SysUser.LoginName_ == ‘admin‘)
.ToPageSQL(100, 0);
int total = 0;
List<SysRole> list = db.SFrom<SysUser>()
.InnerJoin<SysUser2Role>(SysUser.SysUserOID_ == SysUser2Role.SysUserOID_)
.InnerJoin<SysRole>(SysUser2Role.SysRoleOID_ == SysRole.SysRoleOID_)
.Where(SysUser.LoginName_ == ‘admin‘)
.ToPageList<SysRole>(100, 0, out total);
下面有个复杂的语句设计到多表查询、Union、子查询等：
var sql = this.Db.SFrom( this.Db.SFrom<M.SysMenu>() .InnerJoin<M.SysUser2Menu>(M.SysUser2Menu.SysMenuOID_ == M.SysMenu.SysMenuOID_) .Where(M.SysUser2Menu.SysUserOID_ == useroid && (M.SysMenu.ShowMenu_ == 1 || M.SysMenu.ShowMenu_.IsNull())) .Select(M.SysMenu.All) .ToSQL() .Union(this.Db.SFrom<M.SysMenu>() .InnerJoin<M.SysRole2Menu>(M.SysRole2Menu.SysMenuOID_ == M.SysMenu.SysMenuOID_) .Where( M.SysRole2Menu.SysRoleOID_.In(this.Db.SFrom<M.SysUser2Role>() .Where(M.SysUser2Role.SysUserOID_ == useroid) .Select(M.SysUser2Role.SysRoleOID_) .ToSQL() .Union(this.Db.SFrom<M.SysGroup2Role>() .InnerJoin<M.SysGroup2User>(M.SysGroup2Role.SysGroupOID_ == M.SysGroup2User.SysGroupOID_) .Where(M.SysGroup2User.SysUserOID_ == useroid) .Select(M.SysGroup2Role.SysRoleOID_) .ToSQL() ) ) && (M.SysMenu.ShowMenu_ == 1 || M.SysMenu.ShowMenu_.IsNull()) ) .Select(M.SysMenu.All) .ToSQL() ), "A" ) .LeftJoin<M.SysUserMenuTemp>(SQLEntity.Set["A"]["SysMenuOID"] == M.SysUserMenuTemp.SysMenuOID_) .OrderBy(OrderBy.New(M.SysUserMenuTemp.SortNumber_).Add(SQLEntity.Set["A"]["SortNumber"])) .Select(SQLEntity.Set["A"], M.SysUserMenuTemp.SortNumber_, M.SysUserMenuTemp.OpenState_) .ToSQL(); db.ToList<M.SysMenu>(sql);
最终生成 SQL 语句如下：
SELECT [A].[SysMenuOID],[A].[ParentMenuOID],[A].[CreateUserOID],[A].[MenuText],[A].[MenuIconUrl],[A].[MenuAction],[A].[OtherScript],[A].[MenuType],[A].[Remark],[A].[ShowMenu],[A].[SortNumber],[A].[OpenState],[A].[UpdateTime],[SysUserMenuTemp].[SortNumber],[SysUserMenuTemp].[OpenState] FROM (SELECT [SysMenu].[SysMenuOID],[SysMenu].[ParentMenuOID],[SysMenu].[CreateUserOID],[SysMenu].[MenuText],[SysMenu].[MenuIconUrl],[SysMenu].[MenuAction],[SysMenu].[OtherScript],[SysMenu].[MenuType],[SysMenu].[Remark],[SysMenu].[ShowMenu],[SysMenu].[SortNumber],[SysMenu].[OpenState],[SysMenu].[UpdateTime] FROM [SysMenu] INNER JOIN [SysUser2Menu] ON [SysUser2Menu].[SysMenuOID]=[SysMenu].[SysMenuOID] WHERE [SysUser2Menu].[SysUserOID]=@P_43063492ed5a467ab0dddd9043a5e4c8 AND ([SysMenu].[ShowMenu]=@P_7183594707124d339902badd64347c30 OR [SysMenu].[ShowMenu] IS NULL) UNION SELECT [SysMenu].[SysMenuOID],[SysMenu].[ParentMenuOID],[SysMenu].[CreateUserOID],[SysMenu].[MenuText],[SysMenu].[MenuIconUrl],[SysMenu].[MenuAction],[SysMenu].[OtherScript],[SysMenu].[MenuType],[SysMenu].[Remark],[SysMenu].[ShowMenu],[SysMenu].[SortNumber],[SysMenu].[OpenState],[SysMenu].[UpdateTime] FROM [SysMenu] INNER JOIN [SysRole2Menu] ON [SysRole2Menu].[SysMenuOID]=[SysMenu].[SysMenuOID] WHERE [SysRole2Menu].[SysRoleOID] IN(SELECT [SysUser2Role].[SysRoleOID] FROM [SysUser2Role] WHERE [SysUser2Role].[SysUserOID]=@P_fd6c25e426b6426280fdc1d619775e99 UNION SELECT [SysGroup2Role].[SysRoleOID] FROM [SysGroup2Role] INNER JOIN [SysGroup2User] ON [SysGroup2Role].[SysGroupOID]=[SysGroup2User].[SysGroupOID] WHERE [SysGroup2User].[SysUserOID]=@P_19337a08b9dc48baba73b88b83baceca) AND ([SysMenu].[ShowMenu]=@P_2295135526b54da5b7baba3051c65b7b OR [SysMenu].[ShowMenu] IS NULL)) AS [A] LEFT JOIN [SysUserMenuTemp] ON [A].[SysMenuOID]=[SysUserMenuTemp].[SysMenuOID] ORDER BY [SysUserMenuTemp].[SortNumber] ASC,[A].[SortNumber] ASC@P_43063492ed5a467ab0dddd9043a5e4c8: 2f1f7bb3-22ab-4478-a9e1-1090242f2276@P_7183594707124d339902badd64347c30: 1@P_fd6c25e426b6426280fdc1d619775e99: 2f1f7bb3-22ab-4478-a9e1-1090242f2276@P_19337a08b9dc48baba73b88b83baceca: 2f1f7bb3-22ab-4478-a9e1-1090242f2276@P_2295135526b54da5b7baba3051c65b7b: 1
以上都是查询的，增删该就相对简单多了：
//InsertSysUser user = new SysUser();user.SysUserOID = Guid.NewGuid();user.LoginName = ‘a‘;db.IFrom<SysUser>().Execute(user);//UpdateSysUser user = new SysUser();user.SysUserOID = Guid.NewGuid();user.LoginName = ‘a‘;db.UFrom<SysUser>().Execute(user);//Deletedb.DFrom<SysUser>().Where(SysUser.LoginName_.Like(‘%a%‘)).Execute();
以上列举了一些用法，下面说一下代码生成器，模版引擎用的是开源的 TextTemplate，生成代码的模版可以按照自己需求来写。
代码生成其中也只实现了SqlServer和SQLite 的代码生成。
大家可一下载试用下，有什么建议或有什么bug欢迎提出。
下载连接：http://pan.baidu.com/s/1bnlIxrh
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。