| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

View
 

Case Study - Basic User Template

Page history last edited by Dr. Ron Eaglin 9 years, 3 months ago

Basic User Template

 

I have created a template that allows you to create a project based upon the template. This template is a ZIP File that is included for download here.

 

Initial Version  Basic Eaglin User Template Version 1.zip

 

Latest version (6/21/2012) Basic User Template Version 3.zip

 

 

You can create projects using this template after first importing. Here are a set of convenient video explaining the code. To add this to your menu when creating a new project follow the instructions at http://msdn.microsoft.com/en-us/library/y3kkate1.aspx . Bottom line is put the template above in the ProjectTemplates directory (below) and you will have access to it as a New Project under Visual C# 

 

Documents\Visual Studio 2010\Templates\ProjectTemplates\Visual C#\ (put zip file here)

 

 

Summary

 

Features of this template are outlined in the video links below. The database script for this system is included below. This demonstrates a simple role based user system and how to implement it. A lot of programming techniques are demonstrated as part of the template and in the supporting video materials.

 

Video Support

 

User Management Model in Basic User Template - http://online1.daytonastate.edu/player2.php?id=ed57844fa5e051809ead5aa7e3e1d555 

 

User Controls in Basic User Template - http://online1.daytonastate.edu/player2.php?id=4ebccfb3e317c7789f04f7a558df4537 

 

 

Additional Lectures

 

Lecture - Form Objects - User Controls

 

Dynamically Adding User Controls to a Page

 

Lecture - Form Objects - ListView  - This uses this case study to show how to make an editable ListView. A ListView is used in this example to edit user information.

 

Database Code

 

 

Shown is the SQL Server Script for the Database as used in Version 1 of BasicUserTemplate

/****** Object:  Table [dbo].[Users]    Script Date: 06/13/2012 16:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](255) NOT NULL,
    [UserPassword] [nvarchar](63) NOT NULL,
    [UserEmail] [nvarchar](255) NOT NULL,
    [UserCreatedDate] [datetime] NOT NULL,
    [UserIsDeletedBit] [bit] NOT NULL,
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
/****** Object:  Table [dbo].[Roles]    Script Date: 06/13/2012 16:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Roles](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [RoleNameText] [varchar](50) NULL,
    [RoleDescriptionText] [nvarchar](max) NULL,
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 ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PageRoles]    Script Date: 06/13/2012 16:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PageRoles](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [PageFileName] [varchar](250) NOT NULL,
    [RoleID] [int] NOT NULL,
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 ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[UserRoles]    Script Date: 06/13/2012 16:05:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [RoleID] [int] NOT NULL,
    [RoleAssignedByUserID] [int] NULL,
    [RoleAssignedDate] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertUser]    Script Date: 06/13/2012 16:05:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertUser] (
    @UserName NVARCHAR(255),
    @UserPassword NVARCHAR(63),
    @UserEmail NVARCHAR(255) )
AS
BEGIN
INSERT INTO Users
 (UserName, UserPassword, UserEmail, UserCreatedDate, UserIsDeletedBit)
VALUES
 (@Username, @UserPassword, @UserEmail, GETDATE(), 0) 
END
GO
/****** Object:  View [dbo].[view_UserRoles]    Script Date: 06/13/2012 16:06:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[view_UserRoles]
AS
SELECT    Users.id        AS 'UserID',
        Users.UserName    AS 'UserName',
        Users.UserEmail    AS 'UserEmail',
        Roles.id        AS 'RoleID',
        RoleNameText    AS 'RoleName'
FROM
        Users, Roles, UserRoles
WHERE
        UserRoles.UserID = Users.id
        AND UserRoles.RoleID = Roles.id
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertRole]    Script Date: 06/13/2012 16:05:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertRole]
    @RoleNameText VARCHAR(50),
    @RoleDescriptionText NVARCHAR(MAX)
AS
BEGIN
DECLARE @N INT
SELECT @N = COUNT(*) FROM Roles
            WHERE RoleNameText = @RoleNameText
             
IF @N = 0
BEGIN
INSERT INTO Roles
 (RoleNameText, RoleDescriptionText)
VALUES
 (@RoleNameText, @RoleDescriptionText)
END
END
GO
/****** Object:  StoredProcedure [dbo].[sp_AssignRole]    Script Date: 06/13/2012 16:05:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_AssignRole]
    @UserID INT,
    @RoleID INT,
    @AssignedByUserID INT = NULL
AS
BEGIN
DECLARE @N INT
SELECT @N = COUNT(*) FROM UserRoles
            WHERE UserID = @UserID 
            AND RoleID = @RoleID
 
IF @N = 0
BEGIN
INSERT INTO UserRoles
 (UserID, RoleID, RoleAssignedByUserID, RoleAssignedDate)
VALUES
 (@UserID, @RoleID, @AssignedByUserID, GETDATE())
END
END
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertPageRole]    Script Date: 06/13/2012 16:05:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_InsertPageRole]
    @PageFileName VARCHAR(250),
    @RoleID INT = 1
AS
BEGIN
DECLARE @N INT
SELECT @N = COUNT(*) FROM PageRoles
        WHERE PageFileName = @PageFileName
        AND RoleID = @RoleID
IF @N = 0
BEGIN
INSERT INTO PageRoles
 (PageFileName, RoleID)
VALUES
 (@PageFileName, @RoleID)
 
END
END
GO
/****** Object:  ForeignKey [FK__UserRoles__RoleA__07020F21]    Script Date: 06/13/2012 16:05:59 ******/
ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD FOREIGN KEY([RoleAssignedByUserID])
REFERENCES [dbo].[Users] ([id])
GO
/****** Object:  ForeignKey [FK__UserRoles__RoleI__060DEAE8]    Script Date: 06/13/2012 16:05:59 ******/
ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD FOREIGN KEY([RoleID])
REFERENCES [dbo].[Roles] ([id])
GO
/****** Object:  ForeignKey [FK__UserRoles__UserI__0519C6AF]    Script Date: 06/13/2012 16:05:59 ******/
ALTER TABLE [dbo].[UserRoles]  WITH CHECK ADD FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([id])
GO
 

 

 

Comments (0)

You don't have permission to comment on this page.