sql server 2000不支持rownum函数下的分页aspnetpage

来源：互联网发布：linux配置ip 编辑：程序博客网时间：2024/06/10 01:50

/// <summary>
        /// 显示审核通过的商家
        /// </summary>
        /// <param name="gv"></param>
        /// <param name="Pager"></param>
        /// <param name="en"></param>
        public void Asp(GridView gv, AspNetPager Pager, NT_MyBusiness en)
        {
            NT_MyDataBase db = new NT_MyDataBase();
            SqlParameter[] p = new SqlParameter[3];
            p[0] = new SqlParameter("@BuID", SqlDbType.NVarChar, 50);
            p[0].Value = en.BuID;

p[2] = new SqlParameter("@BuName", SqlDbType.VarChar, 50);
p[2].Value = en.BuName;

p[1] = new SqlParameter("@BuStatus", SqlDbType.Int);
p[1].Value = en.BuStatus;

            string recordcountstr = "select count(*) from NT_MyBusiness where 1=1";
            string sql = " select top ";
            sql += Pager.PageSize;
            sql += " *,(case when BuStatus=0 then '审核' else '查看' end) as IsCheck,(case when BuReserve='0' then '锁定该商家' else '解锁该商家' end) as IsLocking from NT_MyBusiness where Id in ( select top ( ";
            sql += Pager.CurrentPageIndex * Pager.PageSize;
            sql += " ) Id from NT_MyBusiness where 1=1 ";
            //加条件
            if (en.BuStatus != 3 && en.BuStatus != null)//通过审核的商家
            {
                sql = sql + " and BuStatus<>0 ";
                recordcountstr = recordcountstr + " and BuStatus<>0 ";
            }
            if (en.BuID != "" && en.BuID != null)//按照商家账号查询
            {
                sql = sql + " and BuID like '%'+@BuID+'%' ";
                recordcountstr = recordcountstr + " and BuID like '%'+@BuID+'%' ";
            }
            if (en.BuName != "" && en.BuName != null)//安装商家姓名查询
            {
                sql = sql + " and BuName like '%'+@BuName+'%' ";
                recordcountstr = recordcountstr + " and BuName like '%'+@BuName+'%' ";
            }
            sql += " and Id not in(select top ";
            sql += (Pager.CurrentPageIndex - 1) * Pager.PageSize;
            sql += " Id from NT_MyBusiness where 1=1 ";
            //加条件
            if (en.BuStatus != 3 && en.BuStatus != null)//通过审核的商家
            {
                sql = sql + " and BuStatus<>0 ";
            }
            if (en.BuID != "" && en.BuID != null)//按照商家账号查询
            {
                sql = sql + " and BuID like '%'+@BuID+'%' ";
            }
            if (en.BuName != "" && en.BuName != null)//安装商家姓名查询
            {
                sql = sql + " and BuName like '%'+@BuName+'%' ";
            }
            sql += " ) ) ";
            //加条件
            if (en.BuStatus != 3 && en.BuStatus != null)//通过审核的商家
            {
                sql = sql + " and BuStatus<>0 ";
            }
            if (en.BuID != "" && en.BuID != null)//按照商家账号查询
            {
                sql = sql + " and BuID like '%'+@BuID+'%' ";
            }
            if (en.BuName != "" && en.BuName != null)//安装商家姓名查询
            {
                sql = sql + " and BuName like '%'+@BuName+'%' ";
            }
            sql+=" order by Id desc ";

int recordcount = Convert.ToInt32(db.ExecuteValue(recordcountstr, p));

            //将满住条件的总的记录数给分页控件的RecordCount属性
            Pager.RecordCount = recordcount;
            gv.DataSource = db.GetDataTable(sql, p);
            gv.DataBind();
        }