2017-5-24 WebForm中分页查询，组合查询

分页查询   组合查询

1.用到的实体类：

 

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; }
}

 

2.数据操作类：

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;
    }
}

3.分页查询的html页面代码：

<body>
    <form id="form1" runat="server">
        <table style="text-align: center; background-color: red; color: black; width: 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:Literal ID="Literal1" runat="server" Text="1"></asp:Literal>]页，
            共[<asp:Literal ID="Literal2" runat="server" Text="1"></asp:Literal>]页  
            <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>

分页查询的后台代码：

public partial class _Default : System.Web.UI.Page
{
    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);
            }
            
        }
        Literal2.Text = SumPageCount().ToString();

        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_Drop_Click(object sender, EventArgs e)
    {
        int page = Convert.ToInt32( DropDownList1.SelectedValue);
        if (page == 7) 
        {
            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 = new abData().selectIds(PageCount, page);
        Repeater1.DataSource = alist;
        Repeater1.DataBind();
        Literal1.Text = DropDownList1.SelectedValue;
    }
    //首页
    void But_First_Click(object sender, EventArgs e)
    {
        But_Next.Visible = true;
        List<ab> alist = new abData().selectIds(PageCount, 1);
        Repeater1.DataSource = alist;
        Repeater1.DataBind();
        Literal1.Text = "1";
    }
    //上一页
    void But_Shang_Click(object sender, EventArgs e)
    {
        But_Next.Visible = true;
        //上一页
        int page = Convert.ToInt32(Literal1.Text) - 1;
        if (page ==1)
        {
            But_Shang.Visible = false ;
        }
        if (page == 0) 
        {
            page = 1;
        }
        List<ab> alist = new abData().selectIds(PageCount, page);
        Repeater1.DataSource = alist;
        Repeater1.DataBind();
        Literal1.Text = page.ToString();
    }
    //下一页
    void But_Next_Click(object sender, EventArgs e)
    {
        But_Next.Visible = true;
        But_Shang.Visible = true;
        //下一页
        int page = Convert.ToInt32(Literal1.Text) + 1;
        if (page >= SumPageCount()) 
        {
            But_Next.Visible = false;
        }
        List<ab> alist = new abData().selectIds(PageCount,page);
        Repeater1.DataSource = alist;
        Repeater1.DataBind();
        Literal1.Text = page.ToString();
    }
    //尾页
    void But_End_Click(object sender, EventArgs e)
    {
        But_Next.Visible = false;
        List<ab> alist = new abData().selectIds(PageCount, SumPageCount());
        Repeater1.DataSource = alist;
        Repeater1.DataBind();
        Literal1.Text = SumPageCount().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;
    }



}

4.组合查询的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="Button1" 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; width: 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:Literal ID="Literal1" runat="server" Text="1"></asp:Literal>]页，
            共[<asp:Literal ID="Literal2" runat="server" Text="1"></asp:Literal>]页  
            <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>

组合查询的后台代码：

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Repeater1.DataSource = new abData().selectAll();
            Repeater1.DataBind();
        }
        Button1.Click += Button1_Click;
    }

    void Button1_Click(object sender, EventArgs e)
    {
        Hashtable hh = new Hashtable();
        string sql = "select * from ab ";
        int count = 0;
        //匹配用户名
        if (Text_Uname.Text.Trim().Length> 0) 
        {
            sql += "where UserName like @a ";
            hh.Add("@a","%"+ Text_Uname.Text.Trim() + "%");
            count++;
        }
        //匹配成绩
        if(Drop_Score.SelectedValue!=null)
        {
            foreach(ListItem li in Drop_Score.Items )
            {
                if (li.Value == "全部成绩" && li.Selected==true)
                {
                    break;
                }
                else
                {
                    if (count > 0)
                    {
                        sql += "and " + Drop_Score.SelectedValue + " ";
                        count++;
                        break;
                    }
                    else
                    {
                        sql += "where " + Drop_Score.SelectedValue + " ";
                        count++;
                        break;
                    }
                    
                } 
                
            }
            
        }
        //匹配班级
        if (Drop_Class.SelectedValue != null) 
        {
            if (count > 0)
            {
                sql += "and Class = '" + Drop_Class.SelectedValue + "'";
            }
            else 
            {
                sql += "where Class = '" + Drop_Class.SelectedValue + "'";
            }
        }
        Literal3.Text = sql;
        List<ab> alist = new abData().selectAll(sql,hh);
        Repeater1.DataSource = alist;
        Repeater1.DataBind();
    }
}