LINQ - 資料集
資料集提供了一種非常有用的記憶體資料表示形式，並用於各種基於資料應用程式。LINQ to Dataset 作為 LINQ to ADO.NET 技術之一，方便以輕鬆的方式對資料集中的資料執行查詢，並提高生產力。
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.
使用 LinQ to Dataset 查詢資料集
在開始使用 LINQ to Dataset 查詢資料集之前，至關重要的是將資料載入到資料集，這可以透過使用 DataAdapter 類或 LINQ to SQL 來完成。使用 LINQ to Dataset 構建查詢的方式與使用 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.
廣告