installaspnetjobsdatabase.sql

asp.net技术内幕的书配源码

/****** Create AspNETJobs Database ******/

Print 'Creating AspNETJobs Database...'

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'AspNETJobs')
	DROP DATABASE [AspNETJobs]
GO

CREATE DATABASE [AspNETJobs]  
GO

Print 'Okay, database created'

use [AspNETJobs]
GO


Print 'Dropping objects...'

/****** Object:  Stored Procedure dbo.UpdateJob    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateJob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateJob]
GO

/****** Object:  Stored Procedure dbo.VerifyPassword    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VerifyPassword]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[VerifyPassword]
GO

/****** Object:  Stored Procedure dbo.addApplication    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[addApplication]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[addApplication]
GO

/****** Object:  Stored Procedure dbo.addEnquiry    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[addEnquiry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[addEnquiry]
GO

/****** Object:  Stored Procedure dbo.addJob    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[addJob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[addJob]
GO

/****** Object:  Stored Procedure dbo.addUser    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[addUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[addUser]
GO

/****** Object:  Stored Procedure dbo.getApplication    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getApplication]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getApplication]
GO

/****** Object:  Stored Procedure dbo.getApplications    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getApplications]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getApplications]
GO

/****** Object:  Stored Procedure dbo.getEnquiries    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getEnquiries]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getEnquiries]
GO

/****** Object:  Stored Procedure dbo.getEnquiry    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getEnquiry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getEnquiry]
GO

/****** Object:  Stored Procedure dbo.getJob    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getJob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getJob]
GO

/****** Object:  Stored Procedure dbo.getJobCount    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getJobCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getJobCount]
GO

/****** Object:  Stored Procedure dbo.getJobs    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getJobs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getJobs]
GO

/****** Object:  Stored Procedure dbo.getNewJobs    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getNewJobs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getNewJobs]
GO

/****** Object:  Stored Procedure dbo.getNewResumes    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getNewResumes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getNewResumes]
GO

/****** Object:  Stored Procedure dbo.getResumeCount    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getResumeCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getResumeCount]
GO

/****** Object:  Stored Procedure dbo.getUserPage    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getUserPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getUserPage]
GO

/****** Object:  Stored Procedure dbo.updateUserInfo    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[updateUserInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[updateUserInfo]
GO

/****** Object:  Table [dbo].[Applications]    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Applications]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Applications]
GO

/****** Object:  Table [dbo].[Enquiries]    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Enquiries]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Enquiries]
GO

/****** Object:  Table [dbo].[Jobs]    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Jobs]
GO

/****** Object:  Table [dbo].[UserList]    Script Date: 8/29/2001 4:52:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserList]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UserList]
GO


Print 'Okay, objects dropped'


Print 'Creating AspNETJobsUser Login'

/****** Object:  Login AspNETJobsUser    Script Date: 8/29/2001 4:52:14 AM ******/
if not exists (select * from master.dbo.syslogins where loginname = N'AspNETJobsUser')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'AspNetJobs', @loginlang = N'us_english'
	if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
		select @logindb = N'master'
	if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
		select @loginlang = @@language
	exec sp_addlogin N'AspNETJobsUser', 'secret', @logindb, @loginlang
END
GO

/****** Object:  User dbo    Script Date: 8/29/2001 4:52:14 AM ******/
/****** Object:  User AspNETJobsUser    Script Date: 8/29/2001 4:52:14 AM ******/
if not exists (select * from dbo.sysusers where name = N'AspNETJobsUser' and uid < 16382)
	EXEC sp_grantdbaccess N'AspNETJobsUser', N'AspNETJobsUser'
GO

/****** Object:  User AspNETJobsUser    Script Date: 8/29/2001 4:52:14 AM ******/
exec sp_addrolemember N'db_owner', N'AspNETJobsUser'
GO


Print 'Okay, login added'

Print 'Creating tables...'

/****** Object:  Table [dbo].[Applications]    Script Date: 8/29/2001 4:52:23 AM ******/
CREATE TABLE [dbo].[Applications] (
	[app_id] [int] IDENTITY (1, 1) NOT NULL ,
	[app_jobID] [int] NULL ,
	[app_from] [varchar] (20) NULL ,
	[app_message] [text] NULL ,
	[app_entrydate] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Enquiries]    Script Date: 8/29/2001 4:52:26 AM ******/
CREATE TABLE [dbo].[Enquiries] (
	[e_id] [int] IDENTITY (1, 1) NOT NULL ,
	[e_to] [varchar] (20) NULL ,
	[e_from] [varchar] (20) NULL ,
	[e_message] [text] NULL ,
	[e_entrydate] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Jobs]    Script Date: 8/29/2001 4:52:26 AM ******/
CREATE TABLE [dbo].[Jobs] (
	[job_id] [int] IDENTITY (1, 1) NOT NULL ,
	[job_username] [varchar] (20) NULL ,
	[job_briefdesc] [varchar] (50) NULL ,
	[job_fulldesc] [text] NULL ,
	[job_entrydate] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object:  Table [dbo].[UserList]    Script Date: 8/29/2001 4:52:26 AM ******/
CREATE TABLE [dbo].[UserList] (
	[ul_userID] [int] IDENTITY (1, 1) NOT NULL ,
	[ul_username] [varchar] (20) NOT NULL ,
	[ul_password] [varchar] (20) NOT NULL ,
	[ul_firstname] [varchar] (30) NOT NULL ,
	[ul_lastname] [varchar] (30) NOT NULL ,
	[ul_email] [varchar] (255) NOT NULL ,
	[ul_briefdesc] [varchar] (50) NULL ,
	[ul_fulldesc] [text] NULL ,
	[ul_isresume] [bit] NOT NULL ,
	[ul_entrydate] [datetime] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Applications] WITH NOCHECK ADD 
	CONSTRAINT [DF__Applicati__app_e__182C9B23] DEFAULT (getdate()) FOR [app_entrydate]
GO

ALTER TABLE [dbo].[Enquiries] WITH NOCHECK ADD 
	CONSTRAINT [DF__Enquiries__e_ent__0EA330E9] DEFAULT (getdate()) FOR [e_entrydate]
GO

ALTER TABLE [dbo].[Jobs] WITH NOCHECK ADD 
	CONSTRAINT [DF__Jobs__job_entryd__1367E606] DEFAULT (getdate()) FOR [job_entrydate]
GO

ALTER TABLE [dbo].[UserList] WITH NOCHECK ADD 
	CONSTRAINT [DF_UserList_ul_isResume] DEFAULT (0) FOR [ul_isresume],
	CONSTRAINT [DF_UserList_ul_entrydate] DEFAULT (getdate()) FOR [ul_entrydate]
GO

Print 'Okay, tables added'

Print 'Creating stored procedures'

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.UpdateJob    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE UpdateJob
(
  @jobID INT,
  @briefdesc Varchar( 50 ),
  @fulldesc Text
)
As
UPDATE Jobs SET
  job_briefdesc= @briefdesc,
  job_fulldesc = @fulldesc
  WHERE job_id = @jobID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.VerifyPassword    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE VerifyPassword
(
  @username Varchar( 20 ),
  @password Varchar( 20 )
)
AS
DECLARE @foundUser Varchar( 20 )
SELECT @foundUser = ul_username
  FROM UserList
  WHERE ul_username = @username
  AND ul_password = @password
IF @foundUser IS NOT NULL
  Return 0
ELSE
  IF Exists( SELECT ul_username
    FROM UserList WHERE ul_username = @username )
    Return 2
  ELSE
    RETURN 1

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.addApplication    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE Procedure addApplication
(
  @from Varchar( 20 ),
  @jobID INT,
  @message Text
)
AS
Insert Applications (
  app_from,
  app_jobID,
  app_message
  ) Values (
  @from,
  @jobID,
  @message
)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.addEnquiry    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE addEnquiry
(
  @to Varchar( 20 ),
  @from Varchar( 20 ),
  @message Text
)
AS
INSERT Enquiries (
  e_to,
  e_from,
  e_message
  ) VALUES (
  @to,
  @from,
  @message
  )

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.addJob    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE Procedure addJob
(
  @username Varchar( 20 ),
  @briefdesc Varchar( 50 ),
  @fulldesc Text
)
AS
Insert Jobs (
  job_username,
  job_briefdesc,
  job_fulldesc
  ) Values (
  @username,
  @briefdesc,
  @fulldesc
)
UPDATE Userlist
  SET ul_isresume = 0 
  WHERE ul_username = @username

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.addUser    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE procedure addUser
(
  @username Varchar( 20 ),
  @password varchar( 20 ),
  @firstname varchar( 30 ),
  @lastname varchar( 30 ),
  @email varchar( 255 ),
  @briefdesc varchar( 50 ),
  @fulldesc Text,
  @isresume Bit
)
As
If Exists( SELECT ul_username FROM UserList
           WHERE ul_username = @username )