Linq 简明教程

LINQ - Dataset

数据集在内存中提供了非常有用的数据表示形式，并且用于各种基于数据的应用程序。作为 LINQ to ADO.NET 技术之一的 LINQ to Dataset 促进了对数据集数据执行查询的便利，并且提高了工作效率。

Introduction of LINQ To Dataset

LINQ to Dataset 使查询任务对开发人员来说变得简单。他们不需要使用特定查询语言编写查询，而可以用编程语言编写相同的查询。LINQ to Dataset 也可用于从多个数据源合并数据时的查询。这也无需任何 LINQ 提供程序，比如 LINQ to SQL 和 LINQ to XML，就可以从内存集合中访问数据。

以下是 LINQ to Dataset 查询的简单示例，其中首先获取数据源，然后使用两个数据表填充数据集。在两个表之间建立关系，然后通过联接子句对两个表创建 LINQ 查询。最后，使用 foreach 循环显示所需结果。

C

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LINQtoDataset {
   class Program {
      static void Main(string[] args) {

         string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

         string sqlSelect = "SELECT * FROM Department;" + "SELECT * FROM Employee;";

         // Create the data adapter to retrieve data from the database
         SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connectString);

         // Create table mappings
         da.TableMappings.Add("Table", "Department");
         da.TableMappings.Add("Table1", "Employee");

         // Create and fill the DataSet
         DataSet ds = new DataSet();
         da.Fill(ds);

         DataRelation dr = ds.Relations.Add("FK_Employee_Department",
                           ds.Tables["Department"].Columns["DepartmentId"],
                           ds.Tables["Employee"].Columns["DepartmentId"]);

         DataTable department = ds.Tables["Department"];
         DataTable employee = ds.Tables["Employee"];

         var query = from d in department.AsEnumerable()
                     join e in employee.AsEnumerable()
                     on d.Field<int>("DepartmentId") equals
                     e.Field<int>("DepartmentId")
                     select new {
                        EmployeeId = e.Field<int>("EmployeeId"),
                        Name = e.Field<string>("Name"),
                        DepartmentId = d.Field<int>("DepartmentId"),
                        DepartmentName = d.Field<string>("Name")
                     };

         foreach (var q in query) {
            Console.WriteLine("Employee Id = {0} , Name = {1} , Department Name = {2}",
               q.EmployeeId, q.Name, q.DepartmentName);
         }

         Console.WriteLine("\nPress any key to continue.");
         Console.ReadKey();
      }
   }
}

VB

Imports System.Data.SqlClient
Imports System.Linq

Module LinqToDataSet

   Sub Main()

      Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()

      Dim sqlSelect As String = "SELECT * FROM Department;" + "SELECT * FROM Employee;"
      Dim sqlCnn As SqlConnection = New SqlConnection(connectString)
      sqlCnn.Open()

      Dim da As New SqlDataAdapter
      da.SelectCommand = New SqlCommand(sqlSelect, sqlCnn)

      da.TableMappings.Add("Table", "Department")
      da.TableMappings.Add("Table1", "Employee")

      Dim ds As New DataSet()
      da.Fill(ds)

      Dim dr As DataRelation = ds.Relations.Add("FK_Employee_Department", ds.Tables("Department").Columns("DepartmentId"), ds.Tables("Employee").Columns("DepartmentId"))

      Dim department As DataTable = ds.Tables("Department")
      Dim employee As DataTable = ds.Tables("Employee")

      Dim query = From d In department.AsEnumerable()
                  Join e In employee.AsEnumerable() On d.Field(Of Integer)("DepartmentId") Equals
                  e.Field(Of Integer)("DepartmentId")
                  Select New Person With { _
                        .EmployeeId = e.Field(Of Integer)("EmployeeId"),
                        .EmployeeName = e.Field(Of String)("Name"),
                        .DepartmentId = d.Field(Of Integer)("DepartmentId"),
                        .DepartmentName = d.Field(Of String)("Name")
                  }

      For Each e In query
         Console.WriteLine("Employee Id = {0} , Name = {1} , Department Name = {2}", e.EmployeeId, e.EmployeeName, e.DepartmentName)
      Next

      Console.WriteLine(vbLf & "Press any key to continue.")
      Console.ReadKey()

   End Sub

   Class Person
      Public Property EmployeeId As Integer
      Public Property EmployeeName As String
      Public Property DepartmentId As Integer
      Public Property DepartmentName As String
   End Class

End Module

当以上 C# 或 VB 代码被编译并执行时，它会生成以下结果 -

Employee Id = 1, Name = William, Department Name = Account
Employee Id = 2, Name = Benjamin, Department Name = Account
Employee Id = 3, Name = Miley, Department Name = Sales

Press any key to continue.

Querying Dataset using LinQ to Dataset

在使用 LINQ to Dataset 查询数据集之前，至关重要的是将数据加载到数据集，这是通过使用 DataAdapter 类或通过 LINQ to SQL 来完成的。使用 LINQ to Dataset 制定的查询与使用 LINQ 以及其他启用了 LINQ 的数据源来制定查询非常相似。

Single-Table Query

在以下单表查询中，从 SalesOrderHeaderTtable 收集所有在线订单，然后显示订单 ID、订单日期以及订单编号作为输出。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LinqToDataset {
   class SingleTable {
      static void Main(string[] args) {

         string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();

         string sqlSelect = "SELECT * FROM Department;";

         // Create the data adapter to retrieve data from the database
         SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connectString);

         // Create table mappings
         da.TableMappings.Add("Table", "Department");

         // Create and fill the DataSet
         DataSet ds = new DataSet();
         da.Fill(ds);

         DataTable department = ds.Tables["Department"];

         var query = from d in department.AsEnumerable()
         select new {
            DepartmentId = d.Field<int>("DepartmentId"),
            DepartmentName = d.Field<string>("Name")
         };

         foreach (var q in query) {
            Console.WriteLine("Department Id = {0} , Name = {1}",
               q.DepartmentId, q.DepartmentName);
         }

         Console.WriteLine("\nPress any key to continue.");
         Console.ReadKey();
      }
   }
}

Imports System.Data.SqlClient
Imports System.Linq

Module LinqToDataSet

   Sub Main()

      Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()

      Dim sqlSelect As String = "SELECT * FROM Department;"
      Dim sqlCnn As SqlConnection = New SqlConnection(connectString)
      sqlCnn.Open()

      Dim da As New SqlDataAdapter
      da.SelectCommand = New SqlCommand(sqlSelect, sqlCnn)

      da.TableMappings.Add("Table", "Department")
      Dim ds As New DataSet()
      da.Fill(ds)

      Dim department As DataTable = ds.Tables("Department")

      Dim query = From d In department.AsEnumerable()
      Select New DepartmentDetail With {
         .DepartmentId = d.Field(Of Integer)("DepartmentId"),
            .DepartmentName = d.Field(Of String)("Name")
      }

      For Each e In query
         Console.WriteLine("Department Id = {0} , Name = {1}", e.DepartmentId, e.DepartmentName)
      Next

      Console.WriteLine(vbLf & "Press any key to continue.")
      Console.ReadKey()
   End Sub

   Public Class DepartmentDetail
      Public Property DepartmentId As Integer
      Public Property DepartmentName As String
   End Class

End Module

当以上 C# 或 VB 代码被编译并执行时，它会生成以下结果 -

Department Id = 1, Name = Account
Department Id = 2, Name = Sales
Department Id = 3, Name = Pre-Sales
Department Id = 4, Name = Marketing

Press any key to continue.