1.linq 框架
---------------------------------------------------------
C# VB
---------------------------------------------------------
.Net Language INtegrated Query(LINQ)
---------------------------------------------------------
Linq enabled data sources
1.linq to objects
2.Linq enabled ADO.NET
----linq to DataSet
----linq to SQL
----linq to Entities
3.linq to XML
---------------------------------------------------------
Data
Objects,Relations,XML
---------------------------------------------------------
2.LINQ for SQL
? 把.NET 类和SQL 数据通过关系进行映射(对象<--->关系的相互映射)
? 把LINQ 查询转化为SQL 语言进行执行
? 支持对插入,更新,删除操作进行跟踪.
? 支持实体级别的验证规则
? 构建于ADO.NET之上并且集成连接池和事ADO.务处理(ADO.NET之上)
举例1:(查询实例)
NorthWindDataContext dc = new NorthWindDataContext();
var query = from P in dc.Products
where P.CategoryID.Value>6
select P;
this.GridView1.DataSource = query;
this.GridView1.DataBind();
举例2:(修改单个记录实例)
NorthWindDataContext dc = new NorthWindDataContext();
Product product = dc.Products.Single(p => p.ProductName == "Tofu");
product.UnitPrice = 1111111111;
product.UnitsInStock = 55;
dc.SubmitChanges();
3.数据表映射
? 映射数据表和实体类之间的关系
? 使用数据库中典型的主/外键进行表示
? 支持灵活的关系查询并且不用写任务的SQL
? 代码就可以执行处理过程
举例3:
-----产品类型 Categories<1>-----> <n>产品Products <n>
-----产品Products <n> <----<1>提供商 Suppliers
Table Suplier
-------------------------------------------------------
SuplierID
CompanyName
.....
Table Category
-------------------------------------------------------
CategoryID
CanegoryName
....
Table Product
-------------------------------------------------------
ProductID
ProductName
....
SuplierID
CategoryID
模型中的代码关联如下:
public partial class Product {
public int ProductID;
public string ProductName;
public Nullable<int> SupplierID;
public Nullable<int> CategoryID;
public Supplier Supplier; //两者组合以来,一对一
public Category Category; //两者组合以来,一对一
}
public partial class Supplier {
public int SupplierID;
public string CompanyName;
public EntitySet<Product> Products; //一对多
}
public partial class Category {
public int CategoryID;
public EntitySet<Product> Products; //一对多
}
我们可以这样理解:在对Product进行数据访问的时候,可以直接访问Supplier和Category的的单个对象;在对Supplier访问时,可以访问对于的一组Products对象;在对Category访问时,可以访问对于的一组Products对象;
举例查询1:(在Supplier里面直接访问Product)
NorthWindDataContext db = new NorthWindDataContext();
GridView1.DataSource = from s in db.Suppliers
select new
{
Name = s.CompanyName,
Country = s.Country,
Products = from p in s.Products //在Supplier里面直接访问Products
select new
{
ProductName = p.ProductName
}
};
GridView1.DataBind();
}
举例2:(在Supplier里面访问Product)
NorthwindDataContext db = new NorthwindDataContext();
var suppliers = from s in db.Suppliers
where s.Products.Count > 2
select s;
foreach (Supplier supplier in suppliers) {
Response.Write("<h3>" + supplier.CompanyName + "</h3>");
foreach (Product product in supplier.Products) {
Response.Write("-- ");
Response.Write(product.ProductName);
Response.Write("<br/>");
}
}
举例3:(使用一些函数)
NorthwindDataContext db = new NorthwindDataContext();
SupplierList.DataSource = from s in db.Suppliers
where s.Products.Count > 2
select s;
SupplierList.DataBind();
NorthwindDataContext db = new NorthwindDataContext();
GridView1.DataSource = (from s in db.Suppliers
where s.Products.Count > 4
select s).Including(s => s.Products)
GridView1.DataBind();
4.联合查询
举例1:两个表的join操作。
NorthWindDataContext db = new NorthWindDataContext();
var custTotalOrders = from c in db.Customers
join o in db.Orders
on c.CustomerID equals o.CustomerID into custOrders //存放早custOrders的临时结果集中
from o in custOrders //从这个结果集中继续查询
select new
{
Customer = c.CompanyName,
OrderDate = o.OrderDate,
OrderTotal = o.Order_Details.Sum(d => d.UnitPrice * d.Quantity)
};
GridView1.DataSource = custTotalOrders;
GridView1.DataBind();
举例2:(直接查询)
var innerJoinQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID
select new { ProductName = prod.Name, Category = category.Name };
5.数据分页
相对于以前的TSQL语句而言,不是一次把所有的数据都取出来,而是根据需要从数据库中读取数据。
int startRow = Convert.ToInt32(Request.QueryString["startRow"]);
NorthwindDataContext db = new NorthwindDataContext();
var results = from c in db.Customers join o in db.Orders
on c.CustomerID equals o.CustomerID into custOrders
from o in custOrders
select new {
Customer = c.CompanyName,
OrderDate = o.OrderDate,
OrderTotal = o.OrderDetails.Sum(d=>d.UnitPrice)
};
GridView1.DataSource = results.Skip(startRow).Take(10);//分页
GridView1.DataBind();
5.更新一个特定的产品
NorthwindDataContext db = new NorthwindDataContext();
Product product = db.Products.Single(p => p.ProductName== "Chai");
product.UnitsInStock = 11;
product.ReorderLevel = 10;
product.UnitsOnOrder = 2;
db.SubmitChanges();
6.插入记录
Order ord = new Order
{
OrderID = 12000,
ShipCity = "Seattle",
OrderDate = DateTime.Now
// …
};
// Add the new object to the Orders collection.
db.Orders.InsertOnSubmit(ord);
// Submit the change to the database.
try
{
db.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e);
// Make some adjustments.
// ...
// Try again.
db.SubmitChanges();
}
7.删除记录
var deleteOrderDetails =
from details in db.OrderDetails
where details.OrderID == 11000
select details;
foreach (var detail in deleteOrderDetails)
{
db.OrderDetails.DeleteOnSubmit(detail);
}
try
{
db.SubmitChanges();
}
catch (Exception e)
{
Console.WriteLine(e);
// Provide for exceptions.
}
8.其它的LINQ for SQL 功能
? 能够执行实体/属性验证
? 可以使用存储过程和视图
? 可以清晰地定义和参与内部的TransactionScope()操作(简单分布式事务的写法,可以指定事务是否包含在上一层事务中,通过DataContex制定)
? 实体类型的保存可以使用同一个DataContext进行多种数据查询
9.LINQ to XML
以前的操作方式:流的方式、结点的方式
? XML 文档的更好的操作
? 支持language integrated queries
? 更方便,更快速更智能更简单的XML API
NorthwindDataContext db = new NorthwindDataContext();
XElement rssRoot = new XElement("rss",
new XAttribute("version", "2.0"),
new XElement("channel"
new XElement("title", "My RSS Feed"),
new XElement("link", "http://weblogs.asp.net"),
new XElement("description", "Northwind Products Feed"),
from product in db.Products
orderby product.ProductName descending
select new XElement("item",
new XElement("title", product.ProductName),
new XElement("link", “p.aspx?id="+product.ProductID),
new XElement("description", "Supplier: " +
product.Supplier.CompanyName)
)
)
);
Response.Write(rssRoot.ToString());