权限管理----页面管理

在Project中，实现权限管理，首先要管理好需要权限控制的页面，因此，我们可以在SQL Server建立一个表[Pages]来存储页面。
下面为表[pages]结构，可参考：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Pages](
    [PagesId] [int] IDENTITY(1,1) NOT NULL,
    [PagesName] [nvarchar](1000) NOT NULL,  
    [SecurityTypeId] [tinyint] NOT NULL,--可选字段，Page所属类别
    [Description] [nvarchar](150) NULL,
 CONSTRAINT [PK__Pages__5070F446] PRIMARY KEY CLUSTERED 
(
    [PagesId] 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

ALTER TABLE [dbo].[Pages]  WITH CHECK ADD  CONSTRAINT [FK_Pages_SecurityType] FOREIGN KEY([SecurityTypeId])
REFERENCES [dbo].[SecurityType] ([SecurityTypeId])
ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[Pages] CHECK CONSTRAINT [FK_Pages_SecurityType]
GO

  

建立插入存储过程usp_Pages_Insert：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE     PROCEDURE [dbo].[usp_Pages_Insert]
(
    @PagesName nvarchar(1000),
    @SecurityTypeId tinyint,
    @Description nvarchar(50)
)
AS    
SELECT [PagesName] FROM [Pages] WHERE [PagesName]=@PagesName
IF @@ROWCOUNT>0
BEGIN
    RAISERROR(N'此页面已经存在，无法添加！',16,1)
    RETURN
END
ELSE

  BEGIN TRANSACTION
        DECLARE @err int
            INSERT INTO dbo.Pages([PagesName],[Description],[SecurityTypeId]) VALUES (@PagesName,@Description,@SecurityTypeId)
        SET @err=@@ERROR
        IF @err<>0    
        BEGIN
    ROLLBACK TRANSACTION        
        END
        
    COMMIT TRANSACTION
GO

  

建立更新存储程usp_Pages_Updatge：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE [dbo].[usp_Pages_Update]
(
@ID tinyint,
@PagesName nvarchar(1000),
@SecurityTypeId tinyint,
@Description nvarchar(50)
)
AS    
 SELECT [PagesName] FROM [Pages] WHERE [PagesName]=@PagesName AND [PagesId] <> @ID
IF @@ROWCOUNT>0
BEGIN
    RAISERROR(N'此页面已经存在，无法更新！',16,1)
    RETURN
END
ELSE

  BEGIN TRANSACTION
        DECLARE @err int
            UPDATE [Pages] SET [PagesName] = @PagesName,[Description] = @Description,[SecurityTypeId] = @SecurityTypeId WHERE [PagesId] = @ID
        SET @err=@@ERROR
        IF @err<>0    
        BEGIN
    ROLLBACK TRANSACTION        
        END
        
    COMMIT TRANSACTION

GO

  

下面示图为页面管理介面：