C# DataTable 对象操作

实现DataTable按字段进行分类、按列数据汇总、序列化对象数组、所有字段转小写、动态对象数组、数据分页

分类DataTableClassfiy实体：

 /// <summary>
    /// 单个分类表
    /// </summary>
    public class DataTableClassfiy
    {
        /// <summary>
        /// 分类名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 分类数据
        /// </summary>
        public DataTable Data { get; set; }

        /// <summary>
        /// 该类型数量
        /// </summary>
        public int Count { get; set; }
    
    }

DataTable 操作类：

/// <summary>
    /// DataTable扩展类，
    /// </summary>
    public static class DataTableEx
    {
        /// <summary>
        /// 通过指定字段对DataTable进行分类
        /// </summary>
        /// <param name="dt">需要分类的表格</param>
        /// <param name="field">需要进行分类的字段</param>
        /// <param name="isFilterNull">是否过滤空属性</param>
        /// <param name="isHideData">是否赋值Data</param>
        /// <returns></returns>
        public static List<DataTableClassfiy> GetTableClassfiy(this DataTable dt, string field, bool isFilterNull = false, bool isHideData = false)
        {

            List<DataTableClassfiy> tableClassfiys = new List<DataTableClassfiy>();
            DataView dv = new DataView(dt);
            DataTable dtTJ = dv.ToTable(true, field);//获取该字段唯一值
            for (int i = 0; i < dtTJ.Rows.Count; i++)
            {
                string value = dtTJ.Rows[i][field].ToString();
                if (isFilterNull && string.IsNullOrWhiteSpace(value)) continue;
                string express = string.Format("{0}='{1}'", field, value);
                var res = dt.Select(express);//按条件查询出符合条件的行
                DataTable resDt = dt.Clone();//克隆一个表结构
                foreach (var j in res)
                {
                    resDt.ImportRow(j);//将符合条件的行放进新表
                }
                DataTableClassfiy tableClassfiy = new DataTableClassfiy()
                {
                    Name = value,
                    Data = !isHideData ? resDt : null,
                    Count = resDt.Rows.Count
                };
                tableClassfiys.Add(tableClassfiy);
            }
            return tableClassfiys;
        }

        /// <summary>
        /// 通过指定列，返回数据汇总
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        public static int GetSumByField(this DataTable dt, string field)
        {
            var count = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                try
                {
                    if (!(dt.Rows[i][field] is DBNull) && !string.IsNullOrWhiteSpace(dt.Rows[i][field].ToString()))
                    {
                        Console.WriteLine(dt.Rows[i][field].ToString());
                        count = count + int.Parse(dt.Rows[i][field].ToString());
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
            return count;
        }

        /// <summary>
        /// 返回DataTable的对象数组
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static List<T> GetTableEntity<T>(this DataTable dt, bool isDesriptionAttribute = false)
        {
            List<T> res = new List<T>();
            Type type = typeof(T);
            var pros = type.GetProperties();

            foreach (DataRow dr in dt.Rows)
            {
                var ins = (T)Activator.CreateInstance(type);
                foreach (var pro in pros)
                {
                    string cName = pro.Name;
                    if (isDesriptionAttribute is true)
                    {
                        cName = ((DescriptionAttribute)(pro.GetCustomAttributes(typeof(DescriptionAttribute), false)[0])).Description;
                    }
                    if (dt.Columns.Contains(cName))
                    {
                        string dataType = pro.PropertyType.ToString().ToLower();
                        if (dataType == "system.int32" || dataType == "system.double")
                        {
                            if (dr[cName] is DBNull)
                            {
                                pro.SetValue(ins, 0);
                                continue;
                            }
                            object value = 0;
                            if (dataType == "system.int32")
                            {
                                value = int.Parse(dr[cName].ToString());
                            }
                            else if (dataType == "system.double")
                            {
                                value = double.Parse(dr[cName].ToString());
                            }
                            pro.SetValue(ins, value);
                            continue;
                        }

                        pro.SetValue(ins, dr[cName] is DBNull ? null : dr[cName]);
                    }

                }

                res.Add(ins);
            }
            return res;
        }

        /// <summary>
        /// 将Datatable所有字段转小写
        /// </summary>
        /// <param name="dt"></param>
        public static void GetColumnsLow(this DataTable dt)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                dc.ColumnName = dc.ColumnName.ToLower();
            }
        }

        /// <summary>
        /// 获取DataTable的动态对象--------------DynamicObject的dynamic不能用于接口返回
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public static List<dynamic> AsObjDynamicEnumerable(this DataTable table)
        {
            return table.AsEnumerable().Select(row => new DynamicRow(row)).ToList<dynamic>();
        }

        /// <summary>
        /// 将DataTable 转换成 List<dynamic>------------ExpandoObject的dynamic才可以用于接口返回
        /// reverse 反转：控制返回结果中是只存在 FilterField 指定的字段,还是排除.
        /// [flase 返回FilterField 指定的字段]|[true 返回结果剔除 FilterField 指定的字段]
        /// FilterField  字段过滤，FilterField 为空 忽略 reverse 参数；返回DataTable中的全部数
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="reverse">
        /// 反转：控制返回结果中是只存在 FilterField 指定的字段,还是排除.
        /// [flase 返回FilterField 指定的字段]|[true 返回结果剔除 FilterField 指定的字段]
        ///</param>
        /// <param name="FilterField">字段过滤，FilterField 为空 忽略 reverse 参数；返回DataTable中的全部数据</param>
        /// <returns>List<dynamic></returns>
        public static List<dynamic> ToDbExObjnamicList(this DataTable table, bool reverse = true, params string[] FilterField)
        {
            var modelList = new List<dynamic>();
            foreach (DataRow row in table.Rows)
            {
                dynamic model = new ExpandoObject();
                 var dict = (IDictionary<string, object>)model;
                foreach (DataColumn column in table.Columns)
                {
                    if (FilterField.Length != 0)
                    {
                        if (reverse == true)
                        {
                            if (!FilterField.Contains(column.ColumnName))
                            {
                                dict[column.ColumnName] = row[column];
                            }
                        }
                        else
                        {
                            if (FilterField.Contains(column.ColumnName))
                            {
                                dict[column.ColumnName] = row[column];
                            }
                        }
                    }
                    else
                    {
                        dict[column.ColumnName] = row[column];
                    }
                }
                modelList.Add(model);
            }
            return modelList;
        }

        /// <summary>
        /// DataRow 转换成 dynamic------------ExpandoObject的dynamic才可以用于接口返回
        /// reverse 反转：控制返回结果中是只存在 FilterField 指定的字段,还是排除.
        /// [flase 返回FilterField 指定的字段]|[true 返回结果剔除 FilterField 指定的字段]
        /// FilterField  字段过滤，FilterField 为空 忽略 reverse 参数；返回DataTable中的全部数
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="reverse">
        /// 反转：控制返回结果中是只存在 FilterField 指定的字段,还是排除.
        /// [flase 返回FilterField 指定的字段]|[true 返回结果剔除 FilterField 指定的字段]
        ///</param>
        /// <param name="FilterField">字段过滤，FilterField 为空 忽略 reverse 参数；返回DataRow中的全部数据</param>
        /// <returns>List<dynamic></returns>
        public static dynamic ToDbExObjnamic(this DataRow row, bool reverse = true, params string[] FilterField)
        {
            dynamic model = new ExpandoObject();
            var dict = (IDictionary<string, object>)model;
            foreach (DataColumn column in row.Table.Columns)
            {
                if (FilterField.Length != 0)
                {
                    if (reverse == true)
                    {
                        if (!FilterField.Contains(column.ColumnName))
                        {
                            dict[column.ColumnName] = row[column] is DBNull ? null : row[column];
                        }
                    }
                    else
                    {
                        if (FilterField.Contains(column.ColumnName))
                        {
                            dict[column.ColumnName] = row[column] is DBNull?null: row[column];
                        }
                    }
                }
                else
                {
                    dict[column.ColumnName] = row[column] is DBNull ? null : row[column];
                }
            }
            return model;
        }

        /// <summary>
        /// DaTable截取
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="beginRecord"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        public static DataTable GetTablePageBreak(this DataTable dt,int beginRecord, int count)
        {
            DataTable dtTemp = dt.Clone();
            int endRecord = beginRecord + count;
            if (endRecord > dt.Rows.Count) endRecord = dt.Rows.Count;
            for (int i = beginRecord; i < endRecord; i++)
            {
                dtTemp.ImportRow(dt.Rows[i]);
            }
            return dtTemp;
        }

        #region Private-Method

        private sealed class DynamicRow : DynamicObject
        {
            private readonly DataRow _row;

            internal DynamicRow(DataRow row) { _row = row; }
            public override bool TryGetMember(GetMemberBinder binder, out object result)
            {
                var retVal = _row.Table.Columns.Contains(binder.Name);
                result = retVal ? _row[binder.Name] : null;
                return retVal;
            }
        }
        #endregion


    }