你的位置:首页 > ASP.net教程

[ASP.net教程]一步步实现自己的ORM(二)


在第一篇《一步步实现自己的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;    }  }

View Code

修改后的实体类如下:

  [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();    }

View Code

运行结果

 

我们再定义一个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();    }

View Code

调用代码:

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

View Code

调用代码:

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

View Code

大功告成,我们修改下调用代码

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