存储过程分页2

存储过程：

 create PROCEDURE [dbo].[proc_SplitPage]
 -- =============================================
 -- Description:    分页，用到了ROW_NUMBER()
 -- =============================================
 @tblName   varchar(255),       -- 表名
 @strFields varchar(1000) = '*', -- 需要返回的列，默认*
 @strOrder varchar(255)='',      -- 排序的字段名，必填
 @strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
 @PageSize   int = 10,          -- 页尺寸,默认10
 @PageIndex int = 1,           -- 页码,默认1
 @strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
 AS
 declare
 @strSQL   varchar(5000)
 if @strWhere !=''
set @strWhere='    where   '+@strWhere      //注：这里的where 可以去掉，在程序中写时要写上where
set @strSQL=
  'SELECT * FROM ('+    'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strFields+' '+
  'FROM '+@tblName+' '+@strWhere+')
  AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
 exec (@strSQL)

sqlhelper.cs

 public DataTable TabPageQuery(string sql, SqlParameter[] paras, CommandType ct)
    {
        DataTable dt = new DataTable();
        SqlCommand cmd = new SqlCommand();
        using (SqlConnection conn = new SqlConnection(sqlHelper.conn))
        {
            conn.Open();
            cmd = new SqlCommand(sql,conn);
            cmd.Parameters.AddRange(paras);
            cmd.CommandType = ct;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            conn.Close();
        }
        return dt;
    }
 

rpInfo.cs

  public DataTable GetList(string tblName, string strOrder, string strOrderType, int PageSize, int PageIndex, string strWhere)
    {
       
        DataTable dt = new DataTable();
        string cmdText = "proc_SplitPage";
        SqlParameter[] paras = new SqlParameter[]
        {    new SqlParameter("@tblName", tblName),
            new SqlParameter("@strOrder", strOrder),
            new SqlParameter("@strOrderType", strOrderType),
            new SqlParameter("@PageSize", PageSize),
            new SqlParameter("@PageIndex", PageIndex),
            new SqlParameter("@strWhere", strWhere)
        };
        dt = new sqlHelper().TabPageQuery(cmdText, paras, CommandType.StoredProcedure);
        return dt;
    }
    /* 获取信息总数 */
    public int CalRecordCount()
    {
        SqlConnection conn = new SqlConnection(sqlHelper.conn);
        SqlCommand cmd;
        int res;
        string cmdText = "select count(1) from EnterInfo ";
        conn.Open();
        using (cmd = new SqlCommand(cmdText,conn))
        {
            res = int.Parse(cmd.ExecuteScalar().ToString());
        }
        conn.Close();
        return res;
    }
    //重载信息总数
    public int CalRecordCount(string where)
    {
        SqlConnection conn = new SqlConnection(sqlHelper.conn);
        SqlCommand cmd;
        int res;
        string cmdText = "select count(1) from EnterInfo  "+where+"";
        conn.Open();
        using (cmd = new SqlCommand(cmdText, conn))
        {
            res = int.Parse(cmd.ExecuteScalar().ToString());
        }
        conn.Close();
        return res;
    }

aspx.cs

 string str2 = "where EX_stuName like'赵%'";

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string strWhere = GetCond(str2);
            bind();
           anpInfo.RecordCount = rp.CalRecordCount(strWhere);
        }
    }

 private void bind()
    {
      rpInfo.DataSource = rp.GetList("EnterInfo","ID","desc",anpInfo.PageSize,anpInfo.CurrentPageIndex,GetCond(str2))
      rpInfo.DataBind();
   }

 /* 设置查询条件 */
    private string GetCond(string str)
    {
        string cond = str;
        return cond;
    }

//分页控件aspnetpager

 protected void anpInfo_PageChanged(object sender, EventArgs e)
    {
        bind();
    }