当前位置:
首页 > temp > 简明python教程 >
-
C# ORM学习笔记:使用特性+反射实现简单ORM
一、原理与环境
在生成数据表的实体类时,利用自定义特性,给它打上表及字段的特性,然后使用反射原理,将自定义特性拼接成增、删、改、查对应的SQL,即可完成一个简单的ORM。
本示例的执行环境:
1)数据库:SQL Server。(可根据自己的需要,建立不同的数据库工厂。)
2)数据表:需使用自增类型(identity)作为数据表的主键。主键名字可以随便起,如ID。
3)实体类:实体类需提供无参构造函数。
二、演示数据表
Person表,包含主键(ID)、姓名(Name)、年龄(Age)、性别(Gender)。
CREATE TABLE [dbo].[Person]( [ID] [BIGINT] IDENTITY(1,1) NOT NULL, [Name] [NVARCHAR](50) NULL, [Age] [INT] NULL, [Gender] [NVARCHAR](10) NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
二、自定义特性
定义两个自定义特性:
2.1、DataTableAttribute
此为数据表特性,包含表名(TableName)、主键(Key)。
[Serializable] public class DataTableAttribute : Attribute { /// <summary> /// 数据表 /// </summary> public string TableName { get; } /// <summary> /// 主键 /// </summary> public string Key { get; } /// <summary> /// 构造函数 /// </summary> /// <param name="tableName"></param> /// <param name="key"></param> public DataTableAttribute(string tableName, string key) { TableName = tableName; Key = key; } }
2.2、DataFieldAttribute
此为字段特性,包含字段名(FieldName)、字段类型(FieldType)、长度(Length)、是否自增(IsIdentity)。
[Serializable] public class DataFieldAttribute : Attribute { public string FieldName { get; set; } public string FieldType { get; set; } public int Length { get; set; } public bool IsIdentity { get; set; } /// <summary> /// 构造函数 /// </summary> /// <param name="fieldName">字段名</param> /// <param name="fieldType">字段类型</param> /// <param name="length">长度</param> /// <param name="isIdentity">是否自增长</param> public DataFieldAttribute(string fieldName, string fieldType, int length, bool isIdentity) { FieldName = fieldName; FieldType = fieldType; Length = length; IsIdentity = isIdentity; } /// <summary> /// 构造函数 /// </summary> /// <param name="fieldName"></param> /// <param name="length"></param> /// <param name="isIdentity"></param> public DataFieldAttribute(string fieldName, string fieldType, int length) : this(fieldName, fieldType, length, false) { } /// <summary> /// 构造函数 /// </summary> /// <param name="fieldName"></param> /// <param name="fieldtype"></param> public DataFieldAttribute(string fieldName, string fieldType) : this(fieldName, fieldType, 0, false) { } /// <summary> /// 构造函数 /// </summary> /// <param name="fieldName"></param> /// <param name="isIdentity"></param> public DataFieldAttribute(string fieldName, bool isIdentity) : this(fieldName, "", 0, isIdentity) { } /// <summary> /// 构造函数 /// </summary> /// <param name="fieldName"></param> public DataFieldAttribute(string fieldName) : this(fieldName, false) { } }
三、生成实体类
3.1、实体类样式
依照前面的规划,Person表需要生成下面这个样子:
using System; using System.Collections.Generic; using System.Text; using LinkTo.ORM.CustomAttribute; namespace LinkTo.ORM.Model { [DataTable("Person","ID")] [Serializable] public class Person { public Person() { } [DataField("ID","bigint",19,true)] public long? ID {get; set;} [DataField("Name","nvarchar",50,false)] public string Name {get; set;} [DataField("Age","int",10,false)] public int? Age {get; set;} [DataField("Gender","nvarchar",10,false)] public string Gender {get; set;} } }
3.2、使用T4模板生成实体类
3.2.1、T4Code文件夹的文本模板
<#@ assembly name="System.Core" #> <#@ assembly name="System.Data" #> <#@ import namespace="System.Linq" #> <#@ import namespace="System.Text" #> <#@ import namespace="System.Collections.Generic" #> <#@ import namespace="System.Data"#> <#@ import namespace="System.Data.SqlClient"#> <#+ #region T4Code /// <summary> /// 数据库架构接口 /// </summary> public interface IDBSchema : IDisposable { List<string> GetTableList(); DataTable GetTableMetadata(string tableName); } /// <summary> /// 数据库架构工厂 /// </summary> public class DBSchemaFactory { static readonly string DatabaseType = "SqlServer"; public static IDBSchema GetDBSchema() { IDBSchema dbSchema; switch (DatabaseType) { case "SqlServer": { dbSchema =new SqlServerSchema(); break; } default: { throw new ArgumentException("The input argument of DatabaseType is invalid."); } } return dbSchema; } } /// <summary> /// SqlServer /// </summary> public class SqlServerSchema : IDBSchema { public string ConnectionString = "Server=.;Database=Test;Uid=sa;Pwd=********;"; public SqlConnection conn; public SqlServerSchema() { conn = new SqlConnection(ConnectionString); conn.Open(); } public List<string> GetTableList() { List<string> list = new List<string>(); string commandText = "SELECT NAME TABLE_NAME FROM SYSOBJECTS WHERE XTYPE='U' ORDER BY NAME"; using(SqlCommand cmd = new SqlCommand(commandText, conn)) { using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { list.Add(dr["TABLE_NAME"].ToString()); } } } return list; } public DataTable GetTableMetadata(string tableName) { string commandText=string.Format ( "SELECT A.NAME TABLE_NAME,B.NAME FIELD_NAME,C.NAME DATATYPE,ISNULL(B.PREC,0) LENGTH, "+ "CONVERT(BIT,CASE WHEN NOT F.ID IS NULL THEN 1 ELSE 0 END) ISKEY, "+ "CONVERT(BIT,CASE WHEN COLUMNPROPERTY(B.ID,B.NAME,'ISIDENTITY') = 1 THEN 1 ELSE 0 END) AS ISIDENTITY, "+ "CONVERT(BIT,B.ISNULLABLE) ISNULLABLE "+ "FROM SYSOBJECTS A INNER JOIN SYSCOLUMNS B ON A.ID=B.ID INNER JOIN SYSTYPES C ON B.XTYPE=C.XUSERTYPE "+ "LEFT JOIN SYSOBJECTS D ON B.ID=D.PARENT_OBJ AND D.XTYPE='PK' "+ "LEFT JOIN SYSINDEXES E ON B.ID=E.ID AND D.NAME=E.NAME "+ "LEFT JOIN SYSINDEXKEYS F ON B.ID=F.ID AND B.COLID=F.COLID AND E.INDID=F.INDID "+ "WHERE A.XTYPE='U' AND A.NAME='{0}' "+ "ORDER BY A.NAME,B.COLORDER", tableName ); using(SqlCommand cmd = new SqlCommand(commandText, conn)) { SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds,"Schema"); return ds.Tables[0]; } } public void Dispose() { if (conn != null) { conn.Close(); } } } #endregion #>
<#@ assembly name="System.Core" #> <#@ assembly name="System.Data" #> <#@ assembly name="EnvDTE" #> <#@ import namespace="System.Linq" #> <#@ import namespace="System.Text" #> <#@ import namespace="System.Collections.Generic" #> <#@ import namespace="System.Data"#> <#@ import namespace="System.IO"#> <#@ import namespace="Microsoft.VisualStudio.TextTemplating"#> <#+ // T4 Template Block manager for handling multiple file outputs more easily. // Copyright (c) Microsoft Corporation.All rights reserved. // This source code is made available under the terms of the Microsoft Public License (MS-PL) // Manager class records the various blocks so it can split them up class Manager { public struct Block { public string Name; public int Start, Length; } public List<Block> blocks = new List<Block>(); public Block currentBlock; public Block footerBlock = new Block(); public Block headerBlock = new Block(); public ITextTemplatingEngineHost host; public ManagementStrategy strategy; public StringBuilder template; public string OutputPath { get; set; } public Manager(ITextTemplatingEngineHost host, StringBuilder template, bool commonHeader) { this.host = host; this.template = template; OutputPath = string.Empty; strategy = ManagementStrategy.Create(host); } public void StartBlock(string name) { currentBlock = new Block { Name = name, Start = template.Length }; } public void StartFooter() { footerBlock.Start = template.Length; } public void EndFooter() { footerBlock.Length = template.Length - footerBlock.Start; } public void StartHeader() { headerBlock.Start = template.Length; } public void EndHeader() { headerBlock.Length = template.Length - headerBlock.Start; } public void EndBlock() { currentBlock.Length = template.Length - currentBlock.Start; blocks.Add(currentBlock); } public void Process(bool split) { string header = template.ToString(headerBlock.Start, headerBlock.Length); string footer = template.ToString(footerBlock.Start, footerBlock.Length); blocks.Reverse(); foreach(Block block in blocks) { string fileName = Path.Combine(OutputPath, block.Name); if (split) { string content = header + template.ToString(block.Start, block.Length) + footer; strategy.CreateFile(fileName, content); template.Remove(block.Start, block.Length); } else { strategy.DeleteFile(fileName); } } } } class ManagementStrategy { internal static ManagementStrategy Create(ITextTemplatingEngineHost host) { return (host is IServiceProvider) ? new VSManagementStrategy(host) : new ManagementStrategy(host); } internal ManagementStrategy(ITextTemplatingEngineHost host) { } internal virtual void CreateFile(string fileName, string content) { File.WriteAllText(fileName, content); } internal virtual void DeleteFile(string fileName) { if (File.Exists(fileName)) File.Delete(fileName); } } class VSManagementStrategy : ManagementStrategy { private EnvDTE.ProjectItem templateProjectItem; internal VSManagementStrategy(ITextTemplatingEngineHost host) : base(host) { IServiceProvider hostServiceProvider = (IServiceProvider)host; if (hostServiceProvider == null) throw new ArgumentNullException("Could not obtain hostServiceProvider"); EnvDTE.DTE dte = (EnvDTE.DTE)hostServiceProvider.GetService(typeof(EnvDTE.DTE)); if (dte == null) throw new ArgumentNullException("Could not obtain DTE from host"); templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile); } internal override void CreateFile(string fileName, string content) { base.CreateFile(fileName, content); ((EventHandler)delegate { templateProjectItem.ProjectItems.AddFromFile(fileName); }).BeginInvoke(null, null, null, null); } internal override void DeleteFile(string fileName) { ((EventHandler)delegate { FindAndDeleteFile(fileName); }).BeginInvoke(null, null, null, null); } private void FindAndDeleteFile(string fileName) { foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems) { if (projectItem.get_FileNames(0) == fileName) { projectItem.Delete(); return; } } } } #>
DBSchema.ttinclude主要实现了数据库工厂的功能。注:请将数据库连接字符串改成您自己的。
MultiDocument.ttinclude主要实现了多文档的功能。
3.2.2、生成实体类的文本模板
<#@ template debug="true" hostspecific="true" language="C#" #> <#@ assembly name="System.Core" #> <#@ import namespace="System.Linq" #> <#@ import namespace="System.Text" #> <#@ import namespace="System.Collections.Generic" #> <#@ output extension=".cs" #> <#@ include file="T4Code/DBSchema.ttinclude"#> <#@ include file="T4Code/MultiDocument.ttinclude"#> <# var manager = new
栏目列表
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀(爬取学校通知公告)
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求:requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量?
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析:SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL,这些神奇的特性你知道吗?
openGauss内核分析:执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
SQL Server -- 解决存储过程传入参数作为s
关于JS定时器的整理
JS中使用Promise.all控制所有的异步请求都完
js中字符串的方法
import-local执行流程与node模块路径解析流程
检测数据类型的四种方法
js中数组的方法,32种方法
前端操作方法
数据类型
window.localStorage.setItem 和 localStorage.setIte
如何完美解决前端数字计算精度丢失与数