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-Methodprivate 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}