I have tried my best to include the all logic. User Rights based on their Role and Role based Menu Link to access application.
Summary:-
1. When user first time login in application, it is compulsory to Change the password.
2. After changing the password, user can access the application.
3. User can see menu link based on their Role.
4. You can perform add, edit and delete operation all Master.
Important Note:-
1. Run all the script one by one.
2. Change the web.config connection setting.
3. Application created using Microsoft Visual Studio Professional 2013 (.Net Framework 4.6). You need to same or higher version to test it.
4. Right click on Login.aspx page and Click on Set As Start Page
Table
1. Role Master
2. Gender Master
3. Menu Master
4. User Master
5. User Details Master
6. Role Menu Rights Master
Store Procedure
1. getLogin
2. sp_ChangePassword
3. getMenu
4. sp_GetGender
5. sp_SaveEditGender
6. sp_DeleteGender
7. sp_GetRole
8. sp_SaveEditRole
9. sp_DeleteRole
10. sp_GetParentMenu
11. sp_GetMenu
12. sp_SaveEditMenu
13. sp_DeleteMenu
14. sp_GetRoleName
15. sp_GetUser
16. sp_SaveEditUser
17. sp_DeleteUser
18. sp_GetUserName
19. sp_GetUserDetails
20. sp_SaveEditUserDetails
21. sp_DeleteUserDetails
22. sp_GetRoleMenuRights
23. sp_SaveRoleMenuRights
24. sp_DeleteRoleMenuRights
User Type
1. DBO.tblRoleMenuRights
ALL SQL SCRIPT
CREATE DATABASE TEST
/*****************************ROLE_MASTER*/
CREATE TABLE ROLE_MASTER
(
RoleId INT identity constraint pk_RoleId primary key,
RoleName VARCHAR(50) NOT NULL unique,
Description VARCHAR(100) NULL,
IsActive BIT NOT NULL default(0),
CreatedBy INT NOT NULL,
CreatedDate DATETIME NOT NULL default(getdate()),
ModifyBy INT NULL,
ModifyDate DATETIME NULL
)
INSERT INTO ROLE_MASTER(RoleName,Description,IsActive,CreatedBy)VALUES('Super Admin','Super Admin',1,1)
INSERT INTO ROLE_MASTER(RoleName,Description,IsActive,CreatedBy)VALUES('Admin','Admin',1,1)
INSERT INTO ROLE_MASTER(RoleName,Description,IsActive,CreatedBy)VALUES('User','User',1,1)
INSERT INTO ROLE_MASTER(RoleName,Description,IsActive,CreatedBy)VALUES('Guest','Guest',1,1)
/*****************************GENDER_MASTER*/
CREATE TABLE GENDER_MASTER
(
GenderId int identity constraint pk_GenderId primary key,
GenderName VARCHAR(10) NOT NULL,
CreatedBy INT NOT NULL,
CreatedDate DATETIME NOT NULL default(getdate()),
ModifyBy INT NULL,
ModifyDate DATETIME NULL
)
INSERT INTO GENDER_MASTER(GenderName,CreatedBy)VALUES('Male',1)
INSERT INTO GENDER_MASTER(GenderName,CreatedBy)VALUES('Female',1)
/*****************************MENU_MASTER*/
CREATE TABLE MENU_MASTER
(
MenuId INT identity constraint pk_MenuId primary key,
MenuName VARCHAR(100) NOT NULL,
MenuLink VARCHAR(100) NOT NULL,
MenuDesc VARCHAR(100) NOT NULL,
MenuParentId INT NULL,
DisplayOrder INT NULL,
IsActive BIT NOT NULL default(0),
WhoseChild INT default(0),
CreatedBy INT NOT NULL,
CreatedDate DATETIME NOT NULL default(getdate()),
ModifyBy INT NULL,
ModifyDate DATETIME NULL
)
--Parent Menu
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('System Master','~/Masters/RoleMaster.aspx','System Master',NULL,1,1,1)
--Parent Sub Menu
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('Role Master','~/Masters/RoleMaster.aspx','Role Master',1,1,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('Gender Master','~/Masters/GenderMaster.aspx','Gender Master',1,2,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('Menu Master','~/Masters/MenuMaster.aspx','Menu Master',1,3,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('User Master','~/Masters/UserMaster.aspx','User Master',1,4,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('User Details Master','~/Masters/UserDetailsMaster.aspx','User Details Master',1,5,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('Role Menu Rights Master','~/Masters/RoleMenuRightsMaster.aspx','User Role Menu Rights Master',1,6,1,1)
--Parent Menu
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('Tutorials','~/Tutorials/CSharp/CSharp.aspx','Tutorials',NULL,1,1,1)
--Parent Sub Menu
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('C#','~/Tutorials/CSharp/CSharp.aspx','C#',7,1,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('MVC','~/Tutorials/MVC/MVC.aspx','MVC',7,2,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('Gridview','~/Tutorials/Gridview/Gridview.aspx','Gridview',7,3,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('JavaScript','~/Tutorials/JavaScript/JavaScript.aspx','JavaScript',7,4,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('jQuery','~/Tutorials/jQuery/jQuery.aspx','jQuery',7,5,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('HTML','Tutorials/HTML/HTML.aspx','HTML',7,6,1,1)
--Parent Menu
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('About Us','../About.aspx','About Us',NULL,1,1,1)
INSERT INTO MENU_MASTER(MenuName,MenuLink,MenuDesc,MenuParentId,DisplayOrder,IsActive,CreatedBy)
VALUES('Contact Us','../Contact.aspx','Contact Us',NULL,1,1,1)
/*****************************USER_MASTER*/
CREATE TABLE USER_MASTER
(
UserId INT identity constraint pk_UserId primary key,
RoleId INT NOT NULL constraint fk_RoleId foreign key references ROLE_MASTER(RoleId),
FirstName VARCHAR(100) NOT NULL,
MiddleName VARCHAR(100) NULL,
LastName VARCHAR(100) NOT NULL,
EmailId VARCHAR(100) NOT NULL unique,
Password VARCHAR(100) NOT NULL,
IsActive BIT NOT NULL default(0),
IsFirstTimePwdChange BIT NOT NULL default(0),
CreatedBy INT NOT NULL,
CreatedDate DATETIME NOT NULL default(getdate()),
ModifyBy INT NULL,
ModifyDate DATETIME NULL
)
INSERT INTO USER_MASTER(RoleId,FirstName,MiddleName,LastName,EmailId,Password,IsActive,CreatedBy)
VALUES('1','Ratan','K','Gupta','ratan@gmail.com','HfvBW28Irb0XbYHiqYK0v3uiLcDhTc3hH8kc7zJH+68=',1,1)
INSERT INTO USER_MASTER(RoleId,FirstName,MiddleName,LastName,EmailId,Password,IsActive,CreatedBy)
VALUES('2','Deepak','','Tiwari','deepak@gmail.com','HfvBW28Irb0XbYHiqYK0v3uiLcDhTc3hH8kc7zJH+68=',1,1)
INSERT INTO USER_MASTER(RoleId,FirstName,MiddleName,LastName,EmailId,Password,IsActive,CreatedBy)
VALUES('3','Rajesh','','Punjabi','rajesh@yahoo.com','HfvBW28Irb0XbYHiqYK0v3uiLcDhTc3hH8kc7zJH+68=',1,1)
UPDATE USER_MASTER SET Password = 'HfvBW28Irb0XbYHiqYK0v3uiLcDhTc3hH8kc7zJH+68='
/*HfvBW28Irb0XbYHiqYK0v3uiLcDhTc3hH8kc7zJH+68= = password*/
/*****************************USER_DETAILS_MASTER*/
CREATE TABLE USER_DETAILS_MASTER
(
UserDetailsId INT identity constraint pk_UserDetailsId primary key,
UserId INT NOT NULL constraint fk_UserId foreign key references USER_Master(UserId),
DOB DATETIME NOT NULL,
GenderId INT NOT NULL constraint fk_GenderId foreign key references GENDER_MASTER(GenderId),
CreatedBy INT NOT NULL,
CreatedDate DATETIME NOT NULL default(getdate()),
ModifyBy INT NULL,
ModifyDate DATETIME NULL
)
INSERT INTO USER_DETAILS_MASTER(UserId,DOB,GenderId,CreatedBy)VALUES('1',DATEADD(YY,-28,GETDATE()-10),1,1)
INSERT INTO USER_DETAILS_MASTER(UserId,DOB,GenderId,CreatedBy)VALUES('2',DATEADD(YY,-25,GETDATE()-20),1,1)
INSERT INTO USER_DETAILS_MASTER(UserId,DOB,GenderId,CreatedBy)VALUES('3',DATEADD(YY,-18,GETDATE()-30),1,1)
/*****************************ROLE_MENU_RIGHTS_MASTER*/
CREATE TABLE ROLE_MENU_RIGHTS_MASTER
(
RoleMenuRightsId INT identity constraint pk_RoleMenuRightsId primary key,
RoleId INT NOT NULL constraint fkey_RoleId foreign key references ROLE_MASTER(RoleId),
MenuId INT NOT NULL constraint fk_MenuId foreign key references MENU_MASTER(MenuId),
[View] BIT NOT NULL,
[Create] BIT NOT NULL,
Edit BIT NOT NULL,
[Delete] BIT NOT NULL,
IsActive BIT NOT NULL default(0),
CreatedBy INT NOT NULL,
CreatedDate DATETIME NOT NULL default(getdate()),
ModifyBy INT NULL,
ModifyDate DATETIME NULL
)
--Super Admin
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,1,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,2,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,3,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,4,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,5,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,6,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,7,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,8,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,9,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,10,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,11,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,12,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,13,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,14,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,15,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(1,16,1,1,1,1,1,1)
--Admin
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,1,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,2,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,3,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,4,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,5,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,6,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,7,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,8,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,9,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,10,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,11,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,12,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,13,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,14,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,15,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(2,16,1,1,1,1,1,1)
--User
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,7,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,8,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,9,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,10,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,11,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,12,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,13,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,14,1,1,1,1,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(3,15,1,1,1,1,1,1)
--Guest
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(4,14,0,0,0,0,1,1)
INSERT INTO ROLE_MENU_RIGHTS_MASTER(RoleId,MenuId,[View],[Create],Edit,[Delete],IsActive,CreatedBy)VALUES(4,15,0,0,0,0,1,1)
/******************************//******************************/
/******************************//******************************/
SELECT * FROM ROLE_MASTER
SELECT * FROM GENDER_MASTER
SELECT * FROM MENU_MASTER
SELECT * FROM USER_MASTER
SELECT * FROM USER_DETAILS_MASTER
SELECT * FROM ROLE_MENU_RIGHTS_MASTER
DROP TABLE ROLE_MENU_RIGHTS_MASTER
DROP TABLE MENU_MASTER
DROP TABLE USER_DETAILS_MASTER
DROP TABLE USER_MASTER
DROP TABLE ROLE_MASTER
DROP TABLE GENDER_MASTER
/******************************//******************************/
/******************************//******************************/
-- getLogin 1,'password'
CREATE PROC getLogin
@UserId INT,
@Password VARCHAR(50)
AS
BEGIN
SELECT
UserId,
RoleId,
CONCAT(FirstName,' ', MiddleName,' ', LastName)'UserName',
EmailId,
Password,
IsActive,
IsFirstTimePwdChange
FROM USER_MASTER
WHERE UserId = @UserId AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS AND IsActive = 1
EXEC getMenu @UserId
END
-- sp_ChangePassword '1','',''
CREATE PROC sp_ChangePassword
@UserId INT,
@Password VARCHAR(50),
@NewPassword VARCHAR(50),
@IsFirstTimeChangePassword BIT = NULL
AS
BEGIN
IF EXISTS ( SELECT *
FROM USER_MASTER
WHERE UserId = @UserId AND Password = @Password COLLATE SQL_Latin1_General_CP1_CS_AS AND IsActive = 1)
BEGIN
IF @IsFirstTimeChangePassword IS NOT NULL OR @IsFirstTimeChangePassword <> ''
BEGIN
UPDATE USER_MASTER
SET
Password = @NewPassword,
IsFirstTimePwdChange = 1
WHERE UserId = @UserId
END
ELSE
BEGIN
UPDATE USER_MASTER
SET
Password = @NewPassword
WHERE UserId = @UserId
END
END
END
-- getMENU '1'
CREATE PROC getMenu
@UserId INT
AS
BEGIN
SELECT
D.UserId,
CONCAT(D.FirstName,' ', D.MiddleName,' ', D.LastName)'UserName',
B.RoleId,
B.RoleName,
C.MenuId,
C.MenuName,
C.MenuLink,
C.MenuDesc,
C.MenuParentId,
A.[View],
A.[Create],
A.[Edit],
A.[Delete]
FROM ROLE_MENU_RIGHTS_MASTER A
INNER JOIN ROLE_MASTER B ON A.RoleId = B.RoleId
INNER JOIN MENU_MASTER C ON A.MenuId = C.MenuId
INNER JOIN USER_MASTER D ON B.RoleId = D.RoleId
WHERE D.UserId = @UserId
AND (A.[View] = 1 OR A.[Create] = 1 OR A.Edit = 1 OR A.[Delete] = 1 OR A.IsActive = 1 )
END
/******************************//******************************/
/******************************GENDER MASTER*//******************************/
--EXEC sp_GetGender @GenderId = 1
CREATE PROC sp_GetGender
@GenderId INT = NULL
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY GenderId ASC)'RowNo',
GenderId,
GenderName
FROM GENDER_MASTER
WHERE (GenderId = @GenderId OR @GenderId IS NULL)
END
--EXEC sp_SaveEditGender null,'Other',1
CREATE PROC sp_SaveEditGender
@GenderId INT = NULL,
@GenderName VARCHAR(20),
@CreatedBy INT
AS
BEGIN
IF @GenderId IS NULL OR @GenderId = ''
BEGIN
INSERT
INTO GENDER_MASTER(
GenderName,
CreatedBy
)
VALUES(
@GenderName,
@CreatedBy
)
END
ELSE
BEGIN
UPDATE GENDER_MASTER
SET GenderName = @GenderName,
ModifyBy = @CreatedBy,
ModifyDate = GETDATE()
WHERE GenderId = @GenderId
END
END
--EXEC sp_DeleteGender 1
CREATE PROC sp_DeleteGender
@GenderId INT
AS
BEGIN
DELETE
FROM GENDER_MASTER
WHERE GenderId = @GenderId
END
/******************************GENDER MASTER*//******************************/
/******************************ROLE MASTER*//******************************/
--EXEC sp_GetRole @@RoleId = 1
CREATE PROC sp_GetRole
@RoleId INT = NULL
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY RoleId ASC)'RowNo',
RoleId,
RoleName,
Description,
CASE WHEN IsActive = 1 THEN 'Yes' ELSE 'No' END IsActive
FROM ROLE_MASTER
WHERE (RoleId = @RoleId OR @RoleId IS NULL)
END
--EXEC sp_SaveEditRole 1
CREATE PROC sp_SaveEditRole
@RoleId INT = NULL,
@RoleName VARCHAR(50),
@Description VARCHAR(100),
@IsActive BIT,
@CreatedBy INT
AS
BEGIN
IF @RoleId IS NULL OR @RoleId = ''
BEGIN
INSERT
INTO ROLE_MASTER(
RoleName,
Description,
IsActive,
CreatedBy
)
VALUES(
@RoleName,
@Description,
@IsActive,
@CreatedBy
)
END
ELSE
BEGIN
UPDATE ROLE_MASTER
SET RoleName = @RoleName,
Description = @Description,
IsActive = @IsActive,
ModifyBy = @CreatedBy,
ModifyDate = GETDATE()
WHERE RoleId = @RoleId
END
END
--EXEC sp_DeleteRole 1
CREATE PROC sp_DeleteRole
@RoleId INT
AS
BEGIN
DELETE
FROM ROLE_MASTER
WHERE RoleId = @RoleId
END
/******************************ROLE MASTER*//******************************/
/******************************MENU MASTER*//******************************/
CREATE PROC sp_GetParentMenu
AS
BEGIN
SELECT MenuId,
MenuName
FROM MENU_MASTER
where MenuParentId IS NULL
END
--EXEC sp_GetMenu @@RoleId = 1
CREATE PROC sp_GetMenu
@MenuId INT = NULL
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY A.MenuId ASC)'RowNo',
A.MenuId,
A.MenuName,
A.MenuLink,
A.MenuDesc,
B.MenuName'ParentMenuName',
A.MenuParentId,
A.DisplayOrder,
CASE WHEN A.IsActive = 1 THEN 'Yes' ELSE 'No' END IsActive,
A.WhoseChild
FROM MENU_MASTER A
LEFT JOIN MENU_MASTER B ON A.MenuParentId = B.MenuId
WHERE (A.MenuId = @MenuId OR @MenuId IS NULL)
END
--EXEC sp_SaveEditMenu 1
CREATE PROC sp_SaveEditMenu
@MenuId INT = NULL,
@MenuName VARCHAR(100),
@MenuLink VARCHAR(100),
@MenuDesc VARCHAR(100),
@MenuParentId INT = NULL,
@DisplayOrder VARCHAR(10),
@IsActive BIT,
@CreatedBy INT
AS
BEGIN
IF @MenuId IS NULL OR @MenuId = ''
BEGIN
INSERT
INTO MENU_MASTER(
MenuName,
MenuLink,
MenuDesc,
MenuParentId,
DisplayOrder,
IsActive,
CreatedBy
)
VALUES(
@MenuName,
@MenuLink,
@MenuDesc,
@MenuParentId,
@DisplayOrder,
@IsActive,
@CreatedBy
)
END
ELSE
BEGIN
UPDATE MENU_MASTER
SET MenuName = @MenuName,
MenuLink = @MenuLink,
MenuDesc = @MenuDesc,
MenuParentId = @MenuParentId,
DisplayOrder = @DisplayOrder,
IsActive = @IsActive,
ModifyBy = @CreatedBy,
ModifyDate = GETDATE()
WHERE MenuId = @MenuId
END
END
--EXEC sp_DeleteMenu 1
CREATE PROC sp_DeleteMenu
@MenuId INT
AS
BEGIN
DELETE
FROM MENU_MASTER
WHERE MenuId = @MenuId
END
/******************************MENU MASTER*//******************************/
/******************************USER MASTER*//******************************/
CREATE PROC sp_GetRoleName
AS
BEGIN
SELECT RoleId,
RoleName
FROM ROLE_MASTER
where IsActive = 1
END
--EXEC sp_GetUser @@RoleId = 1
CREATE PROC sp_GetUser
@UserId INT = NULL
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY A.UserId ASC)'RowNo',
A.UserId,
B.RoleId,
B.RoleName,
A.FirstName,
A.MiddleName,
A.LastName,
A.EmailId,
A.Password,
CASE WHEN A.IsActive = 1 THEN 'Yes' ELSE 'No' END IsActive
FROM USER_MASTER A
LEFT JOIN ROLE_MASTER B ON A.RoleId = B.RoleId
WHERE (A.UserId = @UserId OR @UserId IS NULL)
END
--EXEC sp_SaveEditUser 1
CREATE PROC sp_SaveEditUser
@UserId INT = NULL,
@RoleId INT,
@FirstName VARCHAR(100),
@MiddleName VARCHAR(100),
@LastName VARCHAR(100),
@EmailId VARCHAR(100),
@Password VARCHAR(100),
@IsActive BIT,
@CreatedBy INT
AS
BEGIN
IF @UserId IS NULL OR @UserId = ''
BEGIN
INSERT
INTO USER_MASTER(
RoleId,
FirstName,
MiddleName,
LastName,
EmailId,
Password,
IsActive,
CreatedBy
)
VALUES(
@RoleId,
@FirstName,
@MiddleName,
@LastName,
@EmailId,
@Password,
@IsActive,
@CreatedBy
)
END
ELSE
BEGIN
UPDATE USER_MASTER
SET RoleId = @RoleId,
FirstName = @FirstName,
MiddleName = @MiddleName,
LastName = @LastName,
EmailId = @EmailId,
--Password = @Password,
IsActive = @IsActive,
ModifyBy = @CreatedBy,
ModifyDate = GETDATE()
WHERE UserId = @UserId
END
END
--EXEC sp_DeleteUser 1
CREATE PROC sp_DeleteUser
@UserId INT
AS
BEGIN
DELETE
FROM USER_MASTER
WHERE UserId = @UserId
END
/******************************USER MASTER*//******************************/
/******************************USER DETAILS MASTER*//******************************/
CREATE PROC sp_GetUserName
AS
BEGIN
SELECT UserId,
CONCAT(FirstName,' ', MiddleName,' ', LastName)'UserName'
FROM USER_MASTER
where IsActive = 1
END
--Use sp_GetGender procedure
--EXEC sp_GetUserDetails @@RoleId = 1
CREATE PROC sp_GetUserDetails
@UserDetailsId INT = NULL
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY A.UserDetailsId ASC)'RowNo',
A.UserDetailsId,
A.UserId,
CONCAT(B.FirstName,' ', B.MiddleName,' ', B.LastName)'UserName',
FORMAT(A.DOB,'dd/MM/yyyy')'DOB',
A.GenderId,
C.GenderName
FROM USER_DETAILS_MASTER A
LEFT JOIN USER_MASTER B ON A.UserId = B.UserId
LEFT JOIN GENDER_MASTER C ON A.GenderId = C.GenderId
WHERE (A.UserDetailsId = @UserDetailsId OR @UserDetailsId IS NULL)
END
--EXEC sp_SaveEditUserDetails 1
CREATE PROC sp_SaveEditUserDetails
@UserDetailsId INT = NULL,
@UserId INT,
@DOB DATETIME,
@GenderId INT,
@CreatedBy INT
AS
BEGIN
IF @UserDetailsId IS NULL OR @UserDetailsId = ''
BEGIN
INSERT
INTO USER_DETAILS_MASTER(
UserId,
DOB,
GenderId,
CreatedBy
)
VALUES(
@UserId,
@DOB,
@GenderId,
@CreatedBy
)
END
ELSE
BEGIN
UPDATE USER_DETAILS_MASTER
SET UserId = @UserId,
DOB = @DOB,
GenderId = @GenderId,
ModifyBy = @CreatedBy,
ModifyDate = GETDATE()
WHERE UserDetailsId = @UserDetailsId
END
END
--EXEC sp_DeleteUserDetails 1
CREATE PROC sp_DeleteUserDetails
@UserDetailsId INT
AS
BEGIN
DELETE
FROM USER_DETAILS_MASTER
WHERE UserDetailsId = @UserDetailsId
END
/******************************USER DETAILS MASTER*//******************************/
/******************************ROLE MENU RIGHTS MASTER*//******************************/
--Use sp_GetRole procedure
--EXEC sp_GetUserDetails @@RoleId = 1
CREATE PROC sp_GetRoleMenuRights
@RoleId INT = NULL
AS
BEGIN
SELECT ROW_NUMBER() OVER(ORDER BY A.MenuId ASC)'RowNo',
A.MenuId,
A.MenuName,
B.RoleMenuRightsId,
CASE
WHEN (ISNULL(B.[View],0) = 0 OR ISNULL(B.[Create],0) = 0 OR ISNULL(B.Edit,0) = 0 OR ISNULL(B.[Delete],0) = 0 OR ISNULL(B.IsActive,0) = 0)
THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT)
END AS 'ChkRow',
ISNULL(B.[View],0)'View',
ISNULL(B.[Create],0)'Create',
ISNULL(B.Edit,0)'Edit',
ISNULL(B.[Delete],0)'Delete',
ISNULL(B.IsActive,0)'IsActive'
FROM MENU_MASTER A
LEFT JOIN
(
SELECT A.MenuId,
A.MenuName,
B.RoleMenuRightsId,
B.[View],
B.[Create],
B.Edit,
B.[Delete],
B.IsActive
FROM MENU_MASTER A
INNER JOIN ROLE_MENU_RIGHTS_MASTER B ON A.MenuId = B.MenuId
INNER JOIN ROLE_MASTER C ON B.RoleId = C.RoleId
WHERE C.RoleId=@RoleId
) B ON A.MenuId = B.MenuId
END
--DROP TYPE tblRoleMenuRights
CREATE TYPE DBO.tblRoleMenuRights
AS TABLE
(
RoleId INT NOT NULL,
MenuId INT NOT NULL,
[View] BIT NOT NULL,
[Create] BIT NOT NULL,
Edit BIT NOT NULL,
[Delete] BIT NOT NULL,
IsActive BIT NOT NULL
)
--EXEC sp_SaveRoleMenuRights 1
CREATE PROC sp_SaveRoleMenuRights
@tblRoleMenuRights tblRoleMenuRights READONLY,
@CreatedBy INT
AS
BEGIN
BEGIN TRY
BEGIN TRAN;
MERGE ROLE_MENU_RIGHTS_MASTER AS T
USING @tblRoleMenuRights AS S
ON T.RoleId = S.RoleId AND T.MenuId = S.MenuId
WHEN NOT MATCHED BY TARGET
THEN INSERT
(
RoleId,
MenuId,
[View],
[Create],
Edit,
[Delete],
IsActive,
CreatedBy
)
VALUES
(
S.RoleId,
S.MenuId,
S.[View],
S.[Create],
S.Edit,
S.[Delete],
S.IsActive,
@CreatedBy
)
WHEN MATCHED
THEN UPDATE SET
T.RoleId=S.RoleId,
T.MenuId=S.MenuId,
T.[View]=S.[View],
T.[Create]=S.[Create],
T.Edit=S.Edit,
T.[Delete]=S.[Delete],
T.IsActive=S.IsActive,
T.ModifyBy = @CreatedBy,
T.ModifyDate = GETDATE()
OUTPUT $action, inserted.*, deleted.*;
COMMIT TRAN;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH
END
--EXEC sp_DeleteUserDetails 1
CREATE PROC sp_DeleteRoleMenuRights
@RoleMenuRightsId INT
AS
BEGIN
DELETE
FROM ROLE_MENU_RIGHTS_MASTER
WHERE RoleMenuRightsId = @RoleMenuRightsId
END
/******************************ROLE MENU RIGHTS MASTER*//******************************/
/******************************//******************************/
Note:-
After executing all the script use following credential to Login.
Default password for all user is password.
User Id: - 1
Password: - password
Use following password to open the zip
dotnet-jigyasa.blogspot.in
