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 = "[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();
}
}
}
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 = "[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 = [email protected], 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 = "[email protected]";
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 = "[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 = [email protected], 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 = [email protected], ContactNo = 999999999 Emplyee ID = 2, Name = Miley, Email = [email protected], ContactNo = 999999999 Emplyee ID = 3, Name = Benjamin, Email = [email protected], ContactNo = Press any key to continue.
广告