LINQ – SQL

LINQ to SQL 为将关系数据作为对象进行管理提供了基础结构（运行时）。它是 .NET Framework 3.5 版的一个组件，能够将对象模型的语言集成查询转换为 SQL。然后将这些查询发送到数据库以供执行。从数据库中获取结果后，LINQ to SQL 再次将它们转换为对象。

LINQ To SQL 介绍

对于大多数 ASP.NET 开发人员来说，LINQ to SQL（也称为 DLINQ）是语言集成查询的一个激动人心的部分，因为它允许使用常用的 LINQ 表达式查询 SQL 服务器数据库中的数据。它还允许更新、删除和插入数据，但它遭受的唯一缺点是它对 SQL 服务器数据库的限制。但是，与 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 Provider。

• LINQ to SQL Provider – 将查询转换为 Transact SQL(T-SQL) 并将新查询发送到 ADO Provider 以执行。

• ADO Provider – 执行查询后，将结果以 DataReader 的形式发送到 LINQ to SQL Provider，后者又将其转换为用户对象的形式。

应该注意的是，在执行 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.