C# DataTable 和List之间相互转换的方法

一、List/IEnumerable转换到DataTable/DataView

方法一：

/// <summary>
/// Convert a List{T} to a DataTable.
/// </summary>
 private DataTable ToDataTable<T>(List<T> 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;
}
}

方法二：

public static DataTable ToDataTable<T>(IEnumerable<T> collection)
{
 var props = typeof(T).GetProperties();
 var dt = new DataTable();
 dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
 if (collection.Count() > 0)
 {
     for (int i = 0; i < collection.Count(); i++)
     {
         ArrayList tempList = new ArrayList();
         foreach (PropertyInfo pi in props)
         {
             object obj = pi.GetValue(collection.ElementAt(i), null);
             tempList.Add(obj);
         }
         object[] array = tempList.ToArray();
         dt.LoadDataRow(array, true);
     }
 }
 return dt;
 }

二、DataTable转换到List

方法一:

public static IList<T> ConvertTo<T>(DataTable table)  

{
if (table == null)
{
return null;
}
List rows = new List();
foreach (DataRow row in table.Rows)
{
rows.Add(row);
}
return ConvertTo(rows);
}
public static IList ConvertTo(IList rows)
{
IList list = null;
if (rows != null)
{
list = new List();
foreach (DataRow row in rows)
{
T item = CreateItem(row);
list.Add(item);
}
}
return list;
}

public static T CreateItem<T>(DataRow row)    

{
T obj = default(T);
if (row != null)
{
obj = Activator.CreateInstance();

   foreach (DataColumn column in row.Table.Columns)    
   {    
       PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);    
       try   
       {    
           object value = row[column.ColumnName];    
           prop.SetValue(obj, value, null);    
       }    
       catch   
       {  //You can log something here     
           //throw;    
       }    
   }    
}     

return obj;
}

方法二:

把查询结果以DataTable返回很方便，但是在检索数据时又很麻烦，没有模型类型检索方便。

所以很多人都是按照以下方式做的：

// 获得查询结果
DataTable dt = DbHelper.ExecuteDataTable(...);
// 把DataTable转换为IList
IList users = ConvertToUserInfo(dt);

问题：如果此系统有几十上百个模型，那不是每个模型中都要写个把DataTable转换为此模型的方法吗？

解决：能不能写个通用类，可以把DataTable转换为任何模型，呵呵，这就需要利用反射和泛型了

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Reflection;
namespace NCL.Data
{
///

/// 实体转换辅助类
///

public class ModelConvertHelper where T : new()
{
public static IList ConvertToModel(DataTable dt)
{
// 定义集合
IList ts = new List();

        // 获得此模型的类型   
         Type type = typeof(T);      
        string tempName = "";     
 foreach (DataRow dr in dt.Rows)      
         {    
             T t = new T();     
            // 获得此模型的公共属性      
             PropertyInfo[] propertys = t.GetType().GetProperties(); 
            foreach (PropertyInfo pi in propertys)      
             {      
                 tempName = pi.Name;  // 检查DataTable是否包含此列    

                if (dt.Columns.Contains(tempName))      
                 {      
                    // 判断此属性是否有Setter      
                    if (!pi.CanWrite) continue;         
    object value = dr[tempName];      
                    if (value != DBNull.Value)      
                         pi.SetValue(t, value, null);  
                 }     
             }      
             ts.Add(t);      
         }     
        return ts;     
     }     
 }    

}

使用方式：
// 获得查询结果
DataTable dt = DbHelper.ExecuteDataTable(...);
// 把DataTable转换为IList
IList users = ModelConvertHelper.ConvertToModel(dt);