一步步實現自己的ORM（二）

2019-11-14 16:07:13

字體：大中小

來源：轉載

供稿：網友

在第一篇《一步步實現自己的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);