Database.cs 23 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575
  1. using Lottomat.Data.EF.Extension;
  2. using Lottomat.Util;
  3. using Lottomat.Util.Ioc;
  4. using System;
  5. using System.Collections;
  6. using System.Collections.Generic;
  7. using System.Data;
  8. using System.Data.Common;
  9. using System.Data.Entity;
  10. using System.Data.Entity.Infrastructure;
  11. using System.Data.SqlClient;
  12. using System.Linq;
  13. using System.Linq.Expressions;
  14. using System.Text;
  15. using System.Data.Entity.Core.Metadata.Edm;
  16. using System.Reflection;
  17. using System.Text.RegularExpressions;
  18. using Microsoft.Practices.Unity;
  19. namespace Lottomat.Data.EF
  20. {
  21. /// <summary>
  22. /// 版 本 1.0
  23. /// Copyright (c) 2016-2017
  24. /// 创建人:赵轶
  25. /// 日 期:2015.10.10
  26. /// 描 述:操作数据库
  27. /// </summary>
  28. public class Database : IDatabase
  29. {
  30. #region 构造函数
  31. /// <summary>
  32. /// 构造方法
  33. /// </summary>
  34. /// <param name="connString">链接字符串</param>
  35. /// <param name="DbType">数据库类型</param>
  36. public Database(string connString, string DbType)
  37. {
  38. if (DbType == "")
  39. {
  40. dbcontext = (DbContext)UnityIocHelper.DBInstance.GetService<IDbContext>(new ParameterOverride(
  41. "connString", connString));
  42. }
  43. else
  44. {
  45. dbcontext = (DbContext)UnityIocHelper.DBInstance.GetService<IDbContext>(DbType, new ParameterOverride(
  46. "connString", connString));
  47. }
  48. }
  49. #endregion
  50. #region 属性
  51. /// <summary>
  52. /// 获取 当前使用的数据访问上下文对象
  53. /// </summary>
  54. public DbContext dbcontext { get; set; }
  55. /// <summary>
  56. /// 事务对象
  57. /// </summary>
  58. public DbTransaction dbTransaction { get; set; }
  59. #endregion
  60. #region 事物提交
  61. /// <summary>
  62. /// 事务开始
  63. /// </summary>
  64. /// <returns></returns>
  65. public IDatabase BeginTrans()
  66. {
  67. DbConnection dbConnection = ((IObjectContextAdapter)dbcontext).ObjectContext.Connection;
  68. if (dbConnection.State == ConnectionState.Closed)
  69. {
  70. dbConnection.Open();
  71. }
  72. dbTransaction = dbConnection.BeginTransaction();
  73. return this;
  74. }
  75. /// <summary>
  76. /// 提交当前操作的结果
  77. /// </summary>
  78. public int Commit()
  79. {
  80. try
  81. {
  82. int returnValue = dbcontext.SaveChanges();
  83. if (dbTransaction != null)
  84. {
  85. dbTransaction.Commit();
  86. this.Close();
  87. }
  88. return returnValue;
  89. }
  90. catch (Exception ex)
  91. {
  92. if (ex.InnerException != null && ex.InnerException.InnerException is SqlException)
  93. {
  94. SqlException sqlEx = ex.InnerException.InnerException as SqlException;
  95. string msg = ExceptionMessage.GetSqlExceptionMessage(sqlEx.Number);
  96. throw DataAccessException.ThrowDataAccessException(sqlEx, msg);
  97. }
  98. throw;
  99. }
  100. finally
  101. {
  102. if (dbTransaction == null)
  103. {
  104. this.Close();
  105. }
  106. }
  107. }
  108. /// <summary>
  109. /// 把当前操作回滚成未提交状态
  110. /// </summary>
  111. public void Rollback()
  112. {
  113. this.dbTransaction.Rollback();
  114. this.dbTransaction.Dispose();
  115. this.Close();
  116. }
  117. /// <summary>
  118. /// 关闭连接 内存回收
  119. /// </summary>
  120. public void Close()
  121. {
  122. dbcontext.Dispose();
  123. }
  124. #endregion
  125. #region 执行 SQL 语句
  126. public int ExecuteBySql(string strSql)
  127. {
  128. if (dbTransaction == null)
  129. {
  130. return dbcontext.Database.ExecuteSqlCommand(strSql);
  131. }
  132. else
  133. {
  134. dbcontext.Database.ExecuteSqlCommand(strSql);
  135. return dbTransaction == null ? this.Commit() : 0;
  136. }
  137. }
  138. public int ExecuteBySql(string strSql, params DbParameter[] dbParameter)
  139. {
  140. if (dbTransaction == null)
  141. {
  142. return dbcontext.Database.ExecuteSqlCommand(strSql, dbParameter);
  143. }
  144. else
  145. {
  146. dbcontext.Database.ExecuteSqlCommand(strSql, dbParameter);
  147. return dbTransaction == null ? this.Commit() : 0;
  148. }
  149. }
  150. public int ExecuteByProc(string procName)
  151. {
  152. if (dbTransaction == null)
  153. {
  154. return dbcontext.Database.ExecuteSqlCommand(DbContextExtensions.BuilderProc(procName));
  155. }
  156. else
  157. {
  158. dbcontext.Database.ExecuteSqlCommand(DbContextExtensions.BuilderProc(procName));
  159. return dbTransaction == null ? this.Commit() : 0;
  160. }
  161. }
  162. public int ExecuteByProc(string procName, params DbParameter[] dbParameter)
  163. {
  164. if (dbTransaction == null)
  165. {
  166. return dbcontext.Database.ExecuteSqlCommand(DbContextExtensions.BuilderProc(procName, dbParameter), dbParameter);
  167. }
  168. else
  169. {
  170. dbcontext.Database.ExecuteSqlCommand(DbContextExtensions.BuilderProc(procName, dbParameter), dbParameter);
  171. return dbTransaction == null ? this.Commit() : 0;
  172. }
  173. }
  174. #endregion
  175. #region 对象实体 添加、修改、删除
  176. public int Insert<T>(T entity) where T : class
  177. {
  178. dbcontext.Entry<T>(entity).State = EntityState.Added;
  179. return dbTransaction == null ? this.Commit() : 0;
  180. }
  181. public int Insert<T>(IEnumerable<T> entities) where T : class
  182. {
  183. foreach (var entity in entities)
  184. {
  185. dbcontext.Entry<T>(entity).State = EntityState.Added;
  186. }
  187. return dbTransaction == null ? this.Commit() : 0;
  188. }
  189. public int Delete<T>() where T : class
  190. {
  191. EntitySet entitySet = DbContextExtensions.GetEntitySet<T>(dbcontext);
  192. if (entitySet != null)
  193. {
  194. string tableName = entitySet.MetadataProperties.Contains("Table") && entitySet.MetadataProperties["Table"].Value != null
  195. ? entitySet.MetadataProperties["Table"].Value.ToString()
  196. : entitySet.Name;
  197. return this.ExecuteBySql(DbContextExtensions.DeleteSql(tableName));
  198. }
  199. return -1;
  200. }
  201. public int Delete<T>(T entity) where T : class
  202. {
  203. dbcontext.Set<T>().Attach(entity);
  204. dbcontext.Set<T>().Remove(entity);
  205. return dbTransaction == null ? this.Commit() : 0;
  206. }
  207. public int Delete<T>(IEnumerable<T> entities) where T : class
  208. {
  209. foreach (var entity in entities)
  210. {
  211. dbcontext.Set<T>().Attach(entity);
  212. dbcontext.Set<T>().Remove(entity);
  213. }
  214. return dbTransaction == null ? this.Commit() : 0;
  215. }
  216. public int Delete<T>(Expression<Func<T, bool>> condition) where T : class, new()
  217. {
  218. IEnumerable<T> entities = dbcontext.Set<T>().Where(condition).ToList();
  219. return entities.Count() > 0 ? Delete(entities) : 0;
  220. }
  221. public int Delete<T>(object keyValue) where T : class
  222. {
  223. EntitySet entitySet = DbContextExtensions.GetEntitySet<T>(dbcontext);
  224. if (entitySet != null)
  225. {
  226. string tableName = entitySet.MetadataProperties.Contains("Table") && entitySet.MetadataProperties["Table"].Value != null
  227. ? entitySet.MetadataProperties["Table"].Value.ToString()
  228. : entitySet.Name;
  229. string keyFlied = entitySet.ElementType.KeyMembers[0].Name;
  230. return this.ExecuteBySql(DbContextExtensions.DeleteSql(tableName, keyFlied, keyValue));
  231. }
  232. return -1;
  233. }
  234. public int Delete<T>(object[] keyValue) where T : class
  235. {
  236. EntitySet entitySet = DbContextExtensions.GetEntitySet<T>(dbcontext);
  237. if (entitySet != null)
  238. {
  239. string tableName = entitySet.MetadataProperties.Contains("Table") && entitySet.MetadataProperties["Table"].Value != null
  240. ? entitySet.MetadataProperties["Table"].Value.ToString()
  241. : entitySet.Name;
  242. string keyFlied = entitySet.ElementType.KeyMembers[0].Name;
  243. return this.ExecuteBySql(DbContextExtensions.DeleteSql(tableName, keyFlied, keyValue));
  244. }
  245. return -1;
  246. }
  247. public int Delete<T>(object propertyValue, string propertyName) where T : class
  248. {
  249. EntitySet entitySet = DbContextExtensions.GetEntitySet<T>(dbcontext);
  250. if (entitySet != null)
  251. {
  252. string tableName = entitySet.MetadataProperties.Contains("Table") && entitySet.MetadataProperties["Table"].Value != null
  253. ? entitySet.MetadataProperties["Table"].Value.ToString()
  254. : entitySet.Name;
  255. return this.ExecuteBySql(DbContextExtensions.DeleteSql(tableName, propertyName, propertyValue));
  256. }
  257. return -1;
  258. }
  259. public int Update<T>(T entity) where T : class
  260. {
  261. dbcontext.Set<T>().Attach(entity);
  262. Hashtable props = ConvertExtension.GetPropertyInfo<T>(entity);
  263. foreach (string item in props.Keys)
  264. {
  265. object value = dbcontext.Entry(entity).Property(item).CurrentValue;
  266. if (value != null)
  267. {
  268. if (value.ToString() == "&nbsp;")
  269. dbcontext.Entry(entity).Property(item).CurrentValue = null;
  270. dbcontext.Entry(entity).Property(item).IsModified = true;
  271. }
  272. }
  273. return dbTransaction == null ? this.Commit() : 0;
  274. }
  275. public int Update<T>(IEnumerable<T> entities) where T : class
  276. {
  277. foreach (var entity in entities)
  278. {
  279. this.Update(entity);
  280. }
  281. return dbTransaction == null ? this.Commit() : 0;
  282. }
  283. /// <summary>
  284. /// 批量修改
  285. /// </summary>
  286. /// <param name="modelModifyProps">要修改的列及修改后列的值集合</param>
  287. /// <param name="where">修改的条件</param>
  288. /// <param name="paramModifyStrings">修改列的名称的集合</param>
  289. /// <returns>返回受影响行数</returns>
  290. public int Modify<T>(T modelModifyProps, Expression<Func<T, bool>> where, params string[] paramModifyStrings) where T : class, new()
  291. {
  292. int req = -1;
  293. //获取符合条件的数据
  294. List<T> list = dbcontext.Set<T>().Where(where).ToList();
  295. Type t = typeof(T);
  296. //得到实体类属性值
  297. List<PropertyInfo> propertyInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
  298. //实体-属性集合字典
  299. Dictionary<string, PropertyInfo> dictionaryProps = new Dictionary<string, PropertyInfo>();
  300. //将实体属性重要修改属性,添加到集合中 Key-属性名 Value-属性对象
  301. propertyInfos.ForEach(p =>
  302. {
  303. if (paramModifyStrings.Contains(p.Name))
  304. {
  305. dictionaryProps.Add(p.Name, p);
  306. }
  307. });
  308. //循环要修改的属性名
  309. foreach (string paramModifyString in paramModifyStrings)
  310. {
  311. //判断要修改的属性名是否在实体类的属性集合中
  312. if (dictionaryProps.ContainsKey(paramModifyString))
  313. {
  314. //如果存在则去除要修改属性对象
  315. PropertyInfo info = dictionaryProps[paramModifyString];
  316. //取出要修改的值
  317. object newValue = info.GetValue(modelModifyProps, null);
  318. //批量设置要修改的对象的属性
  319. foreach (T n in list)
  320. {
  321. //为要修改的对象的要修改的属性设置新的值
  322. info.SetValue(n, newValue, null);
  323. }
  324. }
  325. }
  326. //req = dbcontext.SaveChanges();
  327. req = dbTransaction == null ? this.Commit() : 0;
  328. return req;
  329. }
  330. public int Update<T>(Expression<Func<T, bool>> condition) where T : class, new()
  331. {
  332. return 0;
  333. }
  334. #endregion
  335. #region 对象实体 查询
  336. public T FindEntity<T>(object keyValue) where T : class
  337. {
  338. return dbcontext.Set<T>().Find(keyValue);
  339. }
  340. public T FindEntity<T>(Expression<Func<T, bool>> condition) where T : class, new()
  341. {
  342. return dbcontext.Set<T>().Where(condition).FirstOrDefault();
  343. }
  344. public IQueryable<T> IQueryable<T>() where T : class, new()
  345. {
  346. return dbcontext.Set<T>();
  347. }
  348. public IQueryable<T> IQueryable<T>(Expression<Func<T, bool>> condition) where T : class, new()
  349. {
  350. return dbcontext.Set<T>().Where(condition);
  351. }
  352. public IEnumerable<T> FindList<T>() where T : class, new()
  353. {
  354. return dbcontext.Set<T>().ToList();
  355. }
  356. public IEnumerable<T> FindList<T>(Func<T, object> keySelector) where T : class, new()
  357. {
  358. return dbcontext.Set<T>().OrderBy(keySelector).ToList();
  359. }
  360. public IEnumerable<T> FindList<T>(Expression<Func<T, bool>> condition) where T : class, new()
  361. {
  362. return dbcontext.Set<T>().Where(condition).ToList();
  363. }
  364. public IEnumerable<T> FindList<T>(string strSql) where T : class
  365. {
  366. return FindList<T>(strSql, null);
  367. }
  368. public IEnumerable<T> FindList<T>(string strSql, DbParameter[] dbParameter) where T : class
  369. {
  370. using (var dbConnection = dbcontext.Database.Connection)
  371. {
  372. var IDataReader = new DbHelper(dbConnection).ExecuteReader(CommandType.Text, strSql, dbParameter);
  373. return ConvertExtension.IDataReaderToList<T>(IDataReader);
  374. }
  375. }
  376. public IEnumerable<T> FindList<T>(string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
  377. {
  378. string[] _order = !string.IsNullOrEmpty(orderField) ? orderField.Split(',') : new[] { "" };
  379. MethodCallExpression resultExp = null;
  380. var tempData = dbcontext.Set<T>().AsQueryable();
  381. try
  382. {
  383. if (!string.IsNullOrEmpty(_order[0]))
  384. {
  385. foreach (string item in _order)
  386. {
  387. string _orderPart = item;
  388. _orderPart = Regex.Replace(_orderPart, @"\s+", " ");
  389. string[] _orderArry = _orderPart.Split(' ');
  390. string _orderField = _orderArry[0];
  391. bool sort = isAsc;
  392. if (_orderArry.Length == 2)
  393. {
  394. isAsc = _orderArry[1].ToUpper() == "ASC" ? true : false;
  395. }
  396. var parameter = Expression.Parameter(typeof(T), "t");
  397. var property = typeof(T).GetProperty(_orderField);
  398. var propertyAccess = Expression.MakeMemberAccess(parameter, property);
  399. var orderByExp = Expression.Lambda(propertyAccess, parameter);
  400. resultExp = Expression.Call(typeof(Queryable), isAsc ? "OrderBy" : "OrderByDescending", new Type[] { typeof(T), property.PropertyType }, tempData.Expression, Expression.Quote(orderByExp));
  401. }
  402. }
  403. tempData = tempData.Provider.CreateQuery<T>(resultExp);
  404. tempData = tempData.Skip<T>(pageSize * (pageIndex - 1)).Take<T>(pageSize).AsQueryable();
  405. }
  406. catch (Exception e)
  407. {
  408. Console.WriteLine(e);
  409. }
  410. total = tempData.Count();
  411. return tempData.ToList();
  412. }
  413. public IEnumerable<T> FindList<T>(Expression<Func<T, bool>> condition, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class, new()
  414. {
  415. MethodCallExpression resultExp = null;
  416. var tempData = dbcontext.Set<T>().Where(condition);
  417. if (!string.IsNullOrEmpty(orderField))
  418. {
  419. string[] _order = orderField.Split(',');
  420. foreach (string item in _order)
  421. {
  422. string _orderPart = item;
  423. _orderPart = Regex.Replace(_orderPart, @"\s+", " ");
  424. string[] _orderArry = _orderPart.Split(' ');
  425. string _orderField = _orderArry[0];
  426. bool sort = isAsc;
  427. if (_orderArry.Length == 2)
  428. {
  429. isAsc = _orderArry[1].ToUpper() == "ASC" ? true : false;
  430. }
  431. var parameter = Expression.Parameter(typeof(T), "t");
  432. var property = typeof(T).GetProperty(_orderField);
  433. var propertyAccess = Expression.MakeMemberAccess(parameter, property);
  434. var orderByExp = Expression.Lambda(propertyAccess, parameter);
  435. resultExp = Expression.Call(typeof(Queryable), isAsc ? "OrderBy" : "OrderByDescending", new Type[] { typeof(T), property.PropertyType }, tempData.Expression, Expression.Quote(orderByExp));
  436. }
  437. }
  438. tempData = tempData.Provider.CreateQuery<T>(resultExp);
  439. total = tempData.Count();
  440. tempData = tempData.Skip<T>(pageSize * (pageIndex - 1)).Take<T>(pageSize).AsQueryable();
  441. return tempData.ToList();
  442. }
  443. public IEnumerable<T> FindList<T>(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class
  444. {
  445. return FindList<T>(strSql, null, orderField, isAsc, pageSize, pageIndex, out total);
  446. }
  447. public IEnumerable<T> FindList<T>(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total) where T : class
  448. {
  449. using (var dbConnection = dbcontext.Database.Connection)
  450. {
  451. StringBuilder sb = new StringBuilder();
  452. if (pageIndex == 0)
  453. {
  454. pageIndex = 1;
  455. }
  456. int num = (pageIndex - 1) * pageSize;
  457. int num1 = (pageIndex) * pageSize;
  458. string OrderBy = "";
  459. if (!string.IsNullOrEmpty(orderField))
  460. {
  461. if (orderField.ToUpper().IndexOf("ASC") + orderField.ToUpper().IndexOf("DESC") > 0)
  462. {
  463. OrderBy = "Order By " + orderField;
  464. }
  465. else
  466. {
  467. OrderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
  468. }
  469. }
  470. else
  471. {
  472. OrderBy = "order by (select 0)";
  473. }
  474. sb.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
  475. sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
  476. total = Convert.ToInt32(new DbHelper(dbConnection).ExecuteScalar(CommandType.Text, "Select Count(1) From (" + strSql + ") As t", dbParameter));
  477. var IDataReader = new DbHelper(dbConnection).ExecuteReader(CommandType.Text, sb.ToString(), dbParameter);
  478. return ConvertExtension.IDataReaderToList<T>(IDataReader);
  479. }
  480. }
  481. #endregion
  482. #region 数据源查询
  483. public DataTable FindTable(string strSql)
  484. {
  485. return FindTable(strSql, null);
  486. }
  487. public DataTable FindTable(string strSql, DbParameter[] dbParameter)
  488. {
  489. using (var dbConnection = dbcontext.Database.Connection)
  490. {
  491. var IDataReader = new DbHelper(dbConnection).ExecuteReader(CommandType.Text, strSql, dbParameter);
  492. return ConvertExtension.IDataReaderToDataTable(IDataReader);
  493. }
  494. }
  495. public DataTable FindTable(string strSql, string orderField, bool isAsc, int pageSize, int pageIndex, out int total)
  496. {
  497. return FindTable(strSql, null, orderField, isAsc, pageSize, pageIndex, out total);
  498. }
  499. public DataTable FindTable(string strSql, DbParameter[] dbParameter, string orderField, bool isAsc, int pageSize, int pageIndex, out int total)
  500. {
  501. using (var dbConnection = dbcontext.Database.Connection)
  502. {
  503. StringBuilder sb = new StringBuilder();
  504. if (pageIndex == 0)
  505. {
  506. pageIndex = 1;
  507. }
  508. int num = (pageIndex - 1) * pageSize;
  509. int num1 = (pageIndex) * pageSize;
  510. string OrderBy = "";
  511. if (!string.IsNullOrEmpty(orderField))
  512. {
  513. if (orderField.ToUpper().IndexOf("ASC", StringComparison.Ordinal) + orderField.ToUpper().IndexOf("DESC", StringComparison.Ordinal) > 0)
  514. {
  515. OrderBy = "Order By " + orderField;
  516. }
  517. else
  518. {
  519. OrderBy = "Order By " + orderField + " " + (isAsc ? "ASC" : "DESC");
  520. }
  521. }
  522. else
  523. {
  524. OrderBy = "order by (select 0)";
  525. }
  526. sb.Append("Select * From (Select ROW_NUMBER() Over (" + OrderBy + ")");
  527. sb.Append(" As rowNum, * From (" + strSql + ") As T ) As N Where rowNum > " + num + " And rowNum <= " + num1 + "");
  528. total = Convert.ToInt32(new DbHelper(dbConnection).ExecuteScalar(CommandType.Text, "Select Count(1) From (" + strSql + ") As t", dbParameter));
  529. var IDataReader = new DbHelper(dbConnection).ExecuteReader(CommandType.Text, sb.ToString(), dbParameter);
  530. DataTable resultTable = ConvertExtension.IDataReaderToDataTable(IDataReader);
  531. resultTable.Columns.Remove("rowNum");
  532. return resultTable;
  533. }
  534. }
  535. public object FindObject(string strSql)
  536. {
  537. return FindObject(strSql, null);
  538. }
  539. public object FindObject(string strSql, DbParameter[] dbParameter)
  540. {
  541. using (var dbConnection = dbcontext.Database.Connection)
  542. {
  543. return new DbHelper(dbConnection).ExecuteScalar(CommandType.Text, strSql, dbParameter);
  544. }
  545. }
  546. #endregion
  547. }
  548. }