USE [WineShop]GO/****** Object: StoredProcedure [dbo].[GetRecordFromPage2005] Script Date: 06/26/2012 13:52:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[GetRecordFromPage2005] @fieldlist nvarchar(200) = '*', @datasrc nvarchar(200), @filter nvarchar(200) = '', @orderBy nvarchar(200), @pageNum int = 1, @pageSize int = NULLAS SET NOCOUNT ON DECLARE @STMT nvarchar(max) -- SQL to execute ,@recct int -- total # of records (for GridView paging interface) IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1' IF @pageSize IS NULL BEGIN SET @STMT = 'SELECT ' + @fieldlist + 'FROM ' + @datasrc + 'WHERE ' + @filter + 'ORDER BY ' + @orderBy EXEC (@STMT) -- return requested records END ELSE BEGIN SET @STMT = 'SELECT @recct = COUNT(*) FROM ' + @datasrc + ' WHERE ' + @filter EXEC sp_executeSQL @STMT, = N'@recct INT OUTPUT', @recct = @recct OUTPUT --SELECT @recct AS recct -- return the total # of records DECLARE @lbound int, @ubound int SET @pageNum = ABS(@pageNum) SET @pageSize = ABS(@pageSize) IF @pageNum < 1 SET @pageNum = 1 IF @pageSize < 1 SET @pageSize = 1 SET @lbound = ((@pageNum - 1) * @pageSize) SET @ubound = @lbound + @pageSize + 1 IF @lbound >= @recct BEGIN SET @ubound = @recct + 1 SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if -- no records would be on the -- specified page END SET @STMT = 'SELECT ' + @fieldlist + ' FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, * FROM ' + @datasrc + ' WHERE ' + @filter + ' ) AS tbl WHERE row > ' + CONVERT(varchar(9), @lbound) + ' AND row < ' + CONVERT(varchar(9), @ubound) EXEC (@STMT) -- return requested records END
上面的分页的存储过程
后台方法我们应该如何调用呢
存储过程参数说明：
fieldlist：你要显示的字段，datasrc:表名，filter：过滤条件，orderBy：排序，pageNum：页数，pagesize：每页显示几条记录
public static IList<WineComment> WineCommentQueryAll(int pageindex, string orderfiled)
{ using (SqlConnection cn = new SqlConnection(SQLHelp.Conn)) { cn.Open(); string sql = "GetRecordFromPage2005"; SqlCommand cmd = new SqlCommand(sql, cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@fieldlist", "commentid,commentuser,commenttext,commenttime"); cmd.Parameters.AddWithValue("@datasrc", "tb_WineComment"); cmd.Parameters.AddWithValue("@filter", " "); cmd.Parameters.AddWithValue("@orderBy", orderfiled); cmd.Parameters.AddWithValue("@pageNum", pageindex); cmd.Parameters.AddWithValue("@pagesize", 5); SqlDataReader dr = cmd.ExecuteReader(); IList<WineComment> list = new List<WineComment>(); while (dr.Read()) { WineComment wc = new WineComment(); if (dr["CommentID"] != DBNull.Value) { wc.CommentID = (int)dr["CommentID"]; } if (dr["CommentUser"] != DBNull.Value) { wc.CommentUser = (string)dr["CommentUser"]; } if (dr["CommentText"] != DBNull.Value) { wc.CommentText = (string)dr["CommentText"]; } if (dr["CommentTime"] != DBNull.Value) { //wc.CommentTime = (DateTime)dr["CommentTime"]; wc.CommentTime = ((DateTime)dr["CommentTime"]).ToString("yyyy-MM-dd HH:mm:ss"); } list.Add(wc); } dr.Close(); return list; } }前台页面的js代码首先要引用
<script src="js/jquery.1.3.2.js" type="text/javascript"></script> <script src="jqueryPager/jquery.pagination.js" type="text/javascript"></script> <link href="jqueryPager/pagination.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
$().ready(function() { InitWineComment(0); }); function pageselectCallback(page_id, jq) { InitWineComment(page_id); } function InitWineComment(pageindx) { var tbody = ""; $.ajax({ type: "GET", dataType:"json", //返回的格式 ", //处理的页面 data: { action: "GetComments", p: (pageindx+1)}, //附带的参数 beforeSend:function(){$("#divload").show();$("#Pagination").hide();}, complete:function(){$("#divload").hide();$("#Pagination").show()}, success:function(json) { $("#comment tr:gt(0)").remove(); $.each(json, function(index, array) {//解析json var trs = ""; trs += "<tr><td>" + ((index+1)+5*(pageindx-0))+ "</td><td>" +array['CommentUser'] + "</td><td>" + array['CommentText'] + "</td><td>"+array['CommentTime'] + "</td></tr>"; tbody += trs; }); $("#comment").append(tbody); $("#comment tr:gt(0):odd").attr("class", "odd"); $("#comment tr:gt(0):even").attr("class", "enen"); $("#comment tr:gt(0)").hover(function(){ $(this).addClass('mouseover'); },function(){ $(this).removeClass('mouseover'); }); }}); $("#Pagination").pagination(<%=pagecount %>, {//pagecount为评论的总条数 callback: pageselectCallback, //页数改变触发的事件 prev_text: '上一页', next_text: '下一页', items_per_page:5, //每页显示5条 num_display_entries:6, current_page:pageindx, //当前页 num_edge_entries:2 }); }
</script>
Handler.ashx处理页面的代码为
case "GetComments":
int pi=int.Parse(context.Request.QueryString["p"].ToString()); string isorder = "commentid desc"; if (pi == 0) pi = 1; IList<WineComment> comment= WineCommentManager.WineCommentQueryAll(pi,isorder); Content = JavaScriptConvert.SerializeObject(comment); break;context.Response.Write(Content);
前台页面的html的代码很简单
网友评论
ID 评论者 评论内容 评论时间
运行效果如下：
