通过一个简单的文章系统学习MVC3+Linq搭建网站（1）
通过一个简单的CMS文章系统，来学习MVC3+LINQ网站搭建。
第一篇： 数据库建模
文章分类信息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
作者： Leo_wl
出处： http://HdhCmsTestcnblogs测试数据/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息查看更多关于通过一个简单的文章系统学习MVC3+Linq搭建网站（1）的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did49619