一、高级查询与分页查询
1、以...开头 StartsWith
Repeater1.DataSource=con.Users.Where(r=>r.Nickname.StartsWith("李"));
Repeater1.DataBind();
2、以...结尾 EndsWith
Repeater1.DataSource=con.Users.Where(r=>r.Nickname.EndsWith("同"));
Repeater1.DataBind();
3、模糊差(包含) Contains
Repeater1.DataSource=con.Users.Where(r=>r.Nickname.Contains("蘇"));
Repeater1.DataBind();
4、个数 Count()或者Tolist().Count
Response.Write("总个数:"+con.Users.Count());
Response.Write("总个数:"+con.Users.Tolist().Count;
5、最大值 Max(r=>r.列名)
Response.Write("总个数:"+con.Users.Tolist().Max(r=>r.Ids);
6、最小值 Min(r=>r.列名)
Response.Write("总个数:"+con.Users.Tolist().Min(r=>r.Ids);
7、平均值 Average(r=>r.列名)
Response.Write("总个数:"+con.Users.Tolist().Average(r=>r.Ids);
8、求和 Sum(r=>r.列名)
Response.Write("总个数:"+con.Users.Tolist().Sum(r=>r.Ids);
9、升序 OrderBy(r=>r.列名)
Repeater1.DataSource=con.Users.Tolist().OrderBy(r=>r.Ids);
10、降序 OrderByDescending(r=>r.列名)
Repeater1.DataSource=con.Users.Tolist().OrderByDescending(r=>r.Ids);
11、分页 Skip()--跳过多少条 Take()--每页取多少条
Repeater1.DataSource=con.Users.Tolist().Skip(0).Take(PageCount) 表示第一页跳过0条，每页取PageCount条
二、查询分页结合
界面代码
<form id="form1" runat="server"> <div> 车名：<asp:TextBox ID="txt_carname" runat="server"></asp:TextBox> 油耗：<asp:DropDownList ID="dr_oil" 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="txt_oil" runat="server"></asp:TextBox> 价格：<asp:DropDownList ID="dr_price" runat="server"> <asp:ListItem Text="全部" Value="null"></asp:ListItem> <asp:ListItem Text="小于30万" Value="1"></asp:ListItem> <asp:ListItem Text="30-40万" Value="2"></asp:ListItem> <asp:ListItem Text="40-50万" Value="3"></asp:ListItem> <asp:ListItem Text="大于50万" Value="4"></asp:ListItem> </asp:DropDownList> <asp:Button ID="Button1" runat="server" Text="查询" /> <table style=" 100%; background-color: navy; text-align: center"> <tr style="height: 40px; color: white;"> <td>id</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("code") %></td> <td><%#Eval("name") %></td> <td><%#Eval("oil") %></td> <td><%#Eval("powers") %></td> <td><%#Eval("price") %></td> </tr> </ItemTemplate> </asp:Repeater> </table> 当前第【 <asp:Label ID="lab_nownumber" runat="server" Text="1"></asp:Label> 】页，一共【 <asp:Label ID="lab_maxnumber" runat="server" Text=""></asp:Label> 】页。 <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="尾页" /> </div> </form>
后台代码
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class Main : System.Web.UI.Page { int PageCount = 6; protected void Page_Load(object sender, EventArgs e) { Button1.Click += Button1_Click; btn_first.Click += btn_first_Click; btn_next.Click += btn_next_Click; btn_prev.Click += btn_prev_Click; btn_last.Click += btn_last_Click; if (!IsPostBack) { using (Data0216DataClassesDataContext con = new Data0216DataClassesDataContext()) { Repeater1.DataSource = Data().Skip(0).Take(PageCount); Repeater1.DataBind(); lab_maxnumber.Text = Max().ToString(); } } } void btn_last_Click(object sender, EventArgs e) { Repeater1.DataSource = Data().Skip((Max() - 1) * PageCount); Repeater1.DataBind(); lab_nownumber.Text = Max().ToString(); lab_maxnumber.Text = Max().ToString(); } void btn_prev_Click(object sender, EventArgs e) { int a = Convert.ToInt32(lab_nownumber.Text) - 1; if (a > 0) { Repeater1.DataSource = Data().Skip((a - 1) * PageCount).Take(PageCount); Repeater1.DataBind(); lab_nownumber.Text = a.ToString(); lab_maxnumber.Text = Max().ToString(); } } void btn_next_Click(object sender, EventArgs e) { int a = Convert.ToInt32(lab_nownumber.Text) + 1; if (a <= Max()) { Repeater1.DataSource = Data().Skip((a - 1) * PageCount).Take(PageCount); Repeater1.DataBind(); lab_nownumber.Text = a.ToString(); lab_maxnumber.Text = Max().ToString(); } } void btn_first_Click(object sender, EventArgs e) { Repeater1.DataSource = Data().Skip(0).Take(PageCount); Repeater1.DataBind(); lab_nownumber.Text = "1"; lab_maxnumber.Text = Max().ToString(); } void Button1_Click(object sender, EventArgs e) { Repeater1.DataSource = Data().Skip(0).Take(PageCount); Repeater1.DataBind(); lab_nownumber.Text = "1"; lab_maxnumber.Text = Max().ToString(); } public List<car> Data() { using (Data0216DataClassesDataContext con = new Data0216DataClassesDataContext()) { List<car> clist = con.car.ToList(); if (txt_carname.Text.Trim().Length > 0) { clist = clist.Where(r => r.name.Contains(txt_carname.Text.Trim())).ToList(); } if (txt_oil.Text.Trim().Length > 0) { if (dr_oil.SelectedValue == ">") clist = clist.Where(r => r.oil > Convert.ToDecimal(txt_oil.Text)).ToList(); if (dr_oil.SelectedValue == "<") clist = clist.Where(r => r.oil < Convert.ToDecimal(txt_oil.Text)).ToList(); if (dr_oil.SelectedValue == ">=") clist = clist.Where(r => r.oil >= Convert.ToDecimal(txt_oil.Text)).ToList(); if (dr_oil.SelectedValue == "<=") clist = clist.Where(r => r.oil <= Convert.ToDecimal(txt_oil.Text)).ToList(); if (dr_oil.SelectedValue == "=") clist = clist.Where(r => r.oil == Convert.ToDecimal(txt_oil.Text)).ToList(); } if (dr_price.SelectedValue != "null") { if (dr_price.SelectedValue == "1") clist = clist.Where(r => r.price < 30).ToList(); if (dr_price.SelectedValue == "2") clist = clist.Where(r => r.price >= 30 && r.price < 40).ToList(); if (dr_price.SelectedValue == "3") clist = clist.Where(r => r.price >= 40 && r.price < 50).ToList(); if (dr_price.SelectedValue == "4") clist = clist.Where(r => r.price >= 50).ToList(); } return clist; } } public int Max() { return Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(Data().Count) / PageCount)); } }