linq的简单查询 和 组合查询
以Car表和Brand表为例，其中Car表的Brand是Brand表的Brandcode。
（1）建立两表的linq（一定要做好主外键关系，），创建之后不用修改，如要添加，另建文件。
（2）Car表的属性扩展（注意partial）
using System; using System.Collections.Generic; using System.Linq; using System.Web; /// <summary> /// Car 的摘要说明 /// </summary> public partial class Car { public string BrandStr { get { return this.Brand1.Brand_Name; } } }
（3）Car表和Brand表的封装方法（模糊查，开头，结尾，个数，最大值，最小值，平均值，求和，升序，降序，分页，组合查询都在CarData里面）
using System; using System.Collections.Generic; using System.Linq; using System.Web; /// <summary> /// BrandData 的摘要说明 /// </summary> public class BrandData { mydbDataContext cnn = null; public BrandData() { cnn = new mydbDataContext(); } public List<Brand> quan() { return cnn.Brand.ToList(); } }
using System; using System.Collections.Generic; using System.Linq; using System.Web; /// <summary> /// CarData 的摘要说明 /// </summary> public class CarData { mydbDataContext cnn = null; public CarData() { cnn = new mydbDataContext(); } /// <summary> /// 查询全部信息 /// </summary> /// <returns>泛型集合</returns> public List<Car> quan() { return cnn.Car.ToList(); } /// <summary> /// 根据名字模糊查询 /// </summary> /// <param name="name">名称中的某一段字</param> /// <returns>泛型集合</returns> public List<Car> selectname(string name) { return cnn.Car.Where(r => r.Name.Contains(name)).ToList(); } /// <summary> /// 查询name以n开头的所有数据 /// </summary> /// <param name="n">name以n开头的字</param> /// <returns></returns> public List<Car> selectstartname(string n) { return cnn.Car.Where(r => r.Name.StartsWith(n)).ToList();//r.name可以换成别的列 } /// <summary> /// 查询name以n结束的所有数据 /// </summary> /// <param name="n">name以n结束的字</param> /// <returns></returns> public List<Car> selectendname(string n) { return cnn.Car.Where(r => r.Name.EndsWith(n)).ToList();//r.name可以换成别的列 } /// <summary> /// 查询价格最高的那个数 /// </summary> /// <returns>最大数</returns> public decimal? selectpricemax() { return cnn.Car.Max(r => r.Price); } /// <summary> /// 查询价格最小的那个数 /// </summary> /// <returns>最小数</returns> public decimal? selectpricemin() { return cnn.Car.Min(r => r.Price); } /// <summary> /// 查询价格的平均数 /// </summary> /// <returns>平均数</returns> public decimal? selectpriceavg() { return cnn.Car.Average(r=>r.Price); } /// <summary> /// 查询价格的总数 /// </summary> /// <returns>价格总数</returns> public decimal? selectpricesum() { return cnn.Car.Sum(r=>r.Price); } /// <summary> /// 按照价格升序进行排列的集合 /// </summary> /// <returns>集合</returns> public List<Car> order() { return cnn.Car.OrderBy(r=>r.Price).ToList();//按照价格升序 } /// <summary> /// 按照价格降序进行排列的集合 /// </summary> /// <returns>集合</returns> public List<Car> descorder() { return cnn.Car.OrderByDescending(r => r.Price).ToList(); } /// <summary> /// 分页显示 /// </summary> /// <param name="pagecount">每一页的页数</param> /// <param name="num">第几页</param> /// <returns>集合</returns> public List<Car> fenye(int pagecount, int num) { return cnn.Car.Skip(pagecount * (num - 1)).Take(pagecount).ToList();//cnn.Car表示所有的数据 } /// <summary> /// 组合分页查询 /// </summary> /// <param name="clist">所需要分页的集合</param> /// <param name="pagecount">每一页的页数</param> /// <param name="num">第几页</param> /// <returns></returns> public List<Car> zufen(List<Car> clist, int pagecount, int num) { return clist.Skip(pagecount * (num - 1)).Take(pagecount).ToList();//clist表示所需要分页的集合 } }
（4）页面设计
<%@ 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"> 名称<asp:TextBox ID="name_text" runat="server"></asp:TextBox> 品牌<asp:DropDownList ID="brand_dd" runat="server"></asp:DropDownList> 价格<asp:DropDownList ID="pri_dd" runat="server"> <asp:ListItem>>=</asp:ListItem> <asp:ListItem><=</asp:ListItem> </asp:DropDownList><asp:TextBox ID="price_text" runat="server"></asp:TextBox> <asp:Button ID="enter_btn" runat="server" Text="确定" /> <br /> <asp:Label ID="Label1" runat="server" Text=""></asp:Label> <asp:Repeater ID="Repeater1" runat="server"> <HeaderTemplate> <table style="width:100%; background-color:blue;"> <tr> <td>序号</td> <td>名称</td> <td>品牌</td> <td>时间</td> <td>油耗</td> <td>马力</td> <td>价格</td> <td>照片</td> </tr> </HeaderTemplate> <ItemTemplate> <tr style="background-color:gray" class="a"> <td><%#Container.ItemIndex+1 %></td> <td><%#Eval("Name") %></td> <td><%#Eval("BrandStr") %></td> <td><%#Eval("Time","{0:yyyy年MM月dd日}") %></td> <td><%#Eval("Oil") %></td> <td><%#Eval("Powers") %></td> <td><%#Eval("Price") %></td> <td><%#Eval("Pic") %></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> 当前第【<asp:Label ID="numLabel" runat="server" Text=""></asp:Label>】页，共【<asp:Label ID="sumLabel" runat="server" Text=""></asp:Label>】页 <asp:LinkButton ID="previewButton" runat="server">上一页</asp:LinkButton> <asp:LinkButton ID="nextButton" runat="server">下一页</asp:LinkButton> 跳转到第<asp:DropDownList ID="page_dd" runat="server" AutoPostBack="true"></asp:DropDownList>页 </form> </body> </html> <script> var all = new Array(); all =document.getElementsByClassName("a"); for (var i = 0; i < all.length; i++) { var old; all[i].onmouseover = function () { old = this.style.backgroundColor; this.style.backgroundColor = "yellow"; } all[i].onmouseout = function () { this.style.backgroundColor = old; } } </script>
（5）C#端代码
using System; 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 = 3; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { List<Car> list = new CarData().quan(); Repeater1.DataSource = new CarData().fenye(pagecount, 1); ; Repeater1.DataBind(); List<Brand> blist = new BrandData().quan(); brand_dd.Items.Add(new ListItem("全部","null")); for(int i=0;i<blist.Count;i++) { brand_dd.Items.Add(new ListItem(blist[i].Brand_Name,blist[i].Brand_Code)); } numLabel.Text = "1"; sumLabel.Text = pagemax(list, pagecount).ToString(); for (int i = 1; i <= int.Parse(sumLabel.Text); i++) { page_dd.Items.Add(new ListItem(i.ToString(),i.ToString())); } } enter_btn.Click += enter_btn_Click;//查询按钮点击事件 nextButton.Click += nextButton_Click;//下一页的点击事件 previewButton.Click += previewButton_Click;//上一页的事件 page_dd.SelectedIndexChanged += page_dd_SelectedIndexChanged;//跳转到第几页事件 } void page_dd_SelectedIndexChanged(object sender, EventArgs e)//跳转到第几页事件 { int c = int.Parse(page_dd.SelectedValue); using (mydbDataContext cnn = new mydbDataContext())//运用多个集合的交集方法 { int aa = int.Parse(page_dd.SelectedValue); numLabel.Text = aa.ToString();//第几页中的页数 var all = cnn.Car.AsEnumerable();//相当于拼接sql语句 if (name_text.Text.Trim().Length > 0) { var nlist = cnn.Car.Where(r => r.Name.Contains(name_text.Text.Trim())); all = all.Intersect(nlist);//取all集合和nlist集合的公共部分 } if (brand_dd.SelectedValue == "null") { } else { var blist = cnn.Car.Where(r => r.Brand == brand_dd.SelectedValue); all = all.Intersect(blist); } if (price_text.Text.Trim().Length > 0) { if (pri_dd.SelectedIndex == 0) { var plist = cnn.Car.Where(r => r.Price >= decimal.Parse(price_text.Text.Trim())); all = all.Intersect(plist); } else { var plist = cnn.Car.Where(r => r.Price <= decimal.Parse(price_text.Text.Trim())); all = all.Intersect(plist); } } Repeater1.DataSource = new CarData().zufen(all.ToList(), pagecount,aa); Repeater1.DataBind(); } } void previewButton_Click(object sender, EventArgs e)//上一页的事件 { using (mydbDataContext cnn = new mydbDataContext())//运用多个集合的交集方法 { int a = int.Parse(numLabel.Text) -1;//哪一页的页数 if (a <= 0)//判断有没有到最后一页 { return; } numLabel.Text = a.ToString();//第几页中的页数 var all = cnn.Car.AsEnumerable();//相当于拼接sql语句 if (name_text.Text.Trim().Length > 0) { var nlist = cnn.Car.Where(r => r.Name.Contains(name_text.Text.Trim())); all = all.Intersect(nlist);//取all集合和nlist集合的公共部分 } if (brand_dd.SelectedValue == "null") { } else { var blist = cnn.Car.Where(r => r.Brand == brand_dd.SelectedValue); all = all.Intersect(blist); } if (price_text.Text.Trim().Length > 0) { if (pri_dd.SelectedIndex == 0) { var plist = cnn.Car.Where(r => r.Price >= decimal.Parse(price_text.Text.Trim())); all = all.Intersect(plist); } else { var plist = cnn.Car.Where(r => r.Price <= decimal.Parse(price_text.Text.Trim())); all = all.Intersect(plist); } } Repeater1.DataSource = new CarData().zufen(all.ToList(), pagecount, a); Repeater1.DataBind(); } } void nextButton_Click(object sender, EventArgs e)//下一页的点击事件 { using (mydbDataContext cnn = new mydbDataContext())//运用多个集合的交集方法 { int a = int.Parse(numLabel.Text) + 1;//哪一页的页数 if (a > int.Parse(sumLabel.Text))//判断有没有到最后一页 { return; } numLabel.Text = a.ToString();//第几页中的页数 var all = cnn.Car.AsEnumerable();//相当于拼接sql语句 if (name_text.Text.Trim().Length > 0) { var nlist = cnn.Car.Where(r => r.Name.Contains(name_text.Text.Trim())); all = all.Intersect(nlist);//取all集合和nlist集合的公共部分 } if (brand_dd.SelectedValue == "null") { } else { var blist = cnn.Car.Where(r => r.Brand == brand_dd.SelectedValue); all = all.Intersect(blist); } if (price_text.Text.Trim().Length > 0) { if (pri_dd.SelectedIndex == 0) { var plist = cnn.Car.Where(r => r.Price >= decimal.Parse(price_text.Text.Trim())); all = all.Intersect(plist); } else { var plist = cnn.Car.Where(r => r.Price <= decimal.Parse(price_text.Text.Trim())); all = all.Intersect(plist); } } Repeater1.DataSource = new CarData().zufen(all.ToList(),pagecount,a); Repeater1.DataBind(); } } void enter_btn_Click(object sender, EventArgs e)//查询按钮点击事件 { using (mydbDataContext cnn = new mydbDataContext())//运用多个集合的交集方法 { page_dd.Items.Clear(); var all = cnn.Car.AsEnumerable();//相当于拼接sql语句 if (name_text.Text.Trim().Length>0) { var nlist = cnn.Car.Where(r => r.Name.Contains(name_text.Text.Trim())); all = all.Intersect(nlist); } if (brand_dd.SelectedValue == "null") { } else { var blist = cnn.Car.Where(r => r.Brand == brand_dd.SelectedValue); all = all.Intersect(blist); } if (price_text.Text.Trim().Length > 0) { if (pri_dd.SelectedIndex == 0) { var plist = cnn.Car.Where(r => r.Price >= decimal.Parse(price_text.Text.Trim())); all = all.Intersect(plist); } else { var plist = cnn.Car.Where(r => r.Price <= decimal.Parse(price_text.Text.Trim())); all = all.Intersect(plist); } } Repeater1.DataSource = new CarData().zufen(all.ToList(), pagecount, 1); Repeater1.DataBind(); sumLabel.Text = pagemax(all.ToList(), pagecount).ToString(); numLabel.Text = "1"; for (int i = 1; i <= pagemax(all.ToList(), pagecount); i++) { page_dd.Items.Add(new ListItem(i.ToString(),i.ToString())); } } //if (name_text.Text.Trim() == "" && brand_dd.SelectedValue=="null" && price_text.Text.Trim() == "")//如果没有条件就查询全部 //{ // Repeater1.DataSource = new CarData().quan(); // Repeater1.DataBind(); // return; //} //Repeater1.DataSource = new CarData().selectname(name_text.Text.Trim());//根据name中的XXX模糊查询 // Repeater1.DataSource = new CarData().selectstartname(name_text.Text.Trim());//name以XXX开头来查询 //Repeater1.DataSource = new CarData().selectendname(name_text.Text.Trim());//name以XXX结束来查询 //Label1.Text = new CarData().selectendname(name_text.Text.Trim()).Count.ToString();//获取符合条件的个数。其实就是根据模糊查询查出来的集合的子集个数。 //Repeater1.DataSource = new CarData().descorder();//按照价格降序进行排列 //Label1.Text = new CarData().selectpricemax().ToString();//查询价格最大的那个数 //Label1.Text = new CarData().selectpriceavg().ToString();//查询价格的平均数 //Label1.Text = new CarData().selectpricesum().ToString();//查询价格的总数 //Repeater1.DataBind(); } public int pagemax(List<Car> clist,int pagecount)//求最大页数 { int a = clist.Count; return Convert.ToInt32(Math.Ceiling(a / (pagecount * 1.0))); } }
完！！
