Here, I’ve merely thought to write my thoughts about hands-on Angular. This is the third article, which states that you can manage the database and stored procedures.
This is the technology stack for BookMySeat library Application, as shown below.
In the first article, I have shared the technology and other brief information about the components used.
This article elaborates about the database structure, which will be used within this Application, as given below.
If you look at the image shown above, all the columns names are self-explanatory.
Kindly find the complete script for the database, as given below.
- USE [master]
- GO
- /****** Object: Database [BookMySeat] Script Date: 10/13/2016 15:49:26 ******/
- CREATE DATABASE [BookMySeat] ON PRIMARY
- ( NAME = N'BookMySeat', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BookMySeat.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
- LOG ON
- ( NAME = N'BookMySeat_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\BookMySeat_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
- GO
- ALTER DATABASE [BookMySeat] SET COMPATIBILITY_LEVEL = 100
- GO
- IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
- begin
- EXEC [BookMySeat].[dbo].[sp_fulltext_database] @action = 'enable'
- end
- GO
- ALTER DATABASE [BookMySeat] SET ANSI_NULL_DEFAULT OFF
- GO
- ALTER DATABASE [BookMySeat] SET ANSI_NULLS OFF
- GO
- ALTER DATABASE [BookMySeat] SET ANSI_PADDING OFF
- GO
- ALTER DATABASE [BookMySeat] SET ANSI_WARNINGS OFF
- GO
- ALTER DATABASE [BookMySeat] SET ARITHABORT OFF
- GO
- ALTER DATABASE [BookMySeat] SET AUTO_CLOSE OFF
- GO
- ALTER DATABASE [BookMySeat] SET AUTO_CREATE_STATISTICS ON
- GO
- ALTER DATABASE [BookMySeat] SET AUTO_SHRINK OFF
- GO
- ALTER DATABASE [BookMySeat] SET AUTO_UPDATE_STATISTICS ON
- GO
- ALTER DATABASE [BookMySeat] SET CURSOR_CLOSE_ON_COMMIT OFF
- GO
- ALTER DATABASE [BookMySeat] SET CURSOR_DEFAULT GLOBAL
- GO
- ALTER DATABASE [BookMySeat] SET CONCAT_NULL_YIELDS_NULL OFF
- GO
- ALTER DATABASE [BookMySeat] SET NUMERIC_ROUNDABORT OFF
- GO
- ALTER DATABASE [BookMySeat] SET QUOTED_IDENTIFIER OFF
- GO
- ALTER DATABASE [BookMySeat] SET RECURSIVE_TRIGGERS OFF
- GO
- ALTER DATABASE [BookMySeat] SET DISABLE_BROKER
- GO
- ALTER DATABASE [BookMySeat] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
- GO
- ALTER DATABASE [BookMySeat] SET DATE_CORRELATION_OPTIMIZATION OFF
- GO
- ALTER DATABASE [BookMySeat] SET TRUSTWORTHY OFF
- GO
- ALTER DATABASE [BookMySeat] SET ALLOW_SNAPSHOT_ISOLATION OFF
- GO
- ALTER DATABASE [BookMySeat] SET PARAMETERIZATION SIMPLE
- GO
- ALTER DATABASE [BookMySeat] SET READ_COMMITTED_SNAPSHOT OFF
- GO
- ALTER DATABASE [BookMySeat] SET HONOR_BROKER_PRIORITY OFF
- GO
- ALTER DATABASE [BookMySeat] SET READ_WRITE
- GO
- ALTER DATABASE [BookMySeat] SET RECOVERY FULL
- GO
- ALTER DATABASE [BookMySeat] SET MULTI_USER
- GO
- ALTER DATABASE [BookMySeat] SET PAGE_VERIFY CHECKSUM
- GO
- ALTER DATABASE [BookMySeat] SET DB_CHAINING OFF
- GO
- EXEC sys.sp_db_vardecimal_storage_format N'BookMySeat', N'ON'
- GO
- USE [BookMySeat]
- GO
- /****** Object: Table [dbo].[UserDetails] Script Date: 10/13/2016 15:49:27 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[UserDetails](
- [id] [int] IDENTITY(1,1) NOT NULL,
- [username] [nvarchar](20) NOT NULL,
- [userid] [nchar](10) NOT NULL,
- [currentday] [date] NULL,
- [timeslot] [int] NULL,
- [seatno] [int] NULL,
- CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED
- (
- [id] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
- SET IDENTITY_INSERT [dbo].[UserDetails] ON
- INSERT [dbo].[UserDetails] ([id], [username], [userid], [currentday], [timeslot], [seatno]) VALUES (28, N'ABhibav', N'2 ', CAST(0xDA3B0B00 AS Date), 100, 13)
- INSERT [dbo].[UserDetails] ([id], [username], [userid], [currentday], [timeslot], [seatno]) VALUES (29, N'abhi', N'2 ', CAST(0xDA3B0B00 AS Date), 100, 26)
- INSERT [dbo].[UserDetails] ([id], [username], [userid], [currentday], [timeslot], [seatno]) VALUES (30, N'abhinavtesting', N'2 ', CAST(0xDA3B0B00 AS Date), 100, 3)
- INSERT [dbo].[UserDetails] ([id], [username], [userid], [currentday], [timeslot], [seatno]) VALUES (31, N'sachi', N'2 ', CAST(0xDA3B0B00 AS Date), 100, 9)
- INSERT [dbo].[UserDetails] ([id], [username], [userid], [currentday], [timeslot], [seatno]) VALUES (32, N'nn', N'2 ', CAST(0xDA3B0B00 AS Date), 100, 8)
- INSERT [dbo].[UserDetails] ([id], [username], [userid], [currentday], [timeslot], [seatno]) VALUES (33, N'new', N'2 ', CAST(0xDA3B0B00 AS Date), 100, 14)
- SET IDENTITY_INSERT [dbo].[UserDetails] OFF
- /****** Object: Table [dbo].[TimeDetails] Script Date: 10/13/2016 15:49:27 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE TABLE [dbo].[TimeDetails](
- [timeslot] [nvarchar](500) NOT NULL
- ) ON [PRIMARY]
- GO
- INSERT [dbo].[TimeDetails] ([timeslot]) VALUES (N'100')
- INSERT [dbo].[TimeDetails] ([timeslot]) VALUES (N'120')
- INSERT [dbo].[TimeDetails] ([timeslot]) VALUES (N'140')
- INSERT [dbo].[TimeDetails] ([timeslot]) VALUES (N'200')
- INSERT [dbo].[TimeDetails] ([timeslot]) VALUES (N'220')
- INSERT [dbo].[TimeDetails] ([timeslot]) VALUES (N'240')
- INSERT [dbo].[TimeDetails] ([timeslot]) VALUES (N'300')
- /****** Object: StoredProcedure [dbo].[ValidateUser] Script Date: 10/13/2016 15:49:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE Procedure [dbo].[ValidateUser]
- (
- @UserName nvarchar(500)
- )
-
- AS
- BEGIN
-
- DECLARE @SlotNum int
-
-
- SELECT @SlotNum=timeslot from UserDetails where username= @UserName
-
-
- select isnull(@SlotNum,-1)
-
-
-
- END
- GO
- /****** Object: StoredProcedure [dbo].[sp_ShowBookDetail] Script Date: 10/13/2016 15:49:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE PROCEDURE [dbo].[sp_ShowBookDetail]
-
- AS
- BEGIN
-
- SELECT username,currentday,timeslot,seatno from UserDetails
- END
- GO
- /****** Object: StoredProcedure [dbo].[sp_GetSeatDetail] Script Date: 10/13/2016 15:49:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
-
-
-
-
-
- CREATE PROCEDURE [dbo].[sp_GetSeatDetail]
- @TimeSlot int,
- @SeatNo int
- AS
- BEGIN
- select username from userdetails where timeslot=@TimeSlot and seatno=@SeatNo and datediff(D,currentday,getdate())=0
- END
- GO
- /****** Object: StoredProcedure [dbo].[sp_GetMySeat] Script Date: 10/13/2016 15:49:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE PROCEDURE [dbo].[sp_GetMySeat]
- @timeslotid int
- AS
- BEGIN
- select Seatno from UserDetails where datediff(D,currentday,getdate())=0 and timeslot=@timeslotid
-
-
- END
- GO
- /****** Object: StoredProcedure [dbo].[sp_DeleteSeat] Script Date: 10/13/2016 15:49:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE PROCEDURE [dbo].[sp_DeleteSeat]
- @SeatNo int,
- @SlotNo int
- AS
- BEGIN
-
-
- SET NOCOUNT ON;
-
-
- delete from userdetails where seatno=@seatNo and timeslot=@SlotNo
- select 1
-
- END
- GO
- /****** Object: StoredProcedure [dbo].[sp_BookMySeat] Script Date: 10/13/2016 15:49:28 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE PROCEDURE [dbo].[sp_BookMySeat]
- @UserName nvarchar(500),
- @TimeSlot int,
- @SeatNo int
- AS
- BEGIN
- insert into UserDetails values(@username,2,getdate(),@TimeSlot,@SeatNo)
- select 1
-
-
-
- END
- GO
- /****** Object: UserDefinedFunction [dbo].[GetBookedSlot] Script Date: 10/13/2016 15:49:29 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[GetBookedSlot]
- (
- @UserEmailID nvarchar(50)
- )
- RETURNS int
- AS
- BEGIN
-
- DECLARE @SlotNum int
-
-
- SELECT @SlotNum= count(timeslot) from UserDetails where userid= @UserEmailID
-
-
- RETURN @SlotNum
- END
- GO
I have also attached SQL script as an attachment also.
After installing the database at your local machine, you can verify an application API integration with the database, using fiddler.
As soon as you execute it jumps into API controller and executes the written code.
This is how you can verify all the defined WebAPI code, using specific URLs.
Hope it’ll help you some day. Enjoy coding.