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 manager(host, generationenvironment, true) { outputpath = path.getdirectoryname(host.templatefile)}; #> <# //system.diagnostics.debugger.launch();//调试 var dbschema = dbschemafactory.getdbschema(); list<string> tablelist = dbschema.gettablelist(); foreach (string tablename in tablelist) { manager.startblock(tablename+".cs"); datatable table = dbschema.gettablemetadata(tablename); //获取主键 string strkey = string.empty; foreach (datarow datarow in table.rows) { if ((bool)datarow["iskey"] == true) { strkey = datarow["field_name"].tostring(); break; } } #> //------------------------------------------------------------------------------- // 此代码由t4模板multmodelauto自动生成 // 生成时间 <#= datetime.now.tostring("yyyy-mm-dd hh:mm:ss") #> // 对此文件的更改可能会导致不正确的行为,并且如果重新生成代码,这些更改将会丢失。 //------------------------------------------------------------------------------- using system; using system.collections.generic; using system.text; using linkto.orm.customattribute; namespace linkto.orm.model { [datatable("<#= tablename #>","<#= strkey #>")] [serializable] public class <#= tablename #> { public <#= tablename #>() { } <# foreach (datarow datarow in table.rows) { //获取数据类型 string dbdatatype = datarow["datatype"].tostring(); string datatype = string.empty; switch (dbdatatype) { case "decimal": case "numeric": case "money": case "smallmoney": datatype = "decimal?"; break; case "char": case "nchar": case "varchar": case "nvarchar": case "text": case "ntext": datatype = "string"; break; case "uniqueidentifier": datatype = "guid?"; break; case "bit": datatype = "bool?"; break; case "real": datatype = "single?"; break; case "bigint": datatype = "long?"; break; case "int": datatype = "int?"; break; case "tinyint": case "smallint": datatype = "short?"; break; case "float": datatype = "float?"; break; case "date": case "datetime": case "datetime2": case "smalldatetime": datatype = "datetime?"; break; case "datetimeoffset ": datatype = "datetimeoffset?"; break; case "timespan ": datatype = "timespan?"; break; case "image": case "binary": case "varbinary": datatype = "byte[]"; break; default: break; } #> [datafield("<#= datarow["field_name"].tostring() #>","<#= datarow["datatype"].tostring() #>",<#= datarow["length"].tostring() #>,<#= datarow["isidentity"].tostring().tolower() #>)] public <#= datatype #> <#= datarow["field_name"].tostring() #> {get; set;} <# } #> } } <# manager.endblock(); } dbschema.dispose(); manager.process(true); #>
注:由于orm拼接sql时使用的是表特性及字段特性,可以看出表特性上使用的表名、字段特性上使用的字段名,都是与数据库一致的。有了这个保障,数据表生成实体类的时候,类名是可以更改的,因为我只需要保证表特性与数据库一致即可。举个例子,我有个数据表person_a,在生成实体类时,类名可以生成为class persona {...},但是表特性依然是[datatable("person_a","...")]。相同的原理,属性名也是可以更改的。
四、orm实现
数据表的curd,主要是通过反射来实现sql拼接,实现如下:
using system; using system.collections; using system.collections.generic; using system.data; using system.data.sqlclient; using system.linq; using system.reflection; using system.text; using system.threading.tasks; using linkto.orm.customattribute; namespace linkto.orm { public static class dbhelper { public static readonly string connectionstring = "server=.;database=test;uid=sa;pwd=********;"; private static readonly hashtable _hashtablename = new hashtable(); //表名缓存 private static readonly hashtable _hashkey = new hashtable(); //主键缓存 /// <summary> /// 数据库连接 /// </summary> /// <returns></returns> public static sqlconnection getconnection() { sqlconnection conn = new sqlconnection(connectionstring); return conn; } /// <summary> /// 新增 /// </summary> /// <typeparam name="tentity"></typeparam> /// <param name="entity"></param> /// <returns></returns> public static int insert<tentity>(tentity entity) where tentity : class { string strtablename = ""; //表名 string strinsertsql = "insert into {0} ({1}) values ({2})"; //sql拼接语句 //获取表名 strtablename = gettablename(entity); //获取字段列表及值列表 stringbuilder strfields = new stringbuilder(); stringbuilder strvalues = new stringbuilder(); list<sqlparameter> paralist = new list<sqlparameter>(); propertyinfo[] infos = entity.gettype().getproperties(); datafieldattribute dfattr = null; object[] dfattrs; int i = 0; foreach (propertyinfo info in infos) { dfattrs = info.getcustomattributes(typeof(datafieldattribute), false); if (dfattrs.length > 0) { dfattr = dfattrs[0] as datafieldattribute; if (dfattr is datafieldattribute) { //自增字段不作处理 if (dfattr.isidentity) continue; strfields.append(i > 0 ? "," + dfattr.fieldname : dfattr.fieldname); strvalues.append(i > 0 ? "," + "@" + dfattr.fieldname : "@" + dfattr.fieldname); i++; paralist.add(new sqlparameter("@" + dfattr.fieldname, info.getvalue(entity, null))); } } } //格式化sql拼接语句 string[] args = new string[] { strtablename, strfields.tostring(), strvalues.tostring() }; strinsertsql = string.format(strinsertsql, args); //执行结果 int result = 0; try { using (sqlconnection conn = getconnection()) { conn.open(); using (sqlcommand cmd = new sqlcommand()) { cmd.commandtext = strinsertsql; cmd.commandtype = commandtype.text; cmd.connection = conn; if (paralist != null) { foreach (sqlparameter param in paralist) { cmd.parameters.add(param); } } result = cmd.executenonquery(); } } } catch (exception ex) { throw new exception(ex.tostring()); } //返回影响行数 return result; } /// <summary> /// 删除 /// </summary> /// <typeparam name="tentity"></typeparam> /// <param name="condition"></param> /// <returns></returns> public static int delete<tentity>(string condition) where tentity : class, new() { string strtablename = ""; //表名 string strdeletesql = "delete from {0} where {1}"; //sql拼接语句 //获取表名 strtablename = gettablename(new tentity()); //格式化sql拼接语句 string[] args = new string[] { strtablename, condition }; strdeletesql = string.format(strdeletesql, args); //执行结果 int result = 0; try { using (sqlconnection conn = getconnection()) { conn.open(); using (sqlcommand cmd = new sqlcommand()) { cmd.commandtext = strdeletesql; cmd.commandtype = commandtype.text; cmd.connection = conn; result = cmd.executenonquery(); } } } catch (exception ex) { throw new exception(ex.tostring()); } //返回影响行数 return result; } /// <summary> /// 更新 /// </summary> /// <typeparam name="tentity"></typeparam> /// <param name="entity"></param> /// <returns></returns> public static int update<tentity>(tentity entity) where tentity : class { string strtablename = ""; //表名 string strupdatesql = "update {0} set {1} where {2}"; //sql拼接语句 string strkey = ""; //主键 string strwhere = ""; //条件 //获取表名及主键 strtablename = gettablename(entity); strkey = getkey(entity); //获取更新列表 stringbuilder strset = new stringbuilder(); list<sqlparameter> paralist = new list<sqlparameter>(); propertyinfo[] infos = entity.gettype().getproperties(); datafieldattribute dfattr = null; object[] dfattrs; int i = 0; foreach (propertyinfo info in infos) { dfattrs = info.getcustomattributes(typeof(datafieldattribute), false); if (dfattrs.length > 0) { dfattr = dfattrs[0] as datafieldattribute; if (dfattr is datafieldattribute) { //条件处理 if (dfattr.fieldname == strkey) { strwhere = strkey + "=" + info.getvalue(entity, null); } //自增字段不作处理 if (dfattr.isidentity) continue; strset.append(i > 0 ? "," + dfattr.fieldname + "=@" + dfattr.fieldname : dfattr.fieldname + "=@" + dfattr.fieldname); i++; paralist.add(new sqlparameter("@" + dfattr.fieldname, info.getvalue(entity, null))); } } } //格式化sql拼接语句 string[] args = new string[] { strtablename, strset.tostring(), strwhere }; strupdatesql = string.format(strupdatesql, args); //执行结果 int result = 0; try { using (sqlconnection conn = getconnection()) { conn.open(); using (sqlcommand cmd = new sqlcommand()) { cmd.commandtext = strupdatesql; cmd.commandtype = commandtype.text; cmd.connection = conn; if (paralist != null) { foreach (sqlparameter param in paralist) { cmd.parameters.add(param); } } result = cmd.executenonquery(); } } } catch (exception ex) { throw new exception(ex.tostring()); } //返回影响行数 return result; } /// <summary> /// 查询 /// </summary> /// <typeparam name="tentity"></typeparam> /// <param name="condition"></param> /// <returns></returns> public static list<tentity> query<tentity>(string condition) where tentity : class, new() { string strtablename = ""; //表名 string strselectsql = "select * from {0} where {1}"; //sql拼接语句 list<tentity> list = new list<tentity>(); //实体列表 //获取表名 strtablename = gettablename(new tentity()); //格式化sql拼接语句 string[] args = new string[] { strtablename, condition }; strselectsql = string.format(strselectsql, args); //获取实体列表 propertyinfo[] infos = typeof(tentity).getproperties(); datafieldattribute dfattr = null; object[] dfattrs; try { using (sqlconnection conn = getconnection()) { conn.open(); using (sqlcommand cmd = new sqlcommand(strselectsql, conn)) { using (sqldatareader dr = cmd.executereader(commandbehavior.closeconnection)) { while (dr.read()) { tentity entity = new tentity(); foreach (propertyinfo info in infos) { dfattrs = info.getcustomattributes(typeof(datafieldattribute), false); if (dfattrs.length > 0) { dfattr = dfattrs[0] as datafieldattribute; if (dfattr is datafieldattribute) { info.setvalue(entity, dr[dfattr.fieldname]); } } } list.add(entity); } } } } } catch (exception ex) { throw new exception(ex.tostring()); } //返回实体列表 return list; } /// <summary> /// 根据实体返回表名 /// </summary> /// <param name="entity"></param> /// <returns></returns> public static string gettablename<tentity>(tentity entity) where tentity : class { type entitytype = entity.gettype(); string strtablename = convert.tostring(_hashtablename[entitytype.fullname]); if (strtablename == "") { if (entitytype.getcustomattributes(typeof(datatableattribute), false)[0] is datatableattribute dtattr) { strtablename = dtattr.tablename; } else { throw new exception(entitytype.tostring() + "未设置datatable特性。"); } _hashtablename[entitytype.fullname] = strtablename; } return strtablename; } /// <summary> /// 根据实体返回主键 /// </summary> /// <param name="entity"></param> /// <returns></returns> public static string getkey<tentity>(tentity entity) where tentity : class { type entitytype = entity.gettype(); string strkey = convert.tostring(_hashkey[entitytype.fullname]); if (strkey == "") { if (entitytype.getcustomattributes(typeof(datatableattribute), false)[0] is datatableattribute dtattr) { strkey = dtattr.key; } else { throw new exception(entitytype.tostring() + "未设置datatable特性。"); } _hashkey[entitytype.fullname] = strkey; } return strkey; } } }
五、运行测试
新建一个控制台程序:
using system; using system.collections.generic; using system.linq; using system.text; using system.threading.tasks; using linkto.orm.model; namespace linkto.orm.client { class program { static void main() { //新增 person insertperson = new person { name = "hello", age = 18, gender = "male" }; int insertresult = dbhelper.insert(insertperson); console.writeline($"共新增了 {insertresult} 条记录。"); //更新 list<person> updatelist = dbhelper.query<person>("name='hello'"); int updateresult = 0; if (updatelist.count > 0) { foreach (var item in updatelist) { person updateperson = item; updateperson.age = 19; updateresult += dbhelper.update(updateperson); } } console.writeline($"共更新了 {updateresult} 条记录。"); //查询 list<person> selectlist = dbhelper.query<person>("name='hello'"); if (selectlist.count > 0) { foreach (var item in selectlist) { console.writeline("person.name = " + item.name); console.writeline("person.age = " + item.age); console.writeline("person.gender = " + item.gender); } } //删除 int deleteresult = dbhelper.delete<person>("name='hello'"); console.writeline($"共删除了 {deleteresult} 条记录。"); console.read(); } } }
运行结果如下:
参考自:
miniorm