installaspnetjobsdatabase.sql

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

  RETURN -1
ELSE
  INSERT UserList (
    ul_username,
    ul_password,
    ul_firstname,
    ul_lastname,
    ul_email,
    ul_briefdesc,
    ul_fulldesc,
    ul_isresume 
  ) VALUES (
    @username,
    @password,
    @firstname,
    @lastname,
    @email,
    @briefdesc,
    @fulldesc,
    @isresume
  )

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.getApplication    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getApplication
(
  @ID INT
)
AS
SELECT app_from, app_message, app_entrydate
  FROM Applications
  WHERE app_id = @ID

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.getApplications    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getApplications
(
  @jobID INT
)
AS
SELECT app_id, app_from, app_entrydate
  FROM Applications
  WHERE app_jobID = @jobID
  ORDER BY app_id DESC

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.getEnquiries    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getEnquiries
(
  @username Varchar( 20 )
)
AS
SELECT e_id, e_entrydate, e_from
  FROM Enquiries
  WHERE e_to = @username 
  ORDER BY e_id DESC

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.getEnquiry    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getEnquiry
(
  @id INT
)
As
SELECT e_from, e_entrydate, e_message
  FROM Enquiries
  WHERE e_id = @id

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.getJob    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE Procedure getJob
(
  @jobID INT
)
AS
SELECT job_briefdesc, job_username, job_entrydate, job_fulldesc
  FROM Jobs
  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.getJobCount    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getJobCount
As
SELECT COUNT(*) FROM Jobs

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.getJobs    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE Procedure getJobs 
(
  @username Varchar( 20 )
)
AS
SELECT job_id,job_entrydate, job_briefdesc
  FROM Jobs
  WHERE job_username = @username
  ORDER BY job_id DESC

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.getNewJobs    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getNewJobs
As
SELECT TOP 50 
  job_id,
  job_briefdesc,
  job_entrydate
FROM Jobs
ORDER BY job_id DESC

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.getNewResumes    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getNewResumes 
AS
SELECT TOP 50 ul_entrydate, ul_username, ul_briefdesc
FROM UserList
WHERE ul_isresume = 1
ORDER BY ul_userid DESC

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.getResumeCount    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getResumeCount
As
SELECT COUNT(*) FROM UserList
WHERE ul_isresume = 1

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.getUserPage    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE getUserPage
(
  @username Varchar( 20 )
)
AS
SELECT ul_briefdesc, ul_fulldesc, ul_isresume
FROM UserList 
WHERE ul_username = @username

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.updateUserInfo    Script Date: 8/29/2001 4:52:27 AM ******/
CREATE PROCEDURE updateUserInfo
(
  @username Varchar( 20 ),
  @briefdesc Varchar( 50 ),
  @fulldesc Text
)
AS
Update UserLIst
  SET 
    ul_briefdesc = @briefdesc,
    ul_fulldesc = @fulldesc
  WHERE ul_username = @username

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Print 'Okay, stored procedures added'

Print 'Adding some fake data'

Insert UserList ( 
  ul_username, 
  ul_password, 
  ul_firstname, 
  ul_lastname, 
  ul_email, 
  ul_briefdesc, 
  ul_fulldesc, 
  ul_isresume, 
  ul_entrydate 
  ) Values (
  'edwinorr',
  'secret',
  'Edwin',
  'Orr',
  '[e-mail]',
  'Looking for an ASP.NET Consulting job!',
  '<h1>Edwin Orr''s Resume</h1> I have more than 30 years experience in the computer industry...',
  1,
  '1/25/2001'
  )
GO

Insert UserList ( 
  ul_username, 
  ul_password, 
  ul_firstname, 
  ul_lastname, 
  ul_email, 
  ul_briefdesc, 
  ul_fulldesc, 
  ul_isresume, 
  ul_entrydate 
  ) Values (
  'suejones',
  'secret',
  'Sue',
  'Jones',
  '[e-mail]',
  'Graphic Designer in need of work',
  'Experience with several design programs. Will consider moving if I receive the right offer.',
  1,
  '3/12/1998'
  )

GO

Insert UserList ( 
  ul_username, 
  ul_password, 
  ul_firstname, 
  ul_lastname, 
  ul_email, 
  ul_briefdesc, 
  ul_fulldesc, 
  ul_isresume, 
  ul_entrydate 
  ) Values (
  'Frank',
  'secret',
  'John',
  'Frank',
  '[e-mail]',
  'Expanding Company in Seattle',
  '<h1>Frank Enterprises</h1>We are a third generation Internet company that plans to expand rapidly in the Seattle area',
  0,
  '4/2/1999'
  )

GO

Insert Jobs (
  job_username,
  job_briefdesc,
  job_fulldesc,
  job_entrydate
  ) Values (
  'frank',
  'Looking for Web Services expert',
  '<blink><h1>Great Job</h1></blink>A great job in the San Francisco area. Looking for someone with experience with <ul><li>ASP.NET Web Services<li>XML<li>XSLT<li>SOAP</ul>',
  '4/2/1999'
  )

GO


Insert Jobs (
  job_username,
  job_briefdesc,
  job_fulldesc,
  job_entrydate
  ) Values (
  'frank',
  'Call for XML Consultants!',
  'We are looking for an XML consultant to finish a project in our Seattle office. A fantastic working environment.',
  '4/2/1999'
  )

GO


Print 'Okay, finished adding fake data'


Print 'All done, if nothing appears in red then everything worked!'