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