OracleContext.cs 6.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167
  1. using DW5S.Entity;
  2. using Microsoft.EntityFrameworkCore;
  3. using Microsoft.EntityFrameworkCore.Metadata.Conventions;
  4. using Microsoft.Extensions.Logging;
  5. using System.Configuration;
  6. namespace DW5S.Repostory
  7. {
  8. public class OracleContext : DbContext
  9. {
  10. public OracleContext()
  11. {
  12. //发布为单文件后找不到Microsoft.EntityFrameworkCore.Relational.dll,需要将这个dll拷贝到AddIns中
  13. }
  14. public async Task ExecuteSqlAsync(string sql)
  15. {
  16. await this.Database.ExecuteSqlRawAsync(sql);
  17. }
  18. /// <summary>
  19. /// 执行SQL查询(查询最大ID需要给int?等可空类型,否则会出错)
  20. /// </summary>
  21. /// <typeparam name="T"></typeparam>
  22. /// <param name="sql"></param>
  23. /// <returns></returns>
  24. public async Task<List<T>> SqlQuery<T>(string sql)
  25. {
  26. var res = await this.Database.SqlQueryRaw<T>(sql).ToListAsync();
  27. return res;
  28. }
  29. /// <summary>
  30. /// 更新所有表的序列值为对应表的最大ID(更新后需要重新编译触发器)
  31. /// </summary>
  32. public void UpdateSeq()
  33. {
  34. List<string> tables = new List<string>();
  35. var props = typeof(OracleContext).GetProperties().Where(p => p.PropertyType.Name.StartsWith("DbSet")).ToList();
  36. foreach (var item in props)
  37. {
  38. var tb = item.PropertyType.GetGenericArguments().First().Name.ToUpper();
  39. tables.Add(tb);
  40. }
  41. foreach (var tb in tables)
  42. {
  43. try
  44. {
  45. int? idMax = this.SqlQuery<int?>($"select max(id) from {tb}").Result.FirstOrDefault();
  46. if (idMax == null)
  47. idMax = 1;
  48. string sql = $"DROP SEQUENCE SQ_{tb}";
  49. this.ExecuteSqlAsync(sql).Wait();
  50. sql = $"CREATE SEQUENCE SQ_{tb} INCREMENT BY 1 START WITH {idMax}";
  51. this.ExecuteSqlAsync(sql).Wait();
  52. }
  53. catch (Exception ex)
  54. {
  55. Console.WriteLine(ex.Message);
  56. }
  57. }
  58. }
  59. protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  60. {
  61. var conStr = ConfigurationManager.ConnectionStrings["OracleCon"].ToString();
  62. Console.WriteLine($"conStr={conStr}");
  63. optionsBuilder.UseOracle(conStr,
  64. options =>
  65. {
  66. //options.MigrationsAssembly("Ips.Sps.Repostory");
  67. options.UseOracleSQLCompatibility("11");//11=Oracle11g,12=Oracle12c
  68. })
  69. //.UseSnakeCaseNamingConvention()//FullName->full_name
  70. //.UseLowerCaseNamingConvention()//FullName->fullname
  71. //.UseUpperSnakeCaseNamingConvention()// FullName->FULL_NAME
  72. .UseUpperCaseNamingConvention();//FullName->FULLNAME
  73. //sql语句输出到日志文件
  74. var loggerFactory = new LoggerFactory();
  75. loggerFactory.AddProvider(new SqlLoggerProvider());
  76. optionsBuilder.UseLoggerFactory(loggerFactory);
  77. base.OnConfiguring(optionsBuilder);
  78. }
  79. protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
  80. {
  81. //EFCore默认表名会带上复数形式,通过这句话删除复数约定
  82. configurationBuilder.Conventions.Remove(typeof(TableNameFromDbSetConvention));
  83. }
  84. protected override void OnModelCreating(ModelBuilder modelBuilder)
  85. {
  86. //在Oracle11g中列名、索引、字段、表名、序列、触发器等名称长度均不能超过30个字符
  87. //自动生成的超出30个字符的需要在下面自定义
  88. modelBuilder.Entity<CgRes>().HasOne(p => p.StationRes).WithMany()
  89. .HasConstraintName("FK_CGRES_STATIONRESID");
  90. modelBuilder.Entity<SampleInfo>().HasOne(p => p.SigInfo).WithOne()
  91. .HasConstraintName("FK_SAMPLEINFO_SIGINFOID");
  92. modelBuilder.Entity<PosRes>().HasOne(p => p.StationRes).WithOne()
  93. .HasConstraintName("FK_POSRES_STATIONRESID");
  94. modelBuilder.Entity<PosRes>().HasOne(p => p.TargetInfo).WithOne()
  95. .HasConstraintName("FK_POSRES_TARGETINFOID");
  96. modelBuilder.Entity<RefTaskFreq>().HasOne(p => p.SampleInfo).WithOne()
  97. .HasConstraintName("FK_REFTASKFREQ_SAMPLEINFOID");
  98. modelBuilder.Entity<RefTaskFreq>().HasOne(p => p.SatInfo).WithOne()
  99. .HasConstraintName("FK_REFTASKFREQ_SATINFOID");
  100. modelBuilder.Entity<LogRes>().HasIndex(p => new { p.Module, p.LogTime, p.LogType })
  101. .HasDatabaseName("IX_LOGRES_MODULE_TIME_TYPE");
  102. modelBuilder.Entity<RefCgRes>().HasIndex(p => new { p.FileTime, p.FrequpHz, p.YbSnr })
  103. .HasDatabaseName("IX_REFCGRES_TIME_FREQ_SNR");
  104. modelBuilder.Entity<CgRes>().HasIndex(p => new { p.TaskID, p.SigTime, p.FrequpHz })
  105. .HasDatabaseName("IX_CGRES_TASKID_TIME_FREQ");
  106. modelBuilder.Entity<PosRes>().HasIndex(p => new { p.TaskID, p.SigTime, p.FrequpHz })
  107. .HasDatabaseName("IX_POSRES_TASKID_TIME_FREQ");
  108. }
  109. public DbSet<XlInfo> XlInfos { set; get; }
  110. public DbSet<TaskTemplate> TaskTemplates { set; get; }
  111. public DbSet<TaskInfo> TaskInfos { set; get; }
  112. public DbSet<TaskSig> TaskSigs { set; get; }
  113. public DbSet<TxInfo> TxInfos { get; set; }
  114. public DbSet<AdCard> AdCards { get; set; }
  115. public DbSet<AdChannel> AdChannels { get; set; }
  116. public DbSet<SatInfo> SatInfos { get; set; }
  117. public DbSet<FixedStation> FixedStation { get; set; }
  118. public DbSet<SigInfo> SigInfos { get; set; }
  119. public DbSet<SigDelay> SigDelays { get; set; }
  120. public DbSet<TargetInfo> TargetInfos { get; set; }
  121. public DbSet<SysSetings> SysSetings { get; set; }
  122. public DbSet<TaskRunnningInfo> TaskRunnningInfos { get; set; }
  123. public DbSet<SampleInfo> SampleInfos { get; set; }
  124. public DbSet<RefTaskFreq> RefTaskFreqs { get; set; }
  125. /*以下是Range分区表的DbSet*/
  126. public DbSet<AdRes> AdRes { get; set; }
  127. public DbSet<LogRes> LogRes { set; get; }
  128. public DbSet<StationRes> StationRes { get; set; }
  129. public DbSet<CxRes> CxRes { get; set; }
  130. public DbSet<CgRes> CgRes { get; set; }
  131. public DbSet<RefCgRes> RefCgRes { get; set; }
  132. public DbSet<CgXgfRes> CgXgfRes { get; set; }
  133. public DbSet<PosRes> PosRes { get; set; }
  134. public DbSet<CheckRes> CheckRes { get; set; }
  135. }
  136. }