ASP.NET 分页+组合查询 练习

时间：2023-03-09 15:02:38
ASP.NET 分页+组合查询 练习

分页和组合查询都是通过拼接SQL语句到数据库查询进行实现

到汽车表(car)中查询 ，汽车表选取了“编号 code”，“车名 name”，“日期 time”，“油耗 oil ”，“马力 powers”这几列

分页查询语句：每页只显示五条数据

查询前五条数据： select top 5 from car

点击下一页按钮：select top 5 from car where code not in(select top 5 code from car)//第二页的时候通过编号，查询前五条之后的数据，第三页括号里top 10，每加一页跳过前面查过的条数，一次类推，需要定义变量等于1，每次加一页给这个变量重新赋值，让5乘以这个变量

上一页按钮也是用此方法

组合查询语句：根据车名、油耗、和马力查询

根据选中的条件进行拼接SQL语句：select * from car where name like '%xxx%' and oil like '%xxx%' and powers > xxx ;

通过选择的任意条件，进行判断，如果前面条件选中，后面跟and xxx，如果前面条件没选中就直接where xxx

分页和组合查询连起来就是：select top 5 from car where  name like '%xxx%' and oil like '%xxx%' and powers > xxx and  code not in(select top 5 code from car);根据不同的选择条件进行拼接，在数据库写一个查询的方法，将拼接好的SQL语句传参到方法中进行执行，调用此方法在前台显示

代码：

数据库实体类：

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

/// <summary>

/// Car 的摘要说明

/// </summary>

public class Car

{

    public Car()

    {

        //

        // TODO: 在此处添加构造函数逻辑

        //

    }

    //封装一个汽车表的实体类

    private string _code;//汽车编号

    public string Code

    {

        get { return _code; }

        set { _code = value; }

    }

    private string _name;//汽车名字

    public string Name

    {

        get { return _name; }

        set { _name = value; }

    }

    private DateTime _time;//出产日期

    public DateTime Time

    {

        get { return _time; }

        set { _time = value; }

    }

    private decimal _oil;//油耗

    public decimal Oil

    {

        get { return _oil; }

        set { _oil = value; }

    }

    private int _powers;//马力

    public int Powers

    {

        get { return _powers; }

        set { _powers = value; }

    }

}

数据库操作类：

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data.SqlClient;

using System.Collections;

/// <summary>

/// CarData 的摘要说明

/// </summary>

public class CarData

{

    SqlConnection conn = null;

    SqlCommand cmd = null;

    public CarData()

    {

        conn = new SqlConnection("server=.;database=netlx;user=sa;pwd=123;");

        cmd = conn.CreateCommand();

    }

    /// <summary>

    /// 查询车辆信息

    /// </summary>

    /// <param name="sql"></param>

    /// <returns></returns>

    public List<Car> select(string sql,Hashtable has)

    {

        List<Car> list = new List<Car>();

        cmd.CommandText = sql;

        cmd.Parameters.Clear();

        foreach (string s in has.Keys)

        {

            cmd.Parameters.Add(s, has[s]);

        }

        conn.Open();

        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)

        {

            while (dr.Read())

            {

                Car cc = new Car();

                cc.Code = dr["code"].ToString();

                cc.Name = dr["name"].ToString();

                cc.Time = Convert.ToDateTime(dr["time"]);

                cc.Oil = Convert.ToDecimal(dr["oil"]);

                cc.Powers = Convert.ToInt32(dr["powers"]);

                list.Add(cc);

            }

        }

        conn.Close();

        return list;

    }

}

页面前台代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default1.aspx.cs" Inherits="Default1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>

    <title></title>

<style type="text/css">

        #Bt {

            position: relative;

            font-family: 黑体;

            font-size: 50px;

            text-align: center;

            letter-spacing: 5px; /*字符间距*/

        }

        #Cx {

        height:30px;

        margin-top:20px;

        }

        table {

            background-color: #07066f;

            width: 100%;

        }

        #Bt1 {

            font-family: 微软雅黑;

            font-size: 25px;

            color: white;

            text-align: center;

        }

        #Nr {

            font-family: 微软雅黑;

            font-size: 25px;

            background-color: white;

            text-align: center;

        }

        td {

            padding: 10px;

        }

    </style>

</head>

<body>

    <div id="Bt">汽车信息展示</div>

    <form id="form1" runat="server">

        <div id="Cx">

            车名：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

            油耗：<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

            马力：<asp:DropDownList ID="Tjf" runat="server">

                <asp:ListItem>&gt;</asp:ListItem>

                <asp:ListItem>&lt;</asp:ListItem>

                <asp:ListItem>=</asp:ListItem>

            </asp:DropDownList><asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

            <asp:Button ID="Select" runat="server" Text="确 定" />

        </div>

        <asp:Repeater ID="Repeater1" runat="server">

            <HeaderTemplate>

                <table>

                    <tr id="Bt1">

                        <td>编号</td>

                        <td>车名</td>

                        <td>日期</td>

                        <td>油耗</td>

                        <td>马力</td>

                    </tr>

            </HeaderTemplate>

            <ItemTemplate>

                <tr id="Nr">

                    <td><%#Eval("Code") %></td>

                    <td><%#Eval("Name") %></td>

                    <td><%#Eval("Time") %></td>

                    <td><%#Eval("Oil") %></td>

                    <td><%#Eval("Powers") %></td>

                </tr>

            </ItemTemplate>

            <FooterTemplate>

                </table>

            </FooterTemplate>

        </asp:Repeater>

        当前是第[<asp:Label ID="Yecode" runat="server" Text="Label"></asp:Label>]页

        <asp:LinkButton ID="First" runat="server">首页</asp:LinkButton>

        <asp:LinkButton ID="Up" runat="server">上一页</asp:LinkButton>

        <asp:LinkButton ID="Down" runat="server">下一页</asp:LinkButton>

        <asp:LinkButton ID="Over" runat="server">末页</asp:LinkButton>

        <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"></asp:DropDownList>

    </form>

</body>

</html>

后台代码：

using System;

using System.Collections;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

public partial class Default1 : System.Web.UI.Page

{

    int count = ;

    Hashtable hs = new Hashtable();//创建一个全局的哈希表集合，用来放SQL语句中的条件，防攻击

    protected void Page_Load(object sender, EventArgs e)

    {

        Select.Click += Select_Click;//条件查询的确定按钮

        Down.Click += Down_Click;//下一页按钮点击事件

        Up.Click += Up_Click;//上一页按钮点击事件

        First.Click += First_Click;//首页按钮点击事件

        Over.Click += Over_Click;//末页按钮点击事件

        DropDownList1.SelectedIndexChanged += DropDownList1_SelectedIndexChanged;//下拉列表选中提交事件

        if (IsPostBack == false)

        {

            Repeater1.DataSource = new CarData().select(TSQL(),hs);

            Repeater1.DataBind();

            Yecode.Text = "";

            First.Enabled = false;

            Up.Enabled = false;

            for (int i = ; i <= MaxNumber(); i++)

            {

                ListItem li = new ListItem(i.ToString(), i.ToString());

                DropDownList1.Items.Add(li);

            }

        }

    }

    //下拉列表选中提交事件

    void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

    {

        int xy = Convert.ToInt32(DropDownList1.SelectedItem.Value);

        Repeater1.DataSource = new CarData().select(TSQL(xy),hs);

        Repeater1.DataBind();

        Yecode.Text = xy.ToString();

        if (Yecode.Text == "")

        {

            First.Enabled = false;

            Up.Enabled = false;

            Over.Enabled = true;

            Down.Enabled = true;

        }

        else if (Yecode.Text == MaxNumber().ToString())

        {

            First.Enabled = true;

            Up.Enabled = true;

            Over.Enabled = false;

            Down.Enabled = false;

        }

        else

        {

            First.Enabled = true;

            Up.Enabled = true;

            Over.Enabled = true;

            Down.Enabled = true;

        }

    }

    //末页按钮点击事件

    void Over_Click(object sender, EventArgs e)

    {

        Repeater1.DataSource = new CarData().select(TSQL(MaxNumber()),hs);

        Repeater1.DataBind();

        Yecode.Text = MaxNumber().ToString();

        DropDownList1.SelectedIndex = Convert.ToInt32(Yecode.Text) - ;

        First.Enabled = true;

        Up.Enabled = true;

        Down.Enabled = false;

        Over.Enabled = false;

    }

    //首页按钮点击事件

    void First_Click(object sender, EventArgs e)

    {

        Repeater1.DataSource = new CarData().select(TSQL(),hs);

        Repeater1.DataBind();

        Yecode.Text = "";

        DropDownList1.SelectedIndex = ;

        First.Enabled = false;

        Up.Enabled = false;

        Down.Enabled = true;

        Over.Enabled = true;

    }

    //上一页按钮点击事件

    void Up_Click(object sender, EventArgs e)

    {

        int Yeshu = Convert.ToInt32(Yecode.Text) - ;

        Yecode.Text = Yeshu.ToString();

        DropDownList1.SelectedIndex = Convert.ToInt32(Yecode.Text) - ;

        Repeater1.DataSource = new CarData().select(TSQL(Yeshu),hs);

        Repeater1.DataBind();

        if (Yecode.Text == "")

        {

            First.Enabled = false;

            Up.Enabled = false;

        }

        Down.Enabled = true;

        Over.Enabled = true;

    }

    //下一页按钮点击事件

    void Down_Click(object sender, EventArgs e)

    {

        int Yeshu = Convert.ToInt32(Yecode.Text) + ;

        Yecode.Text = Yeshu.ToString();

        DropDownList1.SelectedIndex = Convert.ToInt32(Yecode.Text) - ;

        Repeater1.DataSource = new CarData().select(TSQL(Yeshu),hs);

        Repeater1.DataBind();

        if (Yecode.Text == MaxNumber().ToString())

        {

            Down.Enabled = false;

            Over.Enabled = false;

        }

        First.Enabled = true;

        Up.Enabled = true;

    }

    //条件查询的确定按钮

    void Select_Click(object sender, EventArgs e)

    {

        Repeater1.DataSource = new CarData().select(TSQL(),hs);

        Repeater1.DataBind();

        Yecode.Text = "";

        //调用组合查询没有进行分页的拼接SQL语句的方法去数据库进行查询

        List<Car> data = new CarData().select(Tsql(),hs);

        if (data.Count <= count)//如果查询到的数据的数量少于每页显示的条数

        {

            First.Enabled = false;//则这些翻页按钮就不可用

            Up.Enabled = false;

            Down.Enabled = false;

            Over.Enabled = false;

            DropDownList1.Items.Clear();//将选择页面的下拉列表清空

            ListItem li = new ListItem("", "");//只显示1

            DropDownList1.Items.Add(li);

        }

        else //如果大于每页显示的条数

        {

            //则下一页和末页可用。因为一上来首页和上一页是不可用的，当点击下一页或者末页，就会触发点击事件，则其他按钮就会可用

            Down.Enabled = true;

            Over.Enabled = true;

            DropDownList1.Items.Clear();//将选择页面的下拉列表清空

            for (int i = ; i <= MaxNumber(); i++) //根据查出来的最大页数，重新赋值

            {

                ListItem li = new ListItem(i.ToString(), i.ToString());

                DropDownList1.Items.Add(li);

            }

        }

    }

    //根据条件拼接一个SQL语句方法

    public string TSQL(int Yeshu)

    {

        hs.Clear();

        int ccc = ;

        string f1="";

        string f2="";

        string f3="";

        string sql = "select top "+count+" * from car";

        if (TextBox1.Text != "")

        {

            sql += " where name like @name ";

            ccc++;

            f1=" where name like @name ";

            hs.Add("@name", "%" + TextBox1.Text + "%");

        }

        if (TextBox2.Text != "")

        {

            if (ccc > )

            {

                sql += " and oil like @oil ";

                f2 = " and oil like @oil ";

            }

            else

            {

                sql += " where oil like @oil ";

                f2 = " where oil like @oil ";

            }

            hs.Add("@oil", "%" + TextBox2.Text + "%");

            ccc++;

        }

        if (TextBox3.Text != "")

        {

            if (ccc > )

            {

                sql += " and powers " + Tjf.SelectedItem.Value + " @powers";

                f3=" and powers " + Tjf.SelectedItem.Value + " @powers";

            }

            else

            {

                sql += " where powers" + Tjf.SelectedItem.Value + " @powers";

                f3=" where powers" + Tjf.SelectedItem.Value + " @powers";

            }

            hs.Add("@powers", TextBox3.Text);

            ccc++;

        }

        //分页语句

        if (ccc > )

        {

            sql += " and code not in (select top " + count * (Yeshu - ) + " code from car " + f1 + f2 + f3 + ")";

        }

        else

        {

            sql += " where code not in (select top " + count * (Yeshu - ) + " code from car " + f1 + f2 + f3 + ")";

        }

        return sql;

    }

    //查询所有符合条件的数据

    public string Tsql()

    {

        hs.Clear();

        int cc = ;

        string sql = "select * from car";

        if (TextBox1.Text != "")

        {

            sql += " where name like @name ";

            cc++;

            hs.Add("@name", "%" + TextBox1.Text + "%");

        }

        if (TextBox2.Text != "")

        {

            if (cc > )

            {

                sql += " and oil like @oil ";

            }

            else

            {

                sql += " where oil like @oil ";

            }

            hs.Add("@oil", "%" + TextBox2.Text + "%");

            cc++;

        }

        if (TextBox3.Text != "")

        {

            if (cc > )

            {

                sql += " and powers " + Tjf.SelectedItem.Value + " @powers";

            }

            else

            {

                sql += " where powers" + Tjf.SelectedItem.Value + " @powers";

            }

            hs.Add("@powers", TextBox3.Text);

            cc++;

        }

        return sql;

    }

    //根据上面查找数据的数量，除以每页显示的，得到的结果取上限值，得到的是最大页数

    public int MaxNumber()

    {

        List<Car> ccc = new CarData().select(Tsql(),hs);

        double bbb = ccc.Count / (count * 1.0); //总页数，但是是浮点型

        return Convert.ToInt32(Math.Ceiling(bbb));

    }

}

ASP.NET 分页+组合查询 练习

ASP.NET 分页+组合查询 练习

ASP.NET 分页+组合查询 练习

ASP.NET 分页+组合查询 练习

ASP.NET 分页+组合查询 练习

ASP.NET 分页+组合查询 练习

ASP.NET 分页+组合查询 练习

ASP.NET 分页+组合查询 练习

相关文章