Hecky's Project ASP.NET Solution
USE CASE DIAGRAM
- company drivers are checking their duty schedule from garage depot notice board
- company drivers are liaising through Senior Garage Administrator (SGA) who can only deal with one person at a time to address their work related issues (communication)
- The administration has one operational manager who deal with day to day operations and manage all departments
- The administration has an assistant operational manager who deal with disciplinary, public complains, referrals from SGA and occasionally covers operational managers’ job
- There is also accident prevention manager, who deals health and safety and general accident issues and the public in relation to any accident issues on behalf of the company
- And not the least a pay clerk who deals with staff pay
- Driver reports absence
- Driver view holiday history
- Driver and Admin can register online
- Driver can change duty with another driver but needs approval from admin
- Staff view jobs relating to them e.g. meeting minutes
- Driver can send message to operational manager and assistant operational manager
- Driver can view pay slip
Figure 1: Use-case diagram
ERD (Entity Relationship Diagram)
Figure 2: ERD
IMPLEMENTATION
This section discuss the steps taken to develop the application and the challenges and logics put in place to ensure the application meets the requirements discussed in the Analysis stage of this report.
As discussed in the Design stage of this report, the web application was developed using ASP.NET technology with C# the programming language used to manage the back end code. The database used was SQL Server 2012 and T-SQL (Transact Structured Query Language) is the language used to communicate/query the database.
Securing the Website
The site’s security was implemented using the asp.net technology provided for User roles and membership. It allows setting of permissions based on user roles and restricting access to files in the containing folders where permissions were restricted. Doing this makes it easy to keep track of the permissions and as well assign more layers of security to all users without breaking the security system structure put in place.
The access rules for security were declared in the “~/web.config†and it relates to the tables created in the database. One of the major codes that determine the permission is shown below:
Figure 3: Setting the site security in the web.config file
Security Trimming
ASP.NET provide a useful feature called security trimming that allows menu links to be hidden to users who do not have access to view the link. This feature is set up by adding the below code and ensuring that a sitemap is used for menu control.
Figure 4: Sitemap usage
The security trimming feature reduces the link shown to users. For example, the screenshot shown below is for anonymous users who are yet to login:
Figure 5: Menu links shown to anonymous user
Whereas an authenticated user who is a driver can see more links and as well access it. The screenshot below shows the link for a driver:
Figure 6: Menu links shown to a driver
Communicating with the database
A separate class “DBHandler.cs†was created in the app_code directory to handle all database connection, query, inserts etc. This is done to make the site more maintainable and enable reuse of code while querying the database.
This class also includes exception handling code to prevent the web app from breaking when errors occur while querying the database.
Assigning Jobs to Drivers
This task is executed by users with admin privileges and can be found in the page located at "~/Admin/ManageJob". This section requires the use of client and server side validation to prevent the Administrator making mistakes and as well guide them during the creation of a job.
Some of the rules set in place to avoid problems include:
- Driver is not assigned multiple jobs at the same date and time
- One Bus is not assigned to multiple jobs
To avoid assigning multiple jobs to a driver, SQL query was written to check that the driver does not have a duty assigned to him/her at the particular date queried as shown below:
Figure 7: Preventing driver taking multiple jobs on the same date
The result of the above query ensures that an error message is displayed when an administrator attempts to assign a new job for a driver who has a scheduled job within the time frame chosen. This is shown in the screenshot below:
Figure 8: Driver prevented from having multiple jobs assigned at the same time
To avoid assigning multiple jobs to a bus, SQL query was written to check that the bus does not have a duty assigned at the particular date queried as shown below:
Figure 9: Preventing bus having multiple jobs assigned at the same time
The screenshot below shows the result of the above query code used to prevent a bus from been assigned to a job at the same time.
Figure 10: Bus creation with multiple jobs prevented
The view for managing a job is shown below and a validation for assigning a job included in the back end code as shown below:
Figure 11: Manage a Job page
Figure 12: Checks before creating a new job (Back end code)
Database Implementation
The database was created in SQL Server 2012 and the script used can be found in the Appendix section of this report. The database structure is exactly the same as that described in the ERD (Entity Relational Diagram) shown in the design of this report.
The Database name created is “HectorDB†and a total of 26 tables were created with majority of the tables handling the security part of the web application (User roles and membership).
One problem encountered during this implementation is the time taken to perfect my Transact-Sql query skills which was vital in many areas of the implementation including the code written in the ASP.NET pages.
Creating the Message
The implementation of the message required a lot of careful planning and testing to ensure the right user receives the correct message sent to them. A combination of SQL code and C# code was used to implement this task. The screenshot below shows the messages shown to a driver.
Figure 13: Messages displayed to a driver
ANALYSIS A – SQL CODE
USE [master]
GO
/****** Object: Database [HectorDb] ******/
CREATE DATABASE [HectorDb]
USE [HectorDb]
GO
CREATE TABLE [dbo].[Applications](
[ApplicationName] [nvarchar](235) NOT NULL,
[ApplicationId] [uniqueidentifier] NOT NULL,
[Description] [nvarchar](256) NULL,
PRIMARY KEY CLUSTERED
(
[ApplicationId] 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].[aspnet_Applications] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Applications](
[ApplicationName] [nvarchar](256) NOT NULL,
[LoweredApplicationName] [nvarchar](256) NOT NULL,
[ApplicationId] [uniqueidentifier] NOT NULL,
[Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED
(
[ApplicationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[LoweredApplicationName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[ApplicationName] 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].[aspnet_Membership] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Membership](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Password] [nvarchar](128) NOT NULL,
[PasswordFormat] [int] NOT NULL,
[PasswordSalt] [nvarchar](128) NOT NULL,
[MobilePIN] [nvarchar](16) NULL,
[Email] [nvarchar](256) NULL,
[LoweredEmail] [nvarchar](256) NULL,
[PasswordQuestion] [nvarchar](256) NULL,
[PasswordAnswer] [nvarchar](128) NULL,
[IsApproved] [bit] NOT NULL,
[IsLockedOut] [bit] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[LastLoginDate] [datetime] NOT NULL,
[LastPasswordChangedDate] [datetime] NOT NULL,
[LastLockoutDate] [datetime] NOT NULL,
[FailedPasswordAttemptCount] [int] NOT NULL,
[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
[FailedPasswordAnswerAttemptWindowStart] [datetime] NOT NULL,
[Comment] [ntext] NULL,
PRIMARY KEY NONCLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[aspnet_Paths] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Paths](
[ApplicationId] [uniqueidentifier] NOT NULL,
[PathId] [uniqueidentifier] NOT NULL,
[Path] [nvarchar](256) NOT NULL,
[LoweredPath] [nvarchar](256) NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[PathId] 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].[aspnet_PersonalizationAllUsers] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_PersonalizationAllUsers](
[PathId] [uniqueidentifier] NOT NULL,
[PageSettings] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[PathId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[aspnet_PersonalizationPerUser] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_PersonalizationPerUser](
[Id] [uniqueidentifier] NOT NULL,
[PathId] [uniqueidentifier] NULL,
[UserId] [uniqueidentifier] NULL,
[PageSettings] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[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] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[aspnet_Profile] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Profile](
[UserId] [uniqueidentifier] NOT NULL,
[PropertyNames] [ntext] NOT NULL,
[PropertyValuesString] [ntext] NOT NULL,
[PropertyValuesBinary] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[aspnet_Roles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Roles](
[ApplicationId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
[LoweredRoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
PRIMARY KEY NONCLUSTERED
(
[RoleId] 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].[aspnet_SchemaVersions] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_SchemaVersions](
[Feature] [nvarchar](128) NOT NULL,
[CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
[IsCurrentVersion] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Feature] ASC,
[CompatibleSchemaVersion] 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].[aspnet_Users] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Users](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[UserName] [nvarchar](256) NOT NULL,
[LoweredUserName] [nvarchar](256) NOT NULL,
[MobileAlias] [nvarchar](16) NULL,
[IsAnonymous] [bit] NOT NULL,
[LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[UserId] 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].[aspnet_UsersInRoles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_UsersInRoles](
[UserId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RoleId] 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].[aspnet_WebEvent_Events] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aspnet_WebEvent_Events](
[EventId] [char](32) NOT NULL,
[EventTimeUtc] [datetime] NOT NULL,
[EventTime] [datetime] NOT NULL,
[EventType] [nvarchar](256) NOT NULL,
[EventSequence] [decimal](19, 0) NOT NULL,
[EventOccurrence] [decimal](19, 0) NOT NULL,
[EventCode] [int] NOT NULL,
[EventDetailCode] [int] NOT NULL,
[Message] [nvarchar](1024) NULL,
[ApplicationPath] [nvarchar](256) NULL,
[ApplicationVirtualPath] [nvarchar](256) NULL,
[MachineName] [nvarchar](256) NOT NULL,
[RequestUrl] [nvarchar](1024) NULL,
[ExceptionType] [nvarchar](256) NULL,
[Details] [ntext] NULL,
PRIMARY KEY CLUSTERED
(
[EventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Bus] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Bus](
[busid] [varchar](10) NOT NULL,
[enginenumber] [varchar](50) NULL,
[chasisnumber] [varchar](50) NULL,
[platenumber] [varchar](50) NULL,
[condition] [varchar](50) NULL,
CONSTRAINT [PK_Bus] PRIMARY KEY CLUSTERED
(
[busid] 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].[DriverDuty] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DriverDuty](
[dutyid] [int] IDENTITY(1,1) NOT NULL,
[day_starttime] [datetime] NOT NULL,
[day_endtime] [datetime] NOT NULL,
[username] [varchar](50) NOT NULL,
[startlocid] [int] NOT NULL,
[endlocid] [int] NULL,
[busid] [varchar](10) NULL,
[shiftcompleted] [int] NULL,
CONSTRAINT [PK_DriverDuty] PRIMARY KEY CLUSTERED
(
[dutyid] 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].[EmployeeContract] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmployeeContract](
[contractid] [int] IDENTITY(1,1) NOT NULL,
[salary] [decimal](18, 2) NOT NULL,
[dateemployed] [date] NOT NULL,
[dateunemployed] [date] NULL,
[holidaydaysallowed] [int] NULL,
[commission] [decimal](4, 2) NULL,
[username] [varchar](50) NULL,
CONSTRAINT [PK_EmployeeContract] PRIMARY KEY CLUSTERED
(
[contractid] 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].[HolidayTaken] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HolidayTaken](
[holtakenid] [int] IDENTITY(1,1) NOT NULL,
[startdate] [datetime] NOT NULL,
[enddate] [datetime] NOT NULL,
[approved] [int] NULL,
[username] [varchar](50) NOT NULL,
[holtypeid] [int] NOT NULL,
CONSTRAINT [PK_HolidayTaken] PRIMARY KEY CLUSTERED
(
[holtakenid] 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].[HolidayType] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HolidayType](
[holtypeid] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](150) NOT NULL,
[description] [varchar](200) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Location] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Location](
[locid] [int] IDENTITY(1,1) NOT NULL,
[locname] [varchar](100) NOT NULL,
[locdescription] [varchar](200) NULL,
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[locid] 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].[Memberships] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Memberships](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[Password] [nvarchar](128) NOT NULL,
[PasswordFormat] [int] NOT NULL,
[PasswordSalt] [nvarchar](128) NOT NULL,
[Email] [nvarchar](256) NULL,
[PasswordQuestion] [nvarchar](256) NULL,
[PasswordAnswer] [nvarchar](128) NULL,
[IsApproved] [bit] NOT NULL,
[IsLockedOut] [bit] NOT NULL,
[CreateDate] [datetime] NOT NULL,
[LastLoginDate] [datetime] NOT NULL,
[LastPasswordChangedDate] [datetime] NOT NULL,
[LastLockoutDate] [datetime] NOT NULL,
[FailedPasswordAttemptCount] [int] NOT NULL,
[FailedPasswordAttemptWindowStart] [datetime] NOT NULL,
[FailedPasswordAnswerAttemptCount] [int] NOT NULL,
[FailedPasswordAnswerAttemptWindowsStart] [datetime] NOT NULL,
[Comment] [nvarchar](256) NULL,
PRIMARY KEY CLUSTERED
(
[UserId] 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].[Message] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Message](
[messageid] [int] IDENTITY(1,1) NOT NULL,
[subject] [varchar](100) NOT NULL,
[messagebody] [text] NOT NULL,
[username_from] [varchar](50) NOT NULL,
[username_to] [varchar](50) NOT NULL,
[created_date] [datetime] NULL,
CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
[messageid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Profiles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Profiles](
[UserId] [uniqueidentifier] NOT NULL,
[PropertyNames] [nvarchar](4000) NOT NULL,
[PropertyValueStrings] [nvarchar](4000) NOT NULL,
[PropertyValueBinary] [image] NOT NULL,
[LastUpdatedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Roles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Roles](
[ApplicationId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
[Description] [nvarchar](256) NULL,
PRIMARY KEY CLUSTERED
(
[RoleId] 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].[Users] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[ApplicationId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL,
[UserName] [nvarchar](50) NOT NULL,
[IsAnonymous] [bit] NOT NULL,
[LastActivityDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] 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].[UsersInRoles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UsersInRoles](
[UserId] [uniqueidentifier] NOT NULL,
[RoleId] [uniqueidentifier] NOT NULL,
PRIMARY KEY CLUSTERED
(
[UserId] ASC,
[RoleId] 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].[UsersOpenAuthAccounts] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UsersOpenAuthAccounts](
[ApplicationName] [nvarchar](128) NOT NULL,
[ProviderName] [nvarchar](128) NOT NULL,
[ProviderUserId] [nvarchar](128) NOT NULL,
[ProviderUserName] [nvarchar](max) NOT NULL,
[MembershipUserName] [nvarchar](128) NOT NULL,
[LastUsedUtc] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[ApplicationName] ASC,
[ProviderName] ASC,
[ProviderUserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[UsersOpenAuthData] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UsersOpenAuthData](
[ApplicationName] [nvarchar](128) NOT NULL,
[MembershipUserName] [nvarchar](128) NOT NULL,
[HasLocalPassword] [bit] NOT NULL,
PRIMARY KEY CLUSTERED
(
[ApplicationName] ASC,
[MembershipUserName] 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: View [dbo].[vw_aspnet_Applications] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Applications]
AS SELECT [dbo].[aspnet_Applications].[ApplicationName], [dbo].[aspnet_Applications].[LoweredApplicationName], [dbo].[aspnet_Applications].[ApplicationId], [dbo].[aspnet_Applications].[Description]
FROM [dbo].[aspnet_Applications]
GO
/****** Object: View [dbo].[vw_aspnet_MembershipUsers] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_MembershipUsers]
AS SELECT [dbo].[aspnet_Membership].[UserId],
[dbo].[aspnet_Membership].[PasswordFormat],
[dbo].[aspnet_Membership].[MobilePIN],
[dbo].[aspnet_Membership].[Email],
[dbo].[aspnet_Membership].[LoweredEmail],
[dbo].[aspnet_Membership].[PasswordQuestion],
[dbo].[aspnet_Membership].[PasswordAnswer],
[dbo].[aspnet_Membership].[IsApproved],
[dbo].[aspnet_Membership].[IsLockedOut],
[dbo].[aspnet_Membership].[CreateDate],
[dbo].[aspnet_Membership].[LastLoginDate],
[dbo].[aspnet_Membership].[LastPasswordChangedDate],
[dbo].[aspnet_Membership].[LastLockoutDate],
[dbo].[aspnet_Membership].[FailedPasswordAttemptCount],
[dbo].[aspnet_Membership].[FailedPasswordAttemptWindowStart],
[dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptCount],
[dbo].[aspnet_Membership].[FailedPasswordAnswerAttemptWindowStart],
[dbo].[aspnet_Membership].[Comment],
[dbo].[aspnet_Users].[ApplicationId],
[dbo].[aspnet_Users].[UserName],
[dbo].[aspnet_Users].[MobileAlias],
[dbo].[aspnet_Users].[IsAnonymous],
[dbo].[aspnet_Users].[LastActivityDate]
FROM [dbo].[aspnet_Membership] INNER JOIN [dbo].[aspnet_Users]
ON [dbo].[aspnet_Membership].[UserId] = [dbo].[aspnet_Users].[UserId]
GO
/****** Object: View [dbo].[vw_aspnet_Profiles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Profiles]
AS SELECT [dbo].[aspnet_Profile].[UserId], [dbo].[aspnet_Profile].[LastUpdatedDate],
[DataSize]= DATALENGTH([dbo].[aspnet_Profile].[PropertyNames])
+ DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesString])
+ DATALENGTH([dbo].[aspnet_Profile].[PropertyValuesBinary])
FROM [dbo].[aspnet_Profile]
GO
/****** Object: View [dbo].[vw_aspnet_Roles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Roles]
AS SELECT [dbo].[aspnet_Roles].[ApplicationId], [dbo].[aspnet_Roles].[RoleId], [dbo].[aspnet_Roles].[RoleName], [dbo].[aspnet_Roles].[LoweredRoleName], [dbo].[aspnet_Roles].[Description]
FROM [dbo].[aspnet_Roles]
GO
/****** Object: View [dbo].[vw_aspnet_Users] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_Users]
AS SELECT [dbo].[aspnet_Users].[ApplicationId], [dbo].[aspnet_Users].[UserId], [dbo].[aspnet_Users].[UserName], [dbo].[aspnet_Users].[LoweredUserName], [dbo].[aspnet_Users].[MobileAlias], [dbo].[aspnet_Users].[IsAnonymous], [dbo].[aspnet_Users].[LastActivityDate]
FROM [dbo].[aspnet_Users]
GO
/****** Object: View [dbo].[vw_aspnet_UsersInRoles] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_UsersInRoles]
AS SELECT [dbo].[aspnet_UsersInRoles].[UserId], [dbo].[aspnet_UsersInRoles].[RoleId]
FROM [dbo].[aspnet_UsersInRoles]
GO
/****** Object: View [dbo].[vw_aspnet_WebPartState_Paths] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_Paths]
AS SELECT [dbo].[aspnet_Paths].[ApplicationId], [dbo].[aspnet_Paths].[PathId], [dbo].[aspnet_Paths].[Path], [dbo].[aspnet_Paths].[LoweredPath]
FROM [dbo].[aspnet_Paths]
GO
/****** Object: View [dbo].[vw_aspnet_WebPartState_Shared] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_Shared]
AS SELECT [dbo].[aspnet_PersonalizationAllUsers].[PathId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationAllUsers].[PageSettings]), [dbo].[aspnet_PersonalizationAllUsers].[LastUpdatedDate]
FROM [dbo].[aspnet_PersonalizationAllUsers]
GO
/****** Object: View [dbo].[vw_aspnet_WebPartState_User] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE VIEW [dbo].[vw_aspnet_WebPartState_User]
AS SELECT [dbo].[aspnet_PersonalizationPerUser].[PathId], [dbo].[aspnet_PersonalizationPerUser].[UserId], [DataSize]=DATALENGTH([dbo].[aspnet_PersonalizationPerUser].[PageSettings]), [dbo].[aspnet_PersonalizationPerUser].[LastUpdatedDate]
FROM [dbo].[aspnet_PersonalizationPerUser]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [aspnet_Applications_Index] ******/
CREATE CLUSTERED INDEX [aspnet_Applications_Index] ON [dbo].[aspnet_Applications]
(
[LoweredApplicationName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [aspnet_Membership_index] ******/
CREATE CLUSTERED INDEX [aspnet_Membership_index] ON [dbo].[aspnet_Membership]
(
[ApplicationId] ASC,
[LoweredEmail] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [aspnet_Paths_index] ******/
CREATE UNIQUE CLUSTERED INDEX [aspnet_Paths_index] ON [dbo].[aspnet_Paths]
(
[ApplicationId] ASC,
[LoweredPath] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [aspnet_PersonalizationPerUser_index1] ******/
CREATE UNIQUE CLUSTERED INDEX [aspnet_PersonalizationPerUser_index1] ON [dbo].[aspnet_PersonalizationPerUser]
(
[PathId] ASC,
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [aspnet_Roles_index1] ******/
CREATE UNIQUE CLUSTERED INDEX [aspnet_Roles_index1] ON [dbo].[aspnet_Roles]
(
[ApplicationId] ASC,
[LoweredRoleName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [aspnet_Users_Index] ******/
CREATE UNIQUE CLUSTERED INDEX [aspnet_Users_Index] ON [dbo].[aspnet_Users]
(
[ApplicationId] ASC,
[LoweredUserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [aspnet_PersonalizationPerUser_ncindex2] ******/
CREATE UNIQUE NONCLUSTERED INDEX [aspnet_PersonalizationPerUser_ncindex2] ON [dbo].[aspnet_PersonalizationPerUser]
(
[UserId] ASC,
[PathId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [aspnet_Users_Index2] ******/
CREATE NONCLUSTERED INDEX [aspnet_Users_Index2] ON [dbo].[aspnet_Users]
(
[ApplicationId] ASC,
[LastActivityDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [aspnet_UsersInRoles_index] ******/
CREATE NONCLUSTERED INDEX [aspnet_UsersInRoles_index] ON [dbo].[aspnet_UsersInRoles]
(
[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[aspnet_Applications] ADD DEFAULT (newid()) FOR [ApplicationId]
GO
ALTER TABLE [dbo].[aspnet_Membership] ADD DEFAULT ((0)) FOR [PasswordFormat]
GO
ALTER TABLE [dbo].[aspnet_Paths] ADD DEFAULT (newid()) FOR [PathId]
GO
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] ADD DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[aspnet_Roles] ADD DEFAULT (newid()) FOR [RoleId]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (newid()) FOR [UserId]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT (NULL) FOR [MobileAlias]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD DEFAULT ((0)) FOR [IsAnonymous]
GO
ALTER TABLE [dbo].[Bus] ADD CONSTRAINT [DF_Bus_condition] DEFAULT ('Good') FOR [condition]
GO
ALTER TABLE [dbo].[DriverDuty] ADD CONSTRAINT [DF_DriverDuty_shiftcompleted] DEFAULT ((0)) FOR [shiftcompleted]
GO
ALTER TABLE [dbo].[EmployeeContract] ADD CONSTRAINT [DF_EmployeeContract_dateemployed] DEFAULT (getdate()) FOR [dateemployed]
GO
ALTER TABLE [dbo].[EmployeeContract] ADD CONSTRAINT [DF_EmployeeContract_holidaydaysallowed] DEFAULT ((0)) FOR [holidaydaysallowed]
GO
ALTER TABLE [dbo].[HolidayTaken] ADD CONSTRAINT [DF_HolidayTaken_approved] DEFAULT ((0)) FOR [approved]
GO
ALTER TABLE [dbo].[Message] ADD CONSTRAINT [DF_Message_created_date] DEFAULT (getdate()) FOR [created_date]
GO
ALTER TABLE [dbo].[aspnet_Membership] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_Membership] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[aspnet_Paths] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_PersonalizationAllUsers] WITH CHECK ADD FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
GO
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] WITH CHECK ADD FOREIGN KEY([PathId])
REFERENCES [dbo].[aspnet_Paths] ([PathId])
GO
ALTER TABLE [dbo].[aspnet_PersonalizationPerUser] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[aspnet_Profile] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[aspnet_Roles] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_Users] WITH CHECK ADD FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[aspnet_Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[aspnet_UsersInRoles] WITH CHECK ADD FOREIGN KEY([RoleId])
REFERENCES [dbo].[aspnet_Roles] ([RoleId])
GO
ALTER TABLE [dbo].[aspnet_UsersInRoles] WITH CHECK ADD FOREIGN KEY([UserId])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO
ALTER TABLE [dbo].[Memberships] WITH CHECK ADD CONSTRAINT [MembershipApplication] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [MembershipApplication]
GO
ALTER TABLE [dbo].[Memberships] WITH CHECK ADD CONSTRAINT [MembershipUser] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([UserId])
GO
ALTER TABLE [dbo].[Memberships] CHECK CONSTRAINT [MembershipUser]
GO
ALTER TABLE [dbo].[Profiles] WITH CHECK ADD CONSTRAINT [UserProfile] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([UserId])
GO
ALTER TABLE [dbo].[Profiles] CHECK CONSTRAINT [UserProfile]
GO
ALTER TABLE [dbo].[Roles] WITH CHECK ADD CONSTRAINT [RoleApplication] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[Roles] CHECK CONSTRAINT [RoleApplication]
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [UserApplication] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[Applications] ([ApplicationId])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [UserApplication]
GO
ALTER TABLE [dbo].[UsersInRoles] WITH CHECK ADD CONSTRAINT [UsersInRoleRole] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Roles] ([RoleId])
GO
ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [UsersInRoleRole]
GO
ALTER TABLE [dbo].[UsersInRoles] WITH CHECK ADD CONSTRAINT [UsersInRoleUser] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([UserId])
GO
ALTER TABLE [dbo].[UsersInRoles] CHECK CONSTRAINT [UsersInRoleUser]
GO
ALTER TABLE [dbo].[UsersOpenAuthAccounts] WITH CHECK ADD CONSTRAINT [OpenAuthAccount_UserData] FOREIGN KEY([ApplicationName], [MembershipUserName])
REFERENCES [dbo].[UsersOpenAuthData] ([ApplicationName], [MembershipUserName])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[UsersOpenAuthAccounts] CHECK CONSTRAINT [OpenAuthAccount_UserData]
GO
USE [master]
GO
ALTER DATABASE [HectorDb] SET READ_WRITE
GO

- Tech Guy's blog
- Log in to post comments