Share a self-written. Net ORM tool,. netorm Tool

Share a self-written. Net ORM tool,. netorm Tool

You have registered a blog garden account for several years. Previously, you registered an account to conveniently download articles from other users. I have never written any blog and thought that I can write a blog as long as I have registered an account. I only recently used it to find that I have to apply for a blog. So I applied for a blog, it's useless. Let's go to the topic!

　　

　　

There are also a lot of open-source ORM tools on the Internet, and there are a lot of open-source tools in China, many of which have been downloaded and tried, but it doesn't feel very convenient to use, many people in the garden also shared their own ORM tools and used a tool named MySoft. the Data ORM tool feels that the method of using chained call functions is good, but it also feels a little bad to use, so one weekend later I studied the code and thought about how to implement an ORM tool...

 

I did not know the name of this ORM tool, so I called it ORM directly. When I wrote this tool, I referred to MySoft. data, NBearLite, and PetaPoco. The overall design is not very good. They are written based on their own feelings. interfaces are used less. abstract classes are used more often and support multiple databases, however, it has only been tested in SqlServer and SQLite, and has not been tested in other databases. It can perform complex queries, add, delete, modify, and transaction operations. ORM has not been tested by the system, that is to say, I wrote some small examples to test the stability and performance. We recommend that you do not use them in projects first, you can download the code and modify it, test it, and use it in the project.

 

Let's talk about the usage below. The namespace used by all the classes in the ORM is called the ORM, but some classes that cannot be used in the Code are classified in different folders, but they are all namespaces.

　　

　　

First, create an IDbSession object:

　　

　　

<! -- The database connection string is as follows --> <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');

 

Generate object class:

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;            }        }    }}

 

This IDbSession is best to instantiate only one database, and then save it with a static variable. The above three classes are the classes generated by the user table, role table, and user role relationship table,

The following is an example of a query:

// You can generate an SQL object 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_7183594707124d339902badd641_c30: % a %
*/
// You can also directly execute the query to generate a List <SysUser> list = db. SFrom <SysUser> (). where (SysUser. loginName. like ('% a % ')). toList <SysUser> (); // You can also directly execute the query to generate DataTableDataTable tb = db. SFrom <SysUser> (). where (SysUser. loginName. like ('% a % ')). toDataTable ();
// Multi-Table paging Query
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 );

The following is a complex statement designed for multi-table queries, Union queries, and subqueries:

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);

The final generated SQL statement is as follows:

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

All of the above are queries. adding or deleting a query is much simpler:

//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();

　　

　　

The above lists some usage. The following describes the code generator. The template engine uses an open-source TextTemplate. The template for generating code can be written as needed.

Only SQL Server and SQLite code are generated.

 

You can download and try it out. If you have any suggestions or bugs, please submit them.

Connection: http://pan.baidu.com/s/1bnlIxrh

 

How to Write a three-tier architecture with an ORM, it is best to give an example

ORM can be understood as follows:
Generally, we place the data required by the system in the database. The data displayed on the page is obtained by reading the database and further processing.
The data in the database is structured.
We need to integrate the data in. net Programming with object-oriented programming.
Therefore, we need a mechanism to convert structural data in the database into Object-oriented Data. As a result, there is a common layer-3 architecture in the system architecture:

Bottom Layer: DAL (Data Access Layer)
Intermediate: BLL (Bussiness Logic Layer, business Logic Layer)
Top layer: UI Layer ~

In DAL, we use entity classes to encapsulate database tables:
For example, we construct an article management system. The following table is required:
[Articles] [Categories] [Comments]
Take [Articles] as an example, including the following fields:
[ArticleID] [Title] [Content] [AddedBy] [AddedDate]
The object class corresponding to the DAL layer is [ArticleDetails]
The table contains five attributes, including [ArticleID] [Title.

Corresponding to the three tables in the DB, we have three entity classes.
Create a class SqlArticlesProvider to encapsulate DB operations.
Generally, each method encapsulates a stored procedure ~
For example, in the GetArticles (int categoryID) method.
We connect to the DB, call the SP, and encapsulate the returned DataReader in the List of object classes <ArticleDetails>. To be transmitted to the BLL layer.

BLL layer:
The class in is called a domain object. [Article] [Category] [Comment]
Every class here is the class in our traditional OOP.
Each object contains methods that describe its own attributes and executable behavior.

UI Layer: directly calls the BLL layer class to obtain data, and binds the page displayed by the control through data ~

It's a bit messy... In this case, it is not especially adequate ..
It is more complicated than programming ..
After the three-tier architecture, the system is easier to maintain. There are few changes to the underlying data storage.
Therefore, it is mainly used for large and medium-sized system architectures.

In addition, after the release of LINQ, the encoding volume is also reduced. However, it takes little time to learn LINQ independently.
If you don't want to use LINQ, there are still a lot of tool code that can help automatically build some classes ~

Problems encountered in writing ORM in C # net

You can also set the object value to an empty type. Nullable <T>