新文章

2012年9月14日 星期五

Linq To DataTable & List To DataTable


最近開始使用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());

2 則留言:

  1. 請問一下~第二個list to datatable那個方法~
    在public static DataTable ToDataTable(List items)
    中的list會有錯誤~
    和在var tb = new DataTable(typeof(T).Name);
    中的(T)會出現錯誤~
    可以請問大大一下我該如何解決ㄋㄟ~
    謝謝你喔~麻煩你了

    回覆刪除
    回覆
    1. 您好,我使用上是正常的,請問您的錯誤訊息是?

      刪除