首页 > 代码库 > 2017-5-25 分页加条件查询合体

2017-5-25 分页加条件查询合体

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

<!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>
</head>
<body>
    <form id="form1" runat="server">
    <br />
        姓名:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
        编号:<asp:DropDownList ID="DropDownList2" runat="server">
            <asp:ListItem Text="大于" Value=http://www.mamicode.com/">"></asp:ListItem>
             <asp:ListItem Text="小于" Value=http://www.mamicode.com/"<"></asp:ListItem>
           </asp:DropDownList>
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
        民族:<asp:DropDownList ID="DropDownList3" runat="server">
            <asp:ListItem  Text="n001" Value=http://www.mamicode.com/"n001"></asp:ListItem>
            <asp:ListItem  Text="n002" Value=http://www.mamicode.com/"n002"></asp:ListItem>
            <asp:ListItem  Text="n003" Value=http://www.mamicode.com/"n003"></asp:ListItem>
              <asp:ListItem  Text="n004" Value=http://www.mamicode.com/"n004"></asp:ListItem>
              <asp:ListItem  Text="任意" Value=http://www.mamicode.com/"null"></asp:ListItem>

           </asp:DropDownList>
        <asp:Button ID="Button1" runat="server" Text="查询" /><br />
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
        <br /><br />

   <table style="width:100%;text-align:center;background-color:navy;">
       <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("username")%></td>
             <td><%#Eval("password") %></td>
             <td><%#Eval("nickname") %></td>
             <td><%#Eval("sex") %></td>
             <td><%#Eval("birthday") %></td>
             <td><%#Eval("nation") %></td>
       </tr>
               </ItemTemplate>
           </asp:Repeater>
   </table>
        当前第[<asp:Literal ID="lit_nownumber" runat="server" Text="1"></asp:Literal>]页&nbsp;&nbsp;
        共[<asp:Literal ID="lit_maxnumber" runat="server" Text="1"></asp:Literal>]页&nbsp;&nbsp;
        <asp:Button ID="btn_first" runat="server" Text="首页" />
        <asp:Button ID="btn_prev" runat="server" Text="上一页" />
        <asp:Button ID="btn_next" runat="server" Text="下一页" />
        <asp:Button ID="btn_last" runat="server" Text="尾页" />
        &nbsp;&nbsp;
        <asp:DropDownList ID="DropDownList1" AutoPostBack="true" runat="server"></asp:DropDownList>
        <asp:Button ID="btn_jump" runat="server" Text="跳转" />
        <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>
    </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 _Default : System.Web.UI.Page
{
    int pagecount = 2;//每页显示条数
    int pagenumber = 1;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Repeater1.DataSource = new user1data().select(pagecount, 1);
            Repeater1.DataBind();
            lit_maxnumber.Text = maxpagenumber().ToString();
            for (int i = 1; i <= maxpagenumber(); i++)
            {
                ListItem li = new ListItem(i.ToString(), i.ToString());
                DropDownList1.Items.Add(li);
            }



        }
        btn_last.Click += btn_last_Click;
        btn_next.Click += btn_next_Click;
        btn_prev.Click += btn_prev_Click;
        btn_first.Click += btn_first_Click;
        btn_jump.Click += btn_jump_Click;
        DropDownList1.SelectedIndexChanged += btn_jump_Click;
        Button1.Click += Button1_Click;
    }

    void Button1_Click(object sender, EventArgs e)
    {

        Repeater1.DataSource = enddata(1);
        Repeater1.DataBind();
        lit_nownumber.Text = "1";
        //最大页数改变
        lit_maxnumber.Text = maxpagenumber2().ToString();
    }

    void btn_jump_Click(object sender, EventArgs e)
    {
        int a = Convert.ToInt32(DropDownList1.SelectedValue);
        //将下一页数据绑定到页面中去。
        Repeater1.DataSource = new user1data().select(pagecount, a);
        Repeater1.DataBind();

        //将当前显示的页数改变到页面中去
        lit_nownumber.Text = a.ToString();
    }

    void btn_first_Click(object sender, EventArgs e)
    {

        //将下一页数据绑定到页面中去。
        Repeater1.DataSource = enddata(1);
        Repeater1.DataBind();

        //将当前显示的页数改变到页面中去
        lit_nownumber.Text = "1";
    }

    void btn_prev_Click(object sender, EventArgs e)
    {
        //获取当前页数,计算下一页页数。
        int nextnumber = Convert.ToInt32(lit_nownumber.Text) - 1;
        if (nextnumber < 1) { return; }
        //将下一页数据绑定到页面中去。
        Repeater1.DataSource =enddata(nextnumber);
        Repeater1.DataBind();

        //将当前显示的页数改变到页面中去
        lit_nownumber.Text = nextnumber.ToString();
    }

    void btn_next_Click(object sender, EventArgs e)
    {

        //获取当前页数,计算下一页页数。
        int nextnumber = Convert.ToInt32(lit_nownumber.Text) + 1;
        if (nextnumber > maxpagenumber2()) { return; }
        //将下一页数据绑定到页面中去。
        Repeater1.DataSource = enddata(nextnumber);
        Repeater1.DataBind();

        //将当前显示的页数改变到页面中去
        lit_nownumber.Text = nextnumber.ToString();
    }

    void btn_last_Click(object sender, EventArgs e)
    {
        //将下一页数据绑定到页面中去。
        Repeater1.DataSource = new user1data().select(pagecount, maxpagenumber2());
        Repeater1.DataBind();

        //将当前显示的页数改变到页面中去
        lit_nownumber.Text = maxpagenumber().ToString();
    }


    public int maxpagenumber()
    {
        int a = 0;
        int maxcount = new user1data().selectcount();
        decimal d = Convert.ToDecimal(maxcount) / pagecount;
        a = Convert.ToInt32(Math.Ceiling(d));
        return a;
    }


    public List<user1> enddata(int n)
    {
        Hashtable hs = new Hashtable();
        int count = 0;
        string tsql = "select top " + pagecount + "* from user1 ";
        string sql1 = "";

        if (TextBox1.Text.Trim().Length > 0)
        {
            //匹配名称
            sql1 += "where username like @name";
            hs.Add("@name", "%" + TextBox1.Text.Trim() + "%");
            count++;
        }
        if (TextBox2.Text.Trim().Length > 0)
        {
            if (count > 0)
            {
                //匹配编号
                sql1 += " and ids" + DropDownList2.SelectedValue + " @b ";
            }
            else
            {
                sql1 += " where ids" + DropDownList2.SelectedValue + "@b ";
            }
            hs.Add("@b", TextBox2.Text.Trim());
            count++;
        }
        if (DropDownList3.SelectedValue != "null")
        {
            if (count > 0)
            {
                //匹配民族
                sql1 += " and nation =‘" + DropDownList3.SelectedValue + "";
            }
            else
            {
                sql1 += " where nation =‘" + DropDownList3.SelectedValue + "";
            }
            count++;
        }

        ////上面条件查询拼接完毕后,这里拼接分页语句
        //int nowpagecount = Convert.ToInt32(lit_nownumber.Text);
        tsql += sql1;
        if (count > 0)
        {
            tsql += " and ids not in (select top " + (pagecount * (n - 1)) + " ids from user1 " + sql1 + ")";
        }
        else
        {
            tsql += " where ids not in (select top " + (pagecount * (n - 1)) + " ids from user1 " + sql1 + ")";
        }
        Label1.Text = tsql;
        List<user1> ulist = new user1data().selectall(tsql,hs);
        return ulist;
    }

    public int maxpagenumber2()
    {
        int end = 0;
        Hashtable hs = new Hashtable();
        int count = 0;
        string tsql = "select count(*) from user1 ";
        string sql1 = "";

        if (TextBox1.Text.Trim().Length > 0)
        {
            //匹配名称
            sql1 += "where username like @a ";
            hs.Add("@a", "%" + TextBox1.Text.Trim() + "%");
            count++;
        }
        if (TextBox2.Text.Trim().Length > 0)
        {
            if (count > 0)
            {
                //匹配编号
                sql1 += " and ids" + DropDownList2.SelectedValue + " @b ";
            }
            else
            {
                sql1 += " where ids" + DropDownList2.SelectedValue + "@b ";
            }
            hs.Add("@b", TextBox2.Text.Trim());
            count++;
        }
        if (DropDownList3.SelectedValue != "null")
        {
            if (count > 0)
            {
                //匹配民族
                sql1 += " and nation =‘" + DropDownList3.SelectedValue + "";
            }
            else
            {
                sql1 += " where nation =‘" + DropDownList3.SelectedValue + "";
            }
            count++;
        }

        ////上面条件查询拼接完毕后,这里拼接分页语句
        //int nowpagecount = Convert.ToInt32(lit_nownumber.Text);
        tsql += sql1;
       int aaa= new user1data().selectcount(tsql,hs);
       Label2.Text = aaa.ToString();
       end =Convert.ToInt32( Math.Ceiling( Convert.ToDecimal(aaa) / pagecount));

        return end;
    }
}

技术分享

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

/// <summary>
/// user1 的摘要说明
/// </summary>
public class user1
{
    public int ids { get; set; }
    public string username { get; set; }
    public string password { get; set; }
    public string nickname { get; set; }
    public bool sex { get; set; }
    public DateTime birthday { get; set; }
    public string nation { get; set; }
}
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

/// <summary>
/// user1data 的摘要说明
/// </summary>
public class user1data
{
    SqlConnection conn=null;
    SqlCommand cmd=null;
    public user1data()
    {
        conn = new SqlConnection("server=.;database=data0216;user=sa;pwd=123;");
        cmd = conn.CreateCommand();
    }
    public List<user1> selectall() 
    {
        List<user1> ulist = new List<user1>();
        cmd.CommandText = "select * from user1";
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while(dr.Read())
        {
            user1 u = new user1();
            u.ids = Convert.ToInt32(dr[0]);
            u.username = dr[1].ToString();
            u.password = dr[2].ToString();
            u.nickname = dr[3].ToString();
            u.sex = Convert.ToBoolean(dr[4]);
            u.birthday = Convert.ToDateTime(dr[5]);
            u.nation = dr[6].ToString();
            ulist.Add(u);
        }
        conn.Close();
        return ulist;
    }
    public user1 selectuser(string ids)
    {
        user1 u = null;
        cmd.CommandText = "select * from user1 where ids=@a";
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@a",ids);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
       if(dr.HasRows)
       {
           u = new user1();
           dr.Read();
            u.ids = Convert.ToInt32(dr[0]);
            u.username = dr[1].ToString();
            u.password = dr[2].ToString();
            u.nickname = dr[3].ToString();
            u.sex = Convert.ToBoolean(dr[4]);
            u.birthday = Convert.ToDateTime(dr[5]);
            u.nation = dr[6].ToString();
       }
      
        conn.Close();
        return u;
    }

    public int insertuser(user1 u) 
    {
        int end = 0;
        cmd.CommandText = "insert into user1 values(@a,@b,@c,@d,@e,@f)";
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@a",u.username);
        cmd.Parameters.AddWithValue("@b",u.password);
        cmd.Parameters.AddWithValue("@c",u.nickname);
        cmd.Parameters.AddWithValue("@d",u.sex);
        cmd.Parameters.AddWithValue("@e",u.birthday);
        cmd.Parameters.AddWithValue("@f",u.nation);
        conn.Open();
        end = cmd.ExecuteNonQuery();
        conn.Close();

        return end;
    }

    public int deleteuser(string id) 
    {
        int end = 0;
        cmd.CommandText = "delete from user1 where ids=@a";
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@a",id);
        conn.Open();
        end = cmd.ExecuteNonQuery();
        conn.Close();
        return end;
    }

    public int updateuser(user1 u) 
    {
        int end = 0;
        cmd.CommandText = "update user1 set password=@a,nickname=@b,sex=@c,birthday=@d,nation=@e where ids=@f";
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@a",u.password);
        cmd.Parameters.AddWithValue("@b",u.nickname);
        cmd.Parameters.AddWithValue("@c",u.sex);
        cmd.Parameters.AddWithValue("@d",u.birthday);
        cmd.Parameters.AddWithValue("@e",u.nation);
        cmd.Parameters.AddWithValue("@f",u.ids);
        conn.Open();
        end = cmd.ExecuteNonQuery();
        conn.Close();
        return end;
    }



    public bool hasuser (string uname,string password)
    {
        bool ok = false;
        cmd.CommandText = "select * from user1 where uname=@a and password=@b";
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@a", uname);
        cmd.Parameters.AddWithValue("@b", password);
      
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        if (dr.HasRows) { ok = true; }
        conn.Close();

        return ok;
    }

    public List<user1> select(int pcount, int pnumber)
    {
        List<user1> ulist = new List<user1>();
        cmd.CommandText = "  select top " + pcount + " * from user1 where ids not in(select top " + (pcount * (pnumber - 1)) + " ids from user1)";
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            user1 u = new user1();
            u.ids = Convert.ToInt32(dr[0]);
            u.username = dr[1].ToString();
            u.password = dr[2].ToString();
            u.nickname = dr[3].ToString();
            u.sex = Convert.ToBoolean(dr[4]);
            u.birthday = Convert.ToDateTime(dr[5]);
            u.nation = dr[6].ToString();
            ulist.Add(u);
        }
        conn.Close();
        return ulist;
    }

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

        return a;
    }


    public int selectcount(string tsql1,Hashtable hh)
    {

        Hashtable hs = new Hashtable();
        string tsql = "select * from user1 " + tsql1;
       
        ////上面条件查询拼接完毕后,这里拼接分页语句
        //int nowpagecount = Convert.ToInt32(lit_nownumber.Text);
        int a = 0;
        cmd.CommandText = tsql;
        cmd.Parameters.Clear();
        foreach (string s in hh.Keys)
        {
            cmd.Parameters.Add(s, hh[s]);
        }
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        dr.Read();
        a = Convert.ToInt32(dr[0]);
        conn.Close();

        return a;
    }

    public List<user1> selectall(string tsql,Hashtable hh)
    {
        List<user1> ulist = new List<user1>();
        cmd.CommandText = tsql;
        cmd.Parameters.Clear();
        foreach (string s in hh.Keys)
        {
            cmd.Parameters.Add(s,hh[s]);
        }
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            user1 u = new user1();
            u.ids = Convert.ToInt32(dr[0]);
            u.username = dr[1].ToString();
            u.password = dr[2].ToString();
            u.nickname = dr[3].ToString();
            u.sex = Convert.ToBoolean(dr[4]);
            u.birthday = Convert.ToDateTime(dr[5]);
            u.nation = dr[6].ToString();
            ulist.Add(u);
        }
        conn.Close();
        return ulist;
    }
}

 

2017-5-25 分页加条件查询合体