LINQ Dataset（数据集） - LinQ教程

数据集提供了一个非常有用的数据表示在存储器中，用于数据的基础应用的一个不同范围。LINQ到数据集为一个LINQ来执行查询的数据集的数据无忧的方式ADO.NET工具的技术，并提高生产力。

LINQ到数据集的介绍

LINQ到数据集已取得查询简单的开发任务。 它们并不需要在一个特定的查询语言，可以用编程语言编写相同的查询。LINQ到数据集也是用于查询，其中数据从多个数据源合并使用。这也并不需要任何LINQ提供程序从内存中集合访问从LINQ 到 SQL和LINQ 到 XML读取数据。

下面是其中一个数据源首先获得，然后将数据集填充的两个数据表一个LINQ到数据集的查询的一个简单的例子。关系是表和LINQ查询被Join子句方式，对两个表创建在两者之间建立的。最后，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.

使用LINQ到数据集查询数据集

在开始查询使用LINQ到数据集数据集，这是至关重要的数据加载到数据集，这是通过或者使用DataAdapter类或LINQ到SQL完成的。使用LINQ到数据集查询的提法和通过使用LINQ与其他LINQ使数据源制定查询是非常相似的。

单表查询

在下面的单表查询，所有的在线订单从SalesOrderHeaderTtable收集，然后命令ID，订购日期和订单号显示为输出。

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

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;"
     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.