前面的书房初始化的前端信息已经完善，所以现在开始实现DB的Script部分。
昨晚完成了Web端新增图书信息的功能，现在就差DB的具体实现了。
新增Action:Shelf_Init.sql
因为我把Book相关的信息拆分的比较多，所以更新有点小麻烦。
首先，我需要创建一个Book Type的Matter；
svc.sql
然后，将图片路径保存到FileBank中，并返回FileBankID；
1 CREATE SCHEMA [svc]
2 AUTHORIZATION [dbo];
继续，插入Publisher信息（需要判断name不存在才会insert），然后返回PublisherID；
Shelf_Init.sql
1 CREATE PROCEDURE [base].[Publisher#Insert](@json nvarchar(max), @id int out)
2 WITH ENCRYPTION
3 AS
4 BEGIN
5 ...
6
7 declare @name nvarchar(100);
8 select @name=Publisher from openjson(@json, '$') with (Publisher nvarchar(100))
9
10 -- insert Publisher
11 insert base._Publisher(Name)select @name
12 where not exists(select 1 from base._Publisher p where p.Name=@name);
13
14 select @id=ID from base.Publisher#Raw() where Name=@name;
15 ...
16 END
1 CREATE PROCEDURE [svc].[Shelf$Init](@json nvarchar(max))
2 WITH ENCRYPTION
3 AS
4 BEGIN
5 SET NOCOUNT ON;
6 SET XACT_ABORT ON;
7 BEGIN TRY
8 BEGIN TRAN;
9
10 declare @nickName nvarchar(20), @shelfName nvarchar(20);
11 select @nickName=NickName, @shelfName=ShelfName
12 from openjson (@json, '$')
13 with (
14 NickName nvarchar(20),
15 ShelfName nvarchar(20)
16 );
17
18 insert core._Party(Type, Alias) select k._User, @nickName
19 from core.Party#Type() k;
20 declare @userID int=@@identity;
21
22
23 insert core._Party(PID, Type, Alias) select @userID, k._Shelf, @shelfName
24 from core.Party#Type() k;
25
26 COMMIT TRAN;
27 END TRY
28 BEGIN CATCH
29 if (xact_state() = -1) ROLLBACK TRAN; throw;
30 END CATCH
31 END
好了，我去试试前端能不能初始化信息进DB
继续，插入Binding信息（也需要判断name不存在才insert），返回BindingID；
....
1 CREATE PROCEDURE [base].[Binding#Insert](@json nvarchar(max), @id int out)
2 WITH ENCRYPTION
3 AS
4 BEGIN
5 ...
6
7 declare @name nvarchar(100);
8 select @name=Binding from openjson(@json, '$') with (Binding nvarchar(100))
9
10 -- insert Binding
11 insert base._Binding(Name)select @name
12 where not exists(select 1 from base._Binding p where p.Name=@name);
13
14 select @id=ID from base.Binding#Raw() where Name=@name;
15
16 ...
17 END
美高梅官方网站，在测试之前，我们需要实现一下Init Razor Pages代码：
Init.cshtml.cs
继续，插入Book信息；
1 using M = Shelf;
2 public class InitModel : PageModel
3 {
4 private readonly IShelfRepo _shelfRepo;
5 public InitModel(IShelfRepo shelfRepo)
6 {
7 _shelfRepo = shelfRepo;
8 }
9 [BindProperty]
10 public InitInputModel Input { get; set; }
11
12 public void OnGet()
13 {
14
15 }
16
17 public async Task<IActionResult> OnPostAsync()
18 {
19 if (ModelState.IsValid)
20 {
21 await _shelfRepo.InitAsync(new M.InitSpec
22 {
23 NickName = Input.NickName.Trim(),
24 ShelfName = Input.ShelfName.Trim()
25 });
26 return RedirectToPage("New");
27 }
28 return Page();
29 }
30 }
继续，插入BookInfo的信息；
页面内容也需要修改一下form部分
继续，插入BookNbr信息；
Init.cshtml
继续，插入BookSupplement信息；
1 <form method="post">
2 <div class="form-group form-group-lg">
3 <label asp-for="Input.NickName"></label>
4 <input class="form-control form-control-lg" asp-for="Input.NickName" autocomplete="off">
5
6 </div>
7 <div class="form-group form-group-lg">
8 <label asp-for="Input.ShelfName"></label>
9 <input class="form-control form-control-lg" asp-for="Input.ShelfName" autocomplete="off">
10
11 </div>
12 <div class="form-group text-right">
13 <button class="btn btn-warning btn-lg" type="submit">Save</button>
14 </div>
15 </form>
继续，插入BookTag信息；
填写不动书房的信息：
1 CREATE PROCEDURE [base].[BookTag#Insert](@json nvarchar(max), @bookID bigint)
2 WITH ENCRYPTION
3 AS
4 BEGIN
5 ...
6
7 -- insert Tag
8 insert base._Tag(Name)select value
9 from openjson(@json, '$.Tags') x
10 where not exists(select 1 from base._Tag p where p.Name=x.value);
11
12 insert base._BookTag(BookID, TagID) select @bookID, x.ID
13 from openjson(@json, '$.Tags') j join base.Tag#Raw() x on x.Name=j.value
14
15 ...
16 END
继续，插入BookAuthor信息；
点击Save按钮提交，OK，正常提交了并跳转了。
1 CREATE PROCEDURE [base].[BookAuthor#Insert](@json nvarchar(max), @bookID bigint)
2 WITH ENCRYPTION
3 AS
4 BEGIN
5 ...
6
7 -- insert Author
8 insert base._Author(Name)select value
9 from openjson(@json, '$.Authors') x
10 where not exists(select 1 from base._Author p where p.Name=x.value);
11
12 insert base._BookAuthor(BookID, AuthorID) select @bookID, x.ID
13 from openjson(@json, '$.Authors') j join base.Author#Raw() x on x.Name=j.value
14
15 ...
16 END
本文由美高梅官方网站发布于数据统计,转载请注明出处：记开发个人图书收藏清单小程序开发（七）DB设计
