ASP.NET网上商城(修订改版)

-- Drop Foreign Key Constraints IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Comment_MemberInfo') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE Comment DROP CONSTRAINT FK_Comment_MemberInfo ; IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Comment_Storage') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE Comment DROP CONSTRAINT FK_Comment_Storage ; IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Discount_Storage') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE Discount DROP CONSTRAINT FK_Discount_Storage ; IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_OrderAddress_MemberInfo') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE OrderAddress DROP CONSTRAINT FK_OrderAddress_MemberInfo ; IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_OrderMember_MemberInfo') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE OrderMember DROP CONSTRAINT FK_OrderMember_MemberInfo ; IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_SentInfo_Storage') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE SentInfo DROP CONSTRAINT FK_SentInfo_Storage ; IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_SentInfo_UserInfo') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE SentInfo DROP CONSTRAINT FK_SentInfo_UserInfo ; IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_ShopCart_MemberInfo') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE ShopCart DROP CONSTRAINT FK_ShopCart_MemberInfo ; IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id('FK_Storage_CatogoryTable') AND OBJECTPROPERTY(id, 'IsForeignKey') = 1) ALTER TABLE Storage DROP CONSTRAINT FK_Storage_CatogoryTable ; -- Drop Tables IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('CatogoryTable') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE CatogoryTable ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('Comment') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE Comment ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('Discount') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE Discount ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('MemberInfo') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE MemberInfo ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('OrderAddress') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE OrderAddress ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('OrderMember') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE OrderMember ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('SentInfo') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE SentInfo ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('ShopCart') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE ShopCart ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('Storage') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE Storage ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('SystemSettings') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE SystemSettings ; IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE id = object_id('UserInfo') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE UserInfo ; -- Create Tables CREATE TABLE CatogoryTable ( CtID int identity(1,1) NOT NULL, -- 类别ID CtName nvarchar(200) NOT NULL, -- 类别名称 CtParentID int DEFAULT 0 -- 父类别ID ) ; CREATE TABLE Comment ( CmID int identity(1,1) NOT NULL, -- 评论ID CmCommenderID int NOT NULL, -- 评论人员ID CmBookID int NOT NULL, -- 所评论的书籍的ID CmTitle nvarchar(200) NOT NULL, -- 评论标题 CmContent ntext, -- 评论内容 CmDate datetime NOT NULL -- 评论日期 ) ; CREATE TABLE Discount ( DID int identity(1,1) NOT NULL, -- ID SID int NOT NULL, -- 对应的货物ID SMemberLevel int NOT NULL, -- 会员级别 SDiscount float NOT NULL -- 会员折扣价 ) ; CREATE TABLE MemberInfo ( MbID int identity(1,1) NOT NULL, -- 会员ID MbName nvarchar(30) NOT NULL, -- 会员名称 MbPassword nvarchar(32) NOT NULL, -- 密码 MbTrueName nvarchar(50) NOT NULL, -- 真实姓名 MbEmail nvarchar(200), -- Email地址 MbBouns int DEFAULT 0 NOT NULL, -- 会员消费积分 MbDeleted bit DEFAULT 0, -- 删除标记位 MbRegisterDate datetime NOT NULL -- 会员注册时间 ) ; CREATE TABLE OrderAddress ( OaID int identity(1,1) NOT NULL, -- ID OaMemberID int NOT NULL, -- 会员ID OaContactInfo ntext -- 序列化的收货人信息 ) ; CREATE TABLE OrderMember ( OmID int identity(1,1) NOT NULL, -- 订单ID OmMemberID int NOT NULL, OmOrder ntext NOT NULL, -- 序列化的订单实体 OmDate datetime NOT NULL -- 下订单时间 ) ; CREATE TABLE SentInfo ( SiID int identity(1,1) NOT NULL, -- 发货信息ID SStorageID int NOT NULL, -- 货物ID SiCount int NOT NULL, SiSentUserID int NOT NULL, -- 确认为发货状态的用户ID SiDate datetime -- 发货时间 ) ; CREATE TABLE ShopCart ( ScID int identity(1,1) NOT NULL, -- 购物车ID ScMemberID int NOT NULL, -- 会员ID ScShopCart ntext -- 序列化的购物车对象 ) ; CREATE TABLE Storage ( SID int identity(1,1) NOT NULL, -- 货物ID SName nchar(500) NOT NULL, -- 货物名称 STypeID int NOT NULL, -- 所属类别ID SImage nvarchar(500), -- 所略图的URL SWriter nvarchar(500) NOT NULL, -- 作者 SPublisher ntext NOT NULL, -- 出版社 SPublishDate datetime NOT NULL, -- 出版日期 SPublishTimes int DEFAULT 1 NOT NULL, -- 版次 SISBN nvarchar(50) NOT NULL, -- ISBN SPageCount int NOT NULL, -- 页数 SPageSize nvarchar(50) NOT NULL, -- 书的开数 SWordCount int NOT NULL, -- 字数 SPrice float NOT NULL, -- 价格 SIntro ntext, -- 内容简介 SContents bigint, -- 目录 SStorageCount int DEFAULT 0 NOT NULL -- 货物库存 ) ; CREATE TABLE SystemSettings ( SsId int identity(1,1) NOT NULL, -- 设置ID SsKey nvarchar(50) NOT NULL, -- 设置键名 SsValue ntext NOT NULL -- 设置值 ) ; CREATE TABLE UserInfo ( UID int identity(1,1) NOT NULL, -- 用户ID UName nvarchar(50) NOT NULL, -- 用户名 UPassword nvarchar(32) NOT NULL, -- 密码 URoleID int NOT NULL, -- 用户所属角色 UiDeleted bit DEFAULT 0 NOT NULL -- 删除标记位 ) ; -- Create Primary Key Constraints ALTER TABLE CatogoryTable ADD CONSTRAINT PK_CatogoryTable PRIMARY KEY (CtID) ; ALTER TABLE Comment ADD CONSTRAINT PK_Comment PRIMARY KEY (CmID) ; ALTER TABLE Discount ADD CONSTRAINT PK_Discount PRIMARY KEY (DID) ; ALTER TABLE MemberInfo ADD CONSTRAINT PK_MemberInfo PRIMARY KEY (MbID) ; ALTER TABLE OrderAddress ADD CONSTRAINT PK_OrderAddress PRIMARY KEY (OaID) ; ALTER TABLE OrderMember ADD CONSTRAINT PK_OrderMember PRIMARY KEY (OmID) ; ALTER TABLE SentInfo ADD CONSTRAINT PK_SentInfo PRIMARY KEY (SiID) ; ALTER TABLE ShopCart ADD CONSTRAINT PK_ShopCart PRIMARY KEY (ScID) ; ALTER TABLE Storage ADD CONSTRAINT PK_Storage PRIMARY KEY (SID) ; ALTER TABLE SystemSettings ADD CONSTRAINT PK_SystemSettings PRIMARY KEY (SsId) ; ALTER TABLE UserInfo ADD CONSTRAINT PK_UserInfo PRIMARY KEY (UID) ; -- Create Indexes ALTER TABLE CatogoryTable ADD CONSTRAINT UQ_CatogoryTable_CtID UNIQUE (CtID) ; ALTER TABLE Discount ADD CONSTRAINT UQ_Discount_DID UNIQUE (DID) ; ALTER TABLE OrderAddress ADD CONSTRAINT UQ_OrderAddress_OaID UNIQUE (OaID) ; ALTER TABLE OrderMember ADD CONSTRAINT UQ_OrderMember_OmID UNIQUE (OmID) ; ALTER TABLE SentInfo ADD CONSTRAINT UQ_SentInfo_SiID UNIQUE (SiID) ; ALTER TABLE ShopCart ADD CONSTR