• 2017-5-25 WebForm中分页组合查询合并使用


    1.html页面代码:

    <body>
        <form id="form1" runat="server">
    
             <br /> <br />
            用户名:<asp:TextBox ID="Text_Uname" runat="server"></asp:TextBox>
            成绩:<asp:DropDownList ID="Drop_Score" runat="server">
                 <asp:listitem text="全部成绩" Value="全部成绩"></asp:listitem>
                  <asp:listitem text="60分以下" Value="Score <60"></asp:listitem>
                <asp:listitem Text="60分至70分" Value="Score >=60 and Score <70"></asp:listitem>
                <asp:listitem Text="70分至80分" Value="Score >=70 and Score <80"></asp:listitem>
                <asp:listitem Text="80分至90分" Value="Score >=80 and Score <90"></asp:listitem>
                <asp:listitem Text="90分以上" Value="Score >=90"></asp:listitem>
               </asp:DropDownList>
            班级:<asp:DropDownList ID="Drop_Class" runat="server">
                
                <asp:ListItem Text="语文" Value="C001"></asp:ListItem>
                <asp:ListItem Text="数学" Value="C002"></asp:ListItem>
                <asp:ListItem Text="计算机" Value="C003"></asp:ListItem>
                <asp:ListItem Text="英语" Value="C004"></asp:ListItem>
               </asp:DropDownList>
            <asp:Button ID="But_Cha" runat="server" Text="Button" /><br />
            <asp:Literal ID="Literal3" runat="server"></asp:Literal>
             <br /> <br />
    
         <table style="text-align: center; background-color: red; color: black;  100%">
                <tr>
                    <td>Ids</td>
                    <td>用户名</td>
                    <td>密码</td>
                    <td>内容</td>
                    <td>分数</td>
                    <td>班级</td>
                </tr>
    
                <asp:Repeater ID="Repeater1" runat="server">
                    <ItemTemplate>
                        <tr style="background-color: white;">
                            <td><%#Eval("Ids") %></td>
                            <td><%#Eval("UserName") %></td>
                            <td><%#Eval("PassWord") %></td>
                            <td><%#Eval("Title") %></td>
                             <td><%#Eval("Score") %></td>
                            <td><%#Eval("Class") %></td>
    
                        </tr>
                    </ItemTemplate>
                </asp:Repeater>
            </table>
            <div style="text-align:center;">
                当前[<asp:Label ID="Label1" runat="server" Text="1"></asp:Label>]页,
                共[<asp:Label ID="Label2" runat="server" Text="1"></asp:Label>]页  
                <asp:Button ID="But_First" runat="server" Text="首页" />
                <asp:Button ID="But_Shang" runat="server" Text="上一页" />
                <asp:Button ID="But_Next" runat="server" Text="下一页" />
                <asp:Button ID="But_End" runat="server" Text="尾页" />
                <asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList>
                <asp:Button ID="But_Drop" runat="server" Text="跳转" />
    
            </div>
        </form>
    </body>
    View Code

    后台代码:

     int PageCount = 5;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Repeater1.DataSource = new abData().selectIds(PageCount, 1);
                Repeater1.DataBind();
                for (int i = 1; i <= SumPageCount(); i++)
                {
                    ListItem li = new ListItem(i.ToString(), i.ToString());
                    DropDownList1.Items.Add(li);
                }
    
            }
            Label2.Text = SumPageCount().ToString();
            //组合查询事件
            But_Cha.Click += But_Cha_Click;
    
    
    
            //分页的点击事件
            But_Drop.Click += But_Drop_Click;
            But_End.Click += But_End_Click;
            But_Next.Click += But_Next_Click;
            But_Shang.Click += But_Shang_Click;
            But_First.Click += But_First_Click;
    
    
        }
        //查询按钮事件,并且以分页的形式显示
        void But_Cha_Click(object sender, EventArgs e)
        {
            Repeater1.DataSource = chaxun(1);
            Repeater1.DataBind();
            Label1.Text = "1";
            Label2.Text = aaa().ToString();
    
            DropDownList1.Items.Clear();
            for (int i = 1; i <= aaa(); i++)
            {
    
                ListItem li = new ListItem(i.ToString(), i.ToString());
                DropDownList1.Items.Add(li);
    
            }
           
        }
    
        public List<ab> chaxun(int nextNumber )
        {
            Hashtable hh = new Hashtable();
            int count = 0;
            string sql = "select top " + PageCount + " * from ab ";
            string sql2 = "";
            if (Text_Uname.Text.Trim().Length > 0)
            {
                sql2 += "where UserName like @a ";
                hh.Add("@a", "%" + Text_Uname.Text.Trim() + "%");
                count++;
            }
            if (Drop_Score.SelectedValue != "null")
            {
                if (Drop_Score.SelectedValue == "全部成绩")
                {
    
                }
                else
                {
                    if (count > 0)
                    {
                        sql2 += "and " + Drop_Score.SelectedValue + " ";
                    }
                    else
                    {
                        sql2 += "where " + Drop_Score.SelectedValue + " ";
                    }
                    count++;
                }
            }
            if (Drop_Class.SelectedValue != "null")
            {
                if (count > 0)
                {
                    sql2 += "and Class='" + Drop_Class.SelectedValue + "' ";
                }
                else
                {
                    sql2 += "where Class='" + Drop_Class.SelectedValue + "' ";
                }
                count++;
            }
    
            //获取当前页数
           
            //最终查询的sql3语句
            string sql3 = "";
            if (count > 0)
            {
                sql3 = sql + sql2 + "and Ids not in (select top " + (nextNumber-1) * PageCount + " Ids from ab " + sql2 + ")";
            }
            else
            {
                sql3 = sql + sql2;
            }
            Literal3.Text = sql3;
    
            List<ab> ablist = new abData().selectZHCX(sql3, hh);
    
            return ablist;
        }
        //页面跳转
        void But_Drop_Click(object sender, EventArgs e)
        {
            int page = Convert.ToInt32(DropDownList1.SelectedValue);
            if (page == aaa())
            {
                But_Next.Visible = false;
                But_Shang.Visible = true;
            }
            else if (page == 1)
            {
                But_Shang.Visible = false;
                But_Next.Visible = true;
            }
            else
            {
                But_Shang.Visible = true;
                But_Next.Visible = true;
            }
            List<ab> alist = chaxun(Convert.ToInt32(DropDownList1.SelectedValue));
            Repeater1.DataSource = alist;
            Repeater1.DataBind();
            Label1.Text = DropDownList1.SelectedValue;
            Label2.Text = aaa().ToString();
        }
        //首页
        void But_First_Click(object sender, EventArgs e)
        {
            But_Next.Visible = true;
            List<ab> alist = chaxun(1);
            Repeater1.DataSource = alist;
            Repeater1.DataBind();
            Label1.Text = "1";
            Label2.Text = aaa().ToString();
            
        }
        //上一页
        void But_Shang_Click(object sender, EventArgs e)
        {
            But_Next.Visible = true;
            //上一页
            int page = Convert.ToInt32(Label1.Text) - 1;
            if (page == 1)
            {
             
                But_Shang.Visible = false;
            }
            if (page == 0)
            {
                page = 1;
            }
            Label2.Text = aaa().ToString();
            List<ab> alist = chaxun(page);
            Repeater1.DataSource = alist;
            Repeater1.DataBind();
            Label1.Text = page.ToString();
        }
        //下一页
        void But_Next_Click(object sender, EventArgs e)
        {
            But_Next.Visible = true;
            But_Shang.Visible = true;
            //下一页
            int page = Convert.ToInt32(Label1.Text) +1;
            if (page >= aaa())
            {
                
                But_Next.Visible = false;
            }
            Label2.Text = aaa().ToString();
            List<ab> alist = chaxun(page);
            Repeater1.DataSource = alist;
            Repeater1.DataBind();
            Label1.Text = page.ToString();
        }
        //尾页
        void But_End_Click(object sender, EventArgs e)
        {
            But_Next.Visible = false;
            List<ab> alist = chaxun(aaa());
            Repeater1.DataSource = alist;
            Repeater1.DataBind();
            Label1.Text = aaa().ToString();
            Label2.Text = aaa().ToString();
            
        }
        //计算一共要显示的页数
        public int SumPageCount()
        {
            int sum = 1;
            List<ab> alist = new abData().selectAll();
    
            decimal aa = Convert.ToDecimal(alist.Count) / PageCount;
            sum = Convert.ToInt32(Math.Ceiling(aa));
    
    
            return sum;
        }
    
        //计算组合查询的需要显示的页数
        public int aaa() 
        {
    
            int end = 0;
            Hashtable hh = new Hashtable();
            int count = 0;
            string sql = "select * from ab ";
            string sql2 = "";
            if (Text_Uname.Text.Trim().Length > 0)
            {
                sql2 += "where UserName like @a ";
                hh.Add("@a", "%" + Text_Uname.Text.Trim() + "%");
                count++;
            }
            if (Drop_Score.SelectedValue != "null")
            {
                if (Drop_Score.SelectedValue == "全部成绩")
                {
    
                }
                else
                {
                    if (count > 0)
                    {
                        sql2 += "and " + Drop_Score.SelectedValue + " ";
                    }
                    else
                    {
                        sql2 += "where " + Drop_Score.SelectedValue + " ";
                    }
                    count++;
                }
            }
            if (Drop_Class.SelectedValue != "null")
            {
                if (count > 0)
                {
                    sql2 += "and Class='" + Drop_Class.SelectedValue + "' ";
                }
                else
                {
                    sql2 += "where Class='" + Drop_Class.SelectedValue + "' ";
                }
                count++;
            }
            //组合查询的全部数据
            string sqlend = sql + sql2;
            List<ab> aalist = new abData().selectZHCX2(sqlend, hh);
    
            decimal aa = Convert.ToDecimal(aalist.Count) / PageCount;
            end = Convert.ToInt32(Math.Ceiling(aa));
            return end;
        }
    View Code

    实体类

    public class ab
    {
        public int Ids { get; set; }
        public string UserName { get; set; }
        public string PassWord { get; set; }
        public string Title { get; set; }
        public string Score { get; set; }
        public string Class { get; set; }
    }
    View Code

    数据操作类:

    public class abData
    {
        SqlConnection conn = null;
        SqlCommand cmd = null;
        public abData()
        {
            conn = new SqlConnection("server=.;database=stu0314;user=sa;pwd=123");
            cmd = conn.CreateCommand();
        }
        //查询所有的信息
        public List<ab> selectAll() 
        {
            List<ab> alist = new List<ab>();
            cmd.CommandText = "select * from ab";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows) 
            {
                while (dr.Read()) 
                {
                    ab a = new ab();
                    a.Ids = Convert.ToInt32(dr[0]);
                    a.UserName = dr[1].ToString();
                    a.PassWord = dr[2].ToString();
                    a.Title = dr[3].ToString();
                    a.Score = dr[4].ToString();
                    a.Class = dr[5].ToString();
                    alist.Add(a);
                }
            }
            conn.Close();
    
    
            return alist;
        }
        //单纯分页查询前5行数据的信息
        public List<ab> selectIds(int PageCount,int Page)
        {
            List<ab> alist = new List<ab>();
            cmd.CommandText = "select top "+PageCount+" * from  ab where Ids not in (select top "+PageCount*(Page-1)+" Ids from ab)";
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    ab a = new ab();
                    a.Ids = Convert.ToInt32(dr[0]);
                    a.UserName = dr[1].ToString();
                    a.PassWord = dr[2].ToString();
                    a.Title = dr[3].ToString();
                    a.Score = dr[4].ToString();
                    a.Class = dr[5].ToString();
                    alist.Add(a);
                }
            }
            conn.Close();
    
    
            return alist;
        }
        //按条件查询
        public List<ab> selectAll(string sql,Hashtable hh)
        {
          
            List<ab> alist = new List<ab>();
            cmd.CommandText = sql;
            foreach(string s in hh.Keys)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(s,hh[s]);
            }
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    ab a = new ab();
                    a.Ids = Convert.ToInt32(dr[0]);
                    a.UserName = dr[1].ToString();
                    a.PassWord = dr[2].ToString();
                    a.Title = dr[3].ToString();
                    a.Score = dr[4].ToString();
                    a.Class = dr[5].ToString();
                    alist.Add(a);
                }
            }
            conn.Close();
    
    
            return alist;
        }
        //组合查询前5行数据信息
        public List<ab> selectZHCX(string sql,Hashtable hh1)
        {
            List<ab> alist = new List<ab>();
            cmd.CommandText = sql;
            foreach(string s in hh1.Keys)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(s,hh1[s]);
            }
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    ab a = new ab();
                    a.Ids = Convert.ToInt32(dr[0]);
                    a.UserName = dr[1].ToString();
                    a.PassWord = dr[2].ToString();
                    a.Title = dr[3].ToString();
                    a.Score = dr[4].ToString();
                    a.Class = dr[5].ToString();
                    alist.Add(a);
                }
            }
            conn.Close();
    
    
            return alist;
        }
        //根据组合查询总行数
        public List<ab> selectZHCX2(string sql2, Hashtable hh1)
        {
            List<ab> alist = new List<ab>();
            cmd.CommandText = sql2;
            foreach (string s in hh1.Keys)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue(s, hh1[s]);
            }
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    ab a = new ab();
                    a.Ids = Convert.ToInt32(dr[0]);
                    a.UserName = dr[1].ToString();
                    a.PassWord = dr[2].ToString();
                    a.Title = dr[3].ToString();
                    a.Score = dr[4].ToString();
                    a.Class = dr[5].ToString();
                    alist.Add(a);
                }
            }
            conn.Close();
    
    
            return alist;
        }
    
    
       
    
    }
    View Code
  • 相关阅读:
    Vue 服务器端渲染(一)
    vue笔记 介绍及安装 一
    Node.js 学习笔记 (一) 安装配置
    Java开发中的23种设计模式详解(转)
    spring-boot整合ehcache实现缓存机制
    STM32流水灯
    SD卡封转及管脚说明
    随笔分类
    函数的设计之我见
    让灵魂追得上我们疲惫的身体
  • 原文地址:https://www.cnblogs.com/qingnianxu/p/6903945.html
Copyright © 2020-2023  润新知