쿼리에서 DataTable 만들기(LINQ to DataSet)Creating a DataTable From a Query (LINQ to DataSet)

데이터 바인딩에는 일반적으로 DataTable 개체가 사용됩니다.Data binding is a common use of DataTable object. CopyToDataTable 메서드는 쿼리 결과를 받아서 나중에 데이터 바인딩에 사용할 수 있도록 데이터를 DataTable에 복사합니다.The CopyToDataTable method takes the results of a query and copies the data into a DataTable, which can then be used for data binding. 데이터 작업이 수행되면 새 DataTable이 소스 DataTable에 다시 병합됩니다.When the data operations have been performed, the new DataTable is merged back into the source DataTable.

CopyToDataTable 메서드는 다음 프로세스를 사용하여 쿼리에서 DataTable을 만듭니다.The CopyToDataTable method uses the following process to create a DataTable from a query:

  1. CopyToDataTable 메서드는 소스 테이블(DataTable 인터페이스를 구현한 DataTable 개체)에서 IQueryable<T>을 복제합니다.The CopyToDataTable method clones a DataTable from the source table (a DataTable object that implements the IQueryable<T> interface). IEnumerable소스는 일반적으로 LINQ to DataSet 식 또는 메서드 쿼리에서 시작 됩니다.The IEnumerable source has generally originated from a LINQ to DataSet expression or method query.

  2. 복제된 DataTable의 스키마는 소스 테이블의 첫 번째 열거된 DataRow 개체 열을 통해 작성되며 복제된 테이블의 이름은 소스 테이블 이름에 "query"라는 단어를 추가하여 지정됩니다.The schema of the cloned DataTable is built from the columns of the first enumerated DataRow object in the source table and the name of the cloned table is the name of the source table with the word "query" appended to it.

  3. 소스 테이블에서 각 행의 내용은 새 DataRow 개체에 복사된 다음 복제 테이블에 삽입됩니다.For each row in the source table, the content of the row is copied into a new DataRow object, which is then inserted into the cloned table. RowStateRowError 속성은 복사 작업 동안 유지됩니다.The RowState and RowError properties are preserved across the copy operation. 소스의 ArgumentException 개체가 다른 테이블의 개체이면 DataRow이 throw됩니다.An ArgumentException is thrown if the DataRow objects in the source are from different tables.

  4. 쿼리 가능한 입력 테이블의 모든 DataTable 개체가 복사된 후 복제된 DataRow이 반환됩니다.The cloned DataTable is returned after all DataRow objects in the input queryable table have been copied. 소스 시퀀스에 DataRow 개체가 없는 경우 이 메서드는 빈 DataTable을 반환합니다.If the source sequence does not contain any DataRow objects, the method returns an empty DataTable.

메서드를 호출 CopyToDataTable 하면 원본 테이블에 바인딩된 쿼리가 실행 됩니다.Calling the CopyToDataTable method causes the query bound to the source table to execute.

소스 테이블의 행에 null 참조 또는 nullable 값 형식이 있으면 CopyToDataTable 메서드가 해당 값을 Value로 바꿉니다.When the CopyToDataTable method encounters either a null reference or nullable value type in a row in the source table, it replaces the value with Value. 이 방법을 통해 반환된 DataTable에서 null 값이 올바르게 처리됩니다.This way, null values are handled correctly in the returned DataTable.

참고: CopyToDataTable 메서드는 여러 DataTable 또는 DataSet 개체에서 행을 반환할 수 있는 쿼리를 입력으로 허용합니다.Note: The CopyToDataTable method accepts as input a query that can return rows from multiple DataTable or DataSet objects. CopyToDataTable 메서드는 소스 DataTable 또는 DataSet 개체의 데이터만 반환되는 DataTable에 복사하며 속성은 복사하지 않습니다.The CopyToDataTable method will copy the data but not the properties from the source DataTable or DataSet objects to the returned DataTable. DataTableLocale과 같은 반환되는 TableName에 대한 속성은 명시적으로 설정해야 합니다.You will need to explicitly set the properties on the returned DataTable, such as Locale and TableName.

다음 예제에서는 SalesOrderHeader 테이블에 2001년 8월 8일 이후 주문을 쿼리한 다음 CopyToDataTable 메서드를 사용하여 해당 쿼리에서 DataTable을 만듭니다.The following example queries the SalesOrderHeader table for orders after August 8, 2001 and uses the CopyToDataTable method to create a DataTable from that query. DataTable그런 다음에 BindingSource 대 한 프록시 역할을 하는에 바인딩됩니다 DataGridView .The DataTable is then bound to a BindingSource, which acts as proxy for a DataGridView.

// 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> 메서드 만들기Creating a Custom CopyToDataTable<T> Method

기존 CopyToDataTable 메서드는 제네릭 매개 변수 IEnumerable<T>T 형식인 DataRow 소스에서만 작동합니다.The existing CopyToDataTable methods only operate on an IEnumerable<T> source where the generic parameter T is of type DataRow. 이 제한은 유용하지만 이로 인해 일련의 스칼라 형식, 익명 형식을 반환하는 쿼리 또는 테이블 조인을 수행하는 쿼리에서 테이블을 만들지 못하게 됩니다.Although this is useful, it does not allow tables to be created from a sequence of scalar types, from queries that return anonymous types, or from queries that perform table joins. 스칼라 또는 무명 형식의 시퀀스에서 테이블을 로드 하는 두 개의 사용자 지정 메서드를 구현 하는 방법에 대 한 예제는 CopyToDataTable 방법: <T> 제네릭 형식 T가 DataRow가 아닌 CopyToDataTable 구현을 참조 하세요.For an example of how to implement two custom CopyToDataTable methods that load a table from a sequence of scalar or anonymous types, see How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRows.

이 단원의 예제에서는 다음과 같은 사용자 지정 형식을 사용합니다.The examples in this section use the following custom types:

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

예제Example

이 예제에서는 SalesOrderHeaderSalesOrderDetail 테이블에 대해 조인을 수행하여 8월의 온라인 주문을 가져오고 해당 쿼리에서 테이블을 만듭니다.This example performs a join over the SalesOrderHeader and SalesOrderDetail tables to get online orders from the month of August and creates a table from the query.

// Fill the DataSet.
DataSet ds = new DataSet();
ds.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") == true
    && 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()

예제Example

다음 예제에서는 가격이 9.99달러 이상인 항목의 컬렉션을 쿼리하고 쿼리 결과로부터 테이블을 만듭니다.The following example queries a collection for items of price greater than $9.99 and creates a table from the query results.

// Create a sequence.
Item[] 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.
var 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()

예제Example

다음 예제에서는 가격이 9.99달러 이상인 항목의 컬렉션을 쿼리하고 결과를 프로젝션합니다.The following example queries a collection for items of price greater than 9.99 and projects the results. 반환된 일련의 익명 형식은 기존 테이블에 로드됩니다.The returned sequence of anonymous types is loaded into an existing table.

// Create a sequence.
Item[] 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.
DataTable 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)

예제Example

다음 예제에서는 가격이 9.99달러 이상인 항목의 컬렉션을 쿼리하고 결과를 프로젝션합니다.The following example queries a collection for items of price greater than $9.99 and projects the results. 반환된 일련의 익명 형식은 기존 테이블에 로드됩니다.The returned sequence of anonymous types is loaded into an existing table. BookMovies 형식은 Item 형식에서 파생되므로 테이블 스키마는 자동으로 확장됩니다.The table schema is automatically expanded because the Book and Movies types are derived from the Item type.

// Create a sequence.
Item[] 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.
DataTable 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)

예제Example

다음 예제에서는 가격이 9.99달러 이상인 항목의 컬렉션을 쿼리하고 새 테이블에 로드되는 일련의 Double을 반환합니다.The following example queries a collection for items of price greater than $9.99 and returns a sequence of Double, which is loaded into a new table.

// Create a sequence.
Item[] 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()

참고 항목See also