WCSDbCore.cs 5.1 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166
  1. using SqlSugar;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.IO;
  5. using System.Linq;
  6. using WMS.Util;
  7. namespace WMS.Core
  8. {
  9. public class WCSDbCore
  10. {
  11. private static Util.ConnectionConfig SysDbSet = new Util.ConnectionConfig();
  12. public static List<SqlSugar.ConnectionConfig> ConnectionConfigs = new List<SqlSugar.ConnectionConfig>();
  13. static WCSDbCore()
  14. {
  15. var wcsconfig = ConfigHelper.GetConnectionConfigs().WCSConnectionConfig;
  16. ConnectionConfigs.Add(new SqlSugar.ConnectionConfig()
  17. {
  18. ConnectionString = wcsconfig.ConnectionString,//连接符字串
  19. DbType = (DbType)wcsconfig.DataBaseType,//数据库类型
  20. IsAutoCloseConnection = wcsconfig.IsAutoCloseConn //不设成true要手动close);
  21. });
  22. //ConnectionConfigs.Add(new ConnectionConfig()
  23. //{
  24. // ConnectionString = SysDbInfo.GetConfig("wcsdatabaseconfig").ConnectionString,//连接符字串
  25. // DbType = SysDbInfo.GetConfig("wcsdatabaseconfig").DataBaseType,//数据库类型
  26. // IsAutoCloseConnection = SysDbInfo.GetConfig("wcsdatabaseconfig").IsAutoCloseConn //不设成true要手动close);
  27. //});
  28. }
  29. public static SqlSugarScope Db = new SqlSugarScope(new SqlSugar.ConnectionConfig()
  30. {
  31. ConnectionString = ConfigHelper.GetConnectionConfigs().WCSConnectionConfig.ConnectionString,//连接符字串
  32. DbType = (DbType)ConfigHelper.GetConnectionConfigs().WCSConnectionConfig.DataBaseType,//数据库类型
  33. IsAutoCloseConnection = ConfigHelper.GetConnectionConfigs().WCSConnectionConfig.IsAutoCloseConn //不设成true要手动close
  34. },
  35. db =>
  36. {
  37. //(A)全局生效配置点,一般AOP和程序启动的配置扔这里面 ,所有上下文生效
  38. //调试SQL事件,可以删掉
  39. db.Aop.OnLogExecuting = (sql, pars) =>
  40. {
  41. Console.WriteLine(sql);//输出sql,查看执行sql 性能无影响
  42. //获取原生SQL推荐 5.1.4.63 性能OK
  43. //UtilMethods.GetNativeSql(sql,pars)
  44. //获取无参数化SQL 对性能有影响,特别大的SQL参数多的,调试使用
  45. //UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
  46. };
  47. //多个配置就写下面
  48. //db.Ado.IsDisableMasterSlaveSeparation=true;
  49. //注意多租户 有几个设置几个
  50. //db.GetConnection(i).Aop
  51. });
  52. public static SqlSugarClient GetDbCtx()
  53. {
  54. try
  55. {
  56. if (SysDbSet == null || string.IsNullOrWhiteSpace(SysDbSet.ConnectionString))
  57. {
  58. SysDbSet = ConfigHelper.GetConnectionConfigs().WCSConnectionConfig;
  59. if (SysDbSet == null || string.IsNullOrWhiteSpace(SysDbSet.ConnectionString))
  60. {
  61. throw SysExCore.ThrowDbSetError();
  62. }
  63. }
  64. return GetDbCtx(SysDbSet.ConnectionString, (DbType)SysDbSet.DataBaseType, (InitKeyType)SysDbSet.InitKey, SysDbSet.IsAutoCloseConn);
  65. //return GetDbCtx("User ID=C##KWMS;Password=123456;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.200)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SID=ORCL)))", SysDbSet.DataBaseType, SysDbSet.InitKey, SysDbSet.IsAutoCloseConn);
  66. }
  67. catch (Exception ex)
  68. {
  69. throw SysExCore.ThrowDbConnError(ex);
  70. }
  71. }
  72. public static SqlSugarClient GetDbCtx(string connectionString, DbType dbType, InitKeyType initKey = InitKeyType.SystemTable, bool isAutoClose = true)
  73. {
  74. try
  75. {
  76. SqlSugarClient db = new SqlSugarClient(new SqlSugar. ConnectionConfig()
  77. {
  78. ConnectionString = connectionString,//必填, 数据库连接字符串
  79. DbType = dbType, //必填, 数据库类型
  80. IsAutoCloseConnection = isAutoClose, //默认false, 时候知道关闭数据库连接, 设置为true无需使用using或者Close操作
  81. InitKeyType = initKey //默认SystemTable, 字段信息读取, 如:该属性是不是主键,是不是标识列等等信息
  82. });
  83. if (SysSetCore.GetSysSet().IsLogSql)
  84. {
  85. db.Aop.OnLogExecuting = (sql, pars) =>
  86. {
  87. LogSql(sql + "\r\n" + db.Utilities.SerializeObject(pars.ToDictionary(it => it.ParameterName, it => it.Value)));
  88. };
  89. }
  90. return db;
  91. }
  92. catch (Exception ex)
  93. {
  94. throw SysExCore.ThrowDbConnError(ex);
  95. }
  96. }
  97. private static void LogSql(string Sql)
  98. {
  99. try
  100. {
  101. string Path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "LogSQL\\";
  102. if (!Directory.Exists(Path))
  103. {
  104. Directory.CreateDirectory(Path);
  105. }
  106. string filename = Path + "LogDB_" + DateTime.Now.ToString("yyyyMMdd");
  107. string LogText = DateTime.Now.ToString("yyyyMMddHHmmssffff") + " " + Sql;
  108. File.AppendAllLines(filename, new List<string>() { LogText });
  109. }
  110. catch
  111. {
  112. }
  113. }
  114. public static void DbConnExec(Action<SqlSugarClient> Execbody)
  115. {
  116. SqlSugarClient Ctx = null;
  117. try
  118. {
  119. Ctx = GetDbCtx();
  120. Execbody.Invoke(Ctx);
  121. }
  122. catch (Exception ex)
  123. {
  124. throw ex;
  125. }
  126. finally
  127. {
  128. Ctx?.Dispose();
  129. }
  130. }
  131. public static void DbTranExec(Action<SqlSugarClient> Execbody)
  132. {
  133. SqlSugarClient Ctx = null;
  134. try
  135. {
  136. Ctx = GetDbCtx();
  137. Ctx.Ado.BeginTran();
  138. Execbody.Invoke(Ctx);
  139. Ctx.Ado.CommitTran();
  140. }
  141. catch (Exception ex)
  142. {
  143. Ctx.Ado.RollbackTran();
  144. throw ex;
  145. }
  146. finally
  147. {
  148. Ctx?.Dispose();
  149. }
  150. }
  151. }
  152. }