用LINQ to SQL 进行数据访问、更新和删除（附Demo示例程序下载）
用LINQ to SQL 进行数据访问、更新和删除（附Demo示例程序下载）
Posted by EntLib.com, http://www.EntLib.com
2008-6-29
本示例主要包括如下内容：
映射LINQ到数据库 – LINQ to SQL定义新的C# 类、properties、attributes，通过在程序中映射数据库表到实体对象，可以与数据库表交互。
DataContext 类 – 该类支持LINQ的ORM功能。
本示例程序采用Northwind 数据库中的Customers 表。
映射实体类到数据库表：
[Table(Name = "Customers")]
public class Customer
{
private string _CustomerID;
private string _CompanyName;
private string _ContactName;
private string _ContactTitle;
映射字段和属性到数据表列：
[Column(Name = "CustomerID", Storage = "_CustomerID", DbType = "nchar NOT NULL",
IsPrimaryKey = true, IsDbGenerated = false)]
public string CustomerID
{
get { return _CustomerID; }
set { _CustomerID = value; }
}
[Column(Name = "CompanyName", Storage = "_CompanyName", DbType = "nvarchar NOT NULL")]
public string CompanyName
{
get { return _CompanyName; }
set { _CompanyName = value; }
}
创建DataContext 对象：
public partial class CustomersDataContext : DataContext
{
public Table<Customer> Customers;
public CustomersDataContext(String connString) : base(connString) { }
}
查询数据库表Customers / 新增数据记录：
CustomersDataContext customersDataContext = new CustomersDataContext(connString);
// Redirect the log to the console
customersDataContext.Log = Console.Out;
var query = from cust in customersDataContext.Customers
where cust.CustomerID.StartsWith("AN")
select new { cust.CustomerID, cust.CompanyName, cust.ContactName, cust.ContactTitle };
foreach (var row in query)
{
ObjectDumper.Write(row);
}
// Console.WriteLine(customers.GetCommand(query).CommandText);
Customer customer = new Customer();
customer.CustomerID = "Jacky";
customer.CompanyName = "EntLib.com";
customer.ContactName = "http://www.EntLib.com";
customer.ContactTitle = "Developer";
customersDataContext.Customers.InsertOnSubmit(customer);
// The SubmitChanges Method propagates changes to the database
customersDataContext.SubmitChanges();
示例程序界面如下：
如下是Console数据的SQL脚本，分别为查询、更新和删除操作。
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle]
FROM [Customers] AS [t0]
WHERE [t0].[CustomerID] LIKE @p0
-- @p0: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [AN%]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
CustomerID=ANATR CompanyName=Ana Trujillo Emparedados y helados ContactName=Ana Trujillo ContactTitle=Owner
CustomerID=ANTON CompanyName=Antonio Moreno Taquería ContactName=Antonio Moreno ContactTitle=Owner
INSERT INTO [Customers]([CustomerID], [CompanyName], [ContactName], [ContactTitle])
VALUES (@p0, @p1, @p2, @p3)
-- @p0: Input NChar (Size = 5; Prec = 0; Scale = 0) [Jacky]
-- @p1: Input NVarChar (Size = 10; Prec = 0; Scale = 0) [EntLib.com]
-- @p2: Input NVarChar (Size = 21; Prec = 0; Scale = 0) [http://www.EntLib.com]
-- @p3: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [Developer]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
{Inserts: 0, Deletes: 0, Updates: 0}
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle]
FROM [Customers] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Jacky]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
UPDATE [Customers]
SET [CompanyName] = @p4, [ContactTitle] = @p5
WHERE ([CustomerID] = @p0) AND ([CompanyName] = @p1) AND ([ContactName] = @p2) AND ([ContactTitle] = @p3)
-- @p0: Input NChar (Size = 5; Prec = 0; Scale = 0) [Jacky]
-- @p1: Input NVarChar (Size = 10; Prec = 0; Scale = 0) [EntLib.com]
-- @p2: Input NVarChar (Size = 21; Prec = 0; Scale = 0) [http://www.EntLib.com]
-- @p3: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [Developer]
-- @p4: Input NVarChar (Size = 19; Prec = 0; Scale = 0) [EntLib.com-专业电子商务系统]
-- @p5: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [高级软件开发工程师]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle]
FROM [Customers] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input NVarChar (Size = 5; Prec = 0; Scale = 0) [Jacky]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
DELETE FROM [Customers] WHERE ([CustomerID] = @p0) AND ([CompanyName] = @p1) AND ([ContactName] = @p2) AND ([ContactTitle] = @p3)
-- @p0: Input NChar (Size = 5; Prec = 0; Scale = 0) [Jacky]
-- @p1: Input NVarChar (Size = 19; Prec = 0; Scale = 0) [EntLib.com-专业电子商务系统]
-- @p2: Input NVarChar (Size = 21; Prec = 0; Scale = 0) [http://www.EntLib.com]
-- @p3: Input NVarChar (Size = 9; Prec = 0; Scale = 0) [高级软件开发工程师]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8
