前台代碼:
<table> <tr> <td> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="Id" HeaderText="編号" /> <asp:BoundField DataField="NewsTitle" HeaderText="标題" /> <asp:BoundField DataField="NewsContent" HeaderText="内容" /> <asp:BoundField DataField="RealName" HeaderText="建立者" /> <asp:BoundField DataField="CreateTime" DataFormatString="{0:yyyy-mm-dd hh:mm:ss}" HeaderText="建立時間" /> <asp:BoundField DataField="ClassName" HeaderText="類型" /> </Columns> </asp:GridView> </td> </tr> <tr> <td> <asp:LinkButton ID="lbtnFirst" runat="server" OnClick="lbtnFirst_Click">第一頁</asp:LinkButton> <asp:LinkButton ID="lbtnProc" runat="server" OnClick="lbtnProc_Click">上一頁</asp:LinkButton> <asp:LinkButton ID="lbtnNext" runat="server" OnClick="lbtnNext_Click">下一頁</asp:LinkButton> <asp:LinkButton ID="lbtnLast" runat="server" OnClick="lbtnLast_Click">最後一頁</asp:LinkButton> </td> </tr> </table>
背景代碼:
string constr = "data source=.;initial catalog=News;user id=sa;password=111111;"; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { ViewState["pageIndex"] = 1; DataPage();
} }
private void DataPage() { SqlConnection conn = new SqlConnection(constr); conn.Open(); SqlCommand cmd = conn.CreateCommand(); // string sql = "select T1.Id,T1.NewsTitle,SUBSTRING(T1.NewsContent,0,20) as NewsContent,T2.RealName ,T1.CreateTime,T3.ClassName from T_News1 T1 join T_User T2 on T1.NewsCreator=T2.UserId join T_NewsClass T3 on T1.ClassId=T3.ClassId"; string sql = " select T1.Id,T1.NewsTitle,SUBSTRING(T1.NewsContent,0,20) as NewsContent,T2.RealName,T1.CreateTime,T3.ClassName from (select ROW_NUMBER() over (order by Id) as rownumber,* from T_News) T1 join T_User T2 on T1.NewsCreator=T2.UserId join T_NewsClass T3 on T1.ClassId=T3.ClassId where rownumber>(@pageIndex-1)*@pageSize and rownumber<=@pageIndex*@pageSize"; cmd.Parameters.AddWithValue("@pageSize", 3); cmd.Parameters.AddWithValue("@pageIndex", Convert.ToInt32(ViewState["pageIndex"])); cmd.CommandText = sql; SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); adapter.Fill(dt);
string sql1 = "select count(*) from T_News"; cmd.CommandText = sql1; int i =Convert.ToInt32(cmd.ExecuteScalar()); if (i % 3 == 0) { ViewState["pageCount"] = i / 3; } else { ViewState["pageCount"] = i / 3+1; } conn.Close(); conn.Dispose();
GridView1.DataSource = dt; GridView1.DataBind(); }
protected void lbtnFirst_Click(object sender, EventArgs e) { ViewState["pageIndex"] = 1; DataPage(); }
protected void lbtnProc_Click(object sender, EventArgs e) { int i=Convert.ToInt32(ViewState["pageIndex"]) ; if (i>1) { i--; ViewState["pageIndex"] = i; DataPage(); } }
protected void lbtnNext_Click(object sender, EventArgs e) { int i = Convert.ToInt32(ViewState["pageIndex"]); if (i <Convert.ToInt32(ViewState["pageCount"])) { i++; ViewState["pageIndex"] = i; DataPage(); } }
protected void lbtnLast_Click(object sender, EventArgs e) { ViewState["pageIndex"] = ViewState["pageCount"]; DataPage(); }