无涯教程-LINQ - SQL查询 - IPS99技术分享

标签：Name db 无涯 LINQ ContactNo employee SQL Employee

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 to SQL使用

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

步骤2 - 将LINQ添加到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类连接到数据源至关重要。

添加或插入数据

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 = "[email protected]";
         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();
      }
   }
}

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 = "[email protected]"
      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 protected], ContactNo=
343434343, Address=Michael - USA

Press any key to continue.

更新数据

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 = "[email protected]";
         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();
      }
   }
}

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 = "[email protected]"
      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 protected], ContactNo=
999999999, Address=Michael George - UK

Press any key to continue.

删除数据

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

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 protected], ContactNo=999999999
Emplyee ID=2, Name=Miley, [email protected], ContactNo=999999999
Emplyee ID=3, Name=Benjamin, [email protected], ContactNo=

Press any key to continue.

参考链接

https://www.learnfk.com/linq/linq-sql.html

标签：Name,db,无涯,LINQ,ContactNo,employee,SQL,Employee
From： https://blog.51cto.com/u_14033984/8759198

无涯教程-LINQ - SQL查询