c# Linq to sql 基本查询例子

准备工作：1.使用微软的例子数据库-Northwind,没有的可以到微软挂官网去下,附件到sql server2005数据库中

               2.使用微软O/R设计器，将Northwind映射到项目中，以下实例在此环境下进行,使用vs2008

 

以下为代码:

using System; using System.Collections.Generic; using System.Linq; using System.Text; using DBClass;//此处为O/R映射Northwind using System.Data.Linq; using System.Data.Linq.SqlClient; namespace Linq_to_Sql_Demo1 { class Program { static void Main(string[] args) { LinqForLike(); } #region linq中select语句的使用 public static void SqlFroSelectOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from c in db.Customers where c.City == "London" select new { c.CompanyName, c.City }; //上下两句表达意思是一样的，上面为标准查询语句，下面为级连表达式 // var q = db.Customers.Where(c => c.City == "London").Select(c => c.ContactName); /* 手动填写数据库对象后的方法 DataContext dc = new DataContext("server=.//sql2005;database=Northwind;uid=sa;pwd=king_860119"); var q = from c in dc.GetTable<Customers>() where c.City == "London" select c.CompanyName; */ foreach (var c in q) { Console.WriteLine(c); } Console.ReadLine(); } #endregion #region linq中select语句的使用2 public static void SqlFroSelectTwo() { NorthwindDataContext dc = new NorthwindDataContext(); var q=from s in dc.Customers select new{城市名称=s.City}; //在这里相当于声明一个匿名类q,q中存在属性“城市名称”,而其中S为一条条的dc.Customers对象 //其中“城市名称”这个属性当当与对S.city的重命名一样 foreach(var a in q) { Console.WriteLine(a); } Console.Read(); } #endregion #region linq中select语句的使用3 public static void SqlFroSelectThree() { NorthwindDataContext dc = new NorthwindDataContext(); var q=from s in dc.Products select new {s.ProductName,我的条件=s.UnitsInStock-s.UnitsOnOrder<0?"out":"in"}; foreach(var a in q) { Console.WriteLine(a); } Console.Read(); } #endregion #region linq中where语句的使用1 public static void SqlFroWhereOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Products where p.UnitPrice > 10m || p.Discontinued select p; foreach (var a in q) { Console.WriteLine(a.Categories.CategoryID); } Console.Read(); } #endregion #region linq中order语句的使用1 public static void SqlFroOrderOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from s in db.Employees orderby s.EmployeeID descending select new { s.EmployeeID,s.FirstName,s.LastName}; //orderby子句在select子句前面，与where等子句位置无要求 foreach (var s in q) { Console.WriteLine(s.ToString()); } Console.Read(); } #endregion #region Linq中GroupBy语句的使用1 public static void SqlFroGroupByOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Products group p by p.CategoryID into g select g; foreach (var gp in q) { if (gp.Key == 7) { foreach (var p in gp) { //do something } } } Console.ReadKey(); } #endregion #region Linq中GroupBy语句的使用2 public static void SqlForGroupByTwo() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Products group p by p.CategoryID into g select new { CategoryID = g.Key, g }; foreach (var gp in q) { //do something } } #endregion #region Linq中GroupBy语句的使用30-聚合函数的使用 public static void SqlForGroupByThree() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Products group p by p.CategoryID into g select new { g.Key, TotalPrice = g.Sum(p => p.UnitPrice) }; foreach (var a in q) { Console.WriteLine(a); } Console.Read(); } #endregion #region Linq中Join语句的使用1 public static void LinqForJoinOne() { NorthwindDataContext db = new NorthwindDataContext(); var q = from p in db.Customers join o in db.Orders on p.CustomerID equals o.CustomerID select new { p.CustomerID, o.EmployeeID }; //下面注释的语句为此句的lambda的写法 // var q = db.Customers.Join(db.Orders, p => p.CustomerID, o => o.CustomerID, (p, o) => new { p.CustomerID, o.EmployeeID }); foreach (var s in q) { Console.WriteLine(s); } Console.Read(); } #endregion #region Linq中In语句的使用 public static void LinqForInOne() { NorthwindDataContext db = new NorthwindDataContext(); string[] customerID_Set = new string[] { "AROUT", "BOLID", "FISSA" }; var q = from s in db.Orders where customerID_Set.Contains(s.CustomerID) select s.EmployeeID; //相当于sql中的in，意思就是从这个in的集合中取出，如果not in 就在集合的前面加上！ foreach (var a in q) { Console.WriteLine(a); } Console.Read(); } #endregion #region Linq中Like语句的使用 public static void LinqForLike() { NorthwindDataContext db = new NorthwindDataContext(); //中间部分包含And的 var q = from s in db.Customers where s.ContactName.Contains("And") select s.ContactName; //开头部分包含A的 var p = from s in db.Customers where s.ContactName.StartsWith("A") select s.ContactName; //结束部分包含g的 var a = from s in db.Customers where s.ContactName.EndsWith("g") select s.ContactName; //另外的一种写法，需要命名空间System.Data.Linq.SqlClient var c = from s in db.Customers where SqlMethods.Like(s.ContactName, "%g") select s.ContactName; foreach (var b in c) { Console.WriteLine(b); } Console.Read(); } #endregion } }

 

使用O/R映射的数据库实例代码（均为系统自动生成）,连接字符在app.config中

 

附加上增删改的使用

#region linq中insert语句的使用 public static void LinqForInsert() { NorthwindDataContext db = new NorthwindDataContext(); var rgdata = new Region { RegionID = 5, RegionDescription = "test" }; try { db.Region.InsertOnSubmit(rgdata);//插入数据 db.SubmitChanges();//提交更改 } catch (Exception err) { Console.WriteLine(err.Message); } Console.Read(); } #endregion #region Linq中update语句的使用 public static void LinqForUpdata() { NorthwindDataContext db = new NorthwindDataContext(); var o = from c in db.Region where c.RegionID == 5 select c; foreach (var item in o) { if(item.RegionID==5) item.RegionDescription = "test改过了"; } db.SubmitChanges(); } #endregion #region Linq中del语句的使用 public static void LinqForDel() { NorthwindDataContext db = new NorthwindDataContext(); var q = from c in db.Region where c.RegionID == 5 select c; foreach (var item in q) { db.Region.DeleteOnSubmit(item); } db.SubmitChanges(); } #endregion