LINQ to SQL提供了一个基础结构(运行时),用于将关系数据作为对象进行管理.它是.NET Framework 3.5版的一个组件,可以将对象模型的语言集成查询转换为SQL.然后将这些查询发送到数据库以执行.从数据库中获取结果后,LINQ to SQL再次将它们转换为对象.
对于大多数ASP.NET开发人员LINQ to SQL(也称为DLINQ)是语言集成查询的一个令人振奋的部分,因为它允许使用通常的LINQ表达式查询SQL Server数据库中的数据.它还允许更新,删除和插入数据,但它遭受的唯一缺点是它对SQL服务器数据库的限制.但是,LINQ to SQL比ADO.NET有许多好处,比如降低复杂性,减少编码行等等.
下面是一个显示LINQ to SQL执行架构的图表.
第1步 : 与数据库服务器建立新的"数据连接".查看&arrar; Server Explorer&arrar;数据连接&arrar;添加连接
步骤2 : 添加LINQ To SQL类文件
第3步 : 从数据库中选择表并拖放到新的LINQ to SQL类文件中.
第4步 : 将表添加到类文件.
使用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 &minus ;执行查询后,将结果以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代码时,它产生以下结果 :
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.