1 1 using Microsoft.Practices.EnterpriseLibrary.Data;
2 2 using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
3 3 using System;
4 4 using System.Collections.Generic;
5 5 using System.Data;
6 6 using System.Data.Common;
7 7 using System.Data.OleDb;
8 8 using System.Linq;
9 9 using System.Text;
10 10 using System.Threading.Tasks;
11 11 using Dapper;
12 12 using System.Collections;
13 13 using System.Reflection;
14 14 using Knet.H5.Entity.Core;
15 15
16 16 namespace Knet.H5.Toolkit.Data.Core
17 17 {
18 18 public class DapperDBase<T> where T : class,new()
19 19 {
20 20 public string TableName { get; set; }
21 21 public string Primarykey { get; set; }
22 22 public List<string> CoulmnsList { get; set; }
23 23 public DapperDBase()
24 24 {
25 25 var tablenameAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
26 26 Primarykey = tablenameAttribute.PrimaryKey;
27 27 TableName = tablenameAttribute.TableName;
28 28 CoulmnsList = GetEntityProperties(typeof(T));
29 29 }
30 30
31 31 /// 得到web.config里配置项的数据库连接字符串。
32 32 private static readonly string connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora9)));User Id=;Password=;";
33 33
34 34 private static DbConnection _db;
35 35
36 36 private static readonly object objLocker = new object();
37 37
38 38 public static DbConnection DB
39 39 {
40 40 get
41 41 {
42 42 if (_db == null)
43 43 {
44 44 lock (objLocker)
45 45 {
46 46 if (_db == null)
47 47 {
48 48 Database Db = new OracleDatabase(connectionString);
49 49 DbConnection connection = Db.CreateConnection();
50 50 return connection;
51 51 }
52 52 }
53 53 }
54 54 return _db;
55 55 }
56 56 }
57 57
58 58 /// <summary>
59 59 /// 根据ID获取model
60 60 /// </summary>
61 61 /// <param name="Id"></param>
62 62 /// <returns></returns>
63 63 public T GetModelById(object Id)
64 64 {
65 65 string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName + " WHERE ID = :ID ";
66 66 var conditon = new { ID = Id };
67 67 return DB.Query<T>(executeSql, conditon).SingleOrDefault() ?? default(T);
68 68 }
69 69
70 70 /// <summary>
71 71 /// 根据ID获取model
72 72 /// </summary>
73 73 /// <param name="Id"></param>
74 74 /// <returns></returns>
75 75 public T GetModelById(object Id, params string[] selectCoumlns)
76 76 {
77 77 var selectFields = string.Empty;
78 78 if (selectCoumlns.Length > 0)
79 79 {
80 80 selectFields = string.Join<string>(",", selectCoumlns);
81 81 }
82 82 else
83 83 {
84 84 selectFields = string.Join(",", CoulmnsList);
85 85 }
86 86 string executeSql = @" SELECT " + selectFields + " FROM " + this.TableName + " WHERE " + this.Primarykey + " = :ID ";
87 87 var conditon = new { ID = Id };
88 88 return DB.Query<T>(executeSql, conditon).SingleOrDefault() ?? default(T);
89 89 }
90 90
91 91 /// <summary>
92 92 /// 根据ID获取model
93 93 /// </summary>
94 94 /// <param name="Id"></param>
95 95 /// <returns></returns>
96 96 public T GetModelByWhere(object whereObj)
97 97 {
98 98 var wherePro = whereObj.GetType().GetProperties();
99 99 var whereList= new List<string>();
100 100 foreach (var item in wherePro)
101 101 {
102 102 if (item.GetValue(whereObj) == null) continue;
103 103 whereList.Add(string.Format("{0}=:{0}", item.Name));
104 104 }
105 105 string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName;
106 106 if (whereList.Count > 0)
107 107 {
108 108 executeSql += " WHERE " + string.Join(" AND ", whereList);
109 109 }
110 110 return DB.Query<T>(executeSql, whereObj).SingleOrDefault() ?? default(T);
111 111 }
112 112
113 113
114 114
115 115 /// <summary>
116 116 /// 获取列表
117 117 /// </summary>
118 118 /// <param name="whereStr">只能使用且的关系，且属于该model内的字段</param>
119 119 /// <param name="order">排序字段 create_date desc</param>
120 120 /// <returns></returns>
121 121 public List<T> GetList(object whereObj = null, string order = null)
122 122 {
123 123 var whereList = new List<string>();
124 124 if (whereObj != null)
125 125 {
126 126 var wherePro = whereObj.GetType().GetProperties();
127 127 foreach (var item in wherePro)
128 128 {
129 129 if (item.GetValue(whereObj) == null) continue;
130 130 whereList.Add(string.Format("{0}=:{0}", item.Name));
131 131 }
132 132 }
133 133 string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName;
134 134 if (whereList.Count > 0)
135 135 {
136 136 executeSql += " WHERE " + string.Join(" and ", whereList);
137 137 }
138 138 if (!string.IsNullOrEmpty(order))
139 139 {
140 140 executeSql += " ORDER BY " + order;
141 141 }
142 142 return DB.Query<T>(executeSql, whereObj).ToList();
143 143 }
144 144
145 145 /// <summary>
146 146 /// 获取列表
147 147 /// </summary>
148 148 /// <param name="whereStr">只能使用且的关系，且属于该model内的字段</param>
149 149 /// <param name="order">排序字段 create_date desc</param>
150 150 /// <returns></returns>
151 151 public List<T> GetList(string whereStr = null, string order = null)
152 152 {
153 153 string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName;
154 154 if (!string.IsNullOrEmpty(whereStr))
155 155 {
156 156 executeSql += " where " + whereStr;
157 157 }
158 158 if (!string.IsNullOrEmpty(order))
159 159 {
160 160 executeSql += "order by " + order;
161 161 }
162 162 return DB.Query<T>(executeSql, whereStr).ToList();
163 163 }
164 164
165 165
166 166 /// <summary>
167 167 /// 获取全部字段
168 168 /// </summary>
169 169 /// <param name="order">排序</param>
170 170 /// <returns></returns>
171 171 public List<T> GetAllList(string order = null)
172 172 {
173 173 string executeSql = @" SELECT " + string.Join(",", CoulmnsList) + " FROM " + this.TableName;
174 174 if (!string.IsNullOrEmpty(order))
175 175 {
176 176 executeSql += " ORDER BY " + order;
177 177 }
178 178 return DB.Query<T>(executeSql).ToList();
179 179 }
180 180
181 181 /// <summary>
182 182 /// 插入
183 183 /// </summary>
184 184 /// <param name="model"></param>
185 185 /// <returns></returns>
186 186 public int Add(T model)
187 187 {
188 188 string executeSql = @" INSERT INTO " + this.TableName + " (" + string.Join(",", CoulmnsList) + " ) VALUES (" + string.Join(",:", CoulmnsList).Insert(0, ":") + ") ";
189 189 return DB.Execute(executeSql, model);
190 190 }
191 191
192 192 /// <summary>
193 193 /// 更新
194 194 /// </summary>
195 195 /// <param name="model"></param>
196 196 /// <returns></returns>
197 197 public bool Update(T model)
198 198 {
199 199 var wherePro = model.GetType().GetProperties();
200 200 var whereSql = new List<string>();
201 201 foreach (var item in wherePro)
202 202 {
203 203 //if (item.GetValue(model) == null) continue;
204 204 whereSql.Add(string.Format("{0}=:{0}", item.Name));
205 205 }
206 206 string executeSql = @" UPDATE " + this.TableName + " SET " + string.Join(",", whereSql) + " WHERE " + this.Primarykey + "=:" + Primarykey;
207 207 return DB.Execute(executeSql, model) > 0;
208 208 }
209 209
210 210 /// <summary>
211 211 /// 根据条件更新指定的字段
212 212 /// </summary>
213 213 /// <param name="updateCoumlns"></param>
214 214 /// <param name="whereStr"></param>
215 215 /// <returns></returns>
216 216 public bool Update(object updateCoumlns, string whereStr)
217 217 {
218 218 var wherePro = updateCoumlns.GetType().GetProperties();
219 219 var whereSql = new List<string>();
220 220 foreach (var item in wherePro)
221 221 {
222 222 if (item.GetValue(updateCoumlns) == null) continue;
223 223 whereSql.Add(string.Format("{0}=:{0}", item.Name));
224 224 }
225 225 string executeSql = @" UPDATE " + this.TableName + " SET " + string.Join(",", whereSql);
226 226 if (!string.IsNullOrEmpty(whereStr))
227 227 {
228 228 executeSql += " WHERE " + whereStr;
229 229 }
230 230 return DB.Execute(executeSql, updateCoumlns) > 0;
231 231 }
232 232
233 233 //public bool Update(string[] fields,string value)
234 234
235 235 /// <summary>
236 236 /// 获取分页数据
237 237 /// </summary>
238 238 /// <param name="pageIndex"></param>
239 239 /// <param name="pageSize"></param>
240 240 /// <param name="selectFields">查询的字段</param>
241 241 /// <param name="whereObj"></param>
242 242 /// <param name="order"></param>
243 243 /// <returns></returns>
244 244 public PagedList<T> GetPagerList(int pageIndex, int pageSize, string[] selectFields = null, object whereObj = null, string order = null)
245 245 {
246 246 var whereList = new List<string>();
247 247 if (whereObj != null)
248 248 {
249 249 var wherePro = whereObj.GetType().GetProperties();
250 250 foreach (var item in wherePro)
251 251 {
252 252 if (item.GetValue(whereObj) == null) continue;
253 253 whereList.Add(string.Format("{0}=:{0}", item.Name));
254 254 }
255 255 }
256 256 string orderSql = string.Empty, whereSql = string.Empty, fields = " row_.*";
257 257 if (!string.IsNullOrEmpty(order))
258 258 {
259 259 orderSql = " ORDER BY " + order;
260 260 }
261 261 if (whereList.Count > 0)
262 262 {
263 263 whereSql = " WHERE " + string.Join(" and ", whereList);
264 264 }
265 265 if (selectFields != null && selectFields.Length > 0)
266 266 {
267 267 fields = string.Join(",", selectFields);
268 268 }
269 269 string executeSql = @" SELECT COUNT(0) FROM " + this.TableName + whereSql;
270 270 int totalCount = DB.Query<int>(executeSql, whereObj).SingleOrDefault();
271 271 string pagerSql = "SELECT * FROM ( SELECT " + fields + ", rownum rownum_ from ( SELECT * FROM " + this.TableName + whereSql + orderSql + ") row_ where rownum <= " + pageIndex * pageSize + ") where rownum_ >" + (pageIndex - 1) * pageSize + "";
272 272 var source = DB.Query<T>(pagerSql, whereObj).ToList();
273 273 return new PagedList<T>(source, pageIndex, pageSize, totalCount);
274 274 }
275 275
276 276 /// <summary>
277 277 /// 删除数据
278 278 /// </summary>
279 279 /// <param name="id"></param>
280 280 /// <returns></returns>
281 281 public bool Delete(object id)
282 282 {
283 283 string executeSql = @" DELETE FROM " + this.TableName + " WHERE " + this.Primarykey + " = :ID";
284 284 var conditon = new { ID = id };
285 285 return DB.Execute(executeSql, conditon) > 0;
286 286 }
287 287
288 288
289 289 #region 直接执行sql
290 290
291 291 /// <summary>
292 292 /// 执行sql语句，参数都以带入的形式
293 293 /// </summary>
294 294 /// <param name="executeSql"></param>
295 295 /// <param name="value"></param>
296 296 /// <returns></returns>
297 297 public bool Update(string executeSql, object value)
298 298 {
299 299 return DB.Execute(executeSql, value) > 0;
300 300 }
301 301
302 302 /// <summary>
303 303 /// 执行sql语句，参数都以带入的形式
304 304 /// </summary>
305 305 /// <param name="executeSql"></param>
306 306 /// <param name="value"></param>
307 307 /// <returns></returns>
308 308 public int Add(string executeSql, object value)
309 309 {
310 310 return DB.Execute(executeSql, value);
311 311 }
312 312
313 313 /// <summary>
314 314 /// 执行SQL获取table
315 315 /// </summary>
316 316 /// <param name="executeSql"></param>
317 317 /// <returns></returns>
318 318 public DataTable GetTable(string executeSql)
319 319 {
320 320 return DB.Query<DataTable>(executeSql).SingleOrDefault();
321 321 }
322 322
323 323 /// <summary>
324 324 /// 执行SQL获取LIST
325 325 /// </summary>
326 326 /// <param name="executeSql"></param>
327 327 /// <returns></returns>
328 328 public List<T> GetList(string executeSql)
329 329 {
330 330 return DB.Query<T>(executeSql).ToList();
331 331 }
332 332 #endregion
333 333
334 334 /// <summary>
335 335 /// 对datatable进行分页
336 336 /// </summary>
337 337 /// <param name="dt"></param>
338 338 /// <param name="PageIndex"></param>
339 339 /// <param name="PageSize"></param>
340 340 /// <returns></returns>
341 341 public DataTable SplitDataTable(DataTable dt, int PageIndex, int PageSize)
342 342 {
343 343 if (PageIndex == 0)
344 344 return dt;
345 345 DataTable newdt = dt.Clone();
346 346 //newdt.Clear();
347 347 int rowbegin = (PageIndex - 1) * PageSize;
348 348 int rowend = PageIndex * PageSize;
349 349
350 350 if (rowbegin >= dt.Rows.Count)
351 351 return newdt;
352 352
353 353 if (rowend > dt.Rows.Count)
354 354 rowend = dt.Rows.Count;
355 355 for (int i = rowbegin; i <= rowend - 1; i++)
356 356 {
357 357 DataRow newdr = newdt.NewRow();
358 358 DataRow dr = dt.Rows[i];
359 359 foreach (DataColumn column in dt.Columns)
360 360 {
361 361 newdr[column.ColumnName] = dr[column.ColumnName];
362 362 }
363 363 newdt.Rows.Add(newdr);
364 364 }
365 365
366 366 return newdt;
367 367 }
368 368
369 369
370 370 /// <summary>
371 371 /// 获取实体所有属性名称
372 372 /// </summary>
373 373 /// <param name="entity"></param>
374 374 /// <returns></returns>
375 375 private List<string> GetEntityProperties(Type type)
376 376 {
377 377 var list = new List<string>();
378 378 PropertyInfo[] properties = type.GetProperties();
379 379 foreach (var pro in properties)
380 380 {
381 381 var fieldsAttribute = new FieldsAttribute();
382 382 var attrmodel = pro.GetCustomAttributes<FieldsAttribute>(true).FirstOrDefault();
383 383 if (attrmodel != null )
384 384 {//controller上有标记
385 385 fieldsAttribute = attrmodel as FieldsAttribute;
386 386 }
387 387 if (!fieldsAttribute.IsSourceFields) continue;
388 388 // if ()
389 389 list.Add(pro.Name);
390 390 }
391 391 return list;
392 392 }
393 393 }
394 394 }
395
