日常数据库读取经常要分页显示，使用数据绑定的方法只能用在DG之类的重型控件中，对于定制控件，通常要使用分页sql。以下记录两种：
方式一：拼接SQL
public DataTable SpacePhotosList(int pageSize, int currentPage, int userid, int albumid)
{
//try
//{
//"userid=" + userid + " AND albumid=" + albumid
DbParameter[] prams =
{
DbHelper.MakeInParam("@userid", (DbType)SqlDbType.Int, 4,userid),
DbHelper.MakeInParam("@albumid", (DbType)SqlDbType.Int, 4,albumid)
};
int pageTop = (currentPage - 1) * pageSize;
string sql = "";
if (currentPage == 1)
{
sql = "SELECT TOP " + pageSize.ToString() + " * FROM "
+ "[" + BaseConfigs.GetTablePrefix + "photos] WHERE [userid]=@userid AND [albumid]=@albumid ORDER BY [photoid] ASC";
}
else
{
sql = "SELECT TOP " + pageSize.ToString() + " * FROM "
+ "[" + BaseConfigs.GetTablePrefix + "photos] WHERE [photoid] > (SELECT MAX([photoid]) FROM "
+ "(SELECT TOP " + pageTop + " [photoid] FROM [" + BaseConfigs.GetTablePrefix + "photos] WHERE "
+ "[userid]=@userid AND [albumid]=@albumid ORDER BY [photoid] ASC) AS tblTmp ) AND [userid]=@userid "
+ "AND [albumid]=@albumid ORDER BY [photoid] ASC";
}
return DbHelper.ExecuteDataset(CommandType.Text, sql, prams).Tables[0];
//}
//catch
//{
// return new DataTable();
//}
}
方式二：存储过程
public DataTable GetSpacePhotoListByAlbumId(int albumId, int pageIndex, int pageSize, out int totalRecords)
{
string sqlStr = string.Format("select * from {0}photos where albumid={1}", TablePrefix, albumId);
string orderby = "postdate desc";
int rc = 0;
DataTable dt = DbHelper.GetPageDataTable(pageSize, pageIndex, sqlStr, orderby, ref rc);
totalRecords = rc;
return dt;
}
#region 分页处理
/// <summary>
/// 分页GetPage(10,1,"select * from table","id desc");
/// </summary>
/// <param name="pagesize">页面大小</param>
/// <param name="curpageindex">当前页，从开始</param>
/// <param name="select">要进行分页的查询语句</param>
/// <param name="orderby">需要进行排序的列</param>
/// <returns>分页后的表，用DataTable的形式</returns>
public DataTable GetPageDataTable(int pagesize,int curpageindex,string select,string orderby)
{
DbParameter[] parms = {
DbHelper.MakeInParam("@select",DbType.String,300,select.Trim()),
DbHelper.MakeInParam("@pagesize",DbType.Int32,4,pagesize),
DbHelper.MakeInParam("@currentpageindex",DbType.Int32,4,curpageindex),
DbHelper.MakeInParam("@orderby",DbType.String,200,orderby.Trim())
};
return ExecuteDataset(CommandType.StoredProcedure, "dnt_getpage", parms).Tables[0];
}
/// <summary>
/// 分页如：GetPage(10,1,"select * from table","id desc");
/// </summary>
/// <param name="pagesize">页面大小</param>
/// <param name="curpageindex">当前页，从开始</param>
/// <param name="select">要进行分页的查询语句</param>
/// <param name="orderby">需要进行排序的列</param>
/// <returns>分页后的表，用DataReader的形式</returns>
public DbDataReader GetPageReader(int pagesize,int curpageindex, string select, string orderby )
{
DbParameter[] parms = {
DbHelper.MakeInParam("@select",DbType.String,300,select.Trim()),
DbHelper.MakeInParam("@pagesize",DbType.Int32,4,pagesize),
DbHelper.MakeInParam("@currentpageindex",DbType.Int32,4,curpageindex),
DbHelper.MakeInParam("@orderby",DbType.String,200,orderby.Trim())
};
return ExecuteReader(CommandType.StoredProcedure, "dnt_getpage", parms);
}
#endregion
存储过程：
Create PROC dnt_getpage
(
@select varchar(200),
@pagesize int,
@currentpageindex int,
@orderby varchar(200)
)
as
declare @start int;
declare @end int;
declare @sql varchar(500);
set @start=@pagesize * (@currentpageindex - 1) + 1;
set @end= @pagesize * @currentpageindex;
set @select=Stuff(@select,1,6,'select row_number() over(order by '+@orderby+') as rownum,');
set @sql='with tmp as ('+@select+') select * from TMP WHERE rownum>='+cast(@start as varchar(10))+' and rownum<='+cast(@end as varchar(10))
exec (@sql)
return
