[源代码] 《ASP.NET夜话》 随书源代码

USE [ArticleCollectorDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Article] ( [ArticleId] [bigint] IDENTITY(1, 1) NOT NULL , [Title] [nvarchar](80) NOT NULL , [Content] [ntext] NOT NULL , [UserId] [int] NOT NULL , [AddDate] [datetime] NULL , [Hits] [int] NULL , [CatalogId] [int] NOT NULL , [ArticleStatus] [tinyint] NULL , [ArticleOrder] [tinyint] NULL , CONSTRAINT [PK_ARTICLE] PRIMARY KEY CLUSTERED ( [ArticleId] ASC ) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ArticleCatalog] ( [CatalogId] [int] IDENTITY(1, 1) NOT NULL , [CatalogName] [nvarchar](10) NOT NULL , [CatalogOrder] [tinyint] NULL , [CatalogStatus] [tinyint] NULL , CONSTRAINT [PK_ARTICLECATALOG] PRIMARY KEY CLUSTERED ( [CatalogId] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Users] ( [UserId] [int] IDENTITY(1, 1) NOT NULL , [UserName] [varchar](20) NOT NULL , [Passwords] [varchar](40) NOT NULL , [RealName] [nvarchar](8) NOT NULL , [PasswordAnswer] [nvarchar](20) NOT NULL , [PasswordQuestion] [nvarchar](20) NOT NULL , [Birthday] [datetime] NULL , [UserStatus] [tinyint] NULL , [Email] [varchar](200) NOT NULL , [RegDate] [datetime] NULL , [LoginCount] [int] NULL , [LastLoginDate] [datetime] NULL , [IsAdmin] [bit] NULL , CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED ( [UserId] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[VisitHistory] ( [RecordId] [int] IDENTITY(1, 1) NOT NULL , [VisitDate] [datetime] NULL , [VisitIP] [char](20) NOT NULL , [VisitUrl] [varchar](300) NOT NULL , [UrlReferrer] [varchar](300) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[V_ArticleDetails] AS SELECT a.ArticleId , a.Title , a.[Content] , a.UserId , u.UserName , a.AddDate , a.Hits , a.CatalogId , ac.CatalogName , a.ArticleStatus , a.ArticleOrder , ac.CatalogStatus FROM dbo.Article AS a INNER JOIN dbo.Users AS u ON a.UserId = u.UserId INNER JOIN dbo.ArticleCatalog AS ac ON a.CatalogId = ac.CatalogId GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[V_ArticleSummary] AS SELECT a.ArticleId , a.Title , a.UserId , u.UserName , a.AddDate , a.Hits , a.CatalogId , ac.CatalogName , a.ArticleStatus , a.ArticleOrder , ac.CatalogStatus FROM dbo.Article AS a INNER JOIN dbo.Users AS u ON a.UserId = u.UserId INNER JOIN dbo.ArticleCatalog AS ac ON a.CatalogId = ac.CatalogId GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[V_CatalogSummary] AS SELECT TOP ( 100 ) PERCENT ac.CatalogId , ac.CatalogName , ac.CatalogOrder , ac.CatalogStatus , a.ArticleCount FROM dbo.ArticleCatalog AS ac LEFT OUTER JOIN ( SELECT CatalogId , COUNT(1) AS ArticleCount FROM dbo.Article GROUP BY CatalogId �