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