LINQ - SQL查询详解 | 无涯教程

最编程 2024-08-10 17:09:30

...

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) {
     </span><span class="kwd">string</span><span class="pln"> connectString </span><span class="pun">=</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Configuration</span><span class="pun">.</span><span class="typ">ConfigurationManager</span><span class="pun">.</span><span class="typ">ConnectionStrings</span><span class="pun">[</span><span class="str">"LinqToSQLDBConnectionString"</span><span class="pun">].</span><span class="typ">ToString</span><span class="pun">();</span><span class="pln">

     </span><span class="typ">LinqToSQLDataContext</span><span class="pln"> db </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">LinqToSQLDataContext</span><span class="pun">(</span><span class="pln">connectString</span><span class="pun">);</span><span class="pln">           

     </span><span class="com">//Create new Employee</span><span class="pln">
	 
     </span><span class="typ">Employee</span><span class="pln"> newEmployee </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">Employee</span><span class="pun">();</span><span class="pln">
     newEmployee</span><span class="pun">.</span><span class="typ">Name</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Michael"</span><span class="pun">;</span><span class="pln">
     newEmployee</span><span class="pun">.</span><span class="typ">Email</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"yourname@companyname.com"</span><span class="pun">;</span><span class="pln">
     newEmployee</span><span class="pun">.</span><span class="typ">ContactNo</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"343434343"</span><span class="pun">;</span><span class="pln">
     newEmployee</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">3</span><span class="pun">;</span><span class="pln">
     newEmployee</span><span class="pun">.</span><span class="typ">Address</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Michael - USA"</span><span class="pun">;</span><span class="pln">

     </span><span class="com">//Add new Employee to database</span><span class="pln">
     db</span><span class="pun">.</span><span class="typ">Employees</span><span class="pun">.</span><span class="typ">InsertOnSubmit</span><span class="pun">(</span><span class="pln">newEmployee</span><span class="pun">);</span><span class="pln">

     </span><span class="com">//Save changes to Database.</span><span class="pln">
     db</span><span class="pun">.</span><span class="typ">SubmitChanges</span><span class="pun">();</span><span class="pln">

     </span><span class="com">//Get new Inserted Employee            </span><span class="pln">
     </span><span class="typ">Employee</span><span class="pln"> insertedEmployee </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Employees</span><span class="pun">.</span><span class="typ">FirstOrDefault</span><span class="pun">(</span><span class="pln">e </span><span class="pun">⇒</span><span class="pln">e</span><span class="pun">.</span><span class="typ">Name</span><span class="pun">.</span><span class="typ">Equals</span><span class="pun">(</span><span class="str">"Michael"</span><span class="pun">));</span><span class="pln">

     </span><span class="typ">Console</span><span class="pun">.</span><span class="typ">WriteLine</span><span class="pun">(</span><span class="str">"Employee Id={0} , Name={1}, Email={2}, ContactNo={3}, Address={4}"</span><span class="pun">,</span><span class="pln">
                      insertedEmployee</span><span class="pun">.</span><span class="typ">EmployeeId</span><span class="pun">,</span><span class="pln"> insertedEmployee</span><span class="pun">.</span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> insertedEmployee</span><span class="pun">.</span><span class="typ">Email</span><span class="pun">,</span><span class="pln"> 
                      insertedEmployee</span><span class="pun">.</span><span class="typ">ContactNo</span><span class="pun">,</span><span class="pln"> insertedEmployee</span><span class="pun">.</span><span class="typ">Address</span><span class="pun">);</span><span class="pln">

     </span><span class="typ">Console</span><span class="pun">.</span><span class="typ">WriteLine</span><span class="pun">(</span><span class="str">"\nPress any key to continue."</span><span class="pun">);</span><span class="pln">
     </span><span class="typ">Console</span><span class="pun">.</span><span class="typ">ReadKey</span><span class="pun">();</span><span class="pln">
  </span><span class="pun">}</span><span class="pln">

}
}

Module Module1
Sub Main()
  </span><span class="typ">Dim</span><span class="pln"> connectString </span><span class="typ">As</span><span class="pln"> </span><span class="typ">String</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="typ">Configuration</span><span class="pun">.</span><span class="typ">ConfigurationManager</span><span class="pun">.</span><span class="typ">ConnectionStrings</span><span class="pun">(</span><span class="str">"LinqToSQLDBConnectionString"</span><span class="pun">).</span><span class="typ">ToString</span><span class="pun">()</span><span class="pln">

  </span><span class="typ">Dim</span><span class="pln"> db </span><span class="typ">As</span><span class="pln"> </span><span class="typ">New</span><span class="pln"> </span><span class="typ">LinqToSQLDataContext</span><span class="pun">(</span><span class="pln">connectString</span><span class="pun">)</span><span class="pln">

  </span><span class="typ">Dim</span><span class="pln"> newEmployee </span><span class="typ">As</span><span class="pln"> </span><span class="typ">New</span><span class="pln"> </span><span class="typ">Employee</span><span class="pun">()</span><span class="pln">
  
  newEmployee</span><span class="pun">.</span><span class="typ">Name</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Michael"</span><span class="pln">
  newEmployee</span><span class="pun">.</span><span class="typ">Email</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"yourname@companyname.com"</span><span class="pln">
  newEmployee</span><span class="pun">.</span><span class="typ">ContactNo</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"343434343"</span><span class="pln">
  newEmployee</span><span class="pun">.</span><span class="typ">DepartmentId</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">3</span><span class="pln">
  newEmployee</span><span class="pun">.</span><span class="typ">Address</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">"Michael - USA"</span><span class="pln">
 
  db</span><span class="pun">.</span><span class="typ">Employees</span><span class="pun">.</span><span class="typ">InsertOnSubmit</span><span class="pun">(</span><span class="pln">newEmployee</span><span class="pun">)</span><span class="pln">
 
  db</span><span class="pun">.</span><span class="typ">SubmitChanges</span><span class="pun">()</span><span class="pln">
 
  </span><span class="typ">Dim</span><span class="pln"> insertedEmployee </span><span class="typ">As</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> db</span><span class="pun">.</span><span class="typ">Employees</span><span class="pun">.</span><span class="typ">FirstOrDefault</span><span class="pun">(</span><span class="typ">Function</span><span class="pun">(</span><span class="pln">e</span><span class="pun">)</span><span class="pln"> e</span><span class="pun">.</span><span 
						
							上一篇：							
								快速入门 ASP.NET LINQ to XML：学习 Descendants 和 Elements 遍历节点的方法							
						
						
							下一篇：							
								LINQ在.NET 7中的惊人速度提升


															
						
							推荐阅读						
						
														
								
									
										详解YUV系列(三)-------YUV420									
								
							
														
								
									
										解密Android Bitmap转I420的难题，附图文详解YUV420数据格式									
								
							
														
								
									
										YUV420数据格式详解，以图文形式揭示 - 提示：加强阅读理解，配合图片观看更加易懂									
								
							
														
								
									
										详解YUV444、YUV422、YUV420的可视化解释									
								
							
														
								
									
										【Codecs】解析YUV420/YUV422数据格式：图文详解 (全面而详细)									
								
							
														
								
									
										深入解析yuv420格式——图文详解									
								
							
														
								
									
										【音视频系列1】视频格式YUV444、YUV422,、YUV420详解									
								
							
														
								
									
										深入解析YUV420、YUV420(YUY2)、YUV422(YVYU)：以图解方式详解									
								
							
														
								
									
										解决Android yuv420详解的具体操作步骤									
								
							
														
								
									
										学会分析YUV数据：视频采集与处理入门教程