动态生成table并兑现分页效果

动态生成table并实现分页效果

前台代码:

        <table style="width: 100%;">
            <tr>
                <td>
               搜索字：     <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="搜索" OnClick="Button1_Click" />
                    </td>
            </tr>
            <tr>
                <td>
                   <div id="divmain" runat="server"> </div>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Button ID="btnFirst" runat="server" Text="首页" OnClick="btnFirst_Click" />
                      <asp:Button ID="btnPro" runat="server" Text="上一页" OnClick="btnPro_Click" />
                      <asp:Button ID="btnNext" runat="server" Text="下一页" OnClick="btnNext_Click" />
                      <asp:Button ID="btnLast" runat="server" Text="末页" OnClick="btnLast_Click" />
                &nbsp;<asp:Label ID="labCountPage" runat="server"></asp:Label>
                    <asp:Label ID="labpage" runat="server"></asp:Label>
                    <asp:TextBox ID="TextBox2" runat="server" Width="16px"></asp:TextBox>
                    <asp:Button ID="btnGo" runat="server" OnClick="btnGo_Click" Text="Go" />
                </td>

            </tr>
        </table>

 

后台代码:

 

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                ViewState["pageIndex"] = 1;
                dataPage();
            }
        }

        private void dataPage()
        {
            string constr = @"data source=.;initial catalog=News;user id=sa;password=111111;";
            SqlConnection conn = new SqlConnection(constr);
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();

            #region 用存储过程实现数据分页效果

                //cmd.CommandType = CommandType.StoredProcedure;
                //cmd.CommandText = "Pro_SelectNewsPage";
                //cmd.Parameters.AddWithValue("@pageIndex", Convert.ToInt32(ViewState["pageIndex"]));
                //cmd.Parameters.AddWithValue("@pageSize", 3);
                //SqlParameter parm = new SqlParameter("@count", SqlDbType.Int);
                //cmd.Parameters.Add(parm);
                //parm.Direction = ParameterDirection.Output;
            #endregion

            #region 传递SQl语句实现分页并对搜索实现分页默认显示两条记录
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select top 2 * from (select * from T_News where NewsTitle like @title) as t where t.Id not in(select top ((@pageIndex-1)*2) t.Id from (select * from T_News where NewsTitle like @title) as t) order by t.Id";
            cmd.Parameters.AddWithValue("@pageIndex", Convert.ToInt32(ViewState["pageIndex"]));
            cmd.Parameters.AddWithValue("@title","%"+TextBox1.Text.Trim()+"%");
            #endregion
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            //ViewState["pageCount"] = parm.Value;

            cmd.CommandText = "select count(*) from T_News where NewsTitle like @titles";
            cmd.Parameters.AddWithValue("@titles", "%" + TextBox1.Text.Trim() + "%");
           int sum = Convert.ToInt32(cmd.ExecuteScalar());
           if (sum % 2 == 0)
           {
               ViewState["pageCount"] = sum/2;
           }
           else
           {
               ViewState["pageCount"] = sum / 2 + 1;
           }
            conn.Close();
            conn.Dispose();

            StringBuilder sb = new StringBuilder();
            sb.Append("<table>");
            foreach (DataRow row in dt.Rows)
         {
                sb.Append("<tr><td>"+row["NewsTitle"].ToString()+"</td>");
                sb.Append("<td>" + row["NewsContent"].ToString() + "</td>");
                sb.Append("<td>" + row["CreateTime"].ToString() + "</td></tr>");
         }
            sb.Append("</table>");
            divmain.InnerHtml = sb.ToString();
            labCountPage.Text = "总共" + ViewState["pageCount"] + "页";
            labpage.Text = "当前第" + ViewState["pageIndex"] + "页";
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ViewState["pageIndex"] = "1";
            dataPage();
        }

        protected void btnFirst_Click(object sender, EventArgs e)
        {
            ViewState["pageIndex"] = "1";
            dataPage();
        }

        protected void btnPro_Click(object sender, EventArgs e)
        {
            int index = Convert.ToInt32(ViewState["pageIndex"]);
            index--;
            if (index >= 1)
            {
                ViewState["pageIndex"] = index;
                dataPage();
            }
        }

        protected void btnNext_Click(object sender, EventArgs e)
        {
            int index = Convert.ToInt32(ViewState["pageIndex"]);
            index++;
            if (index <= Convert.ToInt32(ViewState["pageCount"]))
            {
                ViewState["pageIndex"] = index;
                dataPage();
            }
        }

        protected void btnLast_Click(object sender, EventArgs e)
        {
            ViewState["pageIndex"] =ViewState["pageCount"];
            dataPage();
        }

        protected void btnGo_Click(object sender, EventArgs e)
        {
            int i=Convert.ToInt32(TextBox2.Text);
            if (i > 0 && i <= Convert.ToInt32(ViewState["pageCount"]))
            {
                ViewState["pageIndex"] = i;
                dataPage();
            }

        }