using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Configuration; using System.Data.SqlClient; using System.Data;
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if(!IsPostBack) BindProduct("1"); }
private void BindProduct(string pageindex) { string str = ConfigurationManager.ConnectionStrings["studentCnn"].ConnectionString; using (SqlConnection sqlCnn = new SqlConnection(str)) { SqlDataAdapter da = new SqlDataAdapter("sp_Student_Select_by_Page_rowNumber", sqlCnn); da.SelectCommand.Parameters.AddWithValue("@pageIndex", pageindex); da.SelectCommand.Parameters.Add("@pageCount", SqlDbType.Int).Direction = ParameterDirection.Output; da.SelectCommand.Parameters.AddWithValue("@pageSize", 2); da.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet(); da.Fill(ds); this.DataList1.DataSource = ds.Tables[0].DefaultView; this.DataList1.DataBind(); this.HiddenField1.Value = pageindex; this.HiddenField2.Value = da.SelectCommand.Parameters["@pageCount"].Value.ToString();
if (index < count) index++; this.BindProduct(index.ToString()); } } //下一页
存储过程的创建:
ALTER PROCEDURE sp_Student_Select_by_Page_rowNumber @pageSize int, --每页记录数量 @pageCount int output, --总页数 @pageIndex int --当前页索引号
AS BEGIN declare @totalRecords int select @totalRecords = count(sid) from student if(@totalRecords % @pageSize = 0) set @pageCount = @totalRecords / @pageSize; else set @pageCount = @totalRecords / @pageSize +1; with temp as (select row_number() over (order by sid) as id,* from student) select * from temp where id between (@pageIndex -1)*@pageSize +1 and @pageIndex * @pageSize return @totalRecords end