1
0

DbHelper.cs 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339
  1. using System;
  2. using System.IO;
  3. using System.Linq;
  4. using System.Threading.Tasks;
  5. using Newtonsoft.Json;
  6. using Newtonsoft.Json.Serialization;
  7. using FreeSql;
  8. using FreeSql.DataAnnotations;
  9. using Admin.Core.Common.Configs;
  10. using Admin.Core.Common.Helpers;
  11. using Admin.Core.Model.Admin;
  12. using FreeSql.Aop;
  13. namespace Admin.Core.Db
  14. {
  15. public class DbHelper
  16. {
  17. /// <summary>
  18. /// 创建数据库
  19. /// </summary>
  20. /// <param name="dbConfig"></param>
  21. /// <returns></returns>
  22. public async static Task CreateDatabase(DbConfig dbConfig)
  23. {
  24. if (!dbConfig.CreateDb || dbConfig.Type == DataType.Sqlite)
  25. {
  26. return;
  27. }
  28. var fsql = new FreeSqlBuilder()
  29. .UseConnectionString(dbConfig.Type, dbConfig.CreateDbConnectionString)
  30. .Build();
  31. try
  32. {
  33. Console.WriteLine("\r\ncreate database started");
  34. await fsql.Ado.ExecuteNonQueryAsync(dbConfig.CreateDbSql);
  35. Console.WriteLine("create database succeed\r\n");
  36. }
  37. catch (Exception e)
  38. {
  39. Console.WriteLine($"create database failed.\n{e.Message}\r\n");
  40. }
  41. }
  42. /// <summary>
  43. /// 同步结构
  44. /// </summary>
  45. public static void SyncStructure(IFreeSql db)
  46. {
  47. //打印结构比对脚本
  48. //var dDL = db.CodeFirst.GetComparisonDDLStatements<PermissionEntity>();
  49. //Console.WriteLine("\r\n" + dDL);
  50. //打印结构同步脚本
  51. //db.Aop.SyncStructureAfter += (s, e) =>
  52. //{
  53. // if (e.Sql.NotNull())
  54. // {
  55. // Console.WriteLine("sync structure sql:\n" + e.Sql);
  56. // }
  57. //};
  58. // 同步结构
  59. Console.WriteLine("\r\nsync structure started");
  60. db.CodeFirst.SyncStructure(
  61. typeof(DictionaryEntity),
  62. typeof(ApiEntity),
  63. typeof(ViewEntity),
  64. typeof(PermissionEntity),
  65. typeof(UserEntity),
  66. typeof(RoleEntity),
  67. typeof(UserRoleEntity),
  68. typeof(RolePermissionEntity)
  69. );
  70. Console.WriteLine("sync structure succeed\r\n");
  71. }
  72. /// <summary>
  73. /// 初始化数据
  74. /// </summary>
  75. /// <typeparam name="T"></typeparam>
  76. /// <param name="db"></param>
  77. /// <param name="data"></param>
  78. /// <returns></returns>
  79. private static async Task InitData<T>(IFreeSql db,T[] data) where T : class
  80. {
  81. var table = typeof(T).GetCustomAttributes(true)
  82. .FirstOrDefault(x => x.GetType() == typeof(TableAttribute)) as TableAttribute;
  83. var tableName = table.Name;
  84. try
  85. {
  86. if (!await db.Queryable<T>().AnyAsync())
  87. {
  88. if (data?.Length > 0)
  89. {
  90. await db.Insert<T>().AppendData(data).ExecuteAffrowsAsync();
  91. Console.WriteLine($"table:{tableName} sync data succeed");
  92. }
  93. else
  94. {
  95. Console.WriteLine($"table: {tableName} import data []");
  96. }
  97. }
  98. else
  99. {
  100. Console.WriteLine($"table: {tableName} record already exists");
  101. }
  102. }
  103. catch (Exception ex)
  104. {
  105. Console.WriteLine($"table: {tableName} sync data failed.\n{ex.Message}");
  106. }
  107. }
  108. /// <summary>
  109. /// 同步数据审计方法
  110. /// </summary>
  111. /// <param name="s"></param>
  112. /// <param name="e"></param>
  113. private static void SyncDataAuditValue(object s, AuditValueEventArgs e)
  114. {
  115. if (e.AuditValueType == AuditValueType.Insert)
  116. {
  117. switch (e.Property.Name)
  118. {
  119. case "CreatedUserId":
  120. e.Value = 1;
  121. break;
  122. case "CreatedUserName":
  123. e.Value = "小雪转中雪";
  124. break;
  125. }
  126. }
  127. else if (e.AuditValueType == AuditValueType.Update)
  128. {
  129. switch (e.Property.Name)
  130. {
  131. case "ModifiedUserId":
  132. e.Value = 1;
  133. break;
  134. case "ModifiedUserName":
  135. e.Value = "小雪转中雪";
  136. break;
  137. }
  138. }
  139. }
  140. /// <summary>
  141. /// 同步数据
  142. /// </summary>
  143. /// <returns></returns>
  144. public static async Task SyncData(IFreeSql db)
  145. {
  146. try
  147. {
  148. Console.WriteLine("\r\nsync data started");
  149. db.Aop.AuditValue += SyncDataAuditValue;
  150. var filePath = Path.Combine(Directory.GetCurrentDirectory(), @"Db\Data\data.json");
  151. var jsonData = FileHelper.ReadFile(filePath);
  152. var data = JsonConvert.DeserializeObject<Data>(jsonData);
  153. await InitData(db, data.Dictionaries);
  154. await InitData(db, data.Apis);
  155. await InitData(db, data.Views);
  156. await InitData(db, data.Permissions);
  157. await InitData(db, data.Users);
  158. await InitData(db, data.Roles);
  159. await InitData(db, data.UserRoles);
  160. await InitData(db, data.RolePermissions);
  161. db.Aop.AuditValue -= SyncDataAuditValue;
  162. Console.WriteLine("sync data succeed\r\n");
  163. }
  164. catch (Exception ex)
  165. {
  166. throw new Exception($"sync data failed.\n{ex.Message}\r\n");
  167. }
  168. }
  169. /// <summary>
  170. /// 生成极简数据
  171. /// </summary>
  172. /// <param name="db"></param>
  173. /// <returns></returns>
  174. public static async Task GenerateSimpleJsonData(IFreeSql db)
  175. {
  176. try
  177. {
  178. Console.WriteLine("\r\ngenerate data started");
  179. #region 数据表
  180. #region 数据字典
  181. var dictionaries = await db.Queryable<DictionaryEntity>().ToListAsync(a => new
  182. {
  183. a.Id,
  184. a.ParentId,
  185. a.Name,
  186. a.Code,
  187. a.Value,
  188. a.Description,
  189. a.Sort
  190. });
  191. #endregion
  192. #region 接口
  193. var apis = await db.Queryable<ApiEntity>().ToListAsync(a => new
  194. {
  195. a.Id,
  196. a.ParentId,
  197. a.Name,
  198. a.Label,
  199. a.Path,
  200. a.HttpMethods,
  201. a.Description,
  202. a.Sort
  203. });
  204. #endregion
  205. #region 视图
  206. var views = await db.Queryable<ViewEntity>().ToListAsync(a => new
  207. {
  208. a.Id,
  209. a.ParentId,
  210. a.Name,
  211. a.Label,
  212. a.Path,
  213. a.Description,
  214. a.Sort
  215. });
  216. #endregion
  217. #region 权限
  218. var permissions = await db.Queryable<PermissionEntity>().ToListAsync(a => new
  219. {
  220. a.Id,
  221. a.ParentId,
  222. a.Label,
  223. a.Type,
  224. a.ViewId,
  225. a.ApiId,
  226. a.Path,
  227. a.Icon,
  228. a.Closable,
  229. a.Opened,
  230. a.NewWindow,
  231. a.External,
  232. a.Sort,
  233. a.Description
  234. });
  235. #endregion
  236. #region 用户
  237. var users = await db.Queryable<UserEntity>().ToListAsync(a => new
  238. {
  239. a.Id,
  240. a.UserName,
  241. a.Password,
  242. a.Name,
  243. a.NickName,
  244. a.Avatar,
  245. a.Status,
  246. a.Remark
  247. });
  248. #endregion
  249. #region 角色
  250. var roles = await db.Queryable<RoleEntity>().ToListAsync(a => new
  251. {
  252. a.Id,
  253. a.Name,
  254. a.Sort,
  255. a.Description
  256. });
  257. #endregion
  258. #region 用户角色
  259. var userRoles = await db.Queryable<UserRoleEntity>().ToListAsync(a => new
  260. {
  261. a.Id,
  262. a.UserId,
  263. a.RoleId
  264. });
  265. #endregion
  266. #region 角色权限
  267. var rolePermissions = await db.Queryable<RolePermissionEntity>().ToListAsync(a => new
  268. {
  269. a.Id,
  270. a.RoleId,
  271. a.PermissionId
  272. });
  273. #endregion
  274. #endregion
  275. if(!(users?.Count > 0))
  276. {
  277. return;
  278. }
  279. #region 生成数据
  280. var settings = new JsonSerializerSettings();
  281. settings.ContractResolver = new CamelCasePropertyNamesContractResolver();
  282. settings.NullValueHandling = NullValueHandling.Ignore;
  283. settings.DefaultValueHandling = DefaultValueHandling.Ignore;
  284. var jsonData = JsonConvert.SerializeObject(new
  285. {
  286. dictionaries,
  287. apis,
  288. views,
  289. permissions,
  290. users,
  291. roles,
  292. userRoles,
  293. rolePermissions
  294. },
  295. //Formatting.Indented,
  296. settings
  297. );
  298. var filePath = Path.Combine(Directory.GetCurrentDirectory(), @"Db\Data\data.json");
  299. FileHelper.WriteFile(filePath, jsonData);
  300. #endregion
  301. Console.WriteLine("generate data succeed\r\n");
  302. }
  303. catch (Exception ex)
  304. {
  305. throw new Exception($"generate data failed。\n{ex.Message}\r\n");
  306. }
  307. }
  308. }
  309. }