1.) 手工书写代码,自定义需要映射的表和关系
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq; //DataContext在此命名空间下
using System.Data.Linq.Mapping; //[Database] [Tabale] 等属性在此命名空间下
namespace LinqConsole
{
[Database(Name = "Northwind")]
class MyContext : DataContext
{
public MyContext(string conStr)
: base(conStr)
{ }
// MyContext中唯一的表,Orders利用Association关联,MyContext中并没有保留Orders的信息
public Table<MyCustomer> Customers;
}
[Table(Name = "Customers")]
public class MyCustomer
{
[Column(IsPrimaryKey = true)]
public string CustomerID;
[Column]
public string CompanyName;
[Column]
public string Country;
[Association(ThisKey = "CustomerID", OtherKey = "CustomerID")]
public EntitySet<MyOrder> Orders;
}
[Table(Name = "Orders")]
public class MyOrder
{
[Column(IsPrimaryKey = true)]
public int OrderID;
[Column]
public string CustomerID;
}
class Program
{
static void Main(string[] args)
{
MyContext ctx = new MyContext("server=.");
var query = from c in ctx.Customers
select c;
foreach (MyCustomer c in query)
{
Console.WriteLine("{0},{1},{2}", c.CustomerID, c.CompanyName, c.Country);
//通过Customer表中的 Association 获得Orders表中数据
Console.WriteLine(c.Orders.Count);
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq; //DataContext在此命名空间下
using System.Data.Linq.Mapping; //[Database] [Tabale] 等属性在此命名空间下
namespace LinqConsole
{
[Database(Name = "Northwind")]
class MyContext : DataContext
{
public MyContext(string conStr)
: base(conStr)
{ }
// MyContext中唯一的表,Orders利用Association关联,MyContext中并没有保留Orders的信息
public Table<MyCustomer> Customers;
}
[Table(Name = "Customers")]
public class MyCustomer
{
[Column(IsPrimaryKey = true)]
public string CustomerID;
[Column]
public string CompanyName;
[Column]
public string Country;
[Association(ThisKey = "CustomerID", OtherKey = "CustomerID")]
public EntitySet<MyOrder> Orders;
}
[Table(Name = "Orders")]
public class MyOrder
{
[Column(IsPrimaryKey = true)]
public int OrderID;
[Column]
public string CustomerID;
}
class Program
{
static void Main(string[] args)
{
MyContext ctx = new MyContext("server=.");
var query = from c in ctx.Customers
select c;
foreach (MyCustomer c in query)
{
Console.WriteLine("{0},{1},{2}", c.CustomerID, c.CompanyName, c.Country);
//通过Customer表中的 Association 获得Orders表中数据
Console.WriteLine(c.Orders.Count);
}
}
}
}
2.) 使用Sqlmetal自动生成code(与在 VS2008可视化操作结果一样)
Sqlmetal 使用如下:
>sqlmetal /?
Microsoft (R) Database Mapping Generator 2008 Beta 2 version 1.00.20706
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.
SqlMetal [options] [<input file>]
Generates code and mapping for the LINQ to SQL component of the .NET framework. SqlMetal can:
- Generate source code and mapping attributes or a mapping file from a database.
- Generate an intermediate dbml file for customization from the database.
- Generate code and mapping attributes or mapping file from a dbml file.
Options:
/server:<name> Database server name.
/database:<name> Database catalog on server.
/user:<name> Login user ID (default: use Windows Authentication).
/password:<password> Login password (default: use Windows Authentication).
/conn:<connection string> Database connection string. Cannot be used with /server, /database, /user or /password option
/timeout:<seconds> Timeout value to use when SqlMetal accesses the database (default: 0 which means infinite).
/views Extract database views.
/functions Extract database functions.
/sprocs Extract stored procedures.
/dbml[:file] Output as dbml. Cannot be used with /map option.
/code[:file] Output as source code. Cannot be used with /dbml option.
/map[:file] Generate mapping file, not attributes. Cannot be used with /dbml option.
/language:<language> Language for source code: VB or C# (default: derived from extension on code file name).
/namespace:<name> Namespace of generated code (default: no namespace).
/context:<type> Name of data context class (default: derived from database name).
/entitybase:<type> Base class of entity classes in the generated code (default: entities have no base class).
/pluralize Automatically pluralize or singularize class and member names using English language rules.
/serialization:<option> Generate serializable classes: None or Unidirectional (default: None).
/provider:<type> Provider type (default: provider is determined at run time).
<input file> May be a SqlExpress mdf file, a SqlCE sdf file, or a dbml intermediate file.
Create code from SqlServer:
SqlMetal /server:myserver /database:northwind /code:nwind.cs /namespace:nwind
Generate intermediate dbml file from SqlServer:
SqlMetal /server:myserver /database:northwind /dbml:northwind.dbml /namespace:nwind
Generate code with external mapping from dbml:
SqlMetal /code:nwind.cs /map:nwind.map northwind.dbml
Generate dbml from a SqlCE sdf file:
SqlMetal /dbml:northwind.dbml northwind.sdf
Generate dbml from SqlExpress local server:
SqlMetal /server:.\sqlexpress /database:northwind /dbml:northwind.dbml
Generate dbml by using a connection string in the command line:
SqlMetal /conn:"server='myserver'; database='northwind'" /dbml:northwind.dbml
Microsoft (R) Database Mapping Generator 2008 Beta 2 version 1.00.20706
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.
SqlMetal [options] [<input file>]
Generates code and mapping for the LINQ to SQL component of the .NET framework. SqlMetal can:
- Generate source code and mapping attributes or a mapping file from a database.
- Generate an intermediate dbml file for customization from the database.
- Generate code and mapping attributes or mapping file from a dbml file.
Options:
/server:<name> Database server name.
/database:<name> Database catalog on server.
/user:<name> Login user ID (default: use Windows Authentication).
/password:<password> Login password (default: use Windows Authentication).
/conn:<connection string> Database connection string. Cannot be used with /server, /database, /user or /password option
/timeout:<seconds> Timeout value to use when SqlMetal accesses the database (default: 0 which means infinite).
/views Extract database views.
/functions Extract database functions.
/sprocs Extract stored procedures.
/dbml[:file] Output as dbml. Cannot be used with /map option.
/code[:file] Output as source code. Cannot be used with /dbml option.
/map[:file] Generate mapping file, not attributes. Cannot be used with /dbml option.
/language:<language> Language for source code: VB or C# (default: derived from extension on code file name).
/namespace:<name> Namespace of generated code (default: no namespace).
/context:<type> Name of data context class (default: derived from database name).
/entitybase:<type> Base class of entity classes in the generated code (default: entities have no base class).
/pluralize Automatically pluralize or singularize class and member names using English language rules.
/serialization:<option> Generate serializable classes: None or Unidirectional (default: None).
/provider:<type> Provider type (default: provider is determined at run time).
<input file> May be a SqlExpress mdf file, a SqlCE sdf file, or a dbml intermediate file.
Create code from SqlServer:
SqlMetal /server:myserver /database:northwind /code:nwind.cs /namespace:nwind
Generate intermediate dbml file from SqlServer:
SqlMetal /server:myserver /database:northwind /dbml:northwind.dbml /namespace:nwind
Generate code with external mapping from dbml:
SqlMetal /code:nwind.cs /map:nwind.map northwind.dbml
Generate dbml from a SqlCE sdf file:
SqlMetal /dbml:northwind.dbml northwind.sdf
Generate dbml from SqlExpress local server:
SqlMetal /server:.\sqlexpress /database:northwind /dbml:northwind.dbml
Generate dbml by using a connection string in the command line:
SqlMetal /conn:"server='myserver'; database='northwind'" /dbml:northwind.dbml
生成Northwind库,生成的代码为mapping.cs,添加入Vs2008就可以直接使用生成的类、方法:
>sqlmetal /server:. /database:northwind /code:mapping.cs /language:csharp
Microsoft (R) Database Mapping Generator 2008 Beta 2 version 1.00.20706
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.
Microsoft (R) Database Mapping Generator 2008 Beta 2 version 1.00.20706
for Microsoft (R) .NET Framework version 3.5
Copyright (C) Microsoft Corporation. All rights reserved.
3.) 使用Sqlmetal生成映射文件.xml
>sqlmetal /server:. /database:northwind /map:mapping.xml
生成mapping.xml文件,部分如下:(只需要Prodcuts表和Orders表)
<?xml version="1.0" encoding="utf-8"?>
<Database Name="northwind" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Table Name="dbo.Customers" Member="Customers">
<Type Name="Customers">
<Column Name="CustomerID" Member="CustomerID" Storage="_CustomerID" DbType="NChar(5) NOT NULL" CanBeNull="false" IsPrimaryKey="true" />
<Column Name="CompanyName" Member="CompanyName" Storage="_CompanyName" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
<Column Name="ContactName" Member="ContactName" Storage="_ContactName" DbType="NVarChar(30)" />
<Column Name="ContactTitle" Member="ContactTitle" Storage="_ContactTitle" DbType="NVarChar(30)" />
<Column Name="Address" Member="Address" Storage="_Address" DbType="NVarChar(60)" />
<Column Name="City" Member="City" Storage="_City" DbType="NVarChar(15)" />
<Column Name="Region" Member="Region" Storage="_Region" DbType="NVarChar(15)" />
<Column Name="PostalCode" Member="PostalCode" Storage="_PostalCode" DbType="NVarChar(10)" />
<Column Name="Country" Member="Country" Storage="_Country" DbType="NVarChar(15)" />
<Column Name="Phone" Member="Phone" Storage="_Phone" DbType="NVarChar(24)" />
<Column Name="Fax" Member="Fax" Storage="_Fax" DbType="NVarChar(24)" />
<Association Name="FK_CustomerCustomerDemo_Customers" Member="CustomerCustomerDemo" Storage="_CustomerCustomerDemo" ThisKey="CustomerID" OtherKey="CustomerID" DeleteRule="NO ACTION" />
<Association Name="FK_Orders_Customers" Member="Orders" Storage="_Orders" ThisKey="CustomerID" OtherKey="CustomerID" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.Orders" Member="Orders">
<Type Name="Orders">
<Column Name="OrderID" Member="OrderID" Storage="_OrderID" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
<Column Name="CustomerID" Member="CustomerID" Storage="_CustomerID" DbType="NChar(5)" />
<Column Name="EmployeeID" Member="EmployeeID" Storage="_EmployeeID" DbType="Int" />
<Column Name="OrderDate" Member="OrderDate" Storage="_OrderDate" DbType="DateTime" />
<Column Name="RequiredDate" Member="RequiredDate" Storage="_RequiredDate" DbType="DateTime" />
<Column Name="ShippedDate" Member="ShippedDate" Storage="_ShippedDate" DbType="DateTime" />
<Column Name="ShipVia" Member="ShipVia" Storage="_ShipVia" DbType="Int" />
<Column Name="Freight" Member="Freight" Storage="_Freight" DbType="Money" />
<Column Name="ShipName" Member="ShipName" Storage="_ShipName" DbType="NVarChar(40)" />
<Column Name="ShipAddress" Member="ShipAddress" Storage="_ShipAddress" DbType="NVarChar(60)" />
<Column Name="ShipCity" Member="ShipCity" Storage="_ShipCity" DbType="NVarChar(15)" />
<Column Name="ShipRegion" Member="ShipRegion" Storage="_ShipRegion" DbType="NVarChar(15)" />
<Column Name="ShipPostalCode" Member="ShipPostalCode" Storage="_ShipPostalCode" DbType="NVarChar(10)" />
<Column Name="ShipCountry" Member="ShipCountry" Storage="_ShipCountry" DbType="NVarChar(15)" />
<Association Name="FK_Order_Details_Orders" Member="OrderDetails" Storage="_OrderDetails" ThisKey="OrderID" OtherKey="OrderID" DeleteRule="NO ACTION" />
<Association Name="FK_Orders_Customers" Member="Customers" Storage="_Customers" ThisKey="CustomerID" OtherKey="CustomerID" IsForeignKey="true" />
<Association Name="FK_Orders_Employees" Member="Employees" Storage="_Employees" ThisKey="EmployeeID" OtherKey="EmployeeID" IsForeignKey="true" />
<Association Name="FK_Orders_Shippers" Member="Shippers" Storage="_Shippers" ThisKey="ShipVia" OtherKey="ShipperID" IsForeignKey="true" />
</Type>
</Table>
</Database>
<Database Name="northwind" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
<Table Name="dbo.Customers" Member="Customers">
<Type Name="Customers">
<Column Name="CustomerID" Member="CustomerID" Storage="_CustomerID" DbType="NChar(5) NOT NULL" CanBeNull="false" IsPrimaryKey="true" />
<Column Name="CompanyName" Member="CompanyName" Storage="_CompanyName" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
<Column Name="ContactName" Member="ContactName" Storage="_ContactName" DbType="NVarChar(30)" />
<Column Name="ContactTitle" Member="ContactTitle" Storage="_ContactTitle" DbType="NVarChar(30)" />
<Column Name="Address" Member="Address" Storage="_Address" DbType="NVarChar(60)" />
<Column Name="City" Member="City" Storage="_City" DbType="NVarChar(15)" />
<Column Name="Region" Member="Region" Storage="_Region" DbType="NVarChar(15)" />
<Column Name="PostalCode" Member="PostalCode" Storage="_PostalCode" DbType="NVarChar(10)" />
<Column Name="Country" Member="Country" Storage="_Country" DbType="NVarChar(15)" />
<Column Name="Phone" Member="Phone" Storage="_Phone" DbType="NVarChar(24)" />
<Column Name="Fax" Member="Fax" Storage="_Fax" DbType="NVarChar(24)" />
<Association Name="FK_CustomerCustomerDemo_Customers" Member="CustomerCustomerDemo" Storage="_CustomerCustomerDemo" ThisKey="CustomerID" OtherKey="CustomerID" DeleteRule="NO ACTION" />
<Association Name="FK_Orders_Customers" Member="Orders" Storage="_Orders" ThisKey="CustomerID" OtherKey="CustomerID" DeleteRule="NO ACTION" />
</Type>
</Table>
<Table Name="dbo.Orders" Member="Orders">
<Type Name="Orders">
<Column Name="OrderID" Member="OrderID" Storage="_OrderID" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" AutoSync="OnInsert" />
<Column Name="CustomerID" Member="CustomerID" Storage="_CustomerID" DbType="NChar(5)" />
<Column Name="EmployeeID" Member="EmployeeID" Storage="_EmployeeID" DbType="Int" />
<Column Name="OrderDate" Member="OrderDate" Storage="_OrderDate" DbType="DateTime" />
<Column Name="RequiredDate" Member="RequiredDate" Storage="_RequiredDate" DbType="DateTime" />
<Column Name="ShippedDate" Member="ShippedDate" Storage="_ShippedDate" DbType="DateTime" />
<Column Name="ShipVia" Member="ShipVia" Storage="_ShipVia" DbType="Int" />
<Column Name="Freight" Member="Freight" Storage="_Freight" DbType="Money" />
<Column Name="ShipName" Member="ShipName" Storage="_ShipName" DbType="NVarChar(40)" />
<Column Name="ShipAddress" Member="ShipAddress" Storage="_ShipAddress" DbType="NVarChar(60)" />
<Column Name="ShipCity" Member="ShipCity" Storage="_ShipCity" DbType="NVarChar(15)" />
<Column Name="ShipRegion" Member="ShipRegion" Storage="_ShipRegion" DbType="NVarChar(15)" />
<Column Name="ShipPostalCode" Member="ShipPostalCode" Storage="_ShipPostalCode" DbType="NVarChar(10)" />
<Column Name="ShipCountry" Member="ShipCountry" Storage="_ShipCountry" DbType="NVarChar(15)" />
<Association Name="FK_Order_Details_Orders" Member="OrderDetails" Storage="_OrderDetails" ThisKey="OrderID" OtherKey="OrderID" DeleteRule="NO ACTION" />
<Association Name="FK_Orders_Customers" Member="Customers" Storage="_Customers" ThisKey="CustomerID" OtherKey="CustomerID" IsForeignKey="true" />
<Association Name="FK_Orders_Employees" Member="Employees" Storage="_Employees" ThisKey="EmployeeID" OtherKey="EmployeeID" IsForeignKey="true" />
<Association Name="FK_Orders_Shippers" Member="Shippers" Storage="_Shippers" ThisKey="ShipVia" OtherKey="ShipperID" IsForeignKey="true" />
</Type>
</Table>
</Database>
相关CS代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LinqConsole
{
class MyContext : DataContext
{
public MyContext(string conStr, MappingSource source)
: base(conStr, source)
{
}
}
public class Customers
{
private string _CustomerID;
public string CustomerID { get{return _CustomerID;} set{_CustomerID=value;} }
}
class Program
{
static void Main(string[] args)
{
DataContext ctx = new DataContext("server=.database=northwind", XmlMappingSource.FromUrl("mapping.xml"));
var query = from c in ctx.GetTable<Customers>()
select c;
foreach (Customers c in query)
{
Console.WriteLine("{0},{1},{2}", c.CustomerID);
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LinqConsole
{
class MyContext : DataContext
{
public MyContext(string conStr, MappingSource source)
: base(conStr, source)
{
}
}
public class Customers
{
private string _CustomerID;
public string CustomerID { get{return _CustomerID;} set{_CustomerID=value;} }
}
class Program
{
static void Main(string[] args)
{
DataContext ctx = new DataContext("server=.database=northwind", XmlMappingSource.FromUrl("mapping.xml"));
var query = from c in ctx.GetTable<Customers>()
select c;
foreach (Customers c in query)
{
Console.WriteLine("{0},{1},{2}", c.CustomerID);
}
}
}
}