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

修改后的实体类如下：

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