ADO+数据库分页加查询功能

最新推荐文章于 2019-11-04 16:21:58 发布

成长的小码农

最新推荐文章于 2019-11-04 16:21:58 发布

阅读量669

点赞数

分类专栏： ado.net Sql Server

本文链接：https://blog.csdn.net/zhs954838550/article/details/8670479

版权

ado.net 同时被 2 个专栏收录

15 篇文章 0 订阅

订阅专栏

Sql Server

2 篇文章 0 订阅

订阅专栏

前台代码

后台代码

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;
using System.Text;

namespace 新闻系统3
{
    public partial class fenye2 : System.Web.UI.Page
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        int pagesize = 3;
        //int pageindex = 1;
        //int pagelastindex=0;
        protected void Page_Load(object sender, EventArgs e)
        {
            PageCount();
            if (!IsPostBack)
            {
                ViewState["pageindex"] = 1;
                txtselect.Text = "";
                DataLoad();
            }

        }
        private void PageCount()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select count(*) from T_News where NewsTitle like @newskey or NewsContent like @newskey";
                    cmd.Parameters.Add(new SqlParameter("@newskey","%" + txtselect.Text + "%"));
                    int pagecount=Convert.ToInt32(cmd.ExecuteScalar());
                    if (pagecount % pagesize == 0)
                    {
                       ViewState["pagelastindex"] = pagecount / pagesize;
                    }
                    else
                    {
                        ViewState["pagelastindex"] = pagecount / pagesize + 1;
                    }
                }
            }
        }

        private void DataLoad()
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select top (@pageSize) * from T_News where (NewsTitle like @newskey or NewsContent like @newskey) and Id not in (select top (@pageSize* (@pageIndex-1)) Id from T_News where NewsTitle like @newskey or NewsContent like @newskey ORDER by Id) order by Id";
                    cmd.Parameters.Add(new SqlParameter("@pageSize", pagesize));
                    cmd.Parameters.Add(new SqlParameter("@pageIndex",Convert.ToInt32(ViewState["pageindex"])));
                    cmd.Parameters.Add(new SqlParameter("@newskey", "%" + txtselect.Text + "%"));
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        StringBuilder sb1 = new StringBuilder();
                        sb1.Append("<table border=1>");
                        sb1.Append("<tr><td>标题</td><td>内容</td><td>创建时间</td></tr>");

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                        sb1.Append("<tr>");
                        sb1.Append("<td>" + dt.Rows[i]["NewsTitle"] + "</td>");
                        sb1.Append("<td>" + dt.Rows[i]["NewsContent"] + "</td>");
                        sb1.Append("<td>" + dt.Rows[i]["CreateTime"] + "</td>");
                        sb1.Append("</tr>");
                        }
                        sb1.Append("</table>");
                        divResult.InnerHtml = sb1.ToString();
                    }
                }
            }
        }

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

        protected void btnPre_Click(object sender, EventArgs e)
        {
            int pageindex = Convert.ToInt32(ViewState["pageindex"]);
            if (pageindex > 1)
            {
                pageindex--;
                ViewState["pageindex"] = pageindex;
                DataLoad();
            }
            else
            {
                Response.Write("这已经是第一页了！");
            }

        }

        protected void btnNext_Click(object sender, EventArgs e)
        {
            int pageindex = Convert.ToInt32(ViewState["pageindex"]);
            if (pageindex <Convert.ToInt32(ViewState["pagelastindex"]))
            {
                pageindex++;
                ViewState["pageindex"] = pageindex;
                DataLoad();
            }
            else
            {
                Response.Write("这已经是最后一页了！");
            }

        }

        protected void btnLast_Click(object sender, EventArgs e)
        {
            ViewState["pageindex"] = ViewState["pagelastindex"];
            DataLoad();
        }

        protected void btnselect_Click(object sender, EventArgs e)
        {
            DataLoad();
        }
    }
}