菜鳥工程師訓練營

之前在工作上碰到一個bug，就是專案使用EntityFramework把資料撈出來，但是發現View沒有設定primary key，這時候如果前面欄位資料都一樣(譬如是同一分類或群組資料)，查出來的第一行資料就會蓋掉其他筆，造成bug，有嘗試在class欄位上加上[key]但還是沒辦法，最後只好改用ADO.NET撈資料再轉成List，所以今天就是要來分享怎麼轉

1.List轉DataTable

 public DataTable ConvertToDataTable<T>(IList<T> data)
        {
            PropertyDescriptorCollection properties =
               TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;

        }

2.DataTable轉List

        /// <summary>
        /// DataTable轉成List 透過反射
        /// </summary>
        public static IList<T> ToList<T>(this DataTable table) where T : new()
        {
            IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();
            IList<T> result = new List<T>();

            foreach (var row in table.Rows)
            {
                var item = CreateItemFromRow<T>((DataRow)row, properties);
                result.Add(item);
            }

            return result;
        }
        private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
        {
            T item = new T();
            foreach (var property in properties)
            {
                property.SetValue(item, row[property.Name], null);
            }
            return item;
        }

        /// <summary>
        /// DataTable轉成List 透過反射 欄位名稱與類別名稱不同 <DataTable欄位名稱.Class屬性名稱>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <param name="mappings"></param>
        /// <returns></returns>
        public static IList<T> ToList<T>(this DataTable table, Dictionary<string, string> mappings) where T : new()
        {
            IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();
            IList<T> result = new List<T>();

            foreach (var row in table.Rows)
            {
                var item = CreateItemFromRow<T>((DataRow)row, properties, mappings);
                result.Add(item);
            }

            return result;
        }
        private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties, Dictionary<string, string> mappings) where T : new()
        {
            T item = new T();
            foreach (var property in properties)
            {
                if (mappings.ContainsKey(property.Name))
                    property.SetValue(item, row[mappings[property.Name]], null);
            }
            return item;
        }

 

 

前面用法都是給DataSet使用，以下是給DataReader使用，對ADO.NET不太熟的可以看前篇文章

        /// <summary>
        /// 通過反射填充泛型集合List的方法
        /// </summary>
        /// <typeparam name="T">資料型態</typeparam>
        /// <param name="reader">單向捲動檢視 (Forward-only) 資料流</param>
        /// <returns>泛型集合List</returns>
        public static List<T> FillDataListGeneric<T>(IDataReader reader) where T : class
        {
            List<T> DataList = new List<T>();
            while (reader.Read())
            {
                T RowInstance = Activator.CreateInstance<T>();
                foreach (PropertyInfo Property in typeof(T).GetProperties())
                {
                    try
                    {
                        if (HasColumn(reader, Property.Name))
                        {
                            if (reader[Property.Name] != DBNull.Value)
                            {
                                Property.SetValue(RowInstance, Convert.ChangeType(reader[Property.Name], Property.PropertyType), null);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("通過反射填充泛型集合時發生錯誤，" + "錯誤原因：" + ex.Message);
                    }
                }
                DataList.Add(RowInstance);
            }
            return DataList;
        }