检查 VB.net 的 DataTable 中是否存在值的最简单/最快的方法？

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

提问by DisgruntledGoat

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 Stringvalue exists in a column of the DataTable. (I'm doing it many times so I want it to be reasonably fast.)

我有一个DataTable（目前有多个列，但如果方便的话，我可以只抓取一列）。我想检查一个String值是否存在于DataTable. （我做了很多次，所以我希望它相当快。）

What is a good way to do this? Iterating through the DataTablerows each time seems like a bad way. Can I convert the column to a flat List/Arrayformat, and use a built in function? Something like myStrList.Contains("value")?

有什么好的方法可以做到这一点？DataTable每次遍历行似乎是一种糟糕的方式。我可以将列转换为平面List/Array格式并使用内置函数吗？像myStrList.Contains("value")什么？

回答by Kiran1016

You can use selectto find whether that value exist or not. If so, it returns rows or it will not. Here is some sample code to help you.

您可以使用它select来查找该值是否存在。如果是这样，它会返回行，否则不会。这里有一些示例代码可以帮助您。

Dim foundRow() As DataRow
foundRow = dt.Select("SalesCategory='HP'")

回答by Steven Doggart

If the data in your DataTabledoesn't change very often, and you search the DataTablemultiple times, and your DataTablecontains many rows, then it's likely going to be a lot faster to build your own index for the data.

如果您的数据DataTable不经常更改，并且您搜索了DataTable多次，并且您DataTable包含许多行，那么为数据构建自己的索引可能会快得多。

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 Dictionaryobject (which is an implementation of a hash table) to build a hash index of your DataTable, for instance:

你也可以用一个简单的字符串数组做同样的事情。或者，您可以使用一个Dictionary对象（它是哈希表的实现）来构建您的 的哈希索引DataTable，例如：

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 DataRowfor a given key, like this:

然后，您可以获得DataRow给定键的匹配项，如下所示：

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 SortedListor SortedDictionaryclass. 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.

您可能还想考虑使用SortedList或SortedDictionary类。这两个都是二叉树的实现。很难说在您的特定场景中，所有这些选项中的哪一个是最快的。这完全取决于数据的类型、索引需要重建的频率、搜索索引的频率、 中的行数DataTable以及需要对找到的项目执行的操作。最好的办法是在测试用例中尝试每一个，看看哪一个最适合你的需要。

回答by Neolisk

You should use row filteror 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 PrimaryKeyto become indexed.

您应该使用行过滤器或DataTable.Rows.Find()而不是 select （select 不使用索引）。根据您的表结构，特别是如果您的相关字段被索引（本地），任何一种方式的性能都应该比循环遍历所有行快得多。在 .NET 中，一组字段需要是PrimaryKey才能被索引。

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 DataTableclass later:

最好是严格打印支票。首先定义了一个底层类型，您还可以定义这个辅助方法，您可以DataTable稍后将其转换为类的扩展方法：

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 MyColumnproperty of type String, it becomes:

如果您的行类具有MyColumntype 属性String，则它变为：

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 myColumnhere does not need to match a physical myColumnin the table/database.

上述方法的好处之一是您可以将计算字段提供给检查条件，因为myColumn这里不需要匹配myColumn表/数据库中的物理。

回答by Divi

bool exists = dt.AsEnumerable().Where(c => c.Field<string>("Author").Equals("your lookup value")).Count() > 0;