共用方式為

LINQ to SQL 的功能

  • 發行項

LINQ to SQL 支援 SQL 開發人員需要的所有重要功能。 您可以查詢資訊，以及在資料表中插入、更新和刪除資訊。

選取

選取 (投影) 的方式很簡單，只要使用您自己的程式語言，撰寫 LINQ 查詢，再執行該查詢來擷取結果即可。 LINQ to SQL 本身就會將所有必要的作業，轉譯成您熟悉的必要 SQL 作業。 如需詳細資訊，請參閱 LINQ to SQL。

在下列範例中，會擷取 London 客戶的公司名稱，並將它們顯示在主控台視窗中。

// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");
// or, if you are not using SQL Server Express
// Northwnd nw = new Northwnd("Database=Northwind;Server=server_name;Integrated Security=SSPI");

var companyNameQuery =
    from cust in nw.Customers
    where cust.City == "London"
    select cust.CompanyName;

foreach (var customer in companyNameQuery)
{
    Console.WriteLine(customer);
}

' Northwnd inherits from System.Data.Linq.DataContext.
Dim nw As New Northwnd("c:\northwnd.mdf")
' or, if you are not using SQL Server Express
' Dim nw As New Northwnd("Database=Northwind;Server=dschwart7;Integrated Security=SSPI")

Dim companyNameQuery = _
    From cust In nw.Customers _
    Where cust.City = "London" _
    Select cust.CompanyName

For Each customer In companyNameQuery
    Console.WriteLine(customer)
Next

插入

若要執行 SQL Insert，只要將物件加入至所建立的物件模型，並在 SubmitChanges 上呼叫 DataContext即可。

在下列範例中，會使用 Customers 將新的客戶與該客戶的相關資訊加入至 InsertOnSubmit資料表。

// Northwnd inherits from System.Data.Linq.DataContext.
Northwnd nw = new Northwnd(@"northwnd.mdf");

Customer cust = new Customer();
cust.CompanyName = "SomeCompany";
cust.City = "London";
cust.CustomerID = "98128";
cust.PostalCode = "55555";
cust.Phone = "555-555-5555";
nw.Customers.InsertOnSubmit(cust);

// At this point, the new Customer object is added in the object model.
// In LINQ to SQL, the change is not sent to the database until
// SubmitChanges is called.
nw.SubmitChanges();

' Northwnd inherits from System.Data.Linq.DataContext.
Dim nw As New Northwnd("c:\northwnd.mdf")

Dim cust As New Customer With {.CompanyName = "SomeCompany", _
    .City = "London", _
    .CustomerID = 98128, _
    .PostalCode = 55555, .Phone = "555-555-5555"}
nw.Customers.InsertOnSubmit(cust)
' At this point, the new Customer object is added in the object model.
' In LINQ to SQL, the change is not sent to the database until
' SubmitChanges is called.
nw.SubmitChanges()

更新

若要更新 ( Update ) 資料庫項目，請先擷取該項目，然後直接在物件模型中編輯該項目。 修改物件之後，再於 SubmitChanges 上呼叫 DataContext 以更新資料庫。

在下列範例中，會擷取所有來自 London 的客戶， 然後將城市的名稱從 "London" 變更為 "London - Metro"。 最後，會呼叫 SubmitChanges ，以將變更傳送至資料庫。

Northwnd nw = new Northwnd(@"northwnd.mdf");

var cityNameQuery =
    from cust in nw.Customers
    where cust.City.Contains("London")
    select cust;

foreach (var customer in cityNameQuery)
{
    if (customer.City == "London")
    {
        customer.City = "London - Metro";
    }
}
nw.SubmitChanges();

Dim nw As New Northwnd("c:\northwnd.mdf")
Dim cityNameQuery = _
    From cust In nw.Customers _
    Where cust.City.Contains("London") _
    Select cust

For Each customer In cityNameQuery
    If customer.City = "London" Then
        customer.City = "London - Metro"
    End If
Next
nw.SubmitChanges()

刪除中

若要刪除 ( Delete ) 項目，請從項目所屬的集合中移除該項目，然後在 SubmitChanges 上呼叫 DataContext ，以認可變更。

注意

LINQ to SQL 無法辨識串聯刪除作業。 若要刪除資料表中設有條件約束的資料列，請參閱操作說明：從資料庫刪除資料列。

在下列範例中，會從資料庫中擷取 CustomerID 為 98128 的客戶。 然後會在確認已擷取該客戶的資料列之後，呼叫 DeleteOnSubmit ，以從集合中移除該物件。 最後，會呼叫 SubmitChanges ，以將刪除轉送至資料庫。

Northwnd nw = new Northwnd(@"northwnd.mdf");
var deleteIndivCust =
    from cust in nw.Customers
    where cust.CustomerID == "98128"
    select cust;

if (deleteIndivCust.Count() > 0)
{
    nw.Customers.DeleteOnSubmit(deleteIndivCust.First());
    nw.SubmitChanges();
}

Dim nw As New Northwnd("c:\northwnd.mdf")
Dim deleteIndivCust = _
    From cust In nw.Customers _
    Where cust.CustomerID = 98128 _
    Select cust

If deleteIndivCust.Count > 0 Then
    nw.Customers.DeleteOnSubmit(deleteIndivCust.First)
    nw.SubmitChanges()
End If

另請參閱