特此说明:俺是菜鸟,虽然俺一直梦想成为老鸟,但是自己学的越多,发现自己越菜。没办法,能力有限啊。这里的这个文章其实也没有啥水平,仅仅是记录自己成长的过程,也希望能够顺便拿出来给其他和我一样的菜鸟参考,大家可以一起讨论,欢迎欢迎。所以如果您是老鸟,建议您一定要估计一下自己得时间价值,因为不小心看了我这篇文章的话,不仅仅让你反胃,还可能耽误您宝贵时间,进而谋杀您的生命。哎呀,我可担当不起。但是如果您已经看了,而且反胃厉害,吐得可怜,就不要再在我这里留下您得笔迹了。好建议我欢迎,批评我得我也接受。有些个别高手那些愤世嫉俗,不屑一顾的话就别往我这里写了,现在冬天,您外面凉快去,谢谢合作,^_^
考虑的系统整体架构的合理性和可扩展性,本系统准备采用3层架构开发,分别为数据层、商务层和表现层。
本系统前端web应用采用C#和ASP.NET开发,后端数据库为MS SQL SERVER,
视频服务器采用Real Helix Server。
系统根目录为coofucoo,其下本项目的目录为vod,vod下面的目录情况如下表描述:
目录名 |
用途 |
Business |
存放商务层类的目录 |
Config |
存放配置文件的目录 |
Configuration |
存放配置类的目录,此类属于商务层 |
core |
存放核心类的目录,此类主要用于继承 |
data |
存放数据层类的目录 |
Images |
存放图片的目录 |
Styles |
存放CSS文件的目录 |
数据库名称为“vod”,共有两个表,分别为:
movies表
列名 |
说明 |
类型 |
大小 |
是否空? |
movie_id |
编号(主键) |
int |
4 |
No |
category_id |
影片类别编号 |
int |
4 |
No |
levell |
影片评级 |
nvarchar |
50 |
Yes |
title |
影片名 |
nvarchar |
50 |
No |
intro |
影片介绍 |
text |
16 |
Yes |
uptime |
影片发布时间 |
datetime |
8 |
No |
showtime |
影片上映时间 |
datetime |
8 |
Yes |
viewcount |
影片点击率 |
bigint |
8 |
No(默认为0) |
image |
相关图片地址 |
nvarchar |
100 |
No |
address |
影片观看地址 |
nvarchar |
100 |
No |
categories表
列名 |
说明 |
类型 |
大小 |
是否空? |
category_id |
分类编号(主键) |
int |
4 |
No |
name |
分类名称 |
nvarchar |
50 |
No |
category_intro |
分类描述 |
nvarchar |
100 |
Yes |
表movies和categories通过字段category_id进行联系。
本系统所有数据操作都有存储过程执行,以下是存储过程设计:
movies:
存储过程名 |
参数 |
描述 |
GetMovies |
@category_id int |
返回指定类型的所有电影列表,包括电影详细内容 |
GetMovieDetails |
@movie_id int |
返回指定新闻条目的完整内容 |
GetMoviesByCount |
|
返回根据点击率排序的电影题目 |
GetAllMovies |
|
根据上传时间返回所有电影信息 |
GetHeadines |
@category_id int |
返回指定类型的所有电影列表,内容仅包括movie_id、title、uptime、viewcount、image |
InsertMovie |
@category_id int
@levell nvarchar(50)
@title nvarchar(50)
@intro text
@uptime datetime
@showtime datetime
@image nvarchar(100)
@address nvarchar(100)
OUTPUT |
插入电影行。如果已经存在一个带有指定category_id、title、showtime的行,那么数据参数@ movie_id就返回-1,并且不执行任何插入操作。否则执行操作,并返回添加的movie_id |
DeleteMovie |
@movie_id int |
删除指定ID的电影条目 |
UpdateMovie |
@movie_id int
@category_id int
@levell nvarchar(50)
@title nvarchar(50)
@intro text
@showtime datetime
@image nvarchar(100)
@address nvarchar(100) |
更新指定电影条目的所有可更改内容 |
AddViewcount |
@movie_id int |
更新指定的电影条目,使其viewcount字段加一 |
categories:
存储过程名 |
参数 |
描述 |
GetCategories |
|
返回Categories表中所有条目内容 |
GetCategoryDetails |
@category_id int |
返回指定ID的条目完整行 |
GetCategoryName |
|
返回所有分类的名称和编号 |
InsertCategory |
@name nvarchar(50)
@category_intro nvarchar(100)
OUTPUT |
插入一个新的分类,如果指定的分类名字已经存在,则返回-1,否则返回新的category_id |
UpdateCategory |
@category_id int
@name nvarchar(50)
@category_intro nvarchar(100) |
更新由category_idategory_id指定的行的所有可更改内容 |
DeleteCategory |
@category_id int |
删除由ategory_id指定的行 | 以下详细提供所有存储过程源代码:
GetMovies: CREATE PROCEDURE GetMovies @category_id int AS
SELECT movie_id, category_id, levell, title, intro, uptime, showtime, viewcount, image, address FROM movies WHERE category_id = @category_id GO
GetMovieDetails: CREATE PROCEDURE GetMovieDetails @movie_id int AS
SELECT movie_id,category_id, levell, title, intro, uptime, showtime, viewcount, image, address FROM movies WHERE movie_id = @movie_id GO
GetMoviesByCount: CREATE PROCEDURE GetMoviesByCount AS
SELECT movie_id, title FROM movies order by viewcount Desc GO
GetAllMovies: CREATE PROCEDURE GetAllMovies AS
SELECT movie_id, levell, title, intro, uptime, showtime, viewcount FROM movies order by uptime Desc GO
GetHeadines: CREATE PROCEDURE GetHeadines @category_id int AS
SELECT movie_id, title, uptime,viewcount,image FROM movies WHERE category_id = @category_id ORDER BY uptime DESC GO
InsertMovie: CREATE PROCEDURE InsertMovie @category_id int, @level nvarchar(50), @title nvarchar(50), @intro text, @uptime datetime, @showtime datetime, @image nvarchar(100), @address nvarchar(100), @movie_id int OUTPUT AS
DECLARE @CurrID int
-- see if the news already exists SELECT @CurrID = movie_id FROM movies WHERE category_id = @category_id AND title = @title AND showtime = @showtime
-- if not, add it IF @CurrID IS NULL BEGIN
INSERT INTO movies (category_id, levell, title, intro, uptime, showtime, image, address) VALUES (@category_id, @level, @title, @intro, @uptime, @showtime, @image, @address)
SET @movie_id = @@IDENTITY
IF @@ERROR > 0 BEGIN RAISERROR ('Insert of movies failed', 16, 1) RETURN 99 END END ELSE BEGIN SET @movie_id = -1 END GO
DeleteMovie: CREATE PROCEDURE DeleteMovie @movie_id int AS
DELETE FROM movies WHERE movie_id = @movie_id GO
UpdateMovie: CREATE PROCEDURE UpdateMovie @movie_id int, @category_id int, @levell nvarchar(50), @title nvarchar(50), @intro text, @showtime datetime, @image nvarchar(100), @address nvarchar(100) AS
BEGIN TRANSACTION UpdateMovie
UPDATE movies SET category_id=@category_id, levell=@levell, title= @title, intro = @intro, showtime = @showtime, image = @image, address = @address WHERE movie_id = @movie_id
IF @@ERROR > 0 BEGIN RAISERROR ('Update of movies failed', 16, 1) ROLLBACK TRANSACTION UpdateMovie RETURN 99 END
COMMIT TRANSACTION UpdateMovie GO
AddViewcount: CREATE PROCEDURE AddViewcount @movie_id int AS
BEGIN TRANSACTION AddViewcount
UPDATE movies SET viewcount=viewcount+1 WHERE movie_id = @movie_id
IF @@ERROR > 0 BEGIN RAISERROR ('AddViewcount of Movies failed', 16, 1) ROLLBACK TRANSACTION AddViewcount RETURN 99 END
COMMIT TRANSACTION AddViewcount GO
GetCategories: CREATE PROCEDURE GetCategories AS
SELECT * FROM categories GO
GetCategoryDetails: CREATE PROCEDURE GetCategoryDetails @category_id int AS
SELECT * FROM categories WHERE category_id = @category_id GO
GetCategoryName: CREATE PROCEDURE GetCategoryName AS
SELECT name FROM categories GO
InsertCategory: CREATE PROCEDURE InsertCategory @name nvarchar(50), @category_intro nvarchar(100), @category_id int OUTPUT AS
DECLARE @CurrID int
-- see if the category already exists SELECT @CurrID = category_id FROM categories WHERE name = @name
-- if not, add it IF @CurrID IS NULL BEGIN
INSERT INTO categories (name, category_intro) VALUES (@name, @category_intro)
SET @category_id = @@IDENTITY
IF @@ERROR > 0 BEGIN RAISERROR ('Insert of categories failed', 16, 1) RETURN 99 END END ELSE BEGIN SET @category_id = -1 END GO
UpdateCategory: CREATE PROCEDURE UpdateCategory @category_id int, @name nvarchar(50), @category_intro nvarchar(100) AS
BEGIN TRANSACTION UpdateCategory
UPDATE categories SET name = @name, category_intro = @category_intro WHERE category_id = @category_id
IF @@ERROR > 0 BEGIN RAISERROR ('Update of categories failed', 16, 1) ROLLBACK TRANSACTION UpdateCategory RETURN 99 END
COMMIT TRANSACTION UpdateCategory GO
DeleteCategory: CREATE PROCEDURE DeleteCategory @category_id int AS
DELETE FROM categories WHERE category_id = @category_id GO
目录设计和数据库设计到此结束,下面就开始程序代码的编写了, 呵呵,欲知后事,且听下回分解。^_^
点击这里游览上一片文章 
|