在生成数据表的实体类时，利用自定义特性，给它打上表及字段的特性，然后使用反射原理，将自定义特性拼接成增、删、改、查对应的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]
定义两个自定义特性：
此为数据表特性，包含表名（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;
}
}
此为字段特性，包含字段名（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)
{ }
}
依照前面的规划，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.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","...")]。相同的原理，属性名也是可以更改的。
数据表的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
如对本文有疑问， 点击进行留言回复！！
使用Visual Studio2019创建C#项目(窗体应用程序、控制台应用程序、Web应用程序)
C#实现获取本地内网(局域网)和外网(公网)IP地址的方法分析
网友评论