Datatable subset of columns from another datatable

Question

I have a datatable with 17 columns and a bunch of data.

I wnat a datatable with only 6 of the columns and the data for those 6 columns.

So I need a subset of the original datatable.

How do I loop through the original datatable with 17 columns and end up with a datatable with only the 6 columns I want with the corresponding data for those 6 columns?

Answer 1

what about data types, and columns? are these same? if yes, you can create

 object[] row = new object[]{// Fill your rows manually};

before filling it create

DataTable dt = new DataTable(); 
dt.Columns.Add("Title",typeof(string etc..));.....

and finally

dt.Rows.Add(row);

Answer 2

Without knowing more about how generic this needs to be its really just...

foreach (DataRow dr in dt.Rows)
{
  newDt.Rows.Add(dt.Rows["col1"],dr.Rows["col5"],etc);
}

Answer 3

Personally, I would avoid creating another instance of a DataTable.

It depends on your situation, of course, but if this is purely for usability and not for security (i.e. you're not trying to remove columns with sensitive data before transmitting it somewhere), then I would create a wrapper object that encapsulates the columns that you want to expose.

The benefit of using a wrapper is in case you are doing any updates, then you can update the source table directly rather than the copy. Whether this really matters, of course, depends on your situation.

A simple example with limited functionality:

public class MyFormOrPage
{
    void UsageExample()
    {
        DataTable allDataTable = new DataTable();
        // populate the data table with whatever logic ...

        // wrap the data table to expose only the Name, Address, and PhoneNumber columns
        var limitedDataTable = new DataTableWrapper(allDataTable, "Name", "Address", "PhoneNumber");

        // iterate over the rows
        foreach (var limitedDataRow in limitedDataTable)
        {
            // iterate over the columns
            for (int i = 0; i < limitedDataTable.ColumnCount; i++)
            {
                object value = limitedDataRow[i];
                // do something with the value ...
            }
        }

        // bind the wrapper to a control
        MyGridControl.DataSource = limitedDataTable;
    }
}

public class DataTableWrapper : IEnumerable<DataRowWrapper>
{
    private DataTable _Table;

    private string[] _ColumnNames;

    public DataTableWrapper(DataTable table, params string[] columnNames)
    {
        this._Table = table;

        this._ColumnNames = columnNames;
    }

    public int ColumnCount
    {
        get { return this._ColumnNames.Length; }
    }

    public IEnumerator<DataRowWrapper> GetEnumerator()
    {
        foreach (DataRow row in this._Table.Rows)
        {
            yield return new DataRowWrapper(row, this._ColumnNames);
        }
    }

    #region IEnumerable Members

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }

    #endregion

    // if you _really_ want to make a copy of the DataTable, you can use this method
    public DataTable CopyToDataTable()
    {
        DataTable copyTable = new DataTable();
        for (int index = 0; index < this._ColumnNames.Length; index++)
        {
            DataColumn column = this._Table.Columns[index];
            copyTable.Columns.Add(column);
        }
        foreach (DataRow row in this._Table.Rows)
        {
            DataRow copyRow = copyTable.NewRow();
            for (int index = 0; index < this._ColumnNames.Length; index++)
            {
                copyRow[index] = row[this._ColumnNames[index]];
            }
            copyTable.Rows.Add(copyRow);
        }
        return copyTable;
    }
}

// let's make this a struct, since potentially very many of these will be instantiated
public struct DataRowWrapper
{
    private DataRow _Row;

    private string[] _ColumnNames;

    public DataRowWrapper(DataRow row, params string[] columnNames)
    {
        this._Row = row;

        this._ColumnNames = columnNames;
    }

    // use this to retrieve column values from a row
    public object this[int index]
    {
        get { return this._Row[this._ColumnNames[index]]; }
        set { this._Row[this._ColumnNames[index]] = value; }
    }

    // just in case this is still needed...
    public object this[string columnName]
    {
        get { return this._Row[columnName]; }
        set { this._Row[columnName] = value; }
    }
}

Answer 4

Private Function createSmallCopyofExistingTable(ByVal SourceTable As DataTable) As DataTable Dim newTable As DataTable = New DataTable()

    'Copy Only 6 columns from the datatable 
    Dim ColumnsToExport() As String = {"ID", "FirstName", "LastName", "DateOfBirth", "City", "State"}

    newTable = SourceTable.DefaultView.ToTable("tempTableName", False, ColumnsToExport)



    Return newTable
End Function