Q:

Simplest/fastest way to check if value exists in DataTable in VB.net?

I have a DataTable (currently with multiple columns but I could just grab one column if it makes it easier). I want to check if a String value exists in a column of the DataTable. (I'm doing it many times so I want it to be reasonably fast.)
What is a good way to do this? Iterating through the DataTable rows each time seems like a bad way. Can I convert the column to a flat List/Array format, and use a built in function? Something like myStrList.Contains("value")?

A:

You can use select to find whether that value exist or not. If so, it returns rows or it will not. Here is some sample code to help you.
Dim foundRow() As DataRow
foundRow = dt.Select("SalesCategory='HP'")

A:

If the data in your DataTable doesn't change very often, and you search the DataTable multiple times, and your DataTable contains many rows, then it's likely going to be a lot faster to build your own index for the data.  
The simplest way to do this is to sort the data by the key column so that you can then do a binary search on the sorted list.  For instance, you can build an index like this:
Private Function BuildIndex(table As DataTable, keyColumnIndex As Integer) As List(Of String)
    Dim index As New List(Of String)(table.Rows.Count)
    For Each row As DataRow in table.Rows
        index.Add(row(keyColumnIndex))
    Next
    index.Sort()
    Return index
End Function

Then, you can check if a value exists in the index quickly with a binary search, like this:
Private Function ItemExists(index As List(Of String), key As String) As Boolean
    Dim index As Integer = index.BinarySearch(key)
    If index >= 0 Then
        Return True
    Else
        Return False
    End If
End Function

You could also do the same thing with a simple string array.  Or, you could use a Dictionary object (which is an implementation of a hash table) to build a hash index of your DataTable, for instance:
Private Function BuildIndex(table As DataTable, keyColumnIndex As Integer) As Dictionary(Of String, DataRow)
    Dim index As New Dictionary(Of String, DataRow)(table.Rows.Count)
    For Each row As DataRow in table.Rows
        index(row(keyColumnIndex)) = row
    Next
    Return index
End Function

Then, you can get the matching DataRow for a given key, like this:
Dim index As Dictionary(Of String, DataRow) = BuildIndex(myDataTable, myKeyColumnIndex)
Dim row As DataRow = Nothing
If index.TryGetValue(myKey, row) Then
   ' row was found, can now use row variable to access all the data in that row
Else
   ' row with that key does not exist
End If

You may also want to look into using either the SortedList or SortedDictionary class.  Both of these are implementations of binary trees.  It's hard to say which of all of these options is going to be fastest in your particular scenario.  It all depends on the type of data, how often the index needs to be re-built, how often you search it, how many rows are in the DataTable, and what you need to do with the found items.  The best thing to do would be to try each one in a test case and see which one works best for what you need.

A:

You should use row filter or DataTable.Rows.Find() instead of select (select does not use indexes). Depending on your table structure, specifically if your field in question is indexed (locally), performance of either way should be much faster than looping through all rows. In .NET, a set of fields needs to be a PrimaryKey to become indexed.
If your field is not indexed, I would avoid both select and row filter, because aside from overhead of class complexity, they don't offer compile time check for correctness of your condition. If it's a long one, you may end up spending lots of time debugging it once in a while.
It is always preferable to have your check strictly typed. Having first defined an underlying type, you can also define this helper method, which you can convert to extension method of DataTable class later:
Shared Function CheckValue(myTable As DataTable, columnName As String, searchValue As String) As Boolean
  For row As DataRow In myTable.Rows
    If row(columnName) = searchValue Then Return True
  Next
  Return False
End Function

or a more generic version of it:
Shared Function CheckValue(myTable As DataTable, checkFunc As Func(Of DataRow, Boolean)) As Boolean
  For Each row As DataRow In myTable.Rows
    If checkFunc(row) Then Return True
  Next
  Return False
End Function

and its usage:
CheckValue(myTable, Function(x) x("myColumn") = "123")

If your row class has MyColumn property of type String, it becomes:
CheckValue(myTable, Function(x) x.myColumn = "123")

One of the benefits of above approach is that you are able to feed calculated fields into your check condition, since myColumn here does not need to match a physical myColumn in the table/database.

