Tally Rows in a DataSet that Match a Condition

  • Article

Today I got a question that comes up often in data application programming about how to count rows in a DataSet that matched a condition. The DataSet may be bound to a DataGridView or other list control and it’s tempting to start looking at the control to see if you can coax it into returning what you need but usually there is a much better way.

For instance, say we have a table in our database called “Inbox” that has varchar fields Subject, From, and Status and we’d like to tally all the rows where the Status = “Unread”. Suppose we’ve also created a typed DataSet that contains this Inbox table. You create a data-bound Windows Form with a DataGridView on it by dragging the table from the Data sources window onto the form (like I showed in this video). When you do this, Visual Studio generates code that hooks up your DataGridView’s DataSource property to a BindingSource object which in turn has it’s DataSource set to your DataSet. This is a good thing. The BindingSource is a simple controller that provides currency between your DataSet and the DataGidView UI. (In WPF this is similar to the CollectionView object). You write code against the BindingSource instead so that it doesn’t matter what kind of control is being used to display the data.

BindingSource to the Rescue

The BindingSource in this case is actually working with a DataView, not the actual DataTable, which may seem confusing. This is needed for related data binding to work (see this video and this one). Since the BindingSource manages the currency (current row position) that is being displayed by the controls, in this example the BindingSource.List will return the DataView and the BindingSource.Current property will always return the DataRowView. You access your typed DataRow by casting the DataRowView.Row property. So to get a count of items in the DataView we could just simply ask the BindingSource for a count of it’s rows no matter what kind of control is being used for the display:

 Dim count = Me.InboxBindingSource.Count

Or we could grab the DataView and ask for its count:

 Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count = dv.Count

Once you have the DataView you can apply more filtering on it directly or we can loop through it to tally the rows where Status = “Unread”.

 Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count = 0
For Each drv As DataRowView In dv
    Dim inboxRow = CType(drv.Row, EmailDataSet.InboxRow)
    If inboxRow.Status = "Unread" Then
        count += 1
    End If
Next

But the neat thing about using the BindingSource is that you always know the current row being displayed. So if we want to tally the rows based on a condition in the current row we go through the BindingSource. You can easily write the code that gets the current row as your typed data row by using a code snippet. Right-click in the editor, select Data – LINQ, XML, Designer, ADO.NET > Designer Features and ADO.NET > Converts BindingSource.Current to a specific row in a DataTable:

image

This code returns a tally of rows in the current view that have the same status as the selected row. Instead of looping through the data manually with For Each, this code sets the Filter property of the DataView instead:

 Dim currentRow As EmailDataSet.InboxRow
currentRow = CType(CType(Me.InboxBindingSource.Current, DataRowView).Row, EmailDataSet.InboxRow)

Dim dv = CType(Me.InboxBindingSource.List, DataView)
dv.RowFilter = "Status ='" & currentRow.Status & "'"
Dim count = dv.Count

This technique causes any bound controls to update to the new filter. To remove the filter, set the Filter property to Nothing or the empty string. Although this code may work for a lot of scenarios, we may have a situation where we do not want to affect any of the bound controls by changing the filter. And even though this is a simple condition it would be nice not to have to manually write the loop ourselves. This is a perfect place to use LINQ instead.

LINQ to the Rescue

If we want to tally the entire DataTable for rows matching a condition we can use LINQ to DataSets with Visual Basic’s Aggregate clause. This doesn’t involve a BindingSource at all but be aware that the DataView that a BindingSource may be displaying data from may be filtered. In the case of this example the DataView contains the same rows as the DataTable (no filter) so we can write a simple Aggregate query to count the rows:

 Dim count As Integer = Aggregate row In Me.EmailDataSet.Inbox _
                       Where row.Status = "Unread" _
                       Into UnreadCount = Count()

This will return an integer indicating the number of rows in the entire DataTable that matched the Where clause. But what if we do want to use the BindingSource and take into account any filters that are being applied to the DataView in which controls are bound? In this case we can still use an Aggregate LINQ query it will just be against the DataView so we will need to cast a few things:

 Dim dv = CType(Me.InboxBindingSource.List, DataView)
Dim count As Integer = Aggregate row In dv.Cast(Of DataRowView).AsQueryable _
                       Where CType(row.Row, EmailDataSet.InboxRow).Status = "Unread" _
                       Into UnreadCount = Count()

Now what would be really nice is to automatically tally this information from the DataSet and display it in a label on the Form anytime the user made any changes to the rows. We can do this easily by handling the BindingSource’s ListChanged event and putting our Aggregate LINQ query in there:

 Private Sub InboxBindingSource_ListChanged() Handles InboxBindingSource.ListChanged
    Dim count As Integer = Aggregate row In Me.EmailDataSet.Inbox _
                           Where row.Status = "Unread" _
                           Into UnreadCount = Count()

    Me.lblStatus.Text = count.ToString() & " unread email."
End Sub

Now this will give us a “live” running tally and we’re not bound to the UI controls at all. Nice. For more information on Aggregate queries and DataSets check out this video.

More resources:

Enjoy!