DataTable 對象的常見用法是資料繫結。 CopyToDataTable方法會取得查詢的結果，並將數據DataTable複製到 ，然後可用於數據系結。 執行資料作業後，新的 DataTable 會合併回來源 DataTable。
CopyToDataTable方法會使用下列程式從查詢建立 DataTable ：
CopyToDataTable 方法會從源數據表中複製 DataTable，這是一個實現 DataTable 介面的 IQueryable<T> 物件。 來源 IEnumerable 通常源自 LINQ to DataSet 運算式或方法查詢。
複製 DataTable 的架構是從源數據表中第一個列舉 DataRow 對象的數據行所建置，而複製數據表的名稱是源數據表的名稱，並附加了 「query」 這個字。
針對源數據表中的每個數據列，數據列的內容會複製到新的 DataRow 物件中，然後插入複製的數據表中。 RowState 屬性和 RowError 屬性會在複製作業中保留。 如果來源中的ArgumentException物件來自不同的資料表，則會拋出DataRow。
被複製的 DataTable 在輸入的可查詢表中的所有 DataRow 對象被複製後返回。 如果來源序列不包含任何 DataRow 物件，此方法會傳回空 DataTable的 。
CopyToDataTable 呼叫方法會導致連結到來源資料表的查詢被執行。
當 CopyToDataTable 方法在來源資料表的列中遇到 Null 參考或可為 Null 的值型別時，它會將值取代為 Value。 如此一來，傳回的 DataTable 中會正確處理空值。
注意：方法 CopyToDataTable 接受輸入，此查詢可以從多個 DataTable 或 DataSet 對象傳回數據列。 方法 CopyToDataTable 會複製來源 DataTable 或 DataSet 物件中的資料，但不會複製其屬性到回傳的 DataTable。 您必須明確設定所傳回的 DataTable 的屬性，例如 Locale 與 TableName。
下列範例會在 2001 年 8 月 8 日之後查詢 SalesOrderHeader 資料表，並使用 CopyToDataTable 方法來從該查詢建立 DataTable 。 DataTable接著會系結至 BindingSource，該 BindingSource充當 的代理。
// Bind the System.Windows.Forms.DataGridView object
// to the System.Windows.Forms.BindingSource object.
dataGridView.DataSource = bindingSource;
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
// Query the SalesOrderHeader table for orders placed
// after August 8, 2001.
IEnumerable<DataRow> query =
from order in orders.AsEnumerable()
where order.Field<DateTime>("OrderDate") > new DateTime(2001, 8, 1)
select order;
// Create a table from the query.
DataTable boundTable = query.CopyToDataTable<DataRow>();
// Bind the table to a System.Windows.Forms.BindingSource object,
// which acts as a proxy for a System.Windows.Forms.DataGridView object.
bindingSource.DataSource = boundTable;
' Bind the System.Windows.Forms.DataGridView object
' to the System.Windows.Forms.BindingSource object.
dataGridView.DataSource = bindingSource
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
' Query the SalesOrderHeader table for orders placed
' after August 8, 2001.
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of DateTime)("OrderDate") > New DateTime(2001, 8, 1) _
Select order
' Create a table from the query.
Dim boundTable As DataTable = query.CopyToDataTable()
' Bind the table to a System.Windows.Forms.BindingSource object,
' which acts as a proxy for a System.Windows.Forms.DataGridView object.
bindingSource.DataSource = boundTable
創建自訂 CopyToDataTable<T> 方法
現有的CopyToDataTable方法僅在來源IEnumerable<T>中，泛型參數T類型為DataRow時運作。 雖然這很有用，但它不允許從純量類型的序列、從傳回匿名類型的查詢，或從執行數據表聯結的查詢建立數據表。 如需了解如何實作兩個自訂方法以從純量或匿名型別載入資料表的範例，請參閱 CopyToDataTable。
本節中的範例會使用下列自定義類型：
public class Item
{
public int Id { get; set; }
public double Price { get; set; }
public string Genre { get; set; }
}
public class Book : Item
{
public string Author { get; set; }
}
public class Movie : Item
{
public string Director { get; set; }
}
Public Class Item
Private _Id As Int32
Private _Price As Double
Private _Genre As String
Public Property Id() As Int32
Get
Return Id
End Get
Set(ByVal value As Int32)
_Id = value
End Set
End Property
Public Property Price() As Double
Get
Return _Price
End Get
Set(ByVal value As Double)
_Price = value
End Set
End Property
Public Property Genre() As String
Get
Return _Genre
End Get
Set(ByVal value As String)
_Genre = value
End Set
End Property
End Class
Public Class Book
Inherits Item
Private _Author As String
Public Property Author() As String
Get
Return _Author
End Get
Set(ByVal value As String)
_Author = value
End Set
End Property
End Class
Public Class Movie
Inherits Item
Private _Director As String
Public Property Director() As String
Get
Return _Director
End Get
Set(ByVal value As String)
_Director = value
End Set
End Property
End Class
範例
此範例會透過對SalesOrderHeader和SalesOrderDetail數據表執行聯結，以取得8月份的線上訂單，並從查詢建立資料表。
// Fill the DataSet.
var ds = new DataSet
{
Locale = CultureInfo.InvariantCulture
};
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
DataTable details = ds.Tables["SalesOrderDetail"];
var query =
from order in orders.AsEnumerable()
join detail in details.AsEnumerable()
on order.Field<int>("SalesOrderID") equals
detail.Field<int>("SalesOrderID")
where order.Field<bool>("OnlineOrderFlag")
&& order.Field<DateTime>("OrderDate").Month == 8
select new
{
SalesOrderID =
order.Field<int>("SalesOrderID"),
SalesOrderDetailID =
detail.Field<int>("SalesOrderDetailID"),
OrderDate =
order.Field<DateTime>("OrderDate"),
ProductID =
detail.Field<int>("ProductID")
};
DataTable orderTable = query.CopyToDataTable();
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim details As DataTable = ds.Tables("SalesOrderDetail")
Dim query = _
From order In orders.AsEnumerable() _
Join detail In details.AsEnumerable() _
On order.Field(Of Integer)("SalesOrderID") Equals _
detail.Field(Of Integer)("SalesOrderID") _
Where order.Field(Of Boolean)("OnlineOrderFlag") = True And _
order.Field(Of DateTime)("OrderDate").Month = 8 _
Select New With _
{ _
.SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
.SalesOrderDetailID = detail.Field(Of Integer)("SalesOrderDetailID"), _
.OrderDate = order.Field(Of DateTime)("OrderDate"), _
.ProductID = detail.Field(Of Integer)("ProductID") _
}
Dim table As DataTable = query.CopyToDataTable()
範例
下列範例會查詢集合中價格大於 $9.99 的項目，並從查詢結果建立表格。
// Create a sequence.
var items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};
// Query for items with price greater than 9.99.
IOrderedEnumerable<Item> query = from i in items
where i.Price > 9.99
orderby i.Price
select i;
// Load the query results into new DataTable.
DataTable table = query.CopyToDataTable();
Dim book1 As New Book()
book1.Id = 1
book1.Price = 13.5
book1.Genre = "Comedy"
book1.Author = "Gustavo Achong"
Dim book2 As New Book
book2.Id = 2
book2.Price = 8.5
book2.Genre = "Drama"
book2.Author = "Jessie Zeng"
Dim movie1 As New Movie
movie1.Id = 1
movie1.Price = 22.99
movie1.Genre = "Comedy"
movie1.Director = "Marissa Barnes"
Dim movie2 As New Movie
movie2.Id = 1
movie2.Price = 13.4
movie2.Genre = "Action"
movie2.Director = "Emmanuel Fernandez"
Dim items(3) As Item
items(0) = book1
items(1) = book2
items(2) = movie1
items(3) = movie2
' Query for items with price greater than 9.99.
Dim query = From i In items _
Where i.Price > 9.99 _
Order By i.Price _
Select New With {i.Price, i.Genre}
Dim table As DataTable
table = query.CopyToDataTable()
範例
下列範例會查詢集合中價格大於9.99的項目，並呈現結果。 傳回的匿名型別序列會載入現有的數據表。
// Create a sequence.
var items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};
// Create a table with a schema that matches that of the query results.
var table = new DataTable();
table.Columns.Add("Price", typeof(int));
table.Columns.Add("Genre", typeof(string));
var query = from i in items
where i.Price > 9.99
orderby i.Price
select new { i.Price, i.Genre };
query.CopyToDataTable(table, LoadOption.PreserveChanges);
Dim book1 As New Book()
book1.Id = 1
book1.Price = 13.5
book1.Genre = "Comedy"
book1.Author = "Gustavo Achong"
Dim book2 As New Book
book2.Id = 2
book2.Price = 8.5
book2.Genre = "Drama"
book2.Author = "Jessie Zeng"
Dim movie1 As New Movie
movie1.Id = 1
movie1.Price = 22.99
movie1.Genre = "Comedy"
movie1.Director = "Marissa Barnes"
Dim movie2 As New Movie
movie2.Id = 1
movie2.Price = 13.4
movie2.Genre = "Action"
movie2.Director = "Emmanuel Fernandez"
Dim items(3) As Item
items(0) = book1
items(1) = book2
items(2) = movie1
items(3) = movie2
' Create a table with a schema that matches that of the query results.
Dim table As DataTable = New DataTable()
table.Columns.Add("Price", GetType(Integer))
table.Columns.Add("Genre", GetType(String))
' Query for items with price greater than 9.99.
Dim query = From i In items _
Where i.Price > 9.99 _
Order By i.Price _
Select New With {i.Price, i.Genre}
query.CopyToDataTable(table, LoadOption.PreserveChanges)
範例
下列範例會查詢集合中價格大於 $9.99 的專案，並展示結果。 傳回的匿名型別序列會載入現有的數據表。 數據表的架構會自動擴展，因為 Book 和 Movies 型別是衍生自 Item 型別。
// Create a sequence.
var items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};
// Load into an existing DataTable, expand the schema and
// autogenerate a new Id.
var table = new DataTable();
DataColumn dc = table.Columns.Add("NewId", typeof(int));
dc.AutoIncrement = true;
table.Columns.Add("ExtraColumn", typeof(string));
var query = from i in items
where i.Price > 9.99
orderby i.Price
select new { i.Price, i.Genre };
query.CopyToDataTable(table, LoadOption.PreserveChanges);
Dim book1 As New Book()
book1.Id = 1
book1.Price = 13.5
book1.Genre = "Comedy"
book1.Author = "Gustavo Achong"
Dim book2 As New Book
book2.Id = 2
book2.Price = 8.5
book2.Genre = "Drama"
book2.Author = "Jessie Zeng"
Dim movie1 As New Movie
movie1.Id = 1
movie1.Price = 22.99
movie1.Genre = "Comedy"
movie1.Director = "Marissa Barnes"
Dim movie2 As New Movie
movie2.Id = 1
movie2.Price = 13.4
movie2.Genre = "Action"
movie2.Director = "Emmanuel Fernandez"
Dim items(3) As Item
items(0) = book1
items(1) = book2
items(2) = movie1
items(3) = movie2
' Load into an existing DataTable, expand the schema and
' autogenerate a new Id.
Dim table As DataTable = New DataTable()
Dim dc As DataColumn = table.Columns.Add("NewId", GetType(Integer))
dc.AutoIncrement = True
table.Columns.Add("ExtraColumn", GetType(String))
Dim query = From i In items _
Where i.Price > 9.99 _
Order By i.Price _
Select New With {i.Price, i.Genre}
query.CopyToDataTable(table, LoadOption.PreserveChanges)
範例
下列範例會查詢集合中價格大於 $9.99 的項目，並傳回序列 Double，此序列會載入新數據表中。
// Create a sequence.
var items = new Item[]
{ new Book{Id = 1, Price = 13.50, Genre = "Comedy", Author = "Gustavo Achong"},
new Book{Id = 2, Price = 8.50, Genre = "Drama", Author = "Jessie Zeng"},
new Movie{Id = 1, Price = 22.99, Genre = "Comedy", Director = "Marissa Barnes"},
new Movie{Id = 1, Price = 13.40, Genre = "Action", Director = "Emmanuel Fernandez"}};
// load sequence of scalars.
IEnumerable<double> query = from i in items
where i.Price > 9.99
orderby i.Price
select i.Price;
DataTable table = query.CopyToDataTable();
Dim book1 As New Book()
book1.Id = 1
book1.Price = 13.5
book1.Genre = "Comedy"
book1.Author = "Gustavo Achong"
Dim book2 As New Book
book2.Id = 2
book2.Price = 8.5
book2.Genre = "Drama"
book2.Author = "Jessie Zeng"
Dim movie1 As New Movie
movie1.Id = 1
movie1.Price = 22.99
movie1.Genre = "Comedy"
movie1.Director = "Marissa Barnes"
Dim movie2 As New Movie
movie2.Id = 1
movie2.Price = 13.4
movie2.Genre = "Action"
movie2.Director = "Emmanuel Fernandez"
Dim items(3) As Item
items(0) = book1
items(1) = book2
items(2) = movie1
items(3) = movie2
Dim query = From i In items _
Where i.Price > 9.99 _
Order By i.Price _
Select i.Price
Dim table As DataTable
table = query.CopyToDataTable()