Linq的分页与组合查询的配合使用

1.首先使用Linq连接数据库，并扩展属性

public partial class User
{
    public string SexStr
    {
        get
        {
            string end = "<暂无>";
            if (this._Sex != null)
            {
                end = Convert.ToBoolean(this._Sex) ? "男" : "女";
            }
            return end;
        }
    }
    public bool SexStr1
    {
        get
        {
            bool end=false;
            
            if (this._Sex != null)
            {
                end = Convert.ToBoolean(this._Sex) ;
            }
            return end;
        }
    }
    public string NationName
    {
        get
        {
            return this.Nation1.NationName;
        }
    }
    public string BirStr
    {
        get
        {
            string end = "<暂无>";
            if(this._Birthday!=null)
            {
                end = Convert.ToDateTime(this._Birthday).ToString("yyyy年MM月dd日");
            }
            return end;
        }
    }
    public int Age
    {
        get
        {
            int end=0;
            if (this._Birthday != null)
            {
                int y = DateTime.Now.Year;
                int a = Convert.ToDateTime(this._Birthday).Year;
                end = y-a;
            }
            return end;
        }
    }
}

扩展属性

2.HTML代码

 1 <form id="form1" runat="server">
 2         <div id="select">
 3             用户名：<asp:TextBox ID="TextBox1" runat="server" Width="130"></asp:TextBox>
 4            性别：<asp:TextBox ID="tb_sex" runat="server" Width="60px"></asp:TextBox>&nbsp
 5             年龄：<asp:DropDownList ID="DropDownList3" runat="server">
 6                 <asp:ListItem Selected="True" Value="&lt;=">&gt;=</asp:ListItem>
 7                 <asp:ListItem Value="&gt;=">&lt;=</asp:ListItem>
 8             </asp:DropDownList><asp:TextBox ID="tb_age" runat="server" Width="60px"></asp:TextBox>
 9              &nbsp<asp:Button ID="Button2" CssClass="Button" runat="server" Text="查询" />&nbsp&nbsp<a href="Insert.aspx" target="_blank" class="link">添加</a></div> 
10         
11         <asp:Repeater ID="Repeater1" runat="server">
12             <HeaderTemplate >
13                 <table style="width: 100%; background-color: #4cff00; text-align: center;">
14                      <tr style="color:#ff6a00;">
15                         <td>用户名</td>
16                         <td>密码</td>
17                         <td>昵称</td>
18                         <td>性别</td>
19                         <td>生日</td>
20                          <td>年龄</td>
21                         <td>民族</td>
22                          <td>操作</td>
23                     </tr>
24             </HeaderTemplate>
25             <ItemTemplate>
26                 <tr style="background-color:#0ff;" class="tr_item">
27                     <td><%#Eval("UserName") %></td>
28                     <td><%#Eval("PassWord") %></td>
29                     <td><%#Eval("NickName") %></td>
30                     <td><%#Eval("SexStr") %></td>
31                     <td><%#Eval("Birthday","{0:yyyy年MM月dd日}") %></td>
32                     <td><%#Eval("Age") %></td>
33                     <td><%#Eval("NationName") %></td>
34                     <td><a href="update.aspx?un=<%#Eval("UserName") %>" target="_blank" class="link">修改</a>&nbsp;&nbsp;
35                         <a id="lian" href="delete.aspx?un=<%#Eval("UserName") %>" onclick="return del()" class="link">删除</a></td>
36                 </tr>
37             </ItemTemplate>
38             <FooterTemplate>
39                 </table>
40             </FooterTemplate>
41         </asp:Repeater>
42         //隐藏域放用户输入的多条件查询内容
43         <asp:HiddenField ID="HiddenField1" runat="server" />
44         <asp:HiddenField ID="HiddenField2" runat="server" />
45         <asp:HiddenField ID="HiddenField3" runat="server" />
46          <div id="aa">当前第&nbsp<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>&nbsp页&nbsp&nbsp共&nbsp<asp:Label ID="Label3" runat="server" Text="Label"></asp:Label>&nbsp页
47             <asp:Button ID="btn_first" runat="server" CssClass="ye" Text="首页" />&nbsp<asp:Button ID="btn_prev" runat="server" Text="上一页" CssClass="ye" />&nbsp<asp:Button ID="btn_next" runat="server" Text="下一页" CssClass="ye" />&nbsp<asp:Button ID="btn_end" runat="server" Text="末页" CssClass="ye"/>
48             <asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="True"></asp:DropDownList></div>
49     </form>

3、C#功能实现

 1  private IEnumerable<global::User> sel(WebDataContext con)
 2     {
 3         var All = con.User.AsEnumerable();
 4         if (HiddenField1.Value.Length > 0)
 5         {
 6             var namelist = con.User.Where(r => r.UserName.Contains(TextBox1.Text.Trim()));
 7 
 8             All = All.Intersect(namelist);
 9         }
10 
11         if (HiddenField2.Value == "男" || HiddenField2.Value == "女")
12         {
13             var sexlist = con.User.Where(r => Convert.ToBoolean(r.Sex) == (tb_sex.Text.Trim() == "男" ? true : false));
14 
15             All = All.Intersect(sexlist);
16         }
17 
18         if (HiddenField3.Value.Length > 0)
19         {
20             int nowyear = DateTime.Now.Year;
21             try
22             {
23                 int age = Convert.ToInt32(tb_age.Text.Trim());
24                 int g = nowyear - age;
25                 DateTime d = Convert.ToDateTime(g.ToString() + "-1-1");
26                 if (DropDownList3.SelectedValue == ">=")
27                 {
28                     var agelist = con.User.Where(r => Convert.ToDateTime(r.Birthday) >= d);
29                     All = All.Intersect(agelist);
30 
31                 }
32                 else
33                 {
34                     DateTime dd = Convert.ToDateTime(g.ToString() + "-12-31");
35                     var agelist = con.User.Where(r => Convert.ToDateTime(r.Birthday) <= dd);
36                     All = All.Intersect(agelist);
37 
38                 }
39             }
40             catch
41             {
42             }
43         }
44         return All;
45     }

查询方法

int Pagecount = 4;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            using (WebDataContext con = new WebDataContext())
            {
                var All = sel(con);
                Repeater1.DataSource = All.Take(Pagecount).ToList();
                Repeater1.DataBind();
            }               
            Label2.Text = "1";//当前页
            Label3.Text = MaxPageNumber().ToString();
            for (int i = 1; i <= MaxPageNumber();i++ )
            {
                DropDownList2.Items.Add(new ListItem(i.ToString(), i.ToString()));
            }
        }
   
    }

初加载

 void Button2_Click(object sender, EventArgs e)//查询按钮
    {
        HiddenField1.Value = TextBox1.Text.Trim();
        HiddenField2.Value = tb_sex.Text.Trim();
        HiddenField3.Value = tb_age.Text.Trim();
        using (WebDataContext con = new WebDataContext())
        {
            var All = sel(con);
            Repeater1.DataSource = All.Take(Pagecount).ToList();
            Repeater1.DataBind();
            Label2.Text = "1";
            Label3.Text = MaxPageNumber().ToString();
            DropDownList2.Items.Clear();
            for (int i = 1; i <= MaxPageNumber(); i++)
            {
                DropDownList2.Items.Add(new ListItem(i.ToString(), i.ToString()));
            }
    }
    }

查询按钮点击

void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
    {
        using (WebDataContext con = new WebDataContext())
        {
            var All = sel(con);
            Repeater1.DataSource = All.Skip(Pagecount * (Convert.ToInt32(DropDownList2.SelectedValue) - 1)).Take(Pagecount).ToList();
            Repeater1.DataBind();
        }
        Label2.Text =DropDownList2.SelectedValue;    }

快捷跳转

 void btn_first_Click(object sender, EventArgs e)
    {
        using (WebDataContext con = new WebDataContext())
        {
            var All = sel(con);
            Repeater1.DataSource = All.Take(Pagecount).ToList();
            Repeater1.DataBind();
        }
        Label2.Text ="1";
        DropDownList2.SelectedValue = "1";
    }

首页

    void btn_end_Click(object sender, EventArgs e)
    {
        using (WebDataContext con = new WebDataContext())
        {
            var All = sel(con);
            Repeater1.DataSource = All.Skip(Pagecount * (Convert.ToInt32(Label3.Text) - 1)).Take(Pagecount).ToList();
            Repeater1.DataBind();   
        }
        Label2.Text = Label3.Text;
        DropDownList2.SelectedValue = Label3.Text;
    }

末页

 void btn_prev_Click(object sender, EventArgs e)
    {
        int NowNumber = Convert.ToInt32(Label2.Text) -1;
        if (NowNumber <1)
        {
            return;
        }
        using (WebDataContext con = new WebDataContext())
        {
            var All = sel(con);
            Repeater1.DataSource = All.Skip(Pagecount * (NowNumber - 1)).Take(Pagecount).ToList();
            Repeater1.DataBind();
        }

        Label2.Text = NowNumber.ToString();
        DropDownList2.SelectedValue = NowNumber.ToString();
    }

void btn_next_Click(object sender, EventArgs e)
    {
        int NowNumber = Convert.ToInt32(Label2.Text) + 1;
        if (NowNumber > Convert.ToInt32(Label3.Text))
        {
            return;
        }
        using (WebDataContext con = new WebDataContext())
        {
            var All = sel(con);
            Repeater1.DataSource = All.Skip(Pagecount * (NowNumber - 1)).Take(Pagecount).ToList();
            Repeater1.DataBind();
        }

        Label2.Text = NowNumber.ToString();
        DropDownList2.SelectedValue = NowNumber.ToString();

    }

 public int MaxPageNumber()
    {
        using (WebDataContext con = new WebDataContext())
        {
            var All = sel(con);
            double nu = All.ToList().Count / (Pagecount * 1.0);
            int num = Convert.ToInt32(Math.Ceiling(nu));
            return num;
        }        
    }

计算总页数

posted on 2016-11-17 19:07 fei！阅读(238) 评论(0) 编辑收藏举报