sql 数据库分页

浏览数：18 / 时间：2015年06月12日

这是一个简单的分页存储过程实例，返回总记录数表和查询得出的表

  
    
    create procedure [dbo].[goodfenye]
(
@tablename varchar(200),//表名称
@pageindex int ,        //当前页数
@pagesize int,          //每页显示的页数
@total int output,       //总记录数
@search varchar(200)     //查询条件
)
as
 
  begin
  declare @strsql varchar(2000)
  declare @sqlwhere varchar (1000)
  declare @sqltotal nvarchar (1000)
   
  if @search!=‘‘
    begin
      set @sqlwhere = ‘1=1 and GoodName=‘+@search
    end
  else
    begin
      set @sqlwhere =‘ 1=1 ‘
    end
 
    set  @strsql =‘select top ‘ + cast(@pagesize as varchar(20))+ ‘ * from ‘ +@tablename + ‘ where gid not in (select top ‘ + cast(@pagesize*(@pageindex-1) as varchar(20)) +‘ gid from ‘+@tablename +‘)‘
    set  @sqltotal=‘select count(1) from ‘+ @tablename 
    begin
    exec sp_executesql @sqltotal,N‘@total int output‘,@total output   //返回总页数
    exec (@strsql)                                                    //返回
    end
  
 end

　　C#调用存储过程如下

 public void databind()
    {

        int temp1 = Convert.ToInt32(lblCurrentPage.Text);
        DataSet ds = new System.Data.DataSet();
       
        SqlCommand mycommand = new SqlCommand();
        SqlDataAdapter asd = new SqlDataAdapter();

        asd.SelectCommand = mycommand;
        
        asd.SelectCommand.Connection = conn;
        asd.SelectCommand.CommandText = "goodfenye";
        asd.SelectCommand.CommandType = CommandType.StoredProcedure;

        conn.Open();
        SqlParameter[] par =
        {
           new  SqlParameter ("@tablename",SqlDbType.VarChar,255),
           new  SqlParameter ("@pageindex",SqlDbType.Int),
           new  SqlParameter ("@pagesize",SqlDbType.Int),
           new SqlParameter("@total",SqlDbType.Int),
           new  SqlParameter ("@search",SqlDbType.VarChar,255)

        };
        par[0].Value = "tabG_Goods";
        par[1].Value = temp1;
        par[2].Value = 5;
        par[3].Direction = ParameterDirection.Output;
        par[4].Value = this.Textname.Text;

        foreach (SqlParameter pa in par)
        {
            asd.SelectCommand.Parameters.Add(pa);
        }
        asd.Fill(ds, "ss");
        conn.Close();
        this.Repeater1.DataSource = ds.Tables[1];
        this.Repeater1.DataBind();
        int count = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
        this.labeltotal.Text = count.ToString();

        int j = count / 5;  //页数
        int i = count % 5;


        if (j >= 1)
        {
            if (i > 0)
            {
                j = j + 1;
            }

        }
        else
        {
            j = 1;

        }

        lblPageCount.Text = j.ToString();

    }