Filtering a Dataset?

[Caraldur]

Hello everyone, I've been working on this problem for a couple days but I am making no progress so I decided to check here and see if I could get some help.

I am filling my dataset with the following query.

SELECT     TaskInfo.taskId, TaskInfo.TaskName, TaskInfo.EquipmentId, TaskInfo.Description, TaskInfo.Interval, TaskInfo.LastCompleted, 
                      EquipmentInfo.equipmentId AS Eq_Id, EquipmentInfo.EquipmentName
FROM         TaskInfo INNER JOIN
                      EquipmentInfo ON TaskInfo.EquipmentId = EquipmentInfo.equipmentId

Once the dataset is filled I would like to run a filter on the dataset but I'm not sure how to go about it. I need to compare the current date to taskInfo.LastCompleted date if the number of days between those two dates is equal to or greater then taskInfo.interval display that row in the dataset.


I can fill the dataset using the above SELECT statement and then loop through the dataset with the following code and then fill another dataset with the appropriate data but I would rather not do that if I can help it.

Public Function getCurrentTask() As DataSet
        Dim ds As New DataSet   'first dataset to fill with data
        Dim result As TimeSpan   'variable to store the timespan between two dates
        Dim storedDate As Date   'date in datarow
        Dim currentDate As Date  'current date
        Dim ds2 As DataSet         'dataset to hold filtered data

        ds = getTaskList() 'call to a function to fill the first dataset

        For Each row As DataRow In ds.Tables("TaskInfoList").Rows
            Then
            storedDate = row.Item("LastCompleted") 'setting value to variable
            currentDate = Date.Today                    'setting value to variable
            result = currentDate - storedDate           'setting value to variable

            If result.Days >= row.Item("interval") Then
                Console.WriteLine("Result: " & result.Days & " | " & "Interval: " & row.Item("interval") & " | " & row.Item("TaskName") & " | " & "Last Completed: " & row.Item("LastCompleted") & " | " & "Today: " & Date.Today)
            End If
        Next

        Return ds2

 

[Caraldur]

Could someone at least point me in the right direction. I really need to get this problem resolved.

Thanks in advance

-Fred

 

[Caraldur]

Well, I decided to just change my SQL statement to only retrieve the require data and fill my dataset.

Here is the SQL statement I used to get the required data

SELECT taskId, TaskName, EquipmentId, Description, Interval, LastCompleted" _
        & " FROM TaskInfo WHERE (LastCompleted <= GETDATE() - Interval)

Thanks for the help anyways.

-Fred

 

[jmcilhinney]

A DataSet contains DataTables and those DataTables contain DataColumns that describe the data and DataRows that contain the data. A DataView provides a view of a DataTable that can be sorted and filtered. You can set the Sort and/or RowFilter properties to expose the data in a sorted and filtered manner, although the DataTable itself is unaffected. Each DataTable is associated with a DataView by default, via its DefaultView property. When you bind a DataTable, it's actually the contents of the DefaultView that gets exposed in the UI. That is because the DataTable class implements the IListSource interface and its GetList method returns its DefaultView, which implements IList.

Generally speaking, it's preferable to bind via a BindingSource. In that case, you bind your DataTable to the BindingSource and the BindingSource to your control(s). You can then set the Sort and Filter properties of the BindingSource. Those values are passed on to the underlying DataView.

The DataView Sort and RowFilter properties, as well as various other ADO.NET methods and properties, support a small subset of SQL-like syntax. That syntax is detailed in the DataColumn.Expression documentation. What you want to do would be cumbersome using that limited functionality. Using your original query to do the filtering should be your first choice if it can be done. That way, you don't retrieve any data from the database that you don't need. As you're using .NET 3.5, another option for local filtering would be to use LINQ to DataSet. It won't actually filter the existing DataTable, but you can execute a LINQ query very succinctly and generate a new DataTable from it, e.g.

newTable = existingTable.AsEnumerable().
                         Where(Function(row) (Date.Today - row.Field(Of Date)("LastCompleted").Date).Days >= interval).
                         CopyToDataTable()