Tally Rows in a DataSet that Match a Condition

บทความ
04/27/2009

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:

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!

Comments

Anonymous
April 27, 2009
PingBack from http://asp-net-hosting.simplynetdev.com/tally-rows-in-a-dataset-that-match-a-condition/
Anonymous
April 27, 2009
Hi, I have a question. How do you get these snippet? Are those your own?
Anonymous
April 28, 2009
The comment has been removed
Anonymous
May 28, 2009
Hai, Beth. I Have a question. How is the code if I don't use typed dataset? In other words, I don't use drag and drop, since I have difficulty if there is a problem rising with typed dataset. I know You always use drag and drop in creating and manipulating database application in article or video. However, perhaps you may use purely code in stating the current row with bindindsource. Thanks in advanced.
Anonymous
May 28, 2009
Hai, Beth. I Have a question. How is the code if I don't use typed dataset? In other words, I don't use drag and drop, since I have difficulty if there is a problem rising with typed dataset. I know You always use drag and drop in creating and manipulating database application in article or video. However, perhaps you may use purely code in stating the current row with bindindsource. Thanks in advanced.
Anonymous
June 02, 2009
Hi El, You still can grab the position from the BindingSource if you are binding to an untyped DataSet/DataTable as well. You just have to deal with converting the values in the rows because you don't have a schema. If your UI is purely dynamic you can end up in this situation, but if you have known properties it's much easier to work with typed datasets and objects. Designer support for data-binding is just one benefit. Dim dt As New DataTable ' create columns and fill rows ... Me.BindingSource1.DataSource = dt '.... Private Sub BindingSource1_PositionChanged() Handles BindingSource1.PositionChanged 'Get the current row Dim row As DataRow row = CType(CType(Me.BindingSource1.Current, DataRowView).Row, DataRow) MsgBox(row("Column1").ToString()) End Sub HTH, -B
Anonymous
June 02, 2009
Hi beth I want to be able to use linq to get a certain recordset from an xml file and then put those results into a datagridview. my code looks like this Dim myPlayer = XDocument.Load(strPath & "�8Stats.xml") Dim xmlPlayer = <Player> <%= From Player In myPlayer...<row> _ Where Player.<PlID>.Value = intPlayerID _ Select Player %> </Player> Me.dgvPlayerStats.DataSource = xmlPlayer Me.dgvPlayerStats.DataBind() obviously this doesnt work. I want to be able to query an xml doc and put the results of that query in a datagridview. Thanks for your time. Joe
Anonymous
June 02, 2009
Hi Joe, Try creating a list of objects from your query instead. You're creating an XElement here and you can't bind this control to that. Try something like this: Dim resultPlayers = _ From Player In myPlayer...<row> _ Where Player.<PlID>.Value = intPlayerID _ Select Player.<PID>.Value, _ Player.<Property1>.Value, ...rest of columns Me.dgvPlayerStats.DataSOurce = resultPlayers.ToList() HTH, -B
Anonymous
September 20, 2010
The Binding Source Position is limited to integer. When I am using sql bigint or Vb Long, How is the binging Source going to bind more than integer value. I need to find positions in long not in integers. my records can be more than 2147483647.
Anonymous
September 21, 2010
Ali, You shouldn't be bringing down that much data into a DataView, you'll have perf problems if you do.

แชร์ผ่าน

Tally Rows in a DataSet that Match a Condition

Comments

แหล่งทรัพยากรเพิ่มเติม