本系列,或多或少,直接或間接依賴入門系列知識。但,依然追求獨立成章。因本文作者水平有限, 文中錯誤難免,敬請讀者指出並諒解。本系列將會和入門並存。
案例
某君被邀為一超市設計數據庫,用來存儲數據。該君根據該超市中實際出現的對象,設計了 Customer, Employee,Order, Product等表,用來保存相應的客戶,員工,訂單,貨品等。太好了,該 君很有oo的思想嗎。
如果,你被要求用類及對象,來描述該關系型數據,你該如何做呢?在linq推出之前,ADO.NET被用 來做數據訪問層。而後,程序員需要自己去編寫事務邏輯層中所出現的類。比如,Customer, Employee ,Order, Product等。然後,程序員組裝所需的sql語句,通過ADO.NET,將返回的記錄,來初始化 Customer等類的對象。在這裡,你已經自己動手將Customer表和Customer類關聯了起來。從Linq To Sql 的設計來看,它主要是為了解決data!=objects 的問題而產生的。現在,有了Table和Class之間的映射 ,數據和對象之間就可以有一個一一對應的關系了。
在Linq To Sql之前,在java領域有Hibernate,在net領域有NHibernate技術,來實現 object/relational 持久和查詢服務。無論是Hibernate還是NHibernate,其配置復雜,上手時間長,已 經不能適應快速開發的需要。而Linq To Sql的推出,恰恰彌補了它們的缺點,徹底降低了程序開發門檻 。
Linq
Linq是Language Integrated Query的縮寫。Linq To Sql是linq中的一部分,其與ADO.NET Orcas的 關系入下。
現在linq分三個部分。Linq To Objects,即以前的linq。其主要是針對CLR-Based Objects的查詢。 即內存操作。Linq Enabled ADO.NET是針對關系型數據的。這又包含三個部分。Linq To Datasets, Linq To Sql, Linq To Entities. 其中Linq To Sql 是大家所熟悉的部分,即以前的Dlinq. 官方的解 釋是,Linq To Datasets, support for ADO.NET Datasets; Linq to SQL, support for SQL Server; Linq to Entities, Support fro Entity Data Model. 記得,以前曾有人問及dlinq與ADO.NET Orcas的 關系,因為它們隸屬於不同的隊伍開發,其中有重疊的部分。而現在,其功能歸結在一起,其重疊部分 已經得到融合。(如果有人是ADO.NET team或熟悉這個的,開辟專欄給我們大家講一下呀。)最後一部 分叫Linq To XML,即以前的Xlinq. 針對xml格式數據的操作。(還有針對ASP.NET的Blinq, 大聲問一下 ,有沒有人懂這個呀)
DBML
所謂dbml,即Database Mark Language。數據庫描述語言,是一種xml格式的文檔,用來描述數據庫 。上面我們講了,不是用類來描述數據嗎?為什麼又有個dbml?是的,dbml只是個中間的產物,其出現 的主要原因是,適應c# 和vb.net語言的不同,做中間緩沖。dbml及數據庫和code關系如下。
Database ----> DBML ------------> Code.
使用sqlmetal可以產生dbml。鍵入如下命令:
sqlmetal /server:yourserver /database:northwind /dbml:YourDbml.dbml
最終可以得到dbml文件,如下:
<?xml version="1.0" encoding="utf-16"?>
<Database Name="northwind" Class="Northwind" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
<Table Name="dbo.Categories" Member="Categories">
<Type Name="Categories">
<Column Name="CategoryID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
<Column Name="CategoryName" Type="System.String" DbType="NVarChar(15) NOT NULL" CanBeNull="false" />
<Column Name="Description" Type="System.String" DbType="NText" CanBeNull="true" UpdateCheck="Never" />
<Column Name="Picture" Type="System.Data.Linq.Binary" DbType="Image" CanBeNull="true" UpdateCheck="Never" />
<Association Name="FK_Products_Categories" Member="Products" OtherKey="CategoryID" Type="Products" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.CustomerCustomerDemo" Member="CustomerCustomerDemo">
<Type Name="CustomerCustomerDemo">
<Column Name="CustomerID" Type="System.String" DbType="NChar(5) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="CustomerTypeID" Type="System.String" DbType="NChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Association Name="FK_CustomerCustomerDemo" Member="CustomerDemographics" ThisKey="CustomerTypeID" Type="CustomerDemographics" IsForeignKey="true" />
<Association Name="FK_CustomerCustomerDemo_Customers" Member="Customers" ThisKey="CustomerID" Type="Customers" IsForeignKey="true" />
</Type>
</Table>
<Table Name="dbo.CustomerDemographics" Member="CustomerDemographics">
<Type Name="CustomerDemographics">
<Column Name="CustomerTypeID" Type="System.String" DbType="NChar(10) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="CustomerDesc" Type="System.String" DbType="NText" CanBeNull="true" UpdateCheck="Never" />
<Association Name="FK_CustomerCustomerDemo" Member="CustomerCustomerDemo" OtherKey="CustomerTypeID" Type="CustomerCustomerDemo" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.Customers" Member="Customers">
<Type Name="Customers">
<Column Name="CustomerID" Type="System.String" DbType="NChar(5) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="CompanyName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
<Column Name="ContactName" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="ContactTitle" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="Address" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
<Column Name="City" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Region" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="PostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
<Column Name="Country" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Phone" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
<Column Name="Fax" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
<Association Name="FK_CustomerCustomerDemo_Customers" Member="CustomerCustomerDemo" OtherKey="CustomerID" Type="CustomerCustomerDemo" DeleteRule="NO ACTION" />
<Association Name="FK_Orders_Customers" Member="Orders" OtherKey="CustomerID" Type="Orders" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.Employees" Member="Employees">
<Type Name="Employees">
<Column Name="EmployeeID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
<Column Name="LastName" Type="System.String" DbType="NVarChar(20) NOT NULL" CanBeNull="false" />
<Column Name="FirstName" Type="System.String" DbType="NVarChar(10) NOT NULL" CanBeNull="false" />
<Column Name="Title" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="TitleOfCourtesy" Type="System.String" DbType="NVarChar(25)" CanBeNull="true" />
<Column Name="BirthDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
<Column Name="HireDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
<Column Name="Address" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
<Column Name="City" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Region" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="PostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
<Column Name="Country" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="HomePhone" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
<Column Name="Extension" Type="System.String" DbType="NVarChar(4)" CanBeNull="true" />
<Column Name="Photo" Type="System.Data.Linq.Binary" DbType="Image" CanBeNull="true" UpdateCheck="Never" />
<Column Name="Notes" Type="System.String" DbType="NText" CanBeNull="true" UpdateCheck="Never" />
<Column Name="ReportsTo" Type="System.Int32" DbType="Int" CanBeNull="true" />
<Column Name="PhotoPath" Type="System.String" DbType="NVarChar(255)" CanBeNull="true" />
<Association Name="FK_Employees_Employees" Member="ReportsToEmployees" ThisKey="ReportsTo" Type="Employees" IsForeignKey="true" />
<Association Name="FK_Employees_Employees" Member="Employee" OtherKey="ReportsTo" Type="Employees" DeleteRule="NO ACTION" />
<Association Name="FK_EmployeeTerritories_Employees" Member="EmployeeTerritories" OtherKey="EmployeeID" Type="EmployeeTerritories" DeleteRule="NO ACTION" />
<Association Name="FK_Orders_Employees" Member="Orders" OtherKey="EmployeeID" Type="Orders" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.EmployeeTerritories" Member="EmployeeTerritories">
<Type Name="EmployeeTerritories">
<Column Name="EmployeeID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="TerritoryID" Type="System.String" DbType="NVarChar(20) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Association Name="FK_EmployeeTerritories_Employees" Member="Employees" ThisKey="EmployeeID" Type="Employees" IsForeignKey="true" />
<Association Name="FK_EmployeeTerritories_Territories" Member="Territories" ThisKey="TerritoryID" Type="Territories" IsForeignKey="true" />
</Type>
</Table>
<Table Name="dbo.Order Details" Member="OrderDetails">
<Type Name="OrderDetails">
<Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="UnitPrice" Type="System.Decimal" DbType="Money NOT NULL" CanBeNull="false" />
<Column Name="Quantity" Type="System.Int16" DbType="SmallInt NOT NULL" CanBeNull="false" />
<Column Name="Discount" Type="System.Single" DbType="Real NOT NULL" CanBeNull="false" />
<Association Name="FK_Order_Details_Orders" Member="Orders" ThisKey="OrderID" Type="Orders" IsForeignKey="true" />
<Association Name="FK_Order_Details_Products" Member="Products" ThisKey="ProductID" Type="Products" IsForeignKey="true" />
</Type>
</Table>
<Table Name="dbo.Orders" Member="Orders">
<Type Name="Orders">
<Column Name="OrderID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
<Column Name="CustomerID" Type="System.String" DbType="NChar(5)" CanBeNull="true" />
<Column Name="EmployeeID" Type="System.Int32" DbType="Int" CanBeNull="true" />
<Column Name="OrderDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
<Column Name="RequiredDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
<Column Name="ShippedDate" Type="System.DateTime" DbType="DateTime" CanBeNull="true" />
<Column Name="ShipVia" Type="System.Int32" DbType="Int" CanBeNull="true" />
<Column Name="Freight" Type="System.Decimal" DbType="Money" CanBeNull="true" />
<Column Name="ShipName" Type="System.String" DbType="NVarChar(40)" CanBeNull="true" />
<Column Name="ShipAddress" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
<Column Name="ShipCity" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="ShipRegion" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="ShipPostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
<Column Name="ShipCountry" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Association Name="FK_Order_Details_Orders" Member="OrderDetails" OtherKey="OrderID" Type="OrderDetails" DeleteRule="NO ACTION" />
<Association Name="FK_Orders_Customers" Member="Customers" ThisKey="CustomerID" Type="Customers" IsForeignKey="true" />
<Association Name="FK_Orders_Employees" Member="Employees" ThisKey="EmployeeID" Type="Employees" IsForeignKey="true" />
<Association Name="FK_Orders_Shippers" Member="Shippers" ThisKey="ShipVia" Type="Shippers" IsForeignKey="true" />
</Type>
</Table>
<Table Name="dbo.Products" Member="Products">
<Type Name="Products">
<Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
<Column Name="ProductName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
<Column Name="SupplierID" Type="System.Int32" DbType="Int" CanBeNull="true" />
<Column Name="CategoryID" Type="System.Int32" DbType="Int" CanBeNull="true" />
<Column Name="QuantityPerUnit" Type="System.String" DbType="NVarChar(20)" CanBeNull="true" />
<Column Name="UnitPrice" Type="System.Decimal" DbType="Money" CanBeNull="true" />
<Column Name="UnitsInStock" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
<Column Name="UnitsOnOrder" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
<Column Name="ReorderLevel" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
<Column Name="Discontinued" Type="System.Boolean" DbType="Bit NOT NULL" CanBeNull="false" />
<Association Name="FK_Order_Details_Products" Member="OrderDetails" OtherKey="ProductID" Type="OrderDetails" DeleteRule="NO ACTION" />
<Association Name="FK_Products_Categories" Member="Categories" ThisKey="CategoryID" Type="Categories" IsForeignKey="true" />
<Association Name="FK_Products_Suppliers" Member="Suppliers" ThisKey="SupplierID" Type="Suppliers" IsForeignKey="true" />
</Type>
</Table>
<Table Name="dbo.Region" Member="Region">
<Type Name="Region">
<Column Name="RegionID" Type="System.Int32" DbType="Int NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="RegionDescription" Type="System.String" DbType="NChar(50) NOT NULL" CanBeNull="false" />
<Association Name="FK_Territories_Region" Member="Territories" OtherKey="RegionID" Type="Territories" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.Shippers" Member="Shippers">
<Type Name="Shippers">
<Column Name="ShipperID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
<Column Name="CompanyName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
<Column Name="Phone" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
<Association Name="FK_Orders_Shippers" Member="Orders" OtherKey="ShipVia" Type="Orders" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.Suppliers" Member="Suppliers">
<Type Name="Suppliers">
<Column Name="SupplierID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
<Column Name="CompanyName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
<Column Name="ContactName" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="ContactTitle" Type="System.String" DbType="NVarChar(30)" CanBeNull="true" />
<Column Name="Address" Type="System.String" DbType="NVarChar(60)" CanBeNull="true" />
<Column Name="City" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Region" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="PostalCode" Type="System.String" DbType="NVarChar(10)" CanBeNull="true" />
<Column Name="Country" Type="System.String" DbType="NVarChar(15)" CanBeNull="true" />
<Column Name="Phone" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
<Column Name="Fax" Type="System.String" DbType="NVarChar(24)" CanBeNull="true" />
<Column Name="HomePage" Type="System.String" DbType="NText" CanBeNull="true" UpdateCheck="Never" />
<Association Name="FK_Products_Suppliers" Member="Products" OtherKey="SupplierID" Type="Products" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.Territories" Member="Territories">
<Type Name="Territories">
<Column Name="TerritoryID" Type="System.String" DbType="NVarChar(20) NOT NULL" IsPrimaryKey="true" CanBeNull="false" />
<Column Name="TerritoryDescription" Type="System.String" DbType="NChar(50) NOT NULL" CanBeNull="false" />
<Column Name="RegionID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
<Association Name="FK_EmployeeTerritories_Territories" Member="EmployeeTerritories" OtherKey="TerritoryID" Type="EmployeeTerritories" DeleteRule="NO ACTION" />
<Association Name="FK_Territories_Region" Member="Region" ThisKey="RegionID" Type="Region" IsForeignKey="true" />
</Type>
</Table>
</Database>
也可以使用該dbml生成code,命令如下,可以用language選項,控制生成vb.net或c#語言的代碼。
sqlmetal YourDbml.dbml /code: nwind.cs
在C#3.0入門系列(七)--之OR工具介紹(上)/C#3.0入門系列(七)--之OR工具介紹(下) 一文中 ,我們介紹了OR Designer工具,它生成的就是dbml,可以使用記事本打開DataClasses1.dbml 文件來看 。有些屬性,是無法從數據庫中抽提出來的,比如繼承,等。而我們又想對其映射做繼承,就需要我們 自己手工去修改dbml。好在OR Designer提供這些功能(以後再介紹)。
關系型數據的映射
數據間的關系,有2種基本關系。1: 1 和1: M。可以參閱C# 3.0入門系列(二)一文,查閱 northwind數據庫中的關系圖。本文用Order 和Order Detail 表,來闡述其關系的映射。Order 為訂單 ,Order Detail 為訂單詳情。其關系形式為,一條Order記錄對應多條Order Detail 記錄。
你可以在上面產生的code中,找到相應的order類,其中有這麼一段
[Table(Name="Orders")]
public partial class Order : INotifyPropertyChanging, INotifyPropertyChanged
{
[Association(Name="Order_OrderDetail", Storage="_OrderDetails", OtherKey="OrderID")]
public EntitySet<OrderDetail> OrderDetails
{
get
{
return this._OrderDetails;
}
set
{
this._OrderDetails.Assign(value);
}
}
}
在Order類中,有個property,叫OrderDetails,是EntitySet<OrderDetail> 類型的。 EntitySet是個集合類型的模板。 其繼承關系如下
EntitySet<TEntity> : IList, ICollection, IList<TEntity>, ICollection<TEntity>, IEnumerable<TEntity>, IEnumerable, IListSource where TEntity : class
在OrderDetails類中,也可以找到這麼一段。
[Table(Name="Order Details")]
public partial class OrderDetail : INotifyPropertyChanging, INotifyPropertyChanged
{
private EntityRef<Order> _Order;
[Association(Name="Order_OrderDetail", Storage="_Order", ThisKey="OrderID", IsForeignKey=true)]
public Order Order
{
get
{
return this._Order.Entity;
}
set
{
Order previousValue = this._Order.Entity;
if (((previousValue != value)
|| (this._Order.HasLoadedOrAssignedValue == false)))
{
this.SendPropertyChanging();
if ((previousValue != null))
{
this._Order.Entity = null;
previousValue.OrderDetails.Remove (this);
}
this._Order.Entity = value;
if ((value != null))
{
value.OrderDetails.Add(this);
this._OrderID = value.OrderID;
}
else
{
this._OrderID = default(int);
}
this.SendPropertyChanged("Order");
}
}
}
}
也就是說Order 在OrderDetail類中,是以EntityRef出現的。這樣,在關系雙方的各端,我們使用 EntityRef和EntitySet來表示其關系。簡言之,One在Many端,以EntityRef出現,而Many在One端,以 EntitySet出現。上例中,在property 中,因其返回的是this._Order.Entity,直接返回的是Order。
對於1:1的關系,雙雙彼此在各自的類中,均以EntityRef出現。大家可以自己試。這樣,Order和 OrderDetail的關系,在各自的類中,都有了體現。體現方式的不同,反映了它們關系主體的不同。