分页查询 模糊查询 合体查询

分页查询

分页需要知道一共多少页 和当前页面分部多少页

<table style="100%; background-color: gray; text-align: center;">
                <tr style="color:white;">
                    <td>ids</td>
                    <td>名字</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("name") %></td>
                            <td><%#Eval("oldprice") %></td>
                            <td><%#Eval("newprice") %></td>
                            <td><%#Eval("ku") %></td>
                            <td><%#Eval("pic") %></td>
                            <td><%#Eval("jieshao") %></td>

                        </tr>
                    </ItemTemplate>
                </asp:Repeater>
            </table>
            <br />
            第【<asp:Literal ID="Literal1" runat="server" Text="1"></asp:Literal>】页
             一共有【<asp:Literal ID="Literal2" runat="server" Text="1"></asp:Literal>】页，
            <asp:Button ID="Button1" runat="server" Text="首页" />
            <asp:Button ID="Button2" runat="server" Text="上一页" />
            <asp:Button ID="Button3" runat="server" Text="下一页" />
            <asp:Button ID="Button4" runat="server" Text="尾页" />
        <asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList>
        <asp:Button ID="Button5" runat="server" Text="跳" />

string s = "";
    mb u = null;
    protected void Page_Load(object sender, EventArgs e)
    {
        s = Request["i"];
        if (s != null)
        {
            u = new mbdata().selectmb(s);
            name.Text = u.name;
            jieshao.Text = u.jieshao;
            oldprice.Text ="￥"+  u.oldprice.ToString();
            newprice.Text = "￥" + u.newprice.ToString();
            kucun.Text = u.ku.ToString();
        }
        else
        {
            Response.Redirect("zhuye.aspx");
        }

        
        Button1.Click += Button1_Click;
        Button2.Click += Button2_Click;
    }

    void Button1_Click(object sender, EventArgs e)
    {
        if (Request.Cookies["aa"]!=null)
        {
            mbfuqian uu = new mbfuqian();
            uu.goumai = Request.Cookies["aa"].Value;
            uu.name = u.name;
            uu.price = Convert.ToInt32(u.newprice);
            uu.shu = Convert.ToInt32(TextBox1.Text);
            uu.gong = Convert.ToInt32(u.newprice) * Convert.ToInt32(TextBox1.Text);
            uu.yifu = false;

            new mbfuqiandata().insert(uu);
            Response.Redirect("Default6.aspx");
           
        }
        else
        {
            Response.Redirect("denglu.aspx");
        }
    }

    void Button2_Click(object sender, EventArgs e)
    {
        if (Request.Cookies["aa"] != null)
        {
            mbfuqian uu = new mbfuqian();
            uu.goumai = Request.Cookies["aa"].Value;
            uu.name = u.name;
            uu.price =Convert.ToInt32( u.newprice);
            uu.shu = Convert.ToInt32(TextBox1.Text);
            uu.gong = Convert.ToInt32(u.newprice) * Convert.ToInt32(TextBox1.Text);
            uu.yifu = false;

            new mbfuqiandata().insert(uu);
        }
        else
        {
            Response.Redirect("denglu.aspx");
        }
    }

public class mbdata
{
    SqlConnection conn = null;
    SqlCommand cmd = null;
    public mbdata()
    {
        conn = new SqlConnection("server=.;database=date0216;user=sa;pwd=123;");
        cmd = conn.CreateCommand();
    }
    public List<mb> selectall()
    {
        List<mb> tbs = new List<mb>();
        cmd.CommandText = "select *from mb";
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            mb t = new mb();
            t.ids = Convert.ToInt32(dr["ids"]);
            t.name = dr["name"].ToString();
            t.oldprice = Convert.ToDecimal(dr["oldprice"]);
            t.newprice = Convert.ToDecimal(dr["newprice"]);
            t.ku = Convert.ToInt32(dr["ku"]);
            t.pic = dr["pic"].ToString();
            t.jieshao = dr["jieshao"].ToString();

            tbs.Add(t);
        }
        conn.Close();
        return tbs;
    }

    public mb selectmb(string i)
    {
        mb u = new mb();
        cmd.CommandText = "select * from mb where ids=@a";
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@a",i);
        conn.Open();
        SqlDataReader dr= cmd.ExecuteReader();
        if (dr.HasRows)
        {
            dr.Read();
            u.name = dr["name"].ToString();
            u.newprice = Convert.ToDecimal(dr["newprice"]);
            u.oldprice = Convert.ToDecimal(dr["oldprice"]);
            u.pic = dr["pic"].ToString();
            u.ku = Convert.ToInt32(dr["ku"]);
            u.jieshao = dr["jieshao"].ToString();


        }
        conn.Close();
        return u;
    
    }

    public List<mb> selectye(int a,int b)
    {
        List<mb> tbs = new List<mb>();
        cmd.CommandText = "select top "+a+" * from mb where ids not in(select top "+a*(b-1)+" ids from mb)";
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                mb t = new mb();
                t.ids = Convert.ToInt32(dr["ids"]);
                t.name = dr["name"].ToString();
                t.oldprice = Convert.ToDecimal(dr["oldprice"]);
                t.newprice = Convert.ToDecimal(dr["newprice"]);
                t.ku = Convert.ToInt32(dr["ku"]);
                t.pic = dr["pic"].ToString();
                t.jieshao = dr["jieshao"].ToString();

                tbs.Add(t);
            }
        }
        conn.Close();
        return tbs;
    }

    

    public int selectcount()
    {
        int a = 0;
        cmd.CommandText = "select count(*) from mb";
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        a = Convert.ToInt32(dr[0]);
        conn.Close();
        return a;
    
    }


    



    public List<mb> selectall(string a  )
    {
        List<mb> tbs = null;
        cmd.CommandText = a;
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            tbs = new List<mb>();
            while (dr.Read())
            {
                mb t = new mb();
                t.ids = Convert.ToInt32(dr["ids"]);
                t.name = dr["name"].ToString();
                t.oldprice = Convert.ToDecimal(dr["oldprice"]);
                t.newprice = Convert.ToDecimal(dr["newprice"]);
                t.ku = Convert.ToInt32(dr["ku"]);
                t.pic = dr["pic"].ToString();
                t.jieshao = dr["jieshao"].ToString();

                tbs.Add(t);
            }
        }
        conn.Close();
        return tbs;
    }


    public int selectallsou(string a)
    {
        int cc = 0;
        cmd.CommandText = a;
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            
            while (dr.Read())
            {
                cc++;
            }
        }
        conn.Close();
        return cc;
    }

}

模糊查询

模糊查询用的字符串拼接

<div>
        <br />
        买啥：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        价格：<asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem Text="大于" Value=">"></asp:ListItem>
            <asp:ListItem Text="小于" Value="<"></asp:ListItem>
            <asp:ListItem Text="等于" Value="="></asp:ListItem>
            <asp:ListItem Text="大于等于" Value=">="></asp:ListItem>
            <asp:ListItem Text="小于等于" Value="<="></asp:ListItem>
           </asp:DropDownList>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <asp:DropDownList ID="DropDownList2" runat="server">
            <asp:ListItem Text="小于" Value="<"></asp:ListItem>
            <asp:ListItem Text="大于" Value=">"></asp:ListItem>
            
            <asp:ListItem Text="等于" Value="="></asp:ListItem>
            <asp:ListItem Text="大于等于" Value=">="></asp:ListItem>
            <asp:ListItem Text="小于等于" Value="<="></asp:ListItem>
           </asp:DropDownList>
        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" Text="搜索" />
        <br />
        <br />


    <table style="100%; background-color: gray; text-align: center;">
                <tr style="color:white;">
                    <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("name") %></td>
                            <td><%#Eval("oldprice") %></td>
                            <td><%#Eval("newprice") %></td>
                            <td><%#Eval("ku") %></td>
                           
                            <td><%#Eval("jieshao") %></td>

                        </tr>
                    </ItemTemplate>
                </asp:Repeater>
            </table>
            <br />
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    </div>

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Repeater1.DataSource = new mbdata().selectall();
            Repeater1.DataBind();
        
        }
        Button1.Click += Button1_Click;
    }

    void Button1_Click(object sender, EventArgs e)
    {
        int count = 0;
        string sql = "select * from mb ";
        if (TextBox1.Text.Trim().Length > 0)
        {
            sql += "where name like '%"+TextBox1.Text.Trim()+"%' ";
            count++;
        }
        if (TextBox2.Text.Trim().Length > 0)
        {
            if (count > 0)
            {
                sql += "and newprice" + DropDownList1.SelectedValue + " " + TextBox2.Text.Trim() + " ";
            }
            else
            {
                sql += "where newprice" + DropDownList1.SelectedValue + " " + TextBox2.Text.Trim() + " ";
            }
            count++;
        }

        if (TextBox3.Text.Trim().Length > 0)
        {
            if (count > 0)
            {
                sql += "and newprice" + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim() + " ";
            }
            else
            {
                sql += "where newprice" + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim() + " ";
            }
            count++;
        }
        List<mb> ulist = new mbdata().selectall(sql);
        Repeater1.DataSource = ulist;
        Repeater1.DataBind();
        
        if (ulist == null)
        {
            Label1.Text = "咱库里没有这个东西";

        }
        
       
    }

合体注意查询条件

select top 5 * from where ids not in( select top (A*b) ids where name like '姚')and name like '姚'

跳过这个满足这个条件的A*b条   不能直接跳过几条

<div>
       
        <br />
        买啥：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        价格：<asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem Text="大于" Value=">"></asp:ListItem>
            <asp:ListItem Text="小于" Value="<"></asp:ListItem>
            <asp:ListItem Text="等于" Value="="></asp:ListItem>
            <asp:ListItem Text="大于等于" Value=">="></asp:ListItem>
            <asp:ListItem Text="小于等于" Value="<="></asp:ListItem>
           </asp:DropDownList>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        <asp:DropDownList ID="DropDownList2" runat="server">
            <asp:ListItem Text="小于" Value="<"></asp:ListItem>
            <asp:ListItem Text="大于" Value=">"></asp:ListItem>
            
            <asp:ListItem Text="等于" Value="="></asp:ListItem>
            <asp:ListItem Text="大于等于" Value=">="></asp:ListItem>
            <asp:ListItem Text="小于等于" Value="<="></asp:ListItem>
           </asp:DropDownList>
        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
        <asp:Button ID="Button1" runat="server" name="user.uptexpireTime" Text="搜索" />
        <br />
        <br />


    <table style="100%; background-color: gray; text-align: center;">
                <tr style="color:white;">
                    <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("name") %></td>
                            <td><%#Eval("oldprice") %></td>
                            <td><%#Eval("newprice") %></td>
                            <td><%#Eval("ku") %></td>
                           
                            <td><%#Eval("jieshao") %></td>

                        </tr>
                    </ItemTemplate>
                </asp:Repeater>
            </table>
            <br />
        这是第【<asp:Literal ID="Literal1" runat="server" Text="1"></asp:Literal>】页，
        一共【<asp:Literal ID="Literal2" runat="server" Text="1"></asp:Literal>】页；
        <asp:Button ID="Button2" runat="server" Text="首页" />
        <asp:Button ID="Button3" runat="server" Text="上一页" />
        <asp:Button ID="Button4" runat="server" Text="下一页" />
        <asp:Button ID="Button5" runat="server" Text="尾页" />
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
        <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>
    </div>

public partial class chaxun : System.Web.UI.Page
{

    int yecount = 3;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Repeater1.DataSource = sou(1);
            Repeater1.DataBind();

            Literal2.Text = suoyouye().ToString();
        }
        Button5.Click += Button5_Click;
        Button2.Click += Button2_Click;
        Button1.Click += Button1_Click;
        Button4.Click += Button4_Click;
        Button3.Click += Button3_Click;
    }

    void Button5_Click(object sender, EventArgs e)
    {
        Repeater1.DataSource = sou(souye());
        Repeater1.DataBind();
        Literal1.Text = souye().ToString();
    }

    void Button2_Click(object sender, EventArgs e)
    {
        Repeater1.DataSource = sou(1);
        Repeater1.DataBind();
        Literal1.Text = "1";
    }
    //下一页

    //上一页
    void Button3_Click(object sender, EventArgs e)
    {

        int number = Convert.ToInt32(Literal1.Text) - 1;
        if (number == 0)
        {
            return;
        }

        Repeater1.DataSource = sou(number);
        Repeater1.DataBind();

        Literal1.Text = (number).ToString();
    }
    //搜索按钮

    void Button1_Click(object sender, EventArgs e)
    {

        Repeater1.DataSource = sou(1);
        Repeater1.DataBind();
        Literal1.Text = "1";
        Literal2.Text = souye().ToString();
        
    }
    //下一页
    void Button4_Click(object sender, EventArgs e)
   
    {

        
        int n = Convert.ToInt32(Literal1.Text) + 1;
        if (n > souye())
        {
            return;
        }
        Repeater1.DataSource = sou(n);
        Repeater1.DataBind();
        Literal1.Text = n.ToString();
      

  
   
    }
    //搜索数据绑定
    public List<mb> sou(int nextye)
    {
        string sql = " ";
        string sqlc = "select top " + yecount + " * from mb ";
        int count = 0;
        int ye = Convert.ToInt32(Literal1.Text);

        if (TextBox1.Text.Trim().Length > 0)
        {
            sql += "where name like '%" + TextBox1.Text.Trim() + "%' ";

            count++;
        }
        if (TextBox2.Text.Trim().Length > 0)
        {
            if (count > 0)
            {
                sql += "and newprice" + DropDownList1.SelectedValue + " " + TextBox2.Text.Trim() + " ";

            }
            else
            {

                sql += "where newprice" + DropDownList1.SelectedValue + " " + TextBox2.Text.Trim() + " ";
            }
            count++;

        }

        if (TextBox3.Text.Trim().Length > 0)
        {
            if (count > 0)
            {

                sql += "and newprice" + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim() + " ";
            }
            else
            {

                sql += "where newprice" + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim() + " ";
            }
            count++;
        }

        sqlc += sql;


        if (count == 0)
        {
            sqlc += " where ids not in(select top " + (nextye-1)*yecount + " ids from mb " + sql + ")";

        }

        else
        {
            sqlc += " and ids not in(select top " + (nextye - 1) * yecount + " ids from mb " + sql + ")";
            
        }
        List<mb> mlist = new hetidata().selectall(sqlc);
        return mlist;
    }
    //搜索的一共多少页
    public int suoyouye()
    {
        int a = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(new mbdata().selectcount()) / yecount));


        return a;
    }


    //搜索多少页
    public int souye()
    {
        
        string sql = "select  count(*) from mb ";
        int count = 0;
        int ye = Convert.ToInt32(Literal1.Text);

        if (TextBox1.Text.Trim().Length > 0)
        {
            sql += "where name like '%" + TextBox1.Text.Trim() + "%' ";

            count++;
        }
        if (TextBox2.Text.Trim().Length > 0)
        {
            if (count > 0)
            {
                sql += "and newprice" + DropDownList1.SelectedValue + " " + TextBox2.Text.Trim() + " ";

            }
            else
            {

                sql += "where newprice" + DropDownList1.SelectedValue + " " + TextBox2.Text.Trim() + " ";
            }
            count++;

        }

        if (TextBox3.Text.Trim().Length > 0)
        {
            if (count > 0)
            {

                sql += "and newprice" + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim() + " ";
            }
            else
            {

                sql += "where newprice" + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim() + " ";
            }
            count++;
        }
        int c = new hetidata().selectcount(sql);

        int a=Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(c)/yecount));

        return a;
       
    }


}

public class hetidata
{
    SqlConnection conn = null;
    SqlCommand cmd = null;
    public hetidata()
    {
        conn = new SqlConnection("server=.;database=date0216;user=sa;pwd=123;");
        cmd = conn.CreateCommand();
    }

    public List<mb> selectall(string a)
    {
        List<mb> tbs = null;
        cmd.CommandText = a;
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            tbs = new List<mb>();
            while (dr.Read())
            {
                mb t = new mb();
                t.ids = Convert.ToInt32(dr["ids"]);
                t.name = dr["name"].ToString();
                t.oldprice = Convert.ToDecimal(dr["oldprice"]);
                t.newprice = Convert.ToDecimal(dr["newprice"]);
                t.ku = Convert.ToInt32(dr["ku"]);
                t.pic = dr["pic"].ToString();
                t.jieshao = dr["jieshao"].ToString();

                tbs.Add(t);
            }
        }
        conn.Close();
        return tbs;
    }
    public List<mb> selectye(int a, int b)
    {
        List<mb> tbs = new List<mb>();
        cmd.CommandText = "select top " + a + " * from mb where ids not in(select top " + a * (b-1) + " ids from mb)";
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                mb t = new mb();
                t.ids = Convert.ToInt32(dr["ids"]);
                t.name = dr["name"].ToString();
                t.oldprice = Convert.ToDecimal(dr["oldprice"]);
                t.newprice = Convert.ToDecimal(dr["newprice"]);
                t.ku = Convert.ToInt32(dr["ku"]);
                t.pic = dr["pic"].ToString();
                t.jieshao = dr["jieshao"].ToString();

                tbs.Add(t);
            }
        }
        conn.Close();
        return tbs;
    }

    public List<mb> selectsousuoyeshu(int a, int b, string c)
    {
        List<mb> tbs = new List<mb>();
        cmd.CommandText = "select top " + a + " * from mb where ids not in(select top " + a * (b - 1) + " ids from mb)" +" "+c;
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows)
        {
            while (dr.Read())
            {
                mb t = new mb();
                t.ids = Convert.ToInt32(dr["ids"]);
                t.name = dr["name"].ToString();
                t.oldprice = Convert.ToDecimal(dr["oldprice"]);
                t.newprice = Convert.ToDecimal(dr["newprice"]);
                t.ku = Convert.ToInt32(dr["ku"]);
                t.pic = dr["pic"].ToString();
                t.jieshao = dr["jieshao"].ToString();

                tbs.Add(t);
            }
        }
        conn.Close();
        return tbs;
    }


    public int selectcount( string c)
    {
        int a = 0;
        cmd.CommandText = c;
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        a = Convert.ToInt32(dr[0]);
        conn.Close();
        return a;

    }

没有加保护