LINQ - SQL
LINQ to SQL 提供了一個用於管理關係資料作為物件的架構（執行時）。它是 .NET Framework 3.5 版本的一個元件，能夠將物件模型的語言整合查詢轉換為 SQL。然後將這些查詢傳送到資料庫以進行執行。從資料庫獲取結果後，LINQ to SQL 會再次將其轉換為物件。
LINQ To SQL 簡介
對於大多數 ASP.NET 開發人員來說，LINQ to SQL（也稱為 DLINQ）是語言整合查詢中令人振奮的一部分，因為它允許使用常規 LINQ 表示式查詢 SQL Server 資料庫中的資料。它還允許更新、刪除和插入資料，但它唯一的缺點是它僅限於 SQL Server 資料庫。但是，與 ADO.NET 相比，LINQ to SQL 具有許多優點，例如降低複雜性、減少程式碼行數等等。
下圖顯示了 LINQ to SQL 的執行架構。
如何使用 LINQ to SQL？
步驟 1 - 與資料庫伺服器建立新的“資料連線”。檢視 &arrar; 伺服器資源管理器 &arrar; 資料連線 &arrar; 新增連線
步驟 2 - 新增 LINQ To SQL 類檔案
步驟 3 - 從資料庫中選擇表並將其拖放到新的 LINQ to SQL 類檔案中。
步驟 4 - 將表新增到類檔案。
使用 LINQ to SQL 查詢
使用 LINQ to SQL 執行查詢的規則與標準 LINQ 查詢類似，即查詢是延遲執行還是立即執行。有各種元件參與 LINQ to SQL 查詢的執行，它們如下所示。
LINQ to SQL API - 代表應用程式請求查詢執行並將其傳送到 LINQ to SQL 提供程式。
LINQ to SQL 提供程式 - 將查詢轉換為 Transact SQL(T-SQL) 並將新查詢傳送到 ADO 提供程式以執行。
ADO 提供程式 - 查詢執行後，將結果以 DataReader 的形式傳送到 LINQ to SQL 提供程式，後者將其轉換為使用者物件的形式。
需要注意的是，在執行 LINQ to SQL 查詢之前，必須透過 DataContext 類連線到資料來源。
使用 LINQ To 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.
廣告