LINQ SQL

LINQ為SQL提供了一個基礎結構(執行時間)，關係資料作為物件的管理。 這是3.5版本的.NET框架的一個組成部分，並巧妙地做物件模型到SQL的語言整合查詢的轉換。這些查詢被傳送給資料庫來執行。從資料庫中獲得的結果後，LINQ到SQL再次它們轉換為物件。

LINQ到SQL簡介

對於大多數ASP.NET開發，LINQ到SQL（也稱為DLINQ）是Langauage整合查詢通用部分，因為這允許在SQL伺服器資料庫查詢資料通過使用常規的LINQ表示式。它還允許更新，刪除和插入資料，但它受到的唯一缺點是它受SQL伺服器資料庫的限制。 但是，也有LINQ的許多好處，SQL在ADO.NET一樣降低了複雜性，編碼等等。

下面是表示LINQ執行架構到SQL的示意圖。

如何使用LINQ到SQL?

• 步驟 1: 建立一個新的“資料連線”到資料庫伺服器。View -> Server Explorer -> Data Connections -> Add Connection

  

• 步驟2: 新增LINQ 到 SQL類檔案

  

• 步驟 3: 選擇資料庫並拖動表格拖放到新的LINQ 到 SQL類檔案。

  

• 步驟 4: 新增表類檔案。

  

使用LINQ到SQL查詢

執行一個查詢使用LINQ到SQL規則類似到一個標準的LINQ查詢，即 執行查詢或者延遲或馬上執行。 在執行使用LINQ到SQL查詢的發揮作用，這些都是下述的元件。

• LINQ到SQL API – 請求查詢執行代表一個應用程式，並把它交給LINQ到SQL提供程式

• LINQ 到SQL提供程式 - 轉換查詢到Transact SQL（T-SQL），並行送新的查詢到ADO提供程式執行

• ADO 提供程式 - 執行查詢之後，傳送結果在一個DataReader形式的LINQ 到 SQL，提供這反過來將其轉換成使用者的物件的形式

應當指出的是，在進行一個LINQ到SQL查詢之前，重要的是要連線到經由DataContext類的資料源。

使用LINQ 到SQL插入，更新和刪除

新增或插入

C#

using System;
using System.Linq;

namespace LINQtoSQL
{
  class LinqToSQLCRUD
  {
     static void Main(string[] args)
     {
        string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

        LinqToSQLDataContext db = new LinqToSQLDataContext(connectString);           

        //Create new Employee
        Employee newEmployee = new Employee();
        newEmployee.Name = "Michael";
        newEmployee.Email = "[email protected]";
        newEmployee.ContactNo = "343434343";
        newEmployee.DepartmentId = 3;
        newEmployee.Address = "Michael - USA";

        //Add new Employee to database
        db.Employees.InsertOnSubmit(newEmployee);

        //Save changes to Database.
        db.SubmitChanges();

        //Get new Inserted Employee            
        Employee insertedEmployee = db.Employees.FirstOrDefault(e =>e.Name.Equals("Michael"));

        Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}, Address = {4}",
                          insertedEmployee.EmployeeId, insertedEmployee.Name, insertedEmployee.Email, 
                          insertedEmployee.ContactNo, insertedEmployee.Address);

        Console.WriteLine("\nPress any key to continue.");
        Console.ReadKey();
     }
  }
}

VB

Module Module1
  Sub Main()
     Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()

     Dim db As New LinqToSQLDataContext(connectString)

     Dim newEmployee As New Employee()
     newEmployee.Name = "Michael"
     newEmployee.Email = "[email protected]"
     newEmployee.ContactNo = "343434343"
     newEmployee.DepartmentId = 3
     newEmployee.Address = "Michael - USA"
     
     db.Employees.InsertOnSubmit(newEmployee)
     
     db.SubmitChanges()
     
     Dim insertedEmployee As Employee = db.Employees.FirstOrDefault(Function(e) e.Name.Equals("Michael"))

     Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}, Address = {4}", insertedEmployee.EmployeeId, insertedEmployee.Name, insertedEmployee.Email, insertedEmployee.ContactNo, insertedEmployee.Address)

     Console.WriteLine(vbLf & "Press any key to continue.")
     Console.ReadKey()
  End Sub
End Module

當C#或VB上面的程式碼被編譯並執行，它會產生以下結果：

Emplyee ID = 4, Name = Michael, Email = [email protected], ContactNo = 
343434343, Address = Michael - USA

Press any key to continue.

更新

C#

using System;
using System.Linq;

namespace LINQtoSQL
{
  class LinqToSQLCRUD
  {
     static void Main(string[] args)
     {
        string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

        LinqToSQLDataContext db = new LinqToSQLDataContext(connectString);

        //Get Employee for update
        Employee employee = db.Employees.FirstOrDefault(e =>e.Name.Equals("Michael"));

        employee.Name = "George Michael";
        employee.Email = "[email protected]";
        employee.ContactNo = "99999999";
        employee.DepartmentId = 2;
        employee.Address = "Michael George - UK";

        //Save changes to Database.
        db.SubmitChanges();

        //Get Updated Employee            
        Employee updatedEmployee = db.Employees.FirstOrDefault(e =>e.Name.Equals("George Michael"));

        Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}, Address = {4}",
                          updatedEmployee.EmployeeId, updatedEmployee.Name, updatedEmployee.Email, 
                          updatedEmployee.ContactNo, updatedEmployee.Address);

        Console.WriteLine("\nPress any key to continue.");
        Console.ReadKey();
     }
  }
}

VB

Module Module1
  Sub Main()
     Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()

     Dim db As New LinqToSQLDataContext(connectString)

     Dim employee As Employee = db.Employees.FirstOrDefault(Function(e) e.Name.Equals("Michael"))

     employee.Name = "George Michael"
     employee.Email = "[email protected]"
     employee.ContactNo = "99999999"
     employee.DepartmentId = 2
     employee.Address = "Michael George - UK"

     db.SubmitChanges()
          
     Dim updatedEmployee As Employee = db.Employees.FirstOrDefault(Function(e) e.Name.Equals("George Michael"))

     Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}, Address = {4}", updatedEmployee.EmployeeId, updatedEmployee.Name, updatedEmployee.Email, updatedEmployee.ContactNo, updatedEmployee.Address)

     Console.WriteLine(vbLf & "Press any key to continue.")
     Console.ReadKey()
  End Sub
End Module

當C#或VB上面的程式碼被編譯並執行，它會產生以下結果：

Emplyee ID = 4, Name = George Michael, Email = [email protected], ContactNo = 
999999999, Address = Michael George - UK

Press any key to continue.

刪除

C#

using System;
using System.Linq;

namespace LINQtoSQL
{
  class LinqToSQLCRUD
  {
     static void Main(string[] args)
     {
        string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

        LinqToSQLDataContext db = newLinqToSQLDataContext(connectString);

        //Get Employee to Delete
        Employee deleteEmployee = db.Employees.FirstOrDefault(e =>e.Name.Equals("George Michael"));

        //Delete Employee
        db.Employees.DeleteOnSubmit(deleteEmployee);

        //Save changes to Database.
        db.SubmitChanges();

        //Get All Employee from Database
        var employeeList = db.Employees;
        foreach (Employee employee in employeeList)
        {
           Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}",
                             employee.EmployeeId, employee.Name, employee.Email, employee.ContactNo);
        }            

        Console.WriteLine("\nPress any key to continue.");
        Console.ReadKey();
     }
  }
}

VB

Module Module1
  Sub Main()
     Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()

     Dim db As New LinqToSQLDataContext(connectString)

     Dim deleteEmployee As Employee = db.Employees.FirstOrDefault(Function(e) e.Name.Equals("George Michael"))

     db.Employees.DeleteOnSubmit(deleteEmployee)

     db.SubmitChanges()

     Dim employeeList = db.Employees
     For Each employee As Employee In employeeList
        Console.WriteLine("Employee Id = {0} , Name = {1}, Email = {2}, ContactNo = {3}", employee.EmployeeId, employee.Name, employee.Email, employee.ContactNo)
     Next 

     Console.WriteLine(vbLf & "Press any key to continue.")
     Console.ReadKey()
  End Sub
End Module

當C#或VB上面的程式碼被編譯並執行，它會產生以下結果：

Emplyee ID = 1, Name = William, Email = [email protected], ContactNo = 999999999
Emplyee ID = 2, Name = Miley, Email = [email protected], ContactNo = 999999999
Emplyee ID = 3, Name = Benjamin, Email = [email protected], ContactNo = 

Press any key to continue.