ASP.NET+SQLServer 2008ページング、シンプルで実用的
2233 ワード
///
///
///
/// , and
///
/// -1
///
///
public DataSet GetListByPage(string strWhere, string orderby, int pageindex, int pagesize)
{
string tablename = "t_JinYinShangHu";
StringBuilder strSql = new StringBuilder();
strSql.Append("select top " + pagesize + " * from " + tablename + " where id not in ");
strSql.Append(" (");
strSql.Append(" select top " + pageindex * pagesize + " id from " + tablename + " order by " + orderby);
strSql.Append(" )");
strSql.Append(strWhere);
strSql.Append(" order by " + orderby);
return DbHelperSQL.Query(strSql.ToString());
}
方法二、
///
///
///
public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby);
}
else
{
strSql.Append("order by T.ID desc");
}
strSql.Append(")AS Row, T.* from t_Real_Estate T ");// ,
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
return dbSys.ExecuteDataSet(strSql.ToString());
}
リスト・ページの使用方法:
int start = AspNetPager1.PageSize * (AspNetPager1.CurrentPageIndex - 1) + 1;// 1
int end = AspNetPager1.PageSize * AspNetPager1.CurrentPageIndex;
//Response.Write(start.ToString() + "," + end.ToString());
rptDataList.DataSource = new dProduct().GetListByPage(where, " Sort asc,Joindate desc,Hits desc", start, end).Tables[0];
rptDataList.DataBind();
また、sqlほど柔軟ではなく、しばらく考慮しない一般的なストレージ・プロシージャのページングもあります.