CODEWITHSUNDEEP
c#linqdatatable
Learner
Learner

Reputation: 353

Get N number of columns from datatable c#

I have a datatable where I need to take n number of columns. For ex: From the below datatable I need to take first 10 columns alone with data and put it in another datatable.

Code:

DataTable dtRecord = DAL.GetRecords();

I tried this and this doesn't take the required column.

var selectColumns = dtRecord .Columns.Cast<System.Data.DataColumn>().Take(10);

Upvotes: 0

Views: 3512

Answers (4)

vicky
vicky

Reputation: 141

You can also use this

private DataTable GetNColumnsFromDataTable(DataTable tblSource, int outputCols)
    {
        DataTable columnOutput = tblSource.Copy();
        if (outputCols > 0 && outputCols < tblSource.Columns.Count)
        {
            while (outputCols < columnOutput.Columns.Count)
            {
                columnOutput.Columns.RemoveAt(columnOutput.Columns.Count - 1);
            }
        }
        return columnOutput;
    }

Upvotes: 2

Alexander Petrov
Alexander Petrov

Reputation: 14231

You can do it like this:

var selectColumns = dtRecord.Columns.Cast<DataColumn>().Take(10);

var dtResult = new DataTable();

foreach (var column in selectColumns)
    dtResult.Columns.Add(column.ColumnName);

foreach (DataRow row in dtRecord.Rows)
    dtResult.Rows.Add(row.ItemArray.Take(10).ToArray());

Perhaps you should create a column of the same type and with the same expression:

dtResult.Columns.Add(column.ColumnName, column.DataType, column.Expression);

Upvotes: 1

NetMage
NetMage

Reputation: 26917

To copy from one DataTable to another, you can extract the columns of interest

var moveCols = dtRecord.Columns.Cast<DataColumn>().Take(10).Select(c => c.ColumnName).ToArray();

Then you must create new DataColumns in a new table, then create new DataRows in the new table:

var newTable = new DataTable();
foreach (var c in moveCols)
    newTable.Columns.Add(c);
foreach (var r in dtRecord.AsEnumerable())
    newTable.Rows.Add(moveCols.Select(c => r[c]).ToArray());

Which you can make an extension method on DataTable:

public static DataTable Slice(this DataTable dt, params string[] colnames) {
    var newTable = new DataTable();
    foreach (var c in colnames)
        newTable.Columns.Add(c, dt.Columns[c].DataType);

    foreach (var r in dt.AsEnumerable())
        newTable.Rows.Add(colnames.Select(c => r[c]).ToArray());

    return newTable;
}

Now you can call

var newTable = dtRecord.Slice(moveCols);

With a nice extension method, you can convert from Dictionarys to a DataTable dynamically:

var newTable = dtRecord.AsEnumerable().Select(r => moveCols.ToDictionary(c => c, c => r[c])).AsDataTable();

I have some for converting ExpandoObject and anonymous objects as well, as well as an extension to convert those to anonymous objects dynamically. Here is the code for Dictionarys to DataTable:

public static DataTable AsDataTable(this IEnumerable<IDictionary<string, object>> rows) {
    var dt = new DataTable();
    if (rows.Count() > 0) {
        foreach (var kv in rows.First())
            dt.Columns.Add(kv.Key, kv.Value.GetType());

        foreach (var r in rows)
            dt.Rows.Add(r.Values.ToArray());
    }
    return dt;
}

public static DataTable AsDataTable(this IEnumerable<Dictionary<string, object>> rows) => ((IEnumerable<IDictionary<string, object>>)rows).AsDataTable();

Upvotes: 1

Rai Vu
Rai Vu

Reputation: 1635

Get 10 columns:

 var tbl = new System.Data.DataTable();
var cols = tbl.Columns.Cast<System.Data.DataColumn>().Take(10);
// if you wish to get first 10 columns...

If you want to get the data, then you have to loop through the columns to get the data.

var data = cols.SelectMany(x => tbl.Rows.Cast().Take(10).Select(y => y[x]));

of course, this will dump all the data into an ienumerable, if you want to use strong typed object or a list of one dimensional array, believe it's fairly simple, for example:

var data2 = cols.Select(x => tbl.Rows.Cast().Take(10).Select(y => y[x]).ToArray());

Upvotes: 0

Related Questions