Convert resultset in to Datatable

Question

How can I convert the result of a LINQ query where I select some columns of a DataTable to a DataTable again?

DataTable dtN = new DataTable();

dtN.Columns.Add("Id");   
dtN.Columns.Add("Name");
dtN.AcceptChanges();

for (int i = 1; i <= 10; i++)
{
    DataRow dr = dtN.NewRow();
    dr["Id"] = i;  
    dr["Name"] = "A"+i.ToString();
    dtN.Rows.Add(dr);
    dtN.AcceptChanges();
}

var data = from r in dtN.AsEnumerable()
           select new { Name = r["Name"].ToString() };

//this line is giving error because CopyToDatatable is not available
DataTable dt = data.CopyToDataTable();

pravprab · Answer 1 · 2014-04-08T07:33:57.803

2

DataTable dtN = new DataTable();

dtN.Columns.Add("Id");
dtN.Columns.Add("Name");
dtN.AcceptChanges();

for (int i = 1; i <= 10; i++)
{
    DataRow dr = dtN.NewRow();
    dr["Id"] = i;
    dr["Name"] = "A" + i.ToString();
    dtN.Rows.Add(dr);
    dtN.AcceptChanges();
}

var data = from r in dtN.AsEnumerable()
            select new { Name = r["Name"].ToString() };

DataTable dt = new DataTable();
dt.Columns.Add("Name");
foreach(var s in data)
{
    DataRow dr = dt.NewRow();
    dr["Name"] = s.Name;
    dt.Rows.Add(dr);
    dt.AcceptChanges();
}
//DataTable dt = data.CopyToDataTable();

EDIT : CopyToDataTable() is only available on IEnumerable<T> where T is or derives from DataRow.

So in your first case var data=from r in dtN.AsEnumerable() select r;

CopyToDataTable() is available because your query returns an IEnumerable<'DataRow> .

But in the second case var data = from r in dtN.AsEnumerable() select new { Name = r["Name"].ToString() };

you are returning an anonymous type. Anonymous types don't carry the extension method for CopyToDataTable().

If you wish to convert a non-datatable-derived T to a datatable, MSDN has a sample class that reflects out any type and performs the conversion .Please Read this link

edited Apr 08 '14 at 07:33

answered Apr 07 '14 at 10:34

pravprab

2,301
3
26
43

Thanks for your support. var data = from r in dtN.AsEnumerable() select r; DataTable dt = data.CopyToDataTable(); Please see the above code block, I do not want to make a new table because it was just an example, in my scenario(real) there are many columns and i need 10 of them so i will have to make a new table and then will have to run a loop. – Pankaj Kumar Apr 07 '14 at 11:30
1

var data=from r in dtN.AsEnumerable() select r; DataTable dt = data.CopyToDataTable();//this is available when we Select whole r(r is the full column in the datatable) But when we use like this var data = from r in dtN.AsEnumerable() select new { Name = r["Name"].ToString() };(not all the columns of the table then we do not get data.CopyToDataTable data.CopyToDataTable()//here is the error now you are saying that for this you will have to run the loop, but if i will be using the loop then what is the use of LINQ then – Pankaj Kumar Apr 07 '14 at 11:50
@user3129402 edited my answer. Hope that will help you – pravprab Apr 08 '14 at 07:34
thanks for solving problem. – Pankaj Kumar Apr 08 '14 at 08:42

score 0 · Answer 2 · answered Apr 08 '14 at 08:35

0

If you have a DataTable with a lot of columns:

var dt = new DataTable();
dt.Columns.Add("Foo",  typeof(string));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Bar",  typeof(int));

dt.Rows.Add(new object[]{"Foo",  "Max",   555});
dt.Rows.Add(new object[]{"Blub", "Marry", 123});
dt.Rows.Add(new object[]{"Bla",  "Mac",   999});

enter image description here

but you're only interested in a DataTable in one or some of the columns, but not all, you can simply use a DataView:

var nameOnlyTable = new DataView(dt).ToTable(false, new []{"Name"});

enter image description here

No need for LINQ.

answered Apr 08 '14 at 08:35

sloth

99,095
21
171
219

Thanks for your support and answer, this can be a good alternate but if there are two collection and you are going to take a inner join and you select one column from first collection and another from the second collection using linq then this will not helpful but more or less it is good alternate. Thanks again – Pankaj Kumar Apr 17 '14 at 05:05

Convert resultset in to Datatable

2 Answers2