Project Setup & Version Details:
Framework & Version: .Net Core 3.1 MVC
- Clone the Repository
- In SQL Server, Create a new Database
- In appsettings.json file, change ConnectionStrings & Open Package Manager Console and run the command update-database
- Execute the stored procedures once the above setup is done.
Home Page:
Q1. Create a REST API using .Net Core MVC and write a method to return a sorted list of
these by Publisher, Author (last, first), then title.
Result View:
BookController - GetSortedBooks()
Q2. Write another API method to return a sorted list by Author (last, first) then title.
Result View:
Book Controller - GetSecondSortedBooks()
Q3. If you had to create one or more tables to store the Book data in a SQL Server/Sql Lite
database, outline the table design along with fields and their datatypes.
Solution:
CREATE TABLE Book(
Id UNIQUEIDENTIFIER PRIMARY KEY,
AuthorLastName NVARCHAR(100) NOT NULL,
AuthorFirstName NVARCHAR(100) NOT NULL,
Title NVARCHAR(MAX) NOT NULL,
Publisher NVARCHAR(MAX) NOT NULL,
Price DECIMAL(18,2) NOT NULL,
MlaCitation NVARCHAR(MAX) NOT NULL,
ChicagoCitation NVARCHAR(MAX) NOT NULL,
JournalTitle NVARCHAR(MAX) NOT NULL,
PublicationDate DATETIME2(7) NOT NULL,
PageRange NVARCHAR(100) NOT NULL,
VolumeNumber INT,
Url NVARCHAR(MAX) NOT NULL
);
Q4. Write stored procedures for steps 1 and 2, and use them in separate API methods to return the same results.
- BookController - GetSortedBooksUsingSproc()
Execute the below Stored Procedure in SQL Server
CREATE or ALTER PROCEDURE GetBooksByFirstSortOrder
AS
BEGIN
SELECT Id, Publisher, CONCAT(AuthorLastName,', ', AuthorFirstName) as AuthorName, Title FROM Books(NOLOCK) ORDER BY Publisher, AuthorName, Title ASC
END;
CREATE or ALTER PROCEDURE GetBooksBySecondSortOrder
AS
BEGIN
SELECT Id, CONCAT(AuthorLastName,', ', AuthorFirstName) as AuthorName, Title FROM Books(NOLOCK) ORDER BY AuthorName, Title ASC
END;
2. BookController - GetSecondSortedBooksUsingSproc()
Q5. Write an API method to return the total price of all books in the database.
Q6. If you have a large list of these in memory and want to save the entire list to the database,
with only one call to the DB server.
Solution:
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'XYZ','The Mystery of Lost Keys','Johnson','Emma',28.00,'','','Detective Chronicles',SYSDATETIME(),'85-92',2,'https://doi.org/10.xxxx/0001')
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'History Books Co.','Historical Insights','Martin','William',5000.00,'','','History Today',SYSDATETIME(),'10-18',5,'https://doi.org/10.xxxx/0002')
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'Wonderful Science Press','The Science of Nature','Williams','Olivia',42.00,'','','Scientific Wonders',SYSDATETIME(),'40-52',4,'https://doi.org/10.xxxx/0003')
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'Starry Skies Publications','Exploring the Universe','Miller','James',49.00,'','','Astronomy Journal',SYSDATETIME(),'60-70',1,'https://doi.org/10.xxxx/0004')
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'XYZ','The Mystery of Lost Keys','Ahahnson','Kils',55.00,'','','Detective Chronicles',SYSDATETIME(),'85-92',2,'https://doi.org/10.xxxx/0005')
Q7. Add a property to the Book class that outputs the MLA (Modern Language Association)
style citation as a string ( https://images.app.goo.gl/YkFgbSGiPmie9GgWA ). Please add
whatever additional properties the Book class needs to generate the citation.
BookController - GetMlaCitation()
Q8. Add another property to generate a Chicago style citation (Chicago Manual of Style)
( https://images.app.goo.gl/w3SRpg2ZFsXewdAj7 ).
BookController - GetChicagoCitation()
Test Data:
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'XYZ','The Mystery of Lost Keys','Johnson','Emma',28.00,'','','Detective Chronicles',SYSDATETIME(),'85-92',2,'https://doi.org/10.xxxx/0001')
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'History Books Co.','Historical Insights','Martin','William',5000.00,'','','History Today',SYSDATETIME(),'10-18',5,'https://doi.org/10.xxxx/0002')
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'Wonderful Science Press','The Science of Nature','Williams','Olivia',42.00,'','','Scientific Wonders',SYSDATETIME(),'40-52',4,'https://doi.org/10.xxxx/0003')
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'Starry Skies Publications','Exploring the Universe','Miller','James',49.00,'','','Astronomy Journal',SYSDATETIME(),'60-70',1,'https://doi.org/10.xxxx/0004')
INSERT INTO [dbo].[Books]([Id],[Publisher],[Title],[AuthorLastName],[AuthorFirstName],[Price],[MlaCitation],[ChicagoCitation] ,[JournalTitle],[PublicationDate],[PageRange],[VolumeNumber],[Url]) VALUES(NEWID(),'XYZ','The Mystery of Lost Keys','Ahahnson','Kils',55.00,'','','Detective Chronicles',SYSDATETIME(),'85-92',2,'https://doi.org/10.xxxx/0005')