CODEWITHSUNDEEP
c#datatabledatasetfiltering
Spitfire2k6
Spitfire2k6

Reputation: 308

Filtering DataTable c#

I have content in a DataTable that I need to filter into 3 specific lists. They are all to be respectively sorted by start date within each of these sets of results. 1 day events, 2-13 day events and >14 day events.

I've tried using DataTable.Select but it seems this doesn't work like a SQL where as I expected(don't know why I expected it to).

This is what I've tried

    DataTable allResults = ds.Tables["Items"];      
    DataTable set1 = new DataTable();
    DataTable set2 = new DataTable();
    DataTable set3 = new DataTable();

    DataRow[] result = allResults.Select("DATEDIFF(day,tcsi_startdate,tcsi_enddate) = 0");
    foreach (DataRow row in result)
    {
      set1.ImportRow(row);
    }
    result = allResults.Select("DATEDIFF(day,tcsi_startdate,tcsi_enddate) > 0 AND DATEDIFF(day,tcsi_startdate,tcsi_enddate) < 14");
    foreach (DataRow row in result)
    {
      set2.ImportRow(row);
    }
    result = allResults.Select("DATEDIFF(day,tcsi_startdate,tcsi_enddate) > 13");
    foreach (DataRow row in result)
    {
      set3.ImportRow(row);
    }

    set1.DefaultView.Sort = "tcsi_startdate";
    set2.DefaultView.Sort = "tcsi_startdate";
    set3.DefaultView.Sort = "tcsi_startdate";

Is there any way in which I can sort the data as I need to using DataTable.Select?

Cheers in Advance

Upvotes: 1

Views: 751

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460058

You can use LINQ-To-DataSet

var filtered = from row in allresults.AsEnumerable()
               let tcsi_startdate = row.Field<DateTime>("tcsi_startdate")
               let tcsi_enddate = row.Field<DateTime>("tcsi_enddate")
               let daysDiff = (tcsi_enddate - tcsi_startdate).Days 
               where daysDiff > 0 && daysDiff < 14
               select row;

If you want to loop the rows use a foreach:

foreach(DataRow row in filtered){...}

If you want a new DataTable from the IEnumerable<DataRow>:

DataTable tblResult = filtered.CopyToDataTable();

Note that this throws an exception if there are no rows in the filtered result. You can ensure that with checking if(filtered.Any()){...}.

If you want a DataRow[]:

DataRow[] rowArray = filtered.ToArray();
// or a list:
List<DataRow> rowList = filtered.ToList();

Upvotes: 4

Related Questions