1 //辅助查询 Author:高兵兵 2 public class QueryHelp 3 { 4 #region IList<T> ToList<T>(string cmdText,string connString) where T : new() 5 /// <summary> 6 /// 返回一个list 7 /// </summary> 8 /// <typeparam name="T">实体模型</typeparam> 9 /// <param name="cmdText">存储过程名</param> 10 /// <param name="connString">连接字符串</param> 11 /// <returns></returns> 12 public static IList<T> ToList<T>(string cmdText, string connString) where T : new() 13 { 14 using (var read = DbHelp.Create().ExecuteDataReader(cmdText, connString)) 15 { 16 IList<T> list = null; 17 var type = typeof(T); 18 if (read.HasRows) 19 { 20 21 list = new List<T>(); 22 } 23 while (read.Read()) 24 { 25 T t = new T(); 26 foreach (PropertyInfo item in type.GetProperties()) 27 { 28 for (int i = 0; i < read.FieldCount; i++) 29 { 30 //属性名与查询出来的列名比较 31 if (item.Name.ToLower() != read.GetName(i).ToLower()) continue; 32 var value = read[i]; 33 if (value != DBNull.Value) 34 { 35 item.SetValue(t, value, null); 36 } 37 break; 38 } 39 } 40 //将创建的对象添加到集合中 41 list.Add(t); 42 } 43 return list; 44 } 45 } 46 #endregion 47 48 #region IList<T> ToList<T>(string cmdText, List<DbParameter> listpar, string connString) where T : class, new() 49 /// <summary> 50 /// 返回一个list 51 /// </summary> 52 /// <typeparam name="T">实体模型</typeparam> 53 /// <param name="cmdText">存储过程名</param> 54 /// <param name="listpar">参数列表</param> 55 /// <param name="connString">连接字符串</param> 56 /// <returns></returns> 57 public static IList<T> ToList<T>(string cmdText, List<DbParameter> listpar, string connString) where T : class, new() 58 { 59 using (var read = DbHelp.Create().ExecuteDataReader(cmdText, listpar, connString)) 60 { 61 List<T> list = null; 62 var type = typeof(T); 63 if (read.HasRows) 64 { 65 66 list = new List<T>(); 67 } 68 while (read.Read()) 69 { 70 T t = new T(); 71 foreach (PropertyInfo item in type.GetProperties()) 72 { 73 for (int i = 0; i < read.FieldCount; i++) 74 { 75 //属性名与查询出来的列名比较 76 if (item.Name.ToLower() != read.GetName(i).ToLower()) continue; 77 object value = read[i]; 78 if (value != DBNull.Value) 79 { 80 item.SetValue(t, value, null); 81 } 82 break; 83 } 84 } 85 //将创建的对象添加到集合中 86 list.Add(t); 87 } 88 return list; 89 } 90 } 91 #endregion 92 93 #region IList<T> ToListAsPager<T>(string cmdText, List<DbParameter> listpar, string connString) where T : class, new() 94 /// <summary> 95 /// 返回一个list 96 /// </summary> 97 /// <typeparam name="T">实体模型</typeparam> 98 /// <param name="cmdText">存储过程名</param> 99 /// <param name="listpar">参数列表</param> 100 /// <param name="connString">连接字符串</param> 101 /// <returns></returns> 102 public static IList<T> ToListAsPager<T>(string cmdText, List<DbParameter> listpar, string connString) where T : class, new() 103 { 104 listpar[listpar.Count-1].Direction = ParameterDirection.Output; 105 using (var read = DbHelp.Create().ExecuteDataReader(cmdText, listpar, connString)) 106 { 107 List<T> list = null; 108 var type = typeof(T); 109 if (read.HasRows) 110 { 111 112 list = new List<T>(); 113 } 114 while (read.Read()) 115 { 116 T t = new T(); 117 foreach (PropertyInfo item in type.GetProperties()) 118 { 119 for (int i = 0; i < read.FieldCount; i++) 120 { 121 //属性名与查询出来的列名比较 122 if (item.Name.ToLower() != read.GetName(i).ToLower()) continue; 123 object value = read[i]; 124 if (value != DBNull.Value) 125 { 126 item.SetValue(t, value, null); 127 } 128 break; 129 } 130 } 131 //将创建的对象添加到集合中 132 list.Add(t); 133 } 134 return list; 135 } 136 } 137 #endregion 138 139 #region T FirstOrDefault<T>(string cmdText, string connString) 140 /// <summary> 141 /// 返回一个实体模型 142 /// </summary> 143 /// <typeparam name="T">实体模型</typeparam> 144 /// <param name="cmdText">存储过程名</param> 145 /// <param name="connString">连接字符串</param> 146 /// <returns></returns> 147 public static T FirstOrDefault<T>(string cmdText, string connString) where T : class,new() 148 { 149 using (var read = DbHelp.Create().ExecuteDataReader(cmdText, connString)) 150 { 151 152 Type type = typeof(T); 153 154 if (!read.Read()) return null; 155 T t = new T(); 156 foreach (PropertyInfo item in type.GetProperties()) 157 { 158 for (int i = 0; i < read.FieldCount; i++) 159 { 160 //属性名与查询出来的列名比较 161 if (item.Name.ToLower() != read.GetName(i).ToLower()) continue; 162 object value = read[i]; 163 if (value != DBNull.Value) 164 { 165 item.SetValue(t, value, null); 166 } 167 break; 168 } 169 170 } 171 return t; 172 } 173 } 174 #endregion 175 176 #region T FirstOrDefault<T>(string cmdText, List<DbParameter> list, string connString) 177 /// <summary> 178 /// 返回一个实体 179 /// </summary> 180 /// <typeparam name="T">实体模型</typeparam> 181 /// <param name="cmdText">存储过程名称</param> 182 /// <param name="ob">object[]</param> 183 /// <param name="connString">连接字符串</param> 184 /// <returns></returns> 185 public static T FirstOrDefault<T>(string cmdText, List<DbParameter> list, string connString) where T : class,new() 186 { 187 using (var read = DbHelp.Create().ExecuteDataReader(cmdText, list, connString)) 188 { 189 var type = typeof(T); 190 if (!read.Read()) return null; 191 var t = new T(); 192 foreach (var item in type.GetProperties()) 193 { 194 for (var i = 0; i < read.FieldCount; i++) 195 { 196 //属性名与查询出来的列名比较 197 if (item.Name.ToLower() != read.GetName(i).ToLower()) continue; 198 var value = read[i]; 199 if (value != DBNull.Value) 200 { 201 item.SetValue(t, value, null); 202 } 203 break; 204 } 205 } 206 return t; 207 } 208 } 209 #endregion 210 211 }
QueryHelp