articletask.sql.cs

来自「该项目中对 SQLHelper 类进行了简单封装」· CS 代码 · 共 78 行

/* 
 * ArticleTask.SQL.cs @Microsoft Visual Studio 2008 <.NET Framework 3.5>
 * AfritXia
 * 2008-01-27
 * 
 * Copyright(c) http://www.AfritXia.NET/
 * 
 */

using System;

using NET.AfritXia.MyHome.DBTask.Definition;

namespace NET.AfritXia.MyHome.DBTask.Access2000
{
	partial class ArticleTask
	{
		// 添加文章
		private const string SQL_Append = @"
			insert into [Article] ( [Title], [Summary], [PostUser], [TextContent] ) 
			values ( @Title, @Summary, @PostUser, @TextContent )";

		// 删除文章
		private const string SQL_Delete = @"
			delete from [Article] where [ArticleUID] = @ArticleID";

		// 更新文章
		private const string SQL_Update = @"
			update [Article] 
			   set [Title] = @Title, 
				   [PostUser] = @PostUser, 
				   [TextContent] = @TextContent, 
				   [Summary] = @Summary, 
				   [UpdateTime] = now() 
			 WHERE [ArticleUID] = @ArticleUID";

		// 浏览文章
		private const string SQL_ViewArticle = @"
			select * from [Article] where ArticleUID = @ArticleUID";

		// 增加点击次数
		private const string SQL_IncreaseClicked = @"
			update [Article] set [Clicked] = [Clicked] + 1 where [ArticleUID] = @ArticleUID";

		/// <summary>
		/// 获取留言列表
		/// </summary>
		/// <param name="orderGistValue"></param>
		/// <param name="startRecord"></param>
		/// <param name="maxRecords"></param>
		/// <returns></returns>
		private static string SQL_GetArticleList(int orderGistValue, int startRecord, int maxRecords)
		{
			string sqlCmdText = @"
				select * from Article as A where A.ArticleUID in (
				select top {1} [ArticleUID] from ( 
				select top {0} [ArticleUID], {2} from [Article] order by {2} desc ) as X order by X.{2} asc ) 
				order by A.{2} desc";

			switch (orderGistValue)
			{
				case OrderGist.ArticleUpdateTime:
					return String.Format(sqlCmdText, (startRecord + maxRecords), maxRecords, "UpdateTime");

				case OrderGist.ArticlePostTime:
					return String.Format(sqlCmdText, (startRecord + maxRecords), maxRecords, "PostTime");

				case OrderGist.ArticleClicked:
					return String.Format(sqlCmdText, (startRecord + maxRecords), maxRecords, "Clicked");

				case OrderGist.AritcleCommentCount:
					return String.Format(sqlCmdText, (startRecord + maxRecords), maxRecords, "__CommentCount");
			}

			throw new NotSupportedException("error orderGistValue");
		}
	}
}