您的位置:澳门新葡8455最新网站 > 数据库管理 > 记开发个人图书收藏清单小程序开发,新增图书

记开发个人图书收藏清单小程序开发,新增图书

发布时间:2019-11-04 10:57编辑:数据库管理浏览(158)

    昨晚完成了Web端新增图书信息的功能,现在就差DB的具体实现了。

    澳门新葡萄京娱乐场,前面的书房初始化的前端信息已经完善,所以现在开始实现DB的Script部分。

    因为我把Book相关的信息拆分的比较多,所以更新有点小麻烦。

    新增Action:Shelf_Init.sql

    首先,我需要创建一个Book Type的Matter;

    澳门新葡萄京娱乐场 1

    然后,将图片路径保存到FileBank中,并返回FileBankID;

    svc.sql

    继续,插入Publisher信息(需要判断name不存在才会insert),然后返回PublisherID;

    1 CREATE SCHEMA [svc]
    2     AUTHORIZATION [dbo];
    
     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
    

    Shelf_Init.sql

     

     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
    

    继续,插入Binding信息(也需要判断name不存在才insert),返回BindingID;

    好了,我去试试前端能不能初始化信息进DB

     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代码:

    继续,插入Book信息;

    Init.cshtml.cs

    继续,插入BookInfo的信息;

     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     }
    

    继续,插入BookNbr信息;

    页面内容也需要修改一下form部分

    继续,插入BookSupplement信息;

    Init.cshtml

    继续,插入BookTag信息;

     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>
    
     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
    

    填写不动书房的信息:

     

    澳门新葡萄京娱乐场 2

    继续,插入BookAuthor信息;

     

     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
    

     点击Save按钮提交,OK,正常提交了并跳转了。

     

    查看下DB有没有数据:

    继续,插入BookTranslator信息;

    澳门新葡萄京娱乐场 3

     1 CREATE PROCEDURE [base].[BookTranslator#Insert](@json nvarchar(max), @bookID bigint)
     2 WITH ENCRYPTION
     3 AS
     4 BEGIN
     5 ...
     6 
     7         -- insert Translator
     8         insert    base._Author(Name)select value
     9         from    openjson(@json, '$.Translators') x
    10         where    not exists(select 1 from base._Author p where p.Name=x.value);
    11 
    12         insert    base._BookTranslator(BookID, TranslatorID) select @bookID, x.ID
    13         from    openjson(@json, '$.Translators') j join base.Author#Raw() x on x.Name=j.value
    14 
    15 ...
    16 END
    

    哈哈,看来一切正常。

     

     

    最后,关联新增的Book信息和Shelf,插入ShelfBook信息。

     

    现在放出Init Script

    Book_Init.sql

     1 CREATE PROCEDURE [svc].[Book$Init](@json nvarchar(max))
     2 WITH ENCRYPTION
     3 AS
     4 BEGIN
     5 ...
     6 
     7         declare    @stringID varchar(36), @userID int,  @shelfID int;
     8         select    @stringID=u.StringID,  @userID=u.ID, @shelfID=s.ID
     9         from    openjson (@json, '$') with (StringID varchar(36))
    10         cross    apply core.User#For(StringID) u
    11         join    core.Party#Raw() s on s.PID=u.ID;
    12 
    13         declare    @stateID int=(select BookCreated from core.Status#ID());
    14 
    15         -- init Matter
    16         insert    core._Matter(Type, UserID, StateID)
    17         select    k._Book, @userID, @stateID from core.Matter#Type() k;
    18         declare    @matterID int=@@identity;
    19 
    20         -- init FileBank
    21         insert    base._FileBank(Type, Url)
    22         select    k._BookImage, ImageUrl
    23         from    openjson(@json, '$') with (ImageUrl varchar(200))
    24         cross    apply base.FileBank#Type() k;
    25         declare    @imageID int=@@identity;
    26         
    27         -- insert Publisher
    28         declare    @publisherID int;
    29         exec    base.Publisher#Insert @json=@json, @id=@publisherID out;
    30         
    31         -- insert Binding
    32         declare    @bindingID int;
    33         exec    base.Binding#Insert @json=@json, @id=@bindingID out;
    34 
    35         -- insert Book
    36         insert    base._Book(ID, Title, PublisherID, BindingID, ImageID)
    37         select    @matterID, Title, @publisherID, @bindingID, @imageID
    38         from    openjson(@json, '$') with (Title nvarchar(100));
    39         
    40         -- insert BookInfo
    41         insert    base._BookInfo(ID, OriginTitle, PageCnt, Pubdate, SubTitle)
    42         select    @matterID, OriginTitle, Pages, Pubdate, SubTitle
    43         from    openjson(@json, '$')
    44         with (
    45             Pages       int, 
    46             Pubdate     char(10), 
    47             SubTitle    nvarchar(150), 
    48             OriginTitle nvarchar(150)
    49         );
    50         
    51         -- insert BookNbr
    52         insert    base._BookNbr(ID, Type, Number)
    53         select    @matterID, k._ISBN13, Isbn13
    54         from    base.BookNbr#Type() k, openjson(@json, '$') with (Isbn13 char(13));
    55         
    56         insert    base._BookNbr(ID, Type, Number)
    57         select    @matterID, k._ISBN10, Isbn10
    58         from    base.BookNbr#Type() k, openjson(@json, '$') with (Isbn10 char(10));
    59         
    60         -- insert BookSupplement
    61         insert    base._BookSupplement(ID, Type, Supplement)
    62         select    @matterID, k._AuthorIntro, AuthorIntro
    63         from    base.BookSupplement#Type() k, openjson(@json, '$') with (AuthorIntro nvarchar(max));
    64         
    65         insert    base._BookSupplement(ID, Type, Supplement)
    66         select    @matterID, k._Summary, Summary
    67         from    base.BookSupplement#Type() k, openjson(@json, '$') with (Summary nvarchar(max));
    68         
    69         insert    base._BookSupplement(ID, Type, Supplement)
    70         select    @matterID, k._Catalog, Catalog
    71         from    base.BookSupplement#Type() k, openjson(@json, '$') with (Catalog nvarchar(max));
    72 
    73         -- insert BookTag
    74         exec    base.BookTag#Insert @json=@json, @bookID=@matterID;
    75         
    76         -- insert BookAuthor
    77         exec    base.BookAuthor#Insert @json=@json, @bookID=@matterID;
    78 
    79         -- insert BookTranslator
    80         exec    base.BookTranslator#Insert @json=@json, @bookID=@matterID;
    81         
    82         -- insert ShelfBook
    83         insert    base._ShelfBook(BookID, ShelfID) values(@matterID, @shelfID);
    84 
    85 ...
    86 END
    

     

    好了,开始测试。

    ...

    查询DB,看看有没有数据进DB:

    澳门新葡萄京娱乐场 4

    澳门新葡萄京娱乐场 5

     

     截图中展示了部分查询结果,基本没什么问题了。

    下面要做的是展示Shelf中的Book信息,要等今天活干完才能继续写了。

     

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:记开发个人图书收藏清单小程序开发,新增图书

    关键词: