最近開始使用LINQ與Entity Framework寫程式,一開始真的吃足苦頭!
看了一堆文章,確實解決了不少問題!
原本在使用SQL開發程式中,通常都是以DataSet為核心,只要能夠產生出DataSet,你想對資料做甚麼處理幾乎沒有難度了,如果是ㄧ些噁心的SQL語法就看個人功力囉...
最主要的是,當我們有需求用到Linq To DataTable & List To DataTable呢?
當然也會有人去試著寫寫轉換Class,我需要這個class的最主要原因在於希望可以彈性使用Crystal Report,因此有個datatable是最好不過了!
但是CR並不支援Nullable型態的欄位,所以在轉換時需要注意!
拜Goole之強大找到了兩個Class使用,轉換無憂無慮!
Linq To DataTable
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 WindowsFormsApplication1 { ////// Summary description for LinqToDataTable /// 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); } }
Example:
var query = from a in product select a; DataTable dt = new DataTable(); dt = query.ToDataTable(rec => new object[] { query });
List To DataTable
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 WindowsFormsApplication1 { public class ListToDataTable { /// /// Convert a List{T} to a DataTable. /// public static DataTable ToDataTable(List items) { var tb = new DataTable(typeof(T).Name); PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance); foreach (PropertyInfo prop in props) { Type t = GetCoreType(prop.PropertyType); tb.Columns.Add(prop.Name, t); } foreach (T item in items) { var values = new object[props.Length]; for (int i = 0; i < props.Length; i++) { values[i] = props[i].GetValue(item, null); } tb.Rows.Add(values); } return tb; } /// /// Determine of specified type is nullable /// public static bool IsNullable(Type t) { return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable < > )); } /// /// Return underlying type if type is Nullable otherwise return the type /// public static Type GetCoreType(Type t) { if (t != null && IsNullable(t)) { if (!t.IsValueType) { return t; } else { return Nullable.GetUnderlyingType(t); } } else { return t; } } } }
Example:
var query = from a in product select a; DataTable dt = new DataTable(); dt = ListToDataTable.ToDataTable(query.ToList());
請問一下~第二個list to datatable那個方法~
回覆刪除在public static DataTable ToDataTable(List items)
中的list會有錯誤~
和在var tb = new DataTable(typeof(T).Name);
中的(T)會出現錯誤~
可以請問大大一下我該如何解決ㄋㄟ~
謝謝你喔~麻煩你了
您好,我使用上是正常的,請問您的錯誤訊息是?
刪除