Membuat DataTable Dari Kueri (LINQ to DataSet)

Artikel
04/06/2023

Pengikatan data adalah penggunaan umum dari objek DataTable. Metode CopyToDataTable mengambil hasil kueri dan menyalin data ke dalam DataTable, yang kemudian dapat digunakan untuk pengikatan data. Ketika operasi data telah dilakukan, DataTable baru digabungkan kembali ke sumber DataTable.

Metode CopyToDataTable menggunakan proses berikut untuk membuat DataTable dari kueri:

Metode CopyToDataTable mengklon DataTable dari tabel sumber (objek DataTable yang mengimplementasikan antarmuka IQueryable<T>). Sumber IEnumerable umumnya berasal dari ekspresi LINQ to DataSet atau kueri metode.
Skema DataTable yang diklon dibangun dari kolom objek DataRow pertama yang dienumerasi di tabel sumber dan nama tabel yang diklon adalah nama tabel sumber dengan kata "kueri" ditambahkan padanya.
Untuk setiap baris di tabel sumber, konten baris disalin ke objek DataRow baru, yang kemudian dimasukkan ke tabel yang diklon. Properti RowState dan RowError dipertahankan di seluruh operasi penyalinan. ArgumentException ditampilkan jika objek DataRow dalam sumber berasal dari tabel yang berbeda.
DataTable yang diklon dikembalikan setelah semua objek DataRow dalam tabel yang dapat dikueri input telah disalin. Jika urutan sumber tidak berisi objek DataRow apa pun, metode akan mengembalikan DataTable kosong.

Memanggil metode CopyToDataTable menyebabkan kueri yang terikat ke tabel sumber untuk dijalankan.

Saat metode CopyToDataTable menemukan referensi null atau jenis nilai yang dapat diubah ke null dalam satu baris di tabel sumber, nilai tersebut akan diganti dengan Value. Dengan cara ini, nilai null ditangani dengan benar di DataTable yang dikembalikan.

Catatan: Metode CopyToDataTable menerima sebagai input kueri yang dapat mengembalikan baris dari beberapa objek DataTable atau DataSet. Metode CopyToDataTable akan menyalin data tetapi bukan properti dari objek DataTable atau DataSet sumber ke DataTable yang dikembalikan. Anda perlu mengatur properti secara eksplisit pada DataTable yang dikembalikan, seperti Locale dan TableName.

Contoh berikut menanyakan tabel SalesOrderHeader untuk pesanan setelah 8 Agustus 2001 dan menggunakan metode CopyToDataTable untuk membuat DataTable dari kueri tersebut. DataTable kemudian terikat ke BindingSource, yang bertindak sebagai proksi untuk 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

Membuat Metode CopyToDataTable<T> Kustom

Metode CopyToDataTable yang ada hanya beroperasi pada sumber IEnumerable<T> dengan parameter generik T memiliki jenis DataRow. Meskipun ini berguna, ini tidak mengizinkan tabel dibuat dari urutan jenis skalar, dari kueri yang mengembalikan jenis anonim, atau dari kueri yang melakukan penggabungan tabel. Untuk contoh cara mengimplementasikan dua metode CopyToDataTable kustom yang memuat tabel dari urutan jenis skalar atau anonim, lihat Cara: Mengimplementasikan CopyToDataTable<T> Jika Jenis Generik T Bukan DataRows.

Contoh di bagian ini menggunakan jenis kustom berikut:

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

Contoh

Contoh ini melakukan penggabungan pada tabel SalesOrderHeader dan SalesOrderDetail untuk mendapatkan pesanan online dari bulan Agustus dan membuat tabel dari kueri.

// 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()

Contoh

Contoh berikut mengkueri koleksi untuk item dengan harga lebih dari $9,99 dan membuat tabel dari hasil kueri.

// 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()

Contoh

Contoh berikut mengkueri koleksi untuk item dengan harga lebih dari 9,99 dan memproyeksikan hasilnya. Urutan yang dikembalikan dari jenis anonim dimuat ke dalam tabel yang ada.

// 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)

Contoh

Contoh berikut mengkueri koleksi untuk item dengan harga lebih dari $9,99 dan memproyeksikan hasilnya. Urutan yang dikembalikan dari jenis anonim dimuat ke dalam tabel yang ada. Skema tabel diperluas secara otomatis karena jenis Book dan Movies berasal dari jenis 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)

Contoh

Contoh berikut mengkueri koleksi untuk item dengan harga lebih dari $9,99 dan mengembalikan urutan Double, yang dimuat ke dalam tabel baru.

// 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()

Membuat DataTable Dari Kueri (LINQ to DataSet)

Membuat Metode CopyToDataTable<T> Kustom

Contoh

Contoh

Contoh

Contoh

Contoh

Lihat juga

Sumber Daya Tambahan: