AspNetPager分页结合存储过程的用法

本来就对这存储过程不是很熟，胡乱的整了一下午 终于可以了 废话不多说 直接上码——————————————

 

存储过程：————————————————自己得加强下=、-

ALTER proc [dbo].[GetPageData](
@pageIndex int,
@pageSize int,
@tbName varchar(50),
@ID varchar(50),
@keyword varchar(100)='',
@count bit=0
)
as
declare @strSQL varchar(6000)
begin
if @keyword=''
begin
set @strSQL='select top '+cast (@pageSize as varchar)+' * from '+ @tbname +' where '
+@ID+' not in (select top '+cast((@pageindex-1)*@pageSize as varchar)+' '+@ID+' from '+@tbname+ ' order by ' +@ID+' desc ) order by '+@ID+ ' desc ';
end
if @keyword!=''
begin

set @strSQL='select top '+cast (@pageSize as varchar)+' * from '+ @tbname +' where '
+@ID+' not in (select top '+cast((@pageindex-1)*@pageSize as varchar)+' '+@ID+' from '+@tbname+ ' where '+@keyword+' order by ' +@ID+' desc ) and ' + @keyword + 'order by '+@ID+ ' desc ';
--set @strSQL=@strSQL+' ' +@keyword;

end
if @count!=0 and @keyword!=''
begin
set @strSQL=' select count(1) as Total from '+@tbname+ ' where ' +@keyword;
end
--print @strSQL;
end
exec (@strSQL);

 

自己写的一个page：-----------

public DataSet GetPageData(int pageIndex, int pageSize, string tbName, string tbID,string keyword, int count)
{
string strConn = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;

SqlConnection conn = new SqlConnection(strConn);

try
{
conn.Open();

SqlCommand cmd = conn.CreateCommand();

cmd.CommandText = "GetPageData";

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter[] sp = new SqlParameter[]{
new SqlParameter("@pageIndex",pageIndex),
new SqlParameter("@pageSize",pageSize),
new SqlParameter("@tbName",tbName),
new SqlParameter("@ID",tbID),
new SqlParameter("@keyword","title like '%"+keyword+"%'"),
new SqlParameter("@count",count)
};
cmd.Parameters.AddRange(sp);

SqlDataAdapter ada = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
DataTable dt = new DataTable();

if (ada != null)
{
ada.Fill(ds);
count = ds.Tables[0].Rows.Count;
return ds;
}
count = 0;
return null;
}
finally
{
conn.Close();
}

 

下面是调用的代码：

   

private void BindData(int page,string keyword)
{
Pager pg = new Pager();
//int count = 0;
Repeater1.DataSource = pg.GetPageData(page, this.AspNetPager1.PageSize, "tb_cp", "sid", keyword, 0);

this.AspNetPager1.RecordCount = (int)pg.GetPageData(page, this.AspNetPager1.PageSize, "tb_cp", "sid", keyword, 1).Tables[0].Rows[0]["Total"];

this.Repeater1.DataBind();
this.Label1.Text = "当前第" + page + "页 总" + this.AspNetPager1.PageCount + "页";
}

 

写给自己看看 也给和我一样刚踏入门的猿们 一起学习

                                        ——————————————————————首篇完工————————————