SQL存储过程的关键代码如下:
CREATE PROCEDURE [dbo].[usp_GetArticleByCategoryIDAndModifiedTime] -- Add the parameters for the stored procedure here @categoryID int=-1, @searchModifiedTime nvarchar(50), @searchTimeRange nvarchar(20) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here if (@categoryID = -1) begin if (@searchTimeRange='dy') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where ModifiedTime between @searchModifiedTime and DATEADD(DAY,1,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='mm') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where ModifiedTime between @searchModifiedTime and DATEADD(MM,1,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='yy') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where ModifiedTime between @searchModifiedTime and DATEADD(YY,1,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='3day') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where ModifiedTime between @searchModifiedTime and DATEADD(DAY,3,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='wk') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where ModifiedTime between @searchModifiedTime and DATEADD(WK,1,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='3mm') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where ModifiedTime between @searchModifiedTime and DATEADD(MM,3,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='before') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where ModifiedTime < @searchModifiedTime order by CreatedTime desc else SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where ModifiedTime > @searchModifiedTime order by CreatedTime desc end else begin if (@searchTimeRange='dy') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID=@categoryID and ModifiedTime between @searchModifiedTime and DATEADD(DAY,1,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='mm') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID=@categoryID and ModifiedTime between @searchModifiedTime and DATEADD(MM,1,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='yy') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID=@categoryID and ModifiedTime between @searchModifiedTime and DATEADD(YY,1,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='3day') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID=@categoryID and ModifiedTime between @searchModifiedTime and DATEADD(DAY,3,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='wk') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID=@categoryID and ModifiedTime between @searchModifiedTime and DATEADD(WK,1,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='3mm') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID=@categoryID and ModifiedTime between @searchModifiedTime and DATEADD(MM,3,@searchModifiedTime) order by CreatedTime desc else if (@searchTimeRange='before') SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID=@categoryID and ModifiedTime < @searchModifiedTime order by CreatedTime desc else SELECT ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks from Article where CategoryID=@categoryID and ModifiedTime > @searchModifiedTime order by CreatedTime desc end END
如果使用分页的存储过程则可以简化代码的编写处理,分页的存储过程见前篇代码所示:
下面给出了C#的逻辑处理代码如下:
/// <summary> /// 得到查询的文章的数量 /// </summary> /// <param name="articleTimeType">文章时间类型,分为“发布时间”与“更新时间”</param> /// <param name="startTime">搜索的开始时间</param> /// <param name="timeRange">搜索的时间范围</param> /// <param name="categoryID">文章类别编号</param> /// <returns>返回查询到的文章的数量</returns> public static long GetArticleRecordCountByArticleTimeTypeAndCategoryID(string articleTimeType, string startTime, string timeRange, int categoryID) { string strWhere = string.Empty; if (articleTimeType.Equals("createTime")) { strWhere = GetSearchWhereByCreateTimeAndCategoryID("CreatedTime", startTime, timeRange, categoryID); } else { strWhere = GetSearchWhereByCreateTimeAndCategoryID("ModifiedTime", startTime, timeRange, categoryID); } return ArticleService.GetArticleRecordCount(strWhere); } /// <summary> /// 根据文章类型和文章是否包含图片进行查询的文章信息 /// </summary> /// <param name="articleTimeType">文章时间类型,分为“发布时间”与“更新时间”</param> /// <param name="startTime">搜索的开始时间</param> /// <param name="timeRange">搜索的时间范围</param> /// <param name="categoryID">文章类别编号</param> /// <param name="pageSize">分页大小</param> /// <param name="pageIndex">当前页的索引</param> /// <returns>返回查询到的文章信息</returns> public static IList<Article> GetArticleByArticleTimeTypeAndCategoryID(string articleTimeType, string startTime, string timeRange, int categoryID, int pageSize, long pageIndex) { string strWhere = string.Empty; if (articleTimeType.Equals("createTime")) { strWhere = GetSearchWhereByCreateTimeAndCategoryID("CreatedTime", startTime, timeRange, categoryID); } else { strWhere = GetSearchWhereByCreateTimeAndCategoryID("ModifiedTime", startTime, timeRange, categoryID); } return ArticleService.GetArticlePagerData("ArticleID,Title,ArticleLabel,CONVERT(NVARCHAR(16),CreatedTime,120) AS CreatedTime,Clicks", "CreatedTime", "desc", strWhere, pageSize, pageIndex); } private static string GetSearchWhereByCreateTimeAndCategoryID(string columnName,string startTime, string timeRange, int categoryID) { string strWhere = string.Empty; string startTimeType = string.Empty; if (startTime.Length == 4) { startTime += "-1-1"; startTimeType = "yy"; } else if (startTime.Length < 8) { startTime += "-1"; startTimeType = "mm"; } else { startTimeType = "dy"; } switch (timeRange) { case "3d": strWhere = columnName + " between '" + startTime + "' and '" + Convert.ToDateTime(startTime).AddDays(3) + "'"; break; case "wk": strWhere = columnName + " between '" + startTime + "' and '" + Convert.ToDateTime(startTime).AddDays(7) + "'"; break; case "3m": strWhere = columnName + " between '" + startTime + "' and '" + Convert.ToDateTime(startTime).AddMonths(3) + "'"; break; case "before": strWhere = columnName + " < '" + startTime + "'"; break; case "after": strWhere = columnName + " > '" + startTime + "'"; break; default: switch (startTimeType) { case "yy": strWhere = columnName + " between '" + startTime + "' and '" + Convert.ToDateTime(startTime).AddYears(1) + "'"; break; case "mm": strWhere = columnName + " between '" + startTime + "' and '" + Convert.ToDateTime(startTime).AddMonths(1) + "'"; break; default: strWhere = columnName + " between '" + startTime + "' and '" + Convert.ToDateTime(startTime).AddDays(1) + "'"; break; } break; } if (categoryID != -1) { strWhere += "and categoryID=" + categoryID; } return strWhere; }