Get Row Count of Distinct Values in a DataTable Column

Question

I have a DataTable that has several hundred rows.  I want to get a row count of distinct values in a particular column.  For example, I have a DataTable of Product Orders, but I want to get the number of unique Customers.

I was using this code, but I can't rely on it because my binding source is filtered from time to time.  If my binding source has filtered out all rows datatable.DefaultView returns 0 rows.

Dim ResultCount As Integer = Me.ds.Tables("ProductOrders").DefaultView.ToTable(True, "Customers").Rows.Count

I don't want to loop thru each row.  Is there a more elegant way to do this?

Thanks in advance,

---

Ryan

Answer 1

Use the same code but with a new DataView instead of the default view

Answer 2

Can you give me an example of what you are talking about?

Thanks,

---

Ryan

Answer 3

The dataview class has a ToTable function that allows you to specify a distinct select based on one or more columns.

http://msdn.microsoft.com/en-us/library/wec2b2e6(v=vs.80).aspx 

I'm not entirely sure of what's best in your situation, but you could use the original table to make a view then immediately produce a table of distinct rows based on the column  or else if you have a view already and want to use it then just call ToTable.  Here's a code example of the concept:

        Dim tbl As New DataTable
        tbl.Columns.Add("Col1", GetType(Int32))

        tbl.LoadDataRow(New Object() {1}, True)
        tbl.LoadDataRow(New Object() {2}, True)
        tbl.LoadDataRow(New Object() {3}, True)
        tbl.LoadDataRow(New Object() {1}, True)
        tbl.LoadDataRow(New Object() {1}, True)

        'Should return 3 distinct values in Col1
        MsgBox(tbl.AsDataView.ToTable(True, "Col1").Rows.Count)

Answer 4

      Dim View As New DataView(ds.Tables("ProductOrders"))
      Dim ResultCount As Integer = View.ToTable(True, "Customers").Rows.Count

Answer 5

What about using LINQ?

        Dim rowCnt As Integer = MyAudioDataSet.AudioInfo.Rows.Count
        Dim qry = From AudioInfo In MyAudioDataSet.AudioInfo _
                  Select AudioInfo.AlbumName Distinct
        Dim rowCntDistinct As Integer = qry.Count
        MessageBox.Show("Row Count: " & rowCnt.ToString("n0") & vbCrLf & _
                        "Unique Album Name Count: " & rowCntDistinct.ToString("n0"))