一步步实现自己的ORM(二)
2018-06-18 02:53:06来源:未知 阅读 ()
在第一篇《一步步实现自己的ORM(一)》里,我们用反射获取类名、属性和值,我们用这些信息开发了简单的INSERT方法,在上一篇文章里我们提到主键为什么没有设置成自增长类型,单单从属性里我们无法识别哪个是主键,今天我们用Attribute来标识列,关于Attribute,引用MSDN里描述
MADN的定义为:公共语言运行时允许添加类似关键字的描述声明,叫做attributes, 它对程序中的元素进行标注,如类型、字段、方法和属性等。Attributes和Microsoft .NET Framework文件的元数据(metadata)保存在一起,可以用来向运行时描述你的代码,或者在程序运行的时候影响应用程序的行为。 我们简单的总结为:定制特性attribute,本质上是一个类,其为目标元素提供关联附加信息,并在运行期以反射的方式来获取附加信息。
简单来说Attribute就是描述类、方法、属性参数等信息的。
可参考《浅析C#中的Attribute》
在这里我们定义2个Attribute,用来描述表和字段。
[AttributeUsage(AttributeTargets.Class)] class TableAttribute : Attribute { /// <summary> /// 表名 /// </summary> public string Name { get; private set; } public TableAttribute(string name) { this.Name = name; } } [AttributeUsage(AttributeTargets.Property)] class ColumnAttribute : Attribute { /// <summary> /// 是否为数据库自动生成 /// </summary> public bool IsGenerated { get; set; } /// <summary> /// 列名 /// </summary> public string Name { get; private set; } public ColumnAttribute(string name) { this.Name = name; } }
修改后的实体类如下:
[Table("tb_Users")] public class User { [Column("UserId",IsGenerated = true)] public int UserId { get; set; } [Column("Email")] public string Email { get; set; } [Column("CreatedTime")] public DateTime CreatedTime { get; set; } }
我们把表结构也修改下
CREATE TABLE [dbo].[tb_Users]( [UserId] [int] NOT NULL identity(1,1) PRIMARY KEY , [Email] [nvarchar](100) NULL, [CreatedTime] [datetime] NULL)
下面的问题,就是我们如何得到表名和字段名呢?我们还是采用反射来实现,先获取表名:
TableAttribute[] tableAttr = (TableAttribute[])typeof(User).GetCustomAttributes(typeof(TableAttribute), true); if (tableAttr.Length>0) { Console.WriteLine(tableAttr[0].Name); }
运行结果
再获取列名,先查找property,然后找Attribute,代码如下:
var properties = typeof(User).GetProperties(); for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var columnAttrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (columnAttrs.Length>0) { Console.WriteLine("字段名:{0},是否为自动生成:{1}", columnAttrs[0].Name, columnAttrs[0].IsGenerated); } }
运行结果
再来修改INSERT 方法如下:
public static int Insert(User user) { var type = typeof(User); Dictionary<string, object> parameters = new Dictionary<string, object>(); var properties = type.GetProperties(); string tableName = string.Empty; TableAttribute[] tableAttrs = (TableAttribute[])typeof(User).GetCustomAttributes(typeof(TableAttribute), true); if (tableAttrs.Length > 0) { tableName = tableAttrs[0].Name; } else { tableName = type.Name; } /*将所有的列放到集合里*/ List<ColumnAttribute> columns = new List<ColumnAttribute>(); for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var attrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (attrs.Length > 0) { columns.Add(attrs[0]); } } StringBuilder sql = new StringBuilder(); sql.Append("INSERT INTO [").Append(tableName).Append("]("); int paramIndex = 0; for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var attrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (attrs.Length > 0 && attrs[0].IsGenerated == false) { if (paramIndex > 0) sql.Append(","); sql.Append(attrs[0].Name); paramIndex++; } } sql.Append(") VALUES ("); paramIndex = 0; for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var attrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (attrs.Length > 0 && attrs[0].IsGenerated == false) { if (paramIndex > 0) sql.Append(","); sql.Append("@p").Append(paramIndex); parameters.Add("@p" + paramIndex, pi.GetValue(user, null)); paramIndex++; } } sql.Append(")"); Console.WriteLine(sql); SqlConnection conn = new SqlConnection(connectionString); var cmd = conn.CreateCommand(); cmd.CommandText = sql.ToString(); foreach (var item in parameters) { var pa = cmd.CreateParameter(); pa.ParameterName = item.Key; pa.Value = item.Value ?? DBNull.Value; cmd.Parameters.Add(pa); } conn.Open(); return cmd.ExecuteNonQuery(); }
运行结果
我们再定义一个IdAttribute 类用来表示主键,它不需要任何属性
[AttributeUsage(AttributeTargets.Property)] class IdAttribute :ColumnAttribute { public IdAttribute(string name) : base(name) { } }
有了主键我们下面可以写UPDATE和DELETE 方法:
UPDATE:
public static int Update(User user) { var type = typeof(User); Dictionary<string, object> parameters = new Dictionary<string, object>(); var properties = type.GetProperties(); string tableName = string.Empty; TableAttribute[] tableAttrs = (TableAttribute[])typeof(User).GetCustomAttributes(typeof(TableAttribute), true); if (tableAttrs.Length > 0) { tableName = tableAttrs[0].Name; } else { tableName = type.Name; } StringBuilder sql = new StringBuilder(); sql.Append("UPDATE [").Append(tableName).Append("] SET "); int paramIndex = 0; for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var idAttrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute), true); if (idAttrs.Length > 0) //如果是主键 跳过 continue; var columnAttrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (columnAttrs.Length > 0) { if (paramIndex > 0) sql.Append(","); // 字段 = @p sql.Append(columnAttrs[0].Name).Append("=").Append("@p" + paramIndex); /*参数*/ parameters.Add("@p" + paramIndex, pi.GetValue(user, null)); paramIndex++; } } sql.Append(" WHERE "); int keyIndex = 0; for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var idAttrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute), true); if (idAttrs.Length > 0) { if (keyIndex > 0) //考虑到有多个主键 sql.Append(" AND "); sql.Append(idAttrs[0].Name).Append("=").Append("@p").Append(paramIndex); /*参数*/ parameters.Add("@p" + paramIndex, pi.GetValue(user, null)); paramIndex++; keyIndex++; } } Console.WriteLine(sql); SqlConnection conn = new SqlConnection(connectionString); var cmd = conn.CreateCommand(); cmd.CommandText = sql.ToString(); foreach (var item in parameters) { var pa = cmd.CreateParameter(); pa.ParameterName = item.Key; pa.Value = item.Value ?? DBNull.Value; cmd.Parameters.Add(pa); } conn.Open(); return cmd.ExecuteNonQuery(); }
调用代码:
Update(new User() { UserId = 1, Email = "new@new.com", CreatedTime = DateTime.Now });
运行结果:
DELETE方法:
public static int DeleteByKey(params object[] values) { var type = typeof(User); Dictionary<string, object> parameters = new Dictionary<string, object>(); var properties = type.GetProperties(); string tableName = string.Empty; TableAttribute[] tableAttrs = (TableAttribute[])typeof(User).GetCustomAttributes(typeof(TableAttribute), true); if (tableAttrs.Length > 0) { tableName = tableAttrs[0].Name; } else { tableName = type.Name; } /*将所有的列放到集合里*/ List<IdAttribute> columns = new List<IdAttribute>(); for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var attrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute), true); if (attrs.Length > 0) { columns.Add(attrs[0]); } } if (columns.Count != values.Length) throw new ArgumentException("参数个数和主键数不一致"); StringBuilder sql = new StringBuilder(); sql.Append("DELETE FROM [").Append(tableName).Append("] ").Append(" WHERE "); for (int i = 0; i < columns.Count; i++) { if (i > 0) //考虑到有多个主键 sql.Append(" AND "); sql.Append(columns[i].Name).Append("=").Append("@p").Append(i); /*参数*/ parameters.Add("@p" + i, values[i]); } Console.WriteLine(sql); SqlConnection conn = new SqlConnection(connectionString); var cmd = conn.CreateCommand(); cmd.CommandText = sql.ToString(); foreach (var item in parameters) { var pa = cmd.CreateParameter(); pa.ParameterName = item.Key; pa.Value = item.Value ?? DBNull.Value; cmd.Parameters.Add(pa); } conn.Open(); return cmd.ExecuteNonQuery(); }
调用代码:
DeleteByKey(1);
运行结果
最后我们把增删改方法放在一个泛型类里。
class EntityHelper { private const string connectionString = ""; public static int Insert<T>(T entity) { var type = typeof(T); Dictionary<string, object> parameters = new Dictionary<string, object>(); var properties = type.GetProperties(); string tableName = string.Empty; TableAttribute[] tableAttrs = (TableAttribute[])type.GetCustomAttributes(typeof(TableAttribute), true); if (tableAttrs.Length > 0) { tableName = tableAttrs[0].Name; } else { tableName = type.Name; } /*将所有的列放到集合里*/ List<ColumnAttribute> columns = new List<ColumnAttribute>(); for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var attrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (attrs.Length > 0) { columns.Add(attrs[0]); } } StringBuilder sql = new StringBuilder(); sql.Append("INSERT INTO [").Append(tableName).Append("]("); int paramIndex = 0; for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var attrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (attrs.Length > 0 && attrs[0].IsGenerated == false) { if (paramIndex > 0) sql.Append(","); sql.Append(attrs[0].Name); paramIndex++; } } sql.Append(") VALUES ("); paramIndex = 0; for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var attrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (attrs.Length > 0 && attrs[0].IsGenerated == false) { if (paramIndex > 0) sql.Append(","); sql.Append("@p").Append(paramIndex); parameters.Add("@p" + paramIndex, pi.GetValue(entity, null)); paramIndex++; } } sql.Append(")"); Console.WriteLine(sql); SqlConnection conn = new SqlConnection(connectionString); var cmd = conn.CreateCommand(); cmd.CommandText = sql.ToString(); foreach (var item in parameters) { var pa = cmd.CreateParameter(); pa.ParameterName = item.Key; pa.Value = item.Value ?? DBNull.Value; cmd.Parameters.Add(pa); } conn.Open(); return cmd.ExecuteNonQuery(); } public static int Update<T>(T entity) { var type = typeof(T); Dictionary<string, object> parameters = new Dictionary<string, object>(); var properties = type.GetProperties(); string tableName = string.Empty; TableAttribute[] tableAttrs = (TableAttribute[])type.GetCustomAttributes(typeof(TableAttribute), true); if (tableAttrs.Length > 0) { tableName = tableAttrs[0].Name; } else { tableName = type.Name; } StringBuilder sql = new StringBuilder(); sql.Append("UPDATE [").Append(tableName).Append("] SET "); int paramIndex = 0; for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var idAttrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute), true); if (idAttrs.Length > 0) //如果是主键 跳过 continue; var columnAttrs = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), true); if (columnAttrs.Length > 0) { if (paramIndex > 0) sql.Append(","); // 字段 = @p sql.Append(columnAttrs[0].Name).Append("=").Append("@p" + paramIndex); /*参数*/ parameters.Add("@p" + paramIndex, pi.GetValue(entity, null)); paramIndex++; } } sql.Append(" WHERE "); int keyIndex = 0; for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var idAttrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute), true); if (idAttrs.Length > 0) { if (keyIndex > 0) //考虑到有多个主键 sql.Append(" AND "); sql.Append(idAttrs[0].Name).Append("=").Append("@p").Append(paramIndex); /*参数*/ parameters.Add("@p" + paramIndex, pi.GetValue(entity, null)); paramIndex++; keyIndex++; } } Console.WriteLine(sql); SqlConnection conn = new SqlConnection(connectionString); var cmd = conn.CreateCommand(); cmd.CommandText = sql.ToString(); foreach (var item in parameters) { var pa = cmd.CreateParameter(); pa.ParameterName = item.Key; pa.Value = item.Value ?? DBNull.Value; cmd.Parameters.Add(pa); } conn.Open(); return cmd.ExecuteNonQuery(); } public static int DeleteByKey<T>(params object[] values) { var type = typeof(T); Dictionary<string, object> parameters = new Dictionary<string, object>(); var properties = type.GetProperties(); string tableName = string.Empty; TableAttribute[] tableAttrs = (TableAttribute[])type.GetCustomAttributes(typeof(TableAttribute), true); if (tableAttrs.Length > 0) { tableName = tableAttrs[0].Name; } else { tableName = type.Name; } /*将所有的列放到集合里*/ List<IdAttribute> columns = new List<IdAttribute>(); for (int i = 0; i < properties.Length; i++) { var pi = properties[i]; var attrs = (IdAttribute[])pi.GetCustomAttributes(typeof(IdAttribute), true); if (attrs.Length > 0) { columns.Add(attrs[0]); } } if (columns.Count != values.Length) throw new ArgumentException("参数个数和主键数不一致"); StringBuilder sql = new StringBuilder(); sql.Append("DELETE FROM [").Append(tableName).Append("] ").Append(" WHERE "); for (int i = 0; i < columns.Count; i++) { if (i > 0) //考虑到有多个主键 sql.Append(" AND "); sql.Append(columns[i].Name).Append("=").Append("@p").Append(i); /*参数*/ parameters.Add("@p" + i, values[i]); } Console.WriteLine(sql); SqlConnection conn = new SqlConnection(connectionString); var cmd = conn.CreateCommand(); cmd.CommandText = sql.ToString(); foreach (var item in parameters) { var pa = cmd.CreateParameter(); pa.ParameterName = item.Key; pa.Value = item.Value ?? DBNull.Value; cmd.Parameters.Add(pa); } conn.Open(); return cmd.ExecuteNonQuery(); } }
大功告成,我们修改下调用代码
EntityHelper.Insert(new User() { Email = "abc@123.com", CreatedTime = DateTime.Now }); EntityHelper.Update(new User() { UserId = 1, Email = "new@new.com", CreatedTime = DateTime.Now }); EntityHelper.DeleteByKey<User>(1);
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
- C++冒泡排序 (基于函数模板实现) 2020-05-31
- opencv-12-高斯滤波-双边滤波(附C++代码实现) 2020-05-10
- 二叉排序树 2020-05-02
- 抽象宠物类的实现 代码参考 2020-04-29
- 虚函数实现多态性 代码参考 2020-04-28
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash