AspNetPager与MSSQL海量分页存储过程结合代码,Asp.Net代码库,德仔网

代码语言
代码分类
【Asp.Net】 AspNetPager与MSSQL海量分页存储过程结合
作者:Dezai.CN / 发布于2013/1/29/ 611
存储过程：
CREATE PROCEDURE UP_GetRecordByPageOrder
 
 @tblName varchar(255),   -- 表名
 @fldName varchar(255),   -- 显示字段名
 @OrderfldName varchar(255),  -- 排序字段名
 @StatfldName varchar(255),  -- 统计字段名
 @PageSize int = 10,   -- 页尺寸
 @PageIndex int = 1,   -- 页码
 @IsReCount bit = 0,   -- 返回记录总数, 非0 值则返回
 @OrderType bit = 0,   -- 设置排序类型, 非0 值则降序
 @strWhere varchar(1000) = ''  -- 查询条件(注意: 不要加where)
AS
 
 declare @strSQL varchar(6000)  -- 主语句
 declare @strTmp varchar(100)   -- 临时变量
 declare @strOrder varchar(400)  -- 排序类型
 
if @OrderType > 0
 begin
  set @strTmp = '<(select min'
  set @strOrder = ' order by [' + @OrderfldName +'] desc'
 end
else
 begin
  set @strTmp = '>(select max'
  set @strOrder = ' order by [' + @OrderfldName +'] asc'
 end
 
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
 + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
 + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
 + @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
 + @strOrder
 
if @strWhere != ''
 set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
 + @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
 + @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
 + @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
 + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
 
if @PageIndex = 1
 begin
  set @strTmp = ''
  if @strWhere != ''
  set @strTmp = ' where ' + @strWhere
 
  set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
  + @tblName + ']' + @strTmp + ' ' + @strOrder
 end
 
 
if @IsReCount > 0
 set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'
 
if @strWhere!=''
 set @strSQL = @strSQL+' where ' + @strWhere
exec (@strSQL)
GO
 
cs部分：
#region 获取分页列表
/// <summary>
/// Pg_Paging
/// </summary>
/// <param name="Tables"></param>
/// <param name="PK"></param>
/// <param name="Sort"></param>
/// <param name="PageNumber"></param>
/// <param name="PageSize"></param>
/// <param name="Fields"></param>
/// <param name="Filter"></param>
/// <param name="Group"></param>
/// <returns></returns>
public static DataSet Pg_Paging(string TableName, string PK, string Fields, int PageSize, int PageIndex, bool OrderType, string Sort, string Filter, bool isCount)
{
    //存储过程名
    string storedProcName = "Pagination";
 
    //设置参数
    SqlParameter[] para = new SqlParameter[]
    {
        new SqlParameter("@tblName",TableName),
        new SqlParameter("@PKName",PK),
        new SqlParameter("@strGotFields",Fields),
        new SqlParameter("@PageSize",PageSize),
        new SqlParameter("@PageIndex",PageIndex),
        new SqlParameter("@OrderType",OrderType),
        new SqlParameter("@strSort",Sort),
        new SqlParameter("@strWhere",Filter),
        new SqlParameter("@isCount",isCount)
    };
    return DBUtility.DbHelperSQL.RunProcedure(storedProcName, para, "singleselectinfo");
}
 
#endregion
protected void Page_Load(object sender, EventArgs e)  
{  
    if (!IsPostBack)  
    {  
        SubjectBind();  
    }  
}  
 
//绑定单选题列表  
protected void SingleSelectBind()  
{  
    string table = " SingleSelect ";  
    string pk = " SingleSelectID ";  
    string sort = "SingleSelectID";  
    bool ordertype = false;  
    int pageindex = 0;  
    int pagesize = 1;  
    string fields = " SingleSelectID,Title ";  
    string filter = "IsDeleted = 0";  
 
    if (AspNetPager1.CurrentPageIndex < 1)  
    {  
        pageindex = 1;  
    }  
    else 
    {  
        pageindex = AspNetPager1.CurrentPageIndex;  
    }  
 
    //动态设置用户自定义文本内容  
    AspNetPager1.RecordCount = Pg_PageCount(table, pk, filter, sort);  
 
    AspNetPager1.PageSize = pagesize;  
    this.GridView1.DataSource = Pg_Paging(table, pk, fields, pagesize, pageindex, ordertype, sort, filter, false).DefaultView;  
    this.GridView1.DataBind();  
}  
 
protected void AspNetPager1_PageChanged(object src, EventArgs e)  
{  
    SingleSelectBind();  
}
试试其它关键字
　分页　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)