LINQ Dataset（資料集）

資料集提供了一個非常有用的資料表示在儲存器中，用於資料的基礎應用的一個不同範圍。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.