By Michael McIntyre

mikemc@getdotnetcode.com

An ADO.NET question that comes up often in .NET forums is: “How do I select rows from a DataTable into a new DataTable?”, and the related question:  “How can I select a subset of a DataTable into a new DataTable?”.

The DataTable’s Clone, Select, and ImportRow methods can be used to accomplish these tasks.

DataTable Clone Method

Clones the structure of a DataTable, including all DataTable schemas and constraints. See comment *1 in the example code.  To learn more about this method click here.

DataTable SelectMethod

Gets an array of DataRow objects. See comment *2 in the example code. To learn more about this method click here.

DataTable ImportRow Method

Copies a DataRow into a DataTable, preserving any property settings, as well as original and current values. See comment *3 in the example code. To learn more about this method click here.

Example Code

The example code below makes these assumptions:

1.       A Visual Studio.NET VB.NET Windows Application project.

2.       A DataSet containing a Customers DataTable.  The schema for the DataTable is:

CustomerID   (Integer)

CustomerName (String)

3.       A DataGrid named OriginalCustomersDataGrid has been added to Form1 in the project.

4.       A DataGrid named CustomersCopyDataGrid has been added to Form1 in the project.

Private Sub Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _

  Handles MyBase.Load

    ' The DemoDataSet used for this example contains a DataTable

    ' named Customers.  Make Customers the DataSource for

    ' the OriginalCustomersDataGrid.

    Me.OriginalCustomersDataGrid.DataSource = Me._DemoDataSet.Tables("Customers")

    ' Call this form's SelectIntoDataTableFunction

    ' passing it a SelectFilter expresssion and the Customers DataTable.

    ' Assign the result returned (new DataTable) as the DataSource for the

    ' CustomersCopyDataGrid.

    ' NOTE: In this example Customers with a CustomerID < 3 are being selected

    ' into a new DataTable.

    Me.CustomersCopyDataGrid.DataSource = SelectIntoDataTable("CustomerID < 3", _

          Me._DemoDataSet.Tables("Customers"))

End Sub

Private Function SelectIntoDataTable(ByVal selectFilter As String, _

  ByVal sourceDataTable As DataTable) As DataTable

    ' *1

    ' Declare a variable of type DataTable named newDataTable.

    ' Call the Clone method on the sourceDataTable passed

    ' into this function. This will produce an empty DataTable with the

    ' same structure as sourceDataTable. Assign the new DataTable

    ' to the newDataTable variable.

    Dim newDataTable As DataTable = sourceDataTable.Clone

    ' *2

    ' Declare a variable of type DataRow Array named dataRows.

    ' Call the Select method on the sourceDataTable passed

    ' into this function using the selectFilter passed into

    ' this function. Assign the DataRows returned to the

    ' dataRows variable.

    Dim dataRows As DataRow() = sourceDataTable.Select(selectFilter)

    ' Declare a variable of type DataRow named typeDataRow.

    Dim typeDataRow As DataRow

    ' Use the typeDataRow to loop through the rows of the sourceDataTable.

    For Each typeDataRow In dataRows

    ' *3

    ' Call newDataTable's ImportRow method

    ' to import a DataRow in sourceDataTable.

        newDataTable.ImportRow(typeDataRow)

    Next

    ' Return the newDataTable.

    Return newDataTable

End Function

Copyright © 2001-2005 aZ Software Developers. All rights reserved.