asp.net中文章内页带上一篇、下一篇,带排序号,先按排序号排序,然后按照ID倒序排序。大侠勿喷,需改进。
1 //上一篇 下一篇 2 string strPage = ""; 3 4 string sqlFirst = "select top 1 * from (select row_number() over(order by sort desc,id desc) as row_number,* from S_Article where classid=@classid) a where classid=@classid and a.row_number<( select row_number from (select row_number() over(order by sort desc,id desc) as row_number,* from S_Article where classid=@classid) a where classid=@classid and id=@id) order by sort ,id"; 5 6 SqlParameter[] p = { 7 new SqlParameter("@classid",SqlDbType.Int,4), 8 new SqlParameter("@id",SqlDbType.Int,4) 9 }; 10 p[0].Value = ClassId; 11 p[1].Value = id; 12 DataTable dtPrve = new DbExec().getDataTable(sqlFirst, false, p); 13 if (dtPrve != null && dtPrve.Rows.Count > 0) 14 { 15 strPage = "<div class=\"shang1 fl\">上一篇:<a href=\"/news/NewsDetail.aspx?pid=" + dtPrve.Rows[0]["ClassID"].ToString() + "&id=" + dtPrve.Rows[0]["id"].ToString() + "\" >" + Utils.SubString(dtPrve.Rows[0]["title"].ToString(), 40, "......") + "</a></div>"; 16 } 17 else 18 { 19 strPage = "<div class=\"shang1 fl\">上一篇:<a href=\"#\" >没有上一篇!</a></div>"; 20 } 21 22 //下一篇 23 24 25 string sqlNext = "select top 1 * from (select row_number() over(order by sort desc,id desc) as row_number,* from S_Article where classid=@classid) a where classid=@classid and a.row_number>( select row_number from (select row_number() over(order by sort desc,id desc) as row_number,* from S_Article where classid=@classid) a where classid=@classid and id=@id) order by sort desc,id desc"; 26 SqlParameter[] p1 = { 27 new SqlParameter("@classid",SqlDbType.Int,4), 28 new SqlParameter("@id",SqlDbType.Int,4) 29 }; 30 p1[0].Value = ClassId; 31 p1[1].Value = id; 32 DataTable dtNext = new DbExec().getDataTable(sqlNext, false, p1); 33 if (dtNext != null && dtNext.Rows.Count > 0) 34 { 35 strPage += "<div class=\"shang1 fr\">下一篇:<a href=\"/news/NewsDetail.aspx?pid=" + dtNext.Rows[0]["ClassID"].ToString() + "&id=" + dtNext.Rows[0]["id"].ToString() + "\" >" + Utils.SubString(dtNext.Rows[0]["title"].ToString(), 40, "......") + "</a></div>"; 36 } 37 else 38 { 39 strPage += "<div class=\"shang1 fr\">下一篇:<a href=\"#\" >没有下一篇!</a></div>"; 40 } 41 42 this.lblPageInfo.Text = strPage;