利用反射将Datatable、SqlDataReader转换成List模型

1. DataTable转IList

 1 public class DataTableToList<T>whereT :new()
 2 
 3 {
 4 
 5 ///<summary>
 6 
 7 ///利用反射将Datatable转换成List模型
 8 
 9 ///</summary>
10 
11 ///<param name="dt"></param>
12 
13 ///<returns></returns>
14 
15 public static List<T> ConvertToList(DataTabledt)
16 
17 {
18 
19 List<T> list =newList<T>();
20 
21 Typetype =typeof(T);
22 
23 stringtempName =string.Empty;
24 
25 foreach(DataRowdrindt.Rows)
26 
27 {
28 
29 T t =newT();
30 
31 PropertyInfo[] propertys =
32 t.GetType().GetProperties();
33 
34 foreach(PropertyInfopiinpropertys)
35 
36 {
37 
38 tempName = pi.Name;
39 
40 if(dt.Columns.Contains(tempName))
41 
42 {
43 
44 if(!pi.CanWrite)
45 
46 {
47 
48 continue;
49 
50 }
51 
52 var value = dr[tempName];
53 
54 if(value !=DBNull.Value)
55 
56 {
57 
58 pi.SetValue(t, value,null);
59 
60 }
61 
62 }
63 
64 }
65 
66 list.Add(t);
67 
68 }
69 
70 returnlist;
71 
72 }
73 
74 }

2. SqlDataReader转IList

 1         /// <summary>
 2         /// 判断SqlDataReader是否存在某列
 3         /// </summary>
 4         /// <param name="dr">SqlDataReader</param>
 5         /// <param name="columnName">列名</param>
 6         /// <returns></returns>
 7         private bool readerExists(SqlDataReader dr, string columnName)
 8         {
 9 
10             dr.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
11 
12             return (dr.GetSchemaTable().DefaultView.Count > 0);
13 
14         }
15 
16         ///<summary>
17         ///利用反射和泛型将SqlDataReader转换成List模型
18         ///</summary>
19         ///<param name="sql">查询sql语句</param>
20         ///<returns></returns>
21 
22         public IList<T> ExecuteToList<T>(string sql) where T : new()
23 
24         {
25             IList<T> list;
26 
27             Type type = typeof (T);
28 
29             string tempName = string.Empty;
30 
31             using (SqlDataReader reader = ExecuteReader(sql))
32             {
33                 if (reader.HasRows)
34                 {
35                     list = new List<T>();
36                     while (reader.Read())
37                     {
38                         T t = new T();
39 
40                         PropertyInfo[] propertys = t.GetType().GetProperties();
41 
42                         foreach (PropertyInfo pi in propertys)
43                         {
44                             tempName = pi.Name;
45 
46                             if (readerExists(reader, tempName))
47                             {
48                                 if (!pi.CanWrite)
49                                 {
50                                     continue;
51                                 }
52                                 var value = reader[tempName];
53 
54                                 if (value != DBNull.Value)
55                                 {
56                                     pi.SetValue(t, value, null);
57                                 }
58 
59                             }
60 
61                         }
62 
63                         list.Add(t);
64 
65                     }
66                     return list;
67                 }
68             }
69             return null;
70         }

3、结果集从存储过程获取

 1   /// <summary>
 2         /// 处理存储过程
 3         /// </summary>
 4         /// <param name="spName">存储过程名</param>
 5         /// <param name="parameters">参数数组</param>
 6         /// <returns>sql数据流</returns>
 7         protected virtual SqlDataReader ExecuteReaderSP(string spName, ArrayList parameters)
 8         {
 9             SqlDataReader result = null;
10             cmd.CommandText = spName;
11             cmd.CommandType = CommandType.StoredProcedure;
12             cmd.Parameters.Clear();
13             if (parameters != null)
14             {
15                 foreach (SqlParameter param in parameters)
16                 {
17                     cmd.Parameters.Add(param);
18                 }
19             }
20             try
21             {
22                 Open();
23                 result = cmd.ExecuteReader(CommandBehavior.CloseConnection);
24             }
25             catch (Exception e)
26             {
27                 if (result != null && (!result.IsClosed))
28                 {
29                     result.Close();
30                 }
31                 LogHelper.WriteLog("
方法异常【ExecuteReaderSP(string spName, ArrayList parameters)】" + spName, e);
32                 throw new Exception(e.Message);
33             }
34             return result;
35         }

 1 <strong>      </strong> ///<summary>
 2         ///利用反射将SqlDataReader转换成List模型
 3         ///</summary>
 4         ///<param name="spName">存储过程名称</param>
 5         ///<returns></returns>
 6 
 7         public IList<T> ExecuteQueryListSP<T>(string spName, params SqlParameter[] listParams) where T : new()
 8         {
 9             IList<T> list;
10 
11             Type type = typeof(T);
12 
13             string tempName = string.Empty;
14 
15             using (SqlDataReader reader = ExecuteReaderSP(spName, new ArrayList(listParams)))
16             {
17                 if (reader.HasRows)
18                 {
19                     list = new List<T>();
20                     while (reader.Read())
21                     {
22                         T t = new T();
23 
24                         PropertyInfo[] propertys = t.GetType().GetProperties();
25 
26                         foreach (PropertyInfo pi in propertys)
27                         {
28                             tempName = pi.Name;
29 
30                             //for (int intField = 0; intField < reader.FieldCount; intField++)
31                             //{//遍历该列名是否存在
32                             //}
33 
34                             if (readerExists(reader, tempName))
35                             {
36                                 if (!pi.CanWrite)
37                                 {
38                                     continue;
39                                 }
40                                 var value = reader[tempName];
41 
42                                 if (value != DBNull.Value)
43                                 {
44                                     pi.SetValue(t, value, null);
45                                 }
46 
47                             }
48 
49                         }
50 
51                         list.Add(t);
52 
53                     }
54                     return list;
55                 }
56             }
57             return null;
58         }

作者：dasihg

转载：http://blog.csdn.net/dasihg/article/details/8943811