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.