分页查询

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

                          
                               
       
      
      
                       
        
                             
ids
                             
名字
                             
老价格
                             
新价格
                             
库存
                             
图片
                             
产品介绍
                        
                        
                                     
<%#Eval("ids") %>
                                     
<%#Eval("name") %>
                                     
<%#Eval("oldprice") %>
                                     
<%#Eval("newprice") %>
                                     
<%#Eval("ku") %>
                                     
<%#Eval("pic") %>
                                     
<%#Eval("jieshao") %>
                                
                                                       
      
            
            第【
      】页             一共有【
      】页，            
                  
                  
                  
              
              
      

 

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
      
        selectall()    {        List
       
         tbs = new List
        
         ();        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
         
           selectye(int a,int b)    {        List
          
            tbs = new List
           
            (); 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
            
              selectall(string a ) { List
             
               tbs = null; cmd.CommandText = a; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { tbs = new List
              
               (); 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; }}
              
             
            
           
          
         
        
       
      

模糊查询

模糊查询用的字符串拼接

 

      
        
       
        买啥：
               价格：
                   
                    
                    
                    
                    
                   
               
               
                   
                    
                                
                    
                    
                   
               
               
               
       
        
       
    
                           
                                
        
       
       
                        
         
                              
ids
                              
名字
                              
老价格
                              
新价格
                              
库存
                                               
产品介绍
                         
                         
                                      
<%#Eval("ids") %>
                                      
<%#Eval("name") %>
                                      
<%#Eval("oldprice") %>
                                      
<%#Eval("newprice") %>
                                      
<%#Eval("ku") %>
                                                                 
<%#Eval("jieshao") %>
                                 
                                                        
       
            
       
        
           
      

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
      
        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条   不能直接跳过几条

      
               
       
        买啥：
               价格：
                   
                    
                    
                    
                    
                   
               
               
                   
                    
                                
                    
                    
                   
               
               
               
       
        
       
    
                           
                                
        
       
       
                        
         
                              
ids
                              
名字
                              
老价格
                              
新价格
                              
库存
                                               
产品介绍
                         
                         
                                      
<%#Eval("ids") %>
                                      
<%#Eval("name") %>
                                      
<%#Eval("oldprice") %>
                                      
<%#Eval("newprice") %>
                                      
<%#Eval("ku") %>
                                                                 
<%#Eval("jieshao") %>
                                 
                                                        
       
            
       
        这是第【
       】页，        一共【
       】页；        
               
               
               
               
               
               
           
      

 

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
      
        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
       
         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
      
        selectall(string a)    {        List
       
         tbs = null;        cmd.CommandText = a;        conn.Open();        SqlDataReader dr = cmd.ExecuteReader();        if (dr.HasRows)        {            tbs = new List
        
         ();            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
         
           selectye(int a, int b)    {        List
          
            tbs = new List
           
            (); 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
            
              selectsousuoyeshu(int a, int b, string c) { List
             
               tbs = new List
              
               (); 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; }
              
             
            
           
          
         
        
       
      

没有加保护