两个高效的ASP分页函数

2022-07-15 80酷酷网 80kuku.com

两个高效的ASP分页函数（统计记录数，分页提取记录）
-
<%
'    /*智能返回分页SQL语句*/
'    /// <summary>
'    /// 功能:智能返回分页SQL语句
'    /// </summary>
'    /// <param name="primaryKey">主键（不能为空）</param>
'    /// <param name="queryFields">提取字段（不能为空）</param>
'    /// <param name="tableName">表（理论上允许多表）</param>
'    /// <param name="condition">条件（可以空）</param>
'    /// <param name="OrderBy">排序，格式：字段名+""+ASC（可以空）</param>
'    /// <param name="pageSize">分页数（不能为空）</param>
'    /// <param name="pageIndex">当前页，起始为：1（不能为空）</param>
'    /// <returns></returns>
Public Function GetPageListSql( primaryKey, queryFields, tableName, condition, orderBy, pageSize, pageIndex)

Dim strTmp,SqlSelect,SqlPrimaryKeySelect,strOrderBy,strWhere,strTop,pageindexsize
strTmp=""
'//---strTmp用于返回的SQL语句
SqlSelect = ""
SqlPrimaryKeySelect = ""
strOrderBy = ""
strWhere = " where 1=1 "
strTop = ""
pageindexsize = 0

'        //0：分页数量
'        //1:提取字段
'        //2:表
'        //3:条件
'        //4:主键不存在的记录
'        //5:排序
        SqlSelect = " select top {0} {1} from {2} {3} {4} {5}"
'       //0:主键
'       //1:TOP数量,为分页数*(排序号-1)
'       //2:表
'       //3:条件
'       //4:排序
        SqlPrimaryKeySelect = " and {0} not in (select {1} {0} from {2} {3} {4}) "

        if orderBy <> "" then
            strOrderBy = " order by "&orderBy
           End if
     if condition <> "" then
            strWhere =strWhere&" and "&condition
         pageindexsize = (pageIndex - 1) * pageSize
          End if
      if cint(pageindexsize) > 0 then

            strTop = " top " & pageindexsize

SqlPrimaryKeySelect = Replace(Replace(Replace(Replace(Replace(SqlPrimaryKeySelect,"{0}", primaryKey),"{1}", strTop),"{2}", tableName),"{3}", strWhere),"{4}", strOrderBy)

strTmp = Replace(Replace(Replace(Replace(Replace(Replace(SqlSelect,"{0}", pageSize),"{1}", queryFields),"{2}", tableName),"{3}", strWhere),"{4}", SqlPrimaryKeySelect),"{5}", strOrderBy)

        else
            strTmp = Replace(Replace(Replace(Replace(Replace(Replace(SqlSelect,"{0}", pageSize),"{1}", queryFields),"{2}", tableName),"{3}", strWhere),"{4}", ""),"{5}", strOrderBy)

        End if
        GetPageListSql= strTmp

End Function

'    /*分页查询数据记录总数获取*/
'    /// <summary>
'    ///功能: 分页查询数据记录总数获取
'    /// </summary>
'    /// <param name="ptbName">----要显示的表或多个表的连接</param>
'    /// <param name="pID">----主表的主键</param>
'    /// <param name="pstrCondition">----查询条件,不需where</param>
'    /// <param name="pDist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param>
'    /// <returns></returns>
Public Function GetPageListCounts( pID, ptbName, pstrCondition, pDist)

' //---存放取得查询结果总数的查询语句
'        //---对含有DISTINCT的查询进行SQL构造
'        //---对含有DISTINCT的总数查询进行SQL构造
Dim strTmp,SqlSelect,SqlCounts
strTmp = ""
SqlSelect = ""
SqlCounts = ""

        if CInt(pDist) = 0 then

            SqlSelect = "Select "
            SqlCounts = "COUNT(*)"

        else

            SqlSelect = "Select DISTINCT "
            SqlCounts = "COUNT(DISTINCT " & pID& ")"
       End if
        if pstrCondition ="" then

            strTmp = SqlSelect & " "& SqlCounts & " FROM " & ptbName & ""

        else

            strTmp = SqlSelect & " " & SqlCounts & " FROM "& ptbName& " Where (1=1) and " & pstrCondition
        End If

        GetPageListCounts= strTmp

End Function