using System; using System.Data; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Reflection; using System.Linq; using System.Xml.Linq; namespace UserFunction { /// <summary> /// Summary description for LinqToDataTable /// </summary> static public class LinqToDataTable { static public DataTable ToDataTable<T>(this IEnumerable<T> varlist, CreateRowDelegate<T> fn) { DataTable dtReturn = new DataTable(); // column names PropertyInfo[] oProps = null; // Could add a check to verify that there is an element 0 foreach (T rec in varlist) { // Use reflection to get property names, to create table, Only first time, others will follow if (oProps == null) { oProps = ((Type)rec.GetType()).GetProperties(); foreach (PropertyInfo pi in oProps) { Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>))) { colType = colType.GetGenericArguments()[0]; } dtReturn.Columns.Add(new DataColumn(pi.Name, colType)); } } DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps) { dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null); } dtReturn.Rows.Add(dr); } return (dtReturn); } public delegate object[] CreateRowDelegate<T>(T t); } } /* * sample: * var query = from ....; * DataTable dt = query.ToDataTable(rec => new object[] { query }); * */
公司有个新项目,使用sqlite作为数据库,其中有一个非常不爽的地方,如果当日期列出现NULL值时,通用DataAdepter.Fill方法填充到DateTable中时会发生错误,后来没办法只好使用dataReader循环读取数据来代替Fill,但又出现一个问题,如果保留表中的数据类型,当日期字段为空时无法插入到DataRow中,最后整个DataTable全使用string类型,这个问题一直困扰了很久,今日在网上无意中得到这样一篇代码,解决了这个问题,关键语句是 dataTable.LoadDataRow(array, true);用此方式可以将为NULL的字写入到值类型的数据单元中.
方法类: using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; //== using System.Collections; using System.Reflection; using System.Collections.Generic; /// <summary> ///tool 的摘要说明 /// </summary> public class tool { //public tool() //{ // // // //TODO: 在此处添加构造函数逻辑 // // //} /// <summary> /// 将泛型集合类转换成DataTable /// </summary> /// <typeparam name="T">集合项类型</typeparam> /// <param name="list">集合</param> /// <returns>数据集(表)</returns> /// //====表中无数据时使用: public static DataTable nullListToDataTable(IList list) { DataTable result = new DataTable(); object temp; if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { //if (!(pi.Name.GetType() is System.Nullable)) if (pi != null) { //pi = (PropertyInfo)temp; result.Columns.Add(pi.Name, pi.PropertyType); } } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return result; } //====表中有数据时使用: public static DataTable noNullListToDataTable<T>(IList<T> list) { DataSet ds = new DataSet(); DataTable dt = new DataTable(typeof(T).Name); DataColumn column; DataRow row; System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance); foreach (T t in list) { if (t == null) continue; row = dt.NewRow(); for (int i = 0, j = myPropertyInfo.Length; i < j; i++) { System.Reflection.PropertyInfo pi = myPropertyInfo[i]; String name = pi.Name; if (dt.Columns[name] == null) { if (pi.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.Int32]") { column = new DataColumn(name, typeof(Int32)); dt.Columns.Add(column); //row[name] = pi.GetValue(t, new object[] {i});//PropertyInfo.GetValue(object,object[]) if (pi.GetValue(t, null) != null) row[name] = pi.GetValue(t, null); else row[name] = System.DBNull.Value; } else { column = new DataColumn(name, pi.PropertyType); dt.Columns.Add(column); row[name] = pi.GetValue(t, null); } } } dt.Rows.Add(row); } ds.Tables.Add(dt); return ds.Tables[0]; } //表中有数据或无数据时使用,可排除DATASET不支持System.Nullable错误 public static DataTable ConvertToDataSet<T>(IList<T> list) { if (list == null || list.Count <= 0) //return null; { DataTable result = new DataTable(); object temp; if (list.Count > 0) { PropertyInfo[] propertys = list[0].GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { //if (!(pi.Name.GetType() is System.Nullable)) //if (pi!=null) { //pi = (PropertyInfo)temp; result.Columns.Add(pi.Name, pi.PropertyType); } } for (int i = 0; i < list.Count; i++) { ArrayList tempList = new ArrayList(); foreach (PropertyInfo pi in propertys) { object obj = pi.GetValue(list[i], null); tempList.Add(obj); } object[] array = tempList.ToArray(); result.LoadDataRow(array, true); } } return result; } else { DataSet ds = new DataSet(); DataTable dt = new DataTable(typeof(T).Name); DataColumn column; DataRow row; System.Reflection.PropertyInfo[] myPropertyInfo = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance); foreach (T t in list) { if (t == null) continue; row = dt.NewRow(); for (int i = 0, j = myPropertyInfo.Length; i < j; i++) { System.Reflection.PropertyInfo pi = myPropertyInfo[i]; String name = pi.Name; if (dt.Columns[name] == null) { if (pi.PropertyType.UnderlyingSystemType.ToString() == "System.Nullable`1[System.Int32]") { column = new DataColumn(name, typeof(Int32)); dt.Columns.Add(column); //row[name] = pi.GetValue(t, new object[] {i});//PropertyInfo.GetValue(object,object[]) if (pi.GetValue(t, null) != null) row[name] = pi.GetValue(t, null); else row[name] = System.DBNull.Value; } else { column = new DataColumn(name, pi.PropertyType); dt.Columns.Add(column); row[name] = pi.GetValue(t, null); } } } dt.Rows.Add(row); } ds.Tables.Add(dt); return ds.Tables[0]; } } } =============================================================================================== 调用: protected void Bind() { //var s = (from p in _7ctourDct.city // orderby p.cityName descending // select p); //List<city> c = new List<city>(); //c.AddRange(s.ToList()); //GridView1.DataSource = c; //GridView1.DataBind(); var s = from subsectionRoute in _7ctourDct.subsectionRoute select subsectionRoute; DataTable dt = new DataTable(); //var s = from subsectionRoute in _7ctourDct.subsectionRoute select subsectionRoute; //IList list = s.ToList(); //dt = ToDataTable(list); //or //dt = tool.ToDataTable(s.ToList()); if (s.ToList().Count >= 1) { dt = tool.noNullListToDataTable(s.ToList()); } else { dt = tool.nullListToDataTable(s.ToList()); } DataRow dr; //如果GRIDVIEW是自定义绑定字段,则必须为DT增加列,否则报错:数据源不包含字段 dt.Columns.Add(new DataColumn("subsectionRouteId", typeof(Int32))); dt.Columns.Add(new DataColumn("loginId", typeof(Int32))); dt.Columns.Add(new DataColumn("dayOrder", typeof(Int32))); dt.Columns.Add(new DataColumn("placeOrder", typeof(Int32))); dt.Columns.Add(new DataColumn("placeInDayOrder", typeof(String))); dt.Columns.Add(new DataColumn("cityId", typeof(Int32))); dt.Columns.Add(new DataColumn("actionTime", typeof(Int32))); dt.Columns.Add(new DataColumn("routeDetail", typeof(String))); dt.Columns.Add(new DataColumn("trafficPriceId", typeof(Int32))); dt.Columns.Add(new DataColumn("trafficDetail", typeof(Int32))); dt.Columns.Add(new DataColumn("trafficRemark", typeof(Int32))); dt.Columns.Add(new DataColumn("eateryPriceId", typeof(Int32))); dt.Columns.Add(new DataColumn("hotelPriceId", typeof(String))); dt.Columns.Add(new DataColumn("remark", typeof(Int32))); dt.Columns.Add(new DataColumn("basicGroupId", typeof(Int32))); dt.Columns.Add(new DataColumn("trafficType", typeof(Int32))); dt.Columns.Add(new DataColumn("trafficId", typeof(String))); for (int nIndex = 1; nIndex <= 8 - s.ToList().Count; nIndex++) { dr = dt.NewRow(); dt.Rows.Add(dr); } GridView1.DataSource = dt; GridView1.DataBind(); }
#region ListToDataTable /// <summary> /// ListToDataTable /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <returns></returns> public static DataTable ToDataTable<T>(this IEnumerable<T> list) { List<PropertyInfo> pList = new List<PropertyInfo>(); Type type = typeof(T); DataTable dt = new DataTable(); Array.ForEach<PropertyInfo>(type.GetProperties(), p => { pList.Add(p); dt.Columns.Add(p.Name, p.PropertyType); }); foreach (var item in list) { DataRow row = dt.NewRow(); pList.ForEach(p => row[p.Name] = p.GetValue(item, null)); dt.Rows.Add(row); } return dt; } #endregion