权限管理----角色与页面关系

做权限管理，我们要做好某一个角色所拥有的控制页面，因此，会在数据库中，建立一张[RolePages]来存储角色与控制页面的关系信息。

 在Asp.net介面，如下示图：

表结构如下：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RolePages](
    [RoleId] [smallint] NOT NULL,
    [PagesId] [int] NOT NULL,
    [IsEnable] [bit] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RolePages]  WITH CHECK ADD  CONSTRAINT [FK_RolePages_Pages] FOREIGN KEY([PagesId])
REFERENCES [dbo].[Pages] ([PagesId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RolePages] CHECK CONSTRAINT [FK_RolePages_Pages]
GO

ALTER TABLE [dbo].[RolePages]  WITH CHECK ADD  CONSTRAINT [FK_RolePages_Role] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Role] ([RoleId])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[RolePages] CHECK CONSTRAINT [FK_RolePages_Role]
GO

 

接下来，我们建立存储过程，专为角色分配页面。

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_RolePages_AssignPagesAction]
(
    @RoleId smallint,
    @PagesId int,
    @IsEnable bit
)
AS
BEGIN TRANSACTION
    DECLARE @err int
IF (SELECT count(*)  FROM [RolePages] WHERE [RoleId] = @RoleId AND [PagesId] = @PagesId ) > 0
    BEGIN        
        UPDATE [RolePages] SET [IsEnable]=@IsEnable WHERE [RoleId] = @RoleId AND [PagesId] = @PagesId 
        SET @err=@@ERROR
        IF @err<>0            
            ROLLBACK TRANSACTION    
    END
ELSE
    BEGIN
        INSERT INTO [RolePages] ([RoleId],[PagesId] ,[IsEnable] ) VALUES (@RoleId ,@PagesId ,@IsEnable)
        SET @err=@@ERROR
        IF @err<>0            
            ROLLBACK TRANSACTION    
    END
COMMIT TRANSACTION
    
GO