CODEWITHSUNDEEP
c#datatableado.net
daftBit
daftBit

Reputation: 35

DataTable Filter with range of dates

I have a section of a program I am working on that needs to filter out all the unnecessary rows and only keep the necessary rows based on a start and end date that is chosen from a date time picker control on my form. A .csv file contains all the dates from the following as an example:

If my datetime picker has been selected for start as 2/18/2015, and an ending date of 3/2/2015, I want to be able to only have these items contained in my data table. I have a method already that reverses the data rows, but I need to know how exactly to go about filtering out the values that are less than or equal to the end date, and greater than or equal to the start date. My plan is to have the filtering done in a separate method in which I pass my existing table that is created from reading the .csv file.

My code so far is as follows:

public DataTable PopulateTable()
{
    DataTable stockTable = new DataTable();
    try
    {
        FileStream stream = new FileStream(BuildFilePath(), FileMode.Open);
        stream.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show("Unable to open filepath");
    }

    StreamReader reader = new StreamReader(BuildFilePath());
    string header = "";
    string line = "";
    char[] delimeter = { ',' };
    string[] headerData;
    string[] rowData;
    //read the header and add the names to the columns of the data table
    header = reader.ReadLine();
    headerData = header.Split(delimeter);
    for (int i = 0; i < 5; ++i)
    {
        if (i == 0)
        {
            stockTable.Columns.Add(new DataColumn(headerData[i], typeof(DateTime)));
        }
        else
            stockTable.Columns.Add(new DataColumn(headerData[i], typeof(decimal)));
    }
    //read each line and populate the data table rows
    while (reader.Peek() > -1)
    {
        line = reader.ReadLine();
        rowData = line.Split(delimeter);
        stockTable.Rows.Add(rowData[0], rowData[1], rowData[2], rowData[3], rowData[4]);
    }
    reader.Close();

    //before the table is returned here call the FilterTable function

    return ReverseRowsInDataTable(stockTable);
}

private DataTable ReverseRowsInDataTable(DataTable table)
{
    DataTable reversedDataTable = table.Clone();
    for (int i = table.Rows.Count - 1; i >= 0; --i)
    {
        reversedDataTable.ImportRow(table.Rows[i]);
    }
    return reversedDataTable;
}

private DataTable FilterTable(DataTable table)
{
    DataTable filteredTable = new DataTable();
    return filteredTable;
}

Upvotes: 3

Views: 8693

Answers (1)

Ric .Net
Ric .Net

Reputation: 5540

I don't understand why you would reverse the rows in the datatable. From my perspective this isn't necessary.

You can filter the rows using a Linq query as follows:

private DataTable FilterTable(DataTable table, DateTime startDate, DateTime endDate)
{
    var filteredRows =
        from row in table.Rows.OfType<DataRow>()
        where (DateTime) row[0] >= startDate
        where (DateTime) row[0] <= endDate
        select row;

    var filteredTable = table.Clone();

    filteredRows.ToList().ForEach(r => filteredTable.ImportRow(r));

    return filteredTable;
}

To return a DataTable with the same structure you can use the .Clone() method and add the filtered rows tho this newly created table.

EDIT

You could do the reverse in the same go. If you would add an extra int column at index 1 while filling and insert the rownumber here like this:

for (int i = 0; i < 6; ++i)
{
    if (i == 0)
    {
        stockTable.Columns.Add(new DataColumn(headerData[i], typeof(DateTime)));
    }
    else if (i == 1)
    {
        stockTable.Columns.Add(new DataColumn("RowNbr", typeof(int)));
    }
    else
        stockTable.Columns.Add(new DataColumn(headerData[i-1], typeof(decimal)));
}
//read each line and populate the data table rows
int j = 0;
while (reader.Peek() > -1)
{
    line = reader.ReadLine();
    rowData = line.Split(delimeter);
    stockTable.Rows.Add(rowData[0], j++, rowData[1], rowData[2], rowData[3], rowData[4]);
}

Reversing the data can be very simple:

private DataTable FilterTable(DataTable table, DateTime startDate, DateTime endDate)
{
    var filteredRows =
        from row in table.Rows.OfType<DataRow>()
        where (DateTime)row[0] >= startDate
        where (DateTime)row[0] <= endDate
        orderby (int)row[1] descending 
        select row;

    var filteredTable = table.Clone();

    filteredRows.ToList().ForEach(r => filteredTable.ImportRow(r));

    return filteredTable;
}

Upvotes: 6

Related Questions