public class DataTableHelper { /// /// 给DataTable增加一个自增列 /// 如果DataTable 存在 identityid 字段 则 直接返回DataTable 不做任何处理 /// /// DataTable /// 返回Datatable 增加字段 identityid public static DataTable AddIdentityColumn(DataTable dt) { if (!dt.Columns.Contains("identityid")) { dt.Columns.Add("identityid"); for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i]["identityid"] = (i + 1).ToString(); } } return dt; } /// /// 检查DataTable 是否有数据行 /// /// DataTable /// public static bool IsHaveRows(DataTable dt) { if (dt != null && dt.Rows.Count > 0) return true; return false; } /// /// DataTable转换成实体列表 /// /// 实体 T /// datatable /// public static IList DataTableToList (DataTable table) where T : class { if (!IsHaveRows(table)) return new List (); IList list = new List (); T model = default(T); foreach (DataRow dr in table.Rows) { model = Activator.CreateInstance (); foreach (DataColumn dc in dr.Table.Columns) { object drValue = dr[dc.ColumnName]; PropertyInfo pi = model.GetType().GetProperty(dc.ColumnName); if (pi != null && pi.CanWrite && (drValue != null && !Convert.IsDBNull(drValue))) { pi.SetValue(model, drValue, null); } } list.Add(model); } return list; } /// /// 实体列表转换成DataTable /// /// 实体 /// 实体列表 /// public static DataTable ListToDataTable (IList list) where T : class { if (list == null || list.Count <= 0) { return null; } DataTable dt = new DataTable(typeof(T).Name); DataColumn column; DataRow row; PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance); int length = myPropertyInfo.Length; bool createColumn = true; foreach (T t in list) { if (t == null) { continue; } row = dt.NewRow(); for (int i = 0; i < length; i++) { PropertyInfo pi = myPropertyInfo[i]; string name = pi.Name; if (createColumn) { column = new DataColumn(name, pi.PropertyType); dt.Columns.Add(column); } row[name] = pi.GetValue(t, null); } if (createColumn) { createColumn = false; } dt.Rows.Add(row); } return dt; } /// /// 将泛型集合类转换成DataTable /// /// 集合项类型 /// 集合 /// 数据集(表) public static DataTable ToDataTable (IList list) { return ToDataTable (list, null); } /// /// 将泛型集合类转换成DataTable /// /// 集合项类型 /// 集合 /// 需要返回的列的列名 /// 数据集(表) public static DataTable ToDataTable (IList list, params string[] propertyName) { List propertyNameList = new List (); if (propertyName != null) propertyNameList.AddRange(propertyName); DataTable result = new DataTable(); if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { if (propertyNameList.Count == 0) { result.Columns.Add(pi.Name, pi.PropertyType); } else { if (propertyNameList.Contains(pi.Name)) { result.Columns.Add(pi.Name, pi.PropertyType); } } } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { if (propertyNameList.Count == 0) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } else { if (propertyNameList.Contains(pi.Name)) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } } } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return result; } /// /// 根据nameList里面的字段创建一个表格,返回该表格的DataTable /// /// 包含字段信息的列表 /// DataTable public static DataTable CreateTable(List nameList) { if (nameList.Count <= 0) return null; DataTable myDataTable = new DataTable(); myDataTable.TableName = "tableName";//增加一个默认的名字 foreach (string columnName in nameList) { myDataTable.Columns.Add(columnName, typeof(string)); } return myDataTable; } /// /// 通过字符列表创建表字段，字段格式可以是： /// 1) a,b,c,d,e /// 2) a|int,b|string,c|bool,d|decimal /// /// /// public static DataTable CreateTable(string nameString) { string[] nameArray = nameString.Split(new char[] { ',', ';' }); List nameList = new List (); DataTable dt = new DataTable(); dt.TableName = "tableName";//增加一个默认的名字 foreach (string item in nameArray) { if (!string.IsNullOrEmpty(item)) { string[] subItems = item.Split('|'); if (subItems.Length == 2) { dt.Columns.Add(subItems[0], ConvertType(subItems[1])); } else { dt.Columns.Add(subItems[0]); } } } return dt; } private static Type ConvertType(string typeName) { typeName = typeName.ToLower().Replace("system.", ""); Type newType = typeof(string); switch (typeName) { case "boolean": case "bool": newType = typeof(bool); break; case "int16": case "short": newType = typeof(short); break; case "int32": case "int": newType = typeof(int); break; case "long": case "int64": newType = typeof(long); break; case "uint16": case "ushort": newType = typeof(ushort); break; case "uint32": case "uint": newType = typeof(uint); break; case "uint64": case "ulong": newType = typeof(ulong); break; case "single": case "float": newType = typeof(float); break; case "string": newType = typeof(string); break; case "guid": newType = typeof(Guid); break; case "decimal": newType = typeof(decimal); break; case "double": newType = typeof(double); break; case "datetime": newType = typeof(DateTime); break; case "byte": newType = typeof(byte); break; case "char": newType = typeof(char); break; } return newType; } /// /// 获得从DataRowCollection转换成的DataRow数组 /// /// DataRowCollection /// public static DataRow[] GetDataRowArray(DataRowCollection drc) { int count = drc.Count; DataRow[] drs = new DataRow[count]; for (int i = 0; i < count; i++) { drs[i] = drc[i]; } return drs; } /// /// 将DataRow数组转换成DataTable，注意行数组的每个元素须具有相同的数据结构， /// 否则当有元素长度大于第一个元素时，抛出异常 /// /// 行数组 /// public static DataTable GetTableFromRows(DataRow[] rows) { if (rows.Length <= 0) { return new DataTable(); } DataTable dt = rows[0].Table.Clone(); dt.DefaultView.Sort = rows[0].Table.DefaultView.Sort; for (int i = 0; i < rows.Length; i++) { dt.LoadDataRow(rows[i].ItemArray, true); } return dt; } /// /// 排序表的视图 /// /// /// /// public static DataTable SortedTable(DataTable dt, params string[] sorts) { if (dt.Rows.Count > 0) { string tmp = ""; for (int i = 0; i < sorts.Length; i++) { tmp += sorts[i] + ","; } dt.DefaultView.Sort = tmp.TrimEnd(','); } return dt; } /// /// 根据条件过滤表的内容 /// /// /// /// public static DataTable FilterDataTable(DataTable dt, string condition) { if (condition.Trim() == "") { return dt; } else { DataTable newdt = new DataTable(); newdt = dt.Clone(); DataRow[] dr = dt.Select(condition); for (int i = 0; i < dr.Length; i++) { newdt.ImportRow((DataRow)dr[i]); } return newdt; } } /// /// 转换.NET的Type到数据库参数的类型 /// /// /// public static DbType TypeToDbType(Type t) { DbType dbt; try { dbt = (DbType)Enum.Parse(typeof(DbType), t.Name); } catch { dbt = DbType.Object; } return dbt; } /// /// 使用分隔符串联表格字段的内容,如：a,b,c /// /// 表格 /// 字段名称 /// 增加的字符串，无则为空 /// 分隔符，如逗号(,) /// public static string ConcatColumnValue(DataTable dt, string columnName, string append, char splitChar) { string result = append; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { result += string.Format("{0}{1}", splitChar, row[columnName]); } } return result.Trim(splitChar); } /// /// 使用逗号串联表格字段的内容,如：a,b,c /// /// 表格 /// 字段名称 /// 增加的字符串，无则为空 /// public static string ConcatColumnValue(DataTable dt, string columnName, string append) { string result = append; if (dt != null && dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { result += string.Format(",{0}", row[columnName]); } } return result.Trim(','); } /// /// 判断表格是否包含指定的全部字段名称,如果其中一个不符合则返回false /// /// 表格对象 /// 字段列名称，逗号分开 /// public static bool ContainAllColumns(DataTable dt, string columnString) { bool result = true; if (dt != null && !string.IsNullOrEmpty(columnString)) { List columnList = columnString.Split(',').ToList(); foreach (string columnName in columnList) { if (!string.IsNullOrEmpty(columnName) && !dt.Columns.Contains(columnName)) { result = false; } } } else { result = false; } return result; } }