LINQ - SQL查询

LINQ - SQL查询 首页 / LinQ入门教程 / LINQ - SQL查询

LINQ to SQL提供了用于将关系数据作为对象进行管理的基础结构(运行时)。它是.NET Framework 3.5版的组件,可以将对象模型的语言集成查询转换为SQL,然后将这些查询发送到数据库以供执行。从数据库获取输出后,LINQ to SQL再次将其转换为对象。

LINQ to SQL简介

对于大多数ASP.NET开发人员而言,LINQ to SQL(也称为DLINQ)是Language Integrated Query的激动人心的部分,因为它允许使用常规的LINQ表达式查询SQL Server数据库中的数据。它还允许更新,删除和插入数据,但是它遭受的唯一缺点是它对SQL Server数据库的限制。但是,通过ADO.NET,LINQ to SQL有很多好处,如降低了复杂性,减少了几行编码等等。

下图显示了LINQ to SQL的执行体系结构。

LINQ SQL Architecture

LINQ to SQL使用

步骤1  -  与数据库服务器进行新的“Data Connection”。

LINQ to SQL

步骤2  -  将LINQ添加到SQL类文件

LINQ to SQL

步骤3  -  从数据库中选择表,并将其拖放到新的LINQ to SQL类文件中。

LINQ to SQL

步骤4  -  将表添加到类文件中。

LINQ to SQL

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类连接到数据源至关重要。

添加或插入数据

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代码编译并运行时,将产生以下输出-

无涯教程网

链接:https://www.learnfk.comhttps://www.learnfk.com/linq/linq-sql.html

来源:LearnFk无涯教程网

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();
         
         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);


         Employee deleteEmployee = db.Employees.FirstOrDefault(e e.Name.Equals("George Michael"));

         //删除 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代码编译并运行时,将产生以下输出-

无涯教程网

链接:https://www.learnfk.comhttps://www.learnfk.com/linq/linq-sql.html

来源:LearnFk无涯教程网

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.

祝学习愉快!(内容编辑有误?请选中要编辑内容 -> 右键 -> 修改 -> 提交!)

技术教程推荐

iOS开发高手课 -〔戴铭〕

RPC实战与核心原理 -〔何小锋〕

如何看懂一幅画 -〔罗桂霞〕

MySQL 必知必会 -〔朱晓峰〕

高楼的性能工程实战课 -〔高楼〕

玩转Vue 3全家桶 -〔大圣〕

自动化测试高手课 -〔柳胜〕

Go进阶 · 分布式爬虫实战 -〔郑建勋〕

现代C++20实战高手课 -〔卢誉声〕

好记忆不如烂笔头。留下您的足迹吧 :)