小奋斗 - 轻松学习从此开始!
IT小奋斗群 QQ群:62017228

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

介绍:List/IEnumerable转换到DataTable/DataView,以及DataTable转换到List

正文:

一、List<T>/IEnumerable转换到DataTable/DataView

 

方法一:

 

 1 /// <summary>
 2 /// Convert a List{T} to a DataTable.
 3 /// </summary>
 4 private DataTable ToDataTable<T>(List<T> items)
 5 {
 6     var tb = new DataTable(typeof (T).Name);
 7  
 8     PropertyInfo[] props = typeof (T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
 9  
10     foreach (PropertyInfo prop in props)
11     {
12         Type t = GetCoreType(prop.PropertyType);
13         tb.Columns.Add(prop.Name, t);
14     }
15  
16     foreach (T item in items)
17     {
18         var values = new object[props.Length];
19  
20         for (int i = 0; i < props.Length; i++)
21         {
22             values[i] = props[i].GetValue(item, null);
23         }
24  
25         tb.Rows.Add(values);
26     }
27  
28     return tb;
29 }
30  
31 /// <summary>
32 /// Determine of specified type is nullable
33 /// </summary>
34 public static bool IsNullable(Type t)
35 {
36     return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
37 }
38  
39 /// <summary>
40 /// Return underlying type if type is Nullable otherwise return the type
41 /// </summary>
42 public static Type GetCoreType(Type t)
43 {
44     if (t != null && IsNullable(t))
45     {
46         if (!t.IsValueType)
47         {
48             return t;
49         }
50         else
51         {
52             return Nullable.GetUnderlyingType(t);
53         }
54     }
55     else
56     {
57         return t;
58     }
59 }

方法二:

 1 public static DataTable ToDataTable<T>(IEnumerable<T> collection)
 2  {
 3      var props = typeof(T).GetProperties();
 4      var dt = new DataTable();
 5      dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
 6      if (collection.Count() > 0)
 7      {
 8          for (int i = 0; i < collection.Count(); i++)
 9          {
10              ArrayList tempList = new ArrayList();
11              foreach (PropertyInfo pi in props)
12              {
13                  object obj = pi.GetValue(collection.ElementAt(i), null);
14                  tempList.Add(obj);
15              }
16              object[] array = tempList.ToArray();
17              dt.LoadDataRow(array, true);
18          }
19      }
20      return dt;
21  }

二、DataTable转换到List

 

方法一:

 1 public static IList<T> ConvertTo<T>(DataTable table)  
 2 {  
 3    if (table == null)  
 4    {  
 5        return null;  
 6    }  
 7  
 8    List<DataRow> rows = new List<DataRow>();  
 9  
10    foreach (DataRow row in table.Rows)  
11    {  
12        rows.Add(row);  
13    }  
14  
15    return ConvertTo<T>(rows);  
16 }  
17  
18 public static IList<T> ConvertTo<T>(IList<DataRow> rows)  
19 {  
20    IList<T> list = null;  
21  
22    if (rows != null)  
23    {  
24        list = new List<T>();  
25  
26        foreach (DataRow row in rows)  
27        {  
28            T item = CreateItem<T>(row);  
29            list.Add(item);  
30        }  
31    }  
32  
33    return list;
34 }    
35  
36 public static T CreateItem<T>(DataRow row)    
37 {
38     T obj = default(T);    
39     if (row != null)    
40     {    
41        obj = Activator.CreateInstance<T>();    
42  
43        foreach (DataColumn column in row.Table.Columns)    
44        {    
45            PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);    
46            try   
47            {    
48                object value = row[column.ColumnName];    
49                prop.SetValue(obj, value, null);    
50            }    
51            catch   
52            {  //You can log something here     
53                //throw;    
54            }    
55        }    
56     }    
57  
58 return obj;    
59 }

方法二:

 

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

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

1
2
3
4
// 获得查询结果 
DataTable dt = DbHelper.ExecuteDataTable(...); 
// 把DataTable转换为IList<UserInfo> 
IList<UserInfo> users = ConvertToUserInfo(dt);

 

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

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

 

 

 

 
 1 using System;      
 2 using System.Collections.Generic;  
 3 using System.Text;    
 4 using System.Data;    
 5 using System.Reflection;  
 6 namespace NCL.Data    
 7 {    
 8     /// <summary>    
 9     /// 实体转换辅助类    
10     /// </summary>    
11     public class ModelConvertHelper<T> where   T : new()    
12      {    
13         public static IList<T> ConvertToModel(DataTable dt)    
14          {    
15             // 定义集合    
16              IList<T> ts = new List<T>(); 
17      
18             // 获得此模型的类型   
19              Type type = typeof(T);      
20             string tempName = "";      
21       
22             foreach (DataRow dr in dt.Rows)      
23              {    
24                  T t = new T();     
25                 // 获得此模型的公共属性      
26                  PropertyInfo[] propertys = t.GetType().GetProperties(); 
27                 foreach (PropertyInfo pi in propertys)      
28                  {      
29                      tempName = pi.Name;  // 检查DataTable是否包含此列    
30    
31                     if (dt.Columns.Contains(tempName))      
32                      {      
33                         // 判断此属性是否有Setter      
34                         if (!pi.CanWrite) continue;         
35    
36                         object value = dr[tempName];      
37                         if (value != DBNull.Value)      
38                              pi.SetValue(t, value, null);  
39                      }     
40                  }      
41                  ts.Add(t);      
42              }     
43             return ts;     
44          }     
45      }    
46 }

使用方式:  

1 // 获得查询结果  
2 DataTable dt = DbHelper.ExecuteDataTable(...);  
3 // 把DataTable转换为IList<UserInfo>  
4 IList<UserInfo> users = ModelConvertHelper<UserInfo>.ConvertToModel(dt);
5  

 

我来评几句
登录后评论

已发表评论数(0)