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 = "yourname@companyname.com";
        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 = "yourname@companyname.com"
     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 = yourname@companyname.com, 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 = "yourname@companyname.com";
        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 = "yourname@companyname.com"
     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 = yourname@companyname.com, 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 = abc@gy.co, ContactNo = 999999999
Emplyee ID = 2, Name = Miley, Email = amp@esds.sds, ContactNo = 999999999
Emplyee ID = 3, Name = Benjamin, Email = asdsad@asdsa.dsd, ContactNo = 

Press any key to continue.