分页存储过程与它的使用方法

文件:	AspNetPager.rar
大小:	36KB
下载:	下载

分页存储过程：

//在数据库中创建好存储过程　getdataset

CREATE proc getdataset
@TableList Varchar(200)='*',--搜索表的字段,比如：’id，datatime，job‘，用逗号隔开
@TableName Varchar(30), --搜索的表名
@SelectWhere Varchar(500)='',--搜索条件，这里不用写where，比如：job=’teacher‘and class='2'
@SelectOrderId Varchar(20),--表主键字段名。比如：id
@SelectOrder Varchar(200)='', --排序，可以使用多字段排序但主键字段必需在最前面.也可以不写，比如：order by class asc
@intPageNo int=1, --页号
@intPageSize int=10 ,--每页显示数
@RecordCount int OUTPUT  --总记录数(存储过程输出参数)
as 
   
declare @TmpSelect      NVarchar(600) 
declare @Tmp     NVarchar(600) 

set nocount on--关闭计数

set @TmpSelect = 'select @RecordCount = count(*) from

execute sp_executesql
@TmpSelect,    --执行上面的sql语句
int OUTPUT' ,   --执行输出数据的sql语句，output出总记录数
@RecordCount  OUTPUT

  if (@RecordCount = 0)    --如果没有贴子，则返回零
       return 0
      
   /*判断页数是否正确*/
  if (@intPageNo - 1) * @intPageSize > @RecordCount   --页号大于总页数，返回错误
     return (-1)
set nocount off--打开计数
if @SelectWhere != ''
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' from where not in(select top '+str((@intPageNo-1)*@intPageSize)+' from +' ) and +'
end
else
begin
set @TmpSelect = 'select top '+str(@intPageSize)+' from where not in(select top '+str((@intPageNo-1)*@intPageSize)+' from )
end
execute sp_executesql @TmpSelect
return(@@rowcount)
GO
使用方法：

string strConn = ConfigurationManager.ConnectionStrings["con"].ToString(); //取得连接字符串
        SqlConnection con = new SqlConnection(strConn);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "P_newpager";
        int pageindex = 0;
        string strWhere = "";　　//查询条件
        if (AspNetPager1.CurrentPageIndex < 1)
        {
            pageindex = 1;
        }
        else
        {
            pageindex = AspNetPager1.CurrentPageIndex;
        }
        SqlParameter[] parameters ={
             new SqlParameter("@tblname",SqlDbType.VarChar,500),
             new SqlParameter("@strGetFields",SqlDbType.VarChar,1000),
             new SqlParameter("@fldName",SqlDbType.VarChar,255),
             new SqlParameter("@pagesize",SqlDbType.Int,4),
             new SqlParameter("@pageindex",SqlDbType.Int,4),
             new SqlParameter("@doCount",SqlDbType.Bit),
             new SqlParameter("@ordertype",SqlDbType.Bit),
             new SqlParameter("@strwhere",SqlDbType.VarChar,2000)};
        parameters[0].Value = "s_worktable sw  with(updlock)join load_table lt on lt.l_id=sw.s_id";//表名
        parameters[1].Value = "s_id,w_id,styleid,substring(w_name,1,8) as w_name,w_sphoto,substring(username,1,8)as username,w_hit"; //列名
        parameters[2].Value = "w_id";
        parameters[3].Value = AspNetPager1.PageSize;//多少条为一页
        parameters[4].Value = pageindex;//索引页
        parameters[5].Value = 0;//这里写死０就行了
        parameters[6].Value = 1;//这里的１是降序，０为升序
        parameters[7].Value = strWhere + strSql.ToString();//查询条件组合
        foreach (SqlParameter p in parameters)
        {
            cmd.Parameters.Add(p);
        }
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        sda.Fill(ds);
        //动态设置用户自定义文本内容
        AspNetPager1.RecordCount = int.Parse(ds.Tables[1].Rows[0][0].ToString());
        AspNetPager1.CustomInfoHTML = "共" + AspNetPager1.RecordCount.ToString() + "条,";
        AspNetPager1.CustomInfoHTML += "" + AspNetPager1.PageCount.ToString() + "页,";
        AspNetPager1.CustomInfoHTML += "第" + AspNetPager1.CurrentPageIndex.ToString() + "页";
        labCountNum.Text = AspNetPager1.RecordCount.ToString();
        DataListPic.DataSource = ds.Tables[0].DefaultView;
        DataListPic.DataBind();

要用到数据分页控件：