学步园

通过一个简单的文章系统学习MVC3+Linq搭建网站：

（1）数据库建模 (2012-03-23 12:10)

（2）如何利用系统自带的用户系统 (2012-03-28 22:41)

（3) 项目文件结构介绍 (2012-04-04 15:41)

源码   数据库创建sql   本示例DEMO

 
第一篇： 数据库建模

文章分类信息Category存储为json文本。一个article文章表，一个users用户表。一个文章信息视图varticle

 

（1）文章分类信息：
很多网站分类信息的改动其实很少，所以这里索性存储为文本的格式。分类信息通常不会有太多数据，修改也很直观。

数据格式：

[
{"Cateid":1,"CateName":"Item01","IsList":0,"OrderId":1,"ReplyPermit":0,"ParentId":0,"Status":1},
{"Cateid":2,"CateName":"Item02","IsList":1,"OrderId":2,"ReplyPermit":1,"ParentId":0,"Status":1},
{"Cateid":3,"CateName":"Item03","IsList":1,"OrderId":3,"ReplyPermit":0,"ParentId":0,"Status":1},
{"Cateid":4,"CateName":"Item03_1","IsList":1,"OrderId":1,"ReplyPermit":0,"ParentId":3,"Status":1},
{"Cateid":5,"CateName":"Item03_2","IsList":1,"OrderId":2,"ReplyPermit":0,"ParentId":3,"Status":1},
{"Cateid":6,"CateName":"Item06","IsList":0,"OrderId":4,"ReplyPermit":0,"ParentId":0,"Status":1}
]

字段分别表示
Cateid：分类Id
CateName：分类名称
IsList：是否显示为列表，1为列表，0表示单页，比如新闻中心为1，联系我们为0
OrderId：排序id
ReplyPermit：是否可评论
ParentId：父id
Status：状态Id

（2）创建数据库表/视图

-- 新建数据库
create database YqCms

use YqCms

--文章表
create table article
(
id int identity(1,1) primary key,
cateid int not null constraint df_article_cateid default(0),--类别id
catepath nvarchar(200) not null constraint df_article_catepath default('0'),--基于类别的纵深路径 ，eg.0,1,124
articleid int not null constraint df_article_articleid default(0),--文章id 值为某id
parentid int not null constraint df_article_parentid default(0),--父id  值为某id
layer int not null constraint df_article_layer default(0),--层，eg.文章值为0，文章回复1，回复再回复2
subcount int not null constraint df_article_subcount default(0),--子数据统计数
catename nvarchar(200) not null constraint df_article_catename default('0'),-- 这里方便读取数据记入类别名称
userid int not null constraint df_article_userid default(0),--用户ID
username nchar(20) not null constraint df_article_username default(''),--用户名
title nvarchar(200) not null constraint df_article_title default(''),--标题
summary nvarchar(500) not null constraint df_article_summary default(''),--摘要
content ntext not null constraint df_article_content default(''),--内容
viewcount int not null constraint df_article_viewcount default(0),--浏览统计
orderid int not null constraint df_article_orderid default(1),--排序
replypermit tinyint not null constraint df_article_replypermit default(1),--是否可回复,1-可,0-不可
status tinyint not null constraint df_article_status default(0),--状态,应付可能的删除,屏蔽等操作
ip nvarchar(20) not null constraint df_article_ip default(''),--ip
createdate datetime not null constraint df_article_createdate default(getdate())--创建时间
)
--Seo表
create table seo
(
articleid int not null constraint df_seo_articleid default(0),
seotitle nvarchar(500) not null constraint df_seo_title default(''),
seodescription nvarchar(1000) not null constraint df_seo_description default(''),
seokeywords nvarchar(500) not null constraint df_seo_keywords default(''),
seometas  nvarchar(1000) not null constraint df_seo_metas default('')
)
--用户表
create table users
(
userid int identity(1,1) primary key,
username nchar(20) not null constraint df_users_username default(''),
password char(32) not null constraint df_users_password default(''),
typeid tinyint not null constraint df_users_typeid default(0),--用户类别
status tinyint not null constraint df_users_status default(0),--用户状态
createdate datetime not null constraint df_users_createdate default(getdate())
)

--创建文章视图
create view varticle
as
select article.*,
isnull(seotitle,'') as seotitle,
isnull(seodescription,'') as seodescription,
isnull(seokeywords,'') as seokeywords,
isnull(seometas,'') as seometas from article
left join seo
on article.id=seo.articleid
where article.layer=0


select * from article
select * from seo
select * from users
select * from varticle

（3）数据操作sql

--插入测试数据
declare @i int
set @i=1
while(@i<100)
begin
insert into article(cateid,catename,title,summary,content)
values(3,'分类名称','title '+cast(@i as varchar),'summary '+cast(@i as varchar),'content '+cast(@i as varchar))
set @i=@i+1
end

update article set articleid=id


--文章录入存储过程
create procedure sp_createarticle
@cateid int,
@catepath nvarchar(100),
@articleid int,
@parentid int,
@layer int,
@catename nvarchar(100),
@userid int,
@username nchar(20),
@title nvarchar(200),
@summary nvarchar(500),
@content ntext,
@replypermit tinyint,
@status tinyint,
@ip nvarchar(20),
@seotitle nvarchar(500),
@seodescription nvarchar(1000),
@seokeywords nvarchar(500),
@seometas nvarchar(1000)

as

declare @aid int

insert into 
article(cateid ,catepath ,articleid ,parentid ,layer ,catename ,userid ,username ,title ,summary ,[content], replypermit, [status], ip ,createdate)
values(@cateid ,@catepath ,@articleid ,@parentid ,@layer ,@catename ,@userid ,@username ,@title ,@summary ,@content, @replypermit, @status ,@ip ,getdate() )

set @aid=SCOPE_IDENTITY()

if @@ERROR=0
begin
if @layer=0
begin
update article set articleid=@aid WHERE id=@aid
if(LTRIM(@seotitle+@seodescription+@seokeywords+@seometas)<>'')
begin
insert into seo(articleid,seotitle,seodescription,seokeywords,seometas) VALUES(@aid,@seotitle,@seodescription,@seokeywords,@seometas)
end
end
else
begin
update article set subcount=subcount+1 where id=@parentid
end
end

SELECT @aid as articleid


--测试插入
exec sp_createarticle 1,'0',0,0,0,'category01',0,'','test_title','test_summary','test_content',0,0,'192.168.1.1','test_seotitle','test_desc',
'test_keys','test_meta'

--文章修改存储过程
create procedure sp_updatearticle
@aid int,
@parentid int,
@title nvarchar(200),
@summary nvarchar(500),
@content ntext,
@replypermit tinyint,
@status tinyint,
@seotitle nvarchar(500),
@seodescription nvarchar(1000),
@seokeywords nvarchar(500),
@seometas nvarchar(1000)

as
begin
--修改文章基础信息
    update article set title=@title,summary=@summary,[content]=@content,replypermit=@replypermit,[status]=@status where id=@aid
--修改seo部分
    if @parentid=0
begin
delete from seo where articleid=@aid
insert into seo(articleid,seotitle,seodescription,seokeywords,seometas) VALUES(@aid,@seotitle,@seodescription,@seokeywords,@seometas)
end
select @@ERROR
end

--文章删除存储过程
create procedure sp_deletearticle
@aid int,
@parentid int

as
begin
if @parentid=0
begin
--删除文章基础信息以及其子记录
            delete from article where articleid=@aid
--删除seo部分
            delete from seo where articleid=@aid
end
else
begin
--删除文章基础信息
            delete from article where id=@aid
--删除可能有的子记录
            while((select count(1) from article where parentid<>0 and parentid not in (select id from article))>0)
begin
delete from article where parentid<>0 and not exists(select 1 from article b where b.id=article.parentid)
end 
end
select @@ERROR
end