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
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/qingnianxu/p/6903945.html