sql 分页查询
SQL codeUSE [guestbook]GO/****** Object: StoredProcedure [dbo].[selectmessage] Script Date: 08/27/2012 21:52:45 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[selectmessage] @pageSize int, @pageIndex intasdeclare @sql nvarchar(200)if(@pageIndex<2)beginset @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook'endelsebeginset @sql='select top '+convert(varchar(10),@pageSize)+' * from gbook whereid>(select max(id) from ( select top '+convert(varchar(10),@pageSize*(@pageIndex-1))+' id from gbook order by id) a)order by id'endexec sp_executesql @sql
C# code //进行数据绑定的方法 public void Bind(int x,int y) { //objPds.DataSource = this.SqlDataSource1.Select(new DataSourceSelectArguments()); int CurrentPage = Convert.ToInt32(labNowPage.Text); PagedDataSource ps = new PagedDataSource();//生成PagedDataSource的实例 //实例化SqlConnection对象 SqlConnection sqlCon = new SqlConnection(); sqlCon.ConnectionString = "server=.;uid=sa;pwd=sa;database=guestbook"; //定义SQL语句 //string SqlStr = "select * from gbook order by id desc"; string SqlStr = "exec selectmessage @pageSize="+x+",@pageIndex="+y+""; //实例化SqlDataAdapter对象 SqlDataAdapter da = new SqlDataAdapter(SqlStr, sqlCon); //实例化数据集DataSet DataSet ds = new DataSet(); da.Fill(ds, "gbook"); ps.DataSource = ds.Tables["gbook"].DefaultView; //ps.AllowPaging = true; //是否可以分页 //ps.PageSize = 2; //显示的数量 ps.CurrentPageIndex = CurrentPage - 1; //取得当前页的页码 lnkbtnFront.Enabled = true; lnkbtnFirst.Enabled = true; lnkbtnNext.Enabled = true; lnkbtnLast.Enabled = true; if (CurrentPage == 1) { lnkbtnFirst.Enabled = false;//不显示第一页按钮 lnkbtnFront.Enabled = false;//不显示上一页按钮 } if (CurrentPage == ps.PageCount) { lnkbtnNext.Enabled = false;//不显示下一页 lnkbtnLast.Enabled = false;//不显示最后一页 } this.labCount.Text = Convert.ToString(ps.PageCount); this.DataList1.DataSource = ps; this.DataList1.DataKeyField = "id"; this.DataList1.DataBind(); if (Session["username"] == null) { //Panel1.Visible = false; } else { Label1.Visible = true; Label1.Text = "欢迎您！" + Session["username"].ToString(); Button1.Visible = true; Label6.Visible = true; Label7.Visible = true; TextBox2.Visible = true; //TextBox3.Visible = true; FCKeditor1.Visible = true; Button6.Visible = true; // Button9.Visible = true; Button4.Visible = true; } if (Session["admin"] == null) { // DataList1.FindControl("Button2").Visible = false; // DataList1.FindControl("Button3").Visible = false; // DataList1.FindControl("TextBox1").Visible = false; } else { Label1.Visible = true; Label1.Text = "欢迎您！" + Session["admin"].ToString(); Button1.Visible = true; //txt.Attributes.Add["href"] = "recall.aspx"; // Button button1 = DataList1.Items[0].FindControl("button1") as Button; // ((Button)DataList1.FindControl("Button2")).Attributes.Add("Visible")= "true"; // ((Button)DataList1.FindControl("Button3")).Attributes["Visible"] = "true"; //(()DataList1.FindControl("TextBox1")).Attributes["Visible"] = "true"; foreach (DataListItem item in DataList1.Items) { Button btn = (Button)item.FindControl("Button7"); btn.Visible = true; } foreach (DataListItem item in DataList1.Items) { Button btn = (Button)item.FindControl("Button8"); btn.Visible = true; } foreach (DataListItem item in DataList1.Items) { FredCK.FCKeditorV2.FCKeditor f2 = (FredCK.FCKeditorV2.FCKeditor)item.FindControl("FCKeditor2"); //TextBox tb = (TextBox)item.FindControl("TextBox1"); f2.Visible = true; } //((Button)DataList1.FindControl("Button2")).Visible = true; //((TextBox)this.DataList1.FindControl("Button2")).Visible = true; //DataList1.FindControl("Button3").Visible = true; // DataList1.FindControl("TextBox1").Visible = true; // Datalist1.Item.FindControl("TextBox1"); // Button b2=(Button)this.FindControl("Button2"); // b2.Visible = true; // Button2.visible='<%#this.Session["UserName"].ToString()=="管理员"%>' } // DataSet ds = new DataSet(); // ds.ReadXml (s) if(DataList1.FindControl(ID = "Label6")!=null) { DataList1.FindControl(ID = "Label5").Visible = true; DataList1.FindControl(ID = "Label6").Visible = true; // DataList1.FindControl(ID="Label6").Text="Eval("repcontent")"; } if (Session["username"] != null) { LinkButton3.Enabled = false; } if (Session["admin"] != null) { LinkButton1.Enabled = false; LinkButton2.Enabled = false; } } //首页 protected void lnkbtnFirst_Click(object sender, EventArgs e) { this.labNowPage.Text = "1"; this.Bind(2, Convert.ToInt32(labNowPage.Text)); } //上一页 protected void lnkbtnFront_Click(object sender, EventArgs e) { this.labNowPage.Text = Convert.ToString(Convert.ToInt32(this.labNowPage.Text) - 1); this.Bind(2, Convert.ToInt32(labNowPage.Text)); } //下一页 protected void lnkbtnNext_Click(object sender, EventArgs e) { this.labNowPage.Text = Convert.ToString(Convert.ToInt32(this.labNowPage.Text) + 1); this.Bind(2, Convert.ToInt32(labNowPage.Text)); } //尾页 protected void lnkbtnLast_Click(object sender, EventArgs e) { this.labNowPage.Text = this.labCount.Text; this.Bind(2, Convert.ToInt32(labNowPage.Text)); } //public PagedDataSource objPds = new PagedDataSource(); //使用分页类 protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { this.Bind(2,Convert.ToInt32(labNowPage.Text)); }