BookDB.txt

Scaffold-DbContext "Your connection string" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Categories, UserType, UserMaster, Book, Cart, CartItems, CustomerOrders, CustomerOrderDetails, Wishlist, WishlistItems -force

---------------------------------------------------------------------------------------
CREATE TABLE Categories (  
CategoryID int IDENTITY(1,1) PRIMARY KEY,  
CategoryName varchar(20) NOT NULL   
)  
GO

INSERT INTO Categories VALUES('Biography');  
INSERT INTO Categories VALUES('Fiction');  
INSERT INTO Categories VALUES('Mystery');  
INSERT INTO Categories VALUES('Fantasy');  
INSERT INTO Categories VALUES('Romance'); 
GO
--------------------------------------------------------------------------
CREATE TABLE UserType (  
UserTypeID int IDENTITY(1,1) PRIMARY KEY,  
UserTypeName varchar(20) NOT NULL   
)  
GO

INSERT INTO UserType VALUES('Admin');  
INSERT INTO UserType VALUES('User'); 
GO
--------------------------------------------------------------------------
CREATE TABLE UserMaster(  
UserID int IDENTITY(1,1) PRIMARY KEY,  
FirstName varchar(20) NOT NULL ,
LastName varchar(20) NOT NULL,
Username varchar(20) NOT NULL,
Password varchar(40) NOT NULL,
Gender varchar(6) NOT NULL,
UserTypeID int NOT NULL,   
)  
GO

INSERT INTO UserMaster VALUES('Ankit','Sharma','adminuser','qwerty','Male',1); -- Add the record for Admin user.
---------------------------------------------------------------------------
CREATE TABLE Book(
BookID int IDENTITY(1,1) PRIMARY KEY,  
Title varchar(100) NOT NULL ,  
Author varchar(100) NOT NULL ,  
Category varchar(20) NOT NULL ,  
Price decimal(10, 2) NOT NULL ,
CoverFileName varchar(100) NULL
)
GO
---------------------------------------------------------------------------
CREATE TABLE Cart(
CartId varchar(36) PRIMARY KEY ,
UserID int NOT NULL,
DateCreated datetime NOT NULL
)
GO
---------------------------------------------------------------------------
CREATE TABLE CartItems(
CartItemId int IDENTITY(1,1) Primary Key,
CartId varchar(36) NOT NULL,
ProductId int NOT NULL,
Quantity int NOT NULL,
)
GO
---------------------------------------------------------------------------
CREATE TABLE CustomerOrders(
OrderId varchar(20) PRIMARY KEY ,
UserID int NOT NULL,
DateCreated datetime NOT NULL,
CartTotal Decimal(10,2) NOT NULL
)
GO
---------------------------------------------------------------------------
CREATE TABLE CustomerOrderDetails(
OrderDetailsId int IDENTITY(1,1) Primary Key,
OrderId varchar(20) NOT NULL,
ProductId int NOT NULL,
Quantity int NOT NULL,
Price decimal(10,2) NOT NULL
)
GO
---------------------------------------------------------------------------
CREATE TABLE Wishlist(
WishlistId varchar(36) PRIMARY KEY,
UserID int NOT NULL,
DateCreated datetime NOT NULL
)
GO
---------------------------------------------------------------------------
CREATE TABLE WishlistItems(
WishlistItemId int IDENTITY(1,1) Primary Key,
WishlistId varchar(36) NOT NULL,
ProductId int NOT NULL
)
GO