Skip to content

03-database.md

Latest commit

 

History

History
74 lines (58 loc) · 3.26 KB

03-database.md

File metadata and controls

74 lines (58 loc) · 3.26 KB
Raw

03 — Database

Server & database

  • Server: NB0316\SQLEXPRESS (default from connection string)
  • Database: booksDb
  • Local connection string uses Windows auth (Trusted_Connection=True); production/IIS uses SQL login mvc_web (see deployment docs).
  • The SQL Server is SQL Server 2008 R2 compatibility level 100 — this drove two decisions:
    • AuditLogController pages in memory (no OFFSET/FETCH).
    • Stored procedures use classic CREATE PROCEDURE / ALTER PROCEDURE, never CREATE OR ALTER.

Tables

Table Purpose Managed by
books Book catalog (Id, Title, Author, Price) stored procedures (no EF writes)
AspNetUsers, AspNetRoles, AspNetUserRoles, AspNetUserClaims, AspNetRoleClaims, AspNetUserLogins, AspNetUserTokens ASP.NET Identity tables EF Core migrations
AuditLogs Audit trail rows (see Models/AuditLog) AuditActionFilter + AuditRetentionService
PageViews Page view hits (see Models/PageView) PageViewFilter

Migrations (EF Core, code-first)

Migration Created Adds
20260706023505_AddIdentityTables 2026-07-06 Identity tables + __EFMigrationsHistory
20260808012119_AddAuditLogs 2026-08-08 AuditLogs table
20260808024835_AddPageViews 2026-08-08 PageViews table

Current model is captured in mvcContextModelSnapshot.cs.

Stored procedures (in mvc/SQL/)

All operate on books. Expected column shape returned by read procs: (Id, Title, Author, Price).

Proc File Purpose
BookViewAll SQL/BookViewAll.sql SELECT * FROM books
BookViewById @Id SQL/BookViewById.sql One row by id
BookAdd @Id,@Title,@Author,@Price SQL/BookAdd.sql INSERT
BookEdit @Id,@Title,@Author,@Price SQL/BookEdit.sql UPDATE
BookAddOrEdit @Id,… SQL/BookAddOrEdit.sql INSERT if @Id=0 else UPDATE
BookDeleteById @Id SQL/BookDeleteById.sql DELETE
BookSearchByTitle @SearchString Scripts/BookSearchByTitle.sql LIKE search on title/author

Notes:

  • BookSearchByTitle exists in the Scripts/ folder (created during this session) and must reproduce the same (Id, Title, Author, Price) column order because BookRepository.Map reads the Id column by position/name.
  • Parameter style: commandType: CommandType.StoredProcedure; the exact binding lives in BookRepository.

Additional files

  • SQL/books.sql — schema + seed (demo books) used when creating booksDb.
  • SQL/booksDb.bak — SQL Server backup of the current booksDb.
  • SQL/GRANT.sql — grants EXECUTE on the book procs (adds dc_book_proc_user / web / mvc_web etc. users as appropriate).

Seeding at startup (Data/SeedData.cs)

Runs every startup inside Program.cs:

  • Creates roles if missing: Admin, Member (SeedData.AdminRole, SeedData.MemberRole).
  • If Admin:Email + Admin:Password are configured (see appsettings.json / IIS env vars), creates that user, sets EmailConfirmed = true, assigns Admin.
  • Failure paths are logged and non-fatal: if the DB is down or credentials missing the site still starts.
  • Existing users are never re-created; upgrades to role assignment are skipped if the user already exists.