c# datatable how to get all the records with a condition

Question

This is my code:

SqlCommand vmpCmd = new SqlCommand("sp", connectionstring);
SqlDataAdapter DAvmp = new SqlDataAdapter(vmpCmd);
DataSet DSvmp = new DataSet();
DSvmp.Clear();
DAvmp.Fill(DSvmp);
DataTable table;
table = DSvmp.Tables[0];

from that table I need to take all the rows that its Campaign column exists in this list List<string> compaines = new List<string>();

What have I tried

I tried this:

table = (from row in table.AsEnumerable()
         where compaines.Contains(row.Field<string>("Campaign"))
         select row);

but I got this exception on the select:

Cannot implicitly convert type 'System.Data.EnumerableRowCollection<System.Data.DataRow>' to 'System.Data.DataTable'

score 3 · Accepted Answer · edited May 23 '17 at 12:13

3

If you want to convert an IEnumerable<DataRow> to a DataTable you can use CopyToDataTable:

var filteredRows = table.AsEnumerable()
    .Where(row => compaines.Contains(row.Field<string>("Campaign")));
table = filteredRows.CopyToDataTable();

But instead of filtering when you've already retrieved all records i would do that in the database. Here's a nice trick to build parameters from a List<string>.

edited May 23 '17 at 12:13

Community

1
1

answered Aug 19 '14 at 09:41

Tim Schmelter

429,027
67
649
891

i am trying your solution – Marco Dinatsoli Aug 19 '14 at 09:43
you have changed you code, is my previous code wrong? when I made var and then copytodatatble it works – Marco Dinatsoli Aug 19 '14 at 09:50
@MarcoDinatsoli: you can always look at the revision-history of a question/answer to see what was changed. The [link](http://stackoverflow.com/posts/25380213/revisions) is below the question/answer. In this case i have replaced a colon with a dot and mentioned that you should filter in the database if possible since that is much more efficient if the table is getting large. – Tim Schmelter Aug 19 '14 at 09:51

score 1 · Answer 2 · answered Aug 19 '14 at 09:41

1

Use CopyToDataTable extension method to convert IEnumerable<DataRow> to DataTable

table = (from row in table.AsEnumerable()
         where compaines.Contains(row.Field<string>("Campaign"))
         select row).CopyToDataTable();

answered Aug 19 '14 at 09:41

Sriram Sakthivel

69,953
7
104
182

score 0 · Answer 3 · answered Aug 19 '14 at 09:39

0

Get the values in var data type like this as LINQ returns System.Data.EnumerableRowCollection<System.Data.DataRow which cannot be implicity converted to Datatable type

var filteredTable = (from row in table.AsEnumerable()
                     where compaines.Contains(row.Field<string>("Campaign"))
                     select row);

answered Aug 19 '14 at 09:39

Rajeev Kumar

4,701
6
43
81

I can't do that because I do this ` table.Compute("Sum(TotalCalls)", "");` when I filter the table – Marco Dinatsoli Aug 19 '14 at 09:41

score 0 · Answer 4 · answered Aug 19 '14 at 09:54

If you want the retrieved records for other operations then its fine otherwise it's better to use filter in your query and for that there are below methods:

You can use parameterized query
You can build your query string using String.Join() from your List

c# datatable how to get all the records with a condition

What have I tried

4 Answers4