I'm trying to write a generic function which can be used to look up an arbitrary value in an arbitrary column in an arbitrary DataTable
, and return the corresponding value in another arbitrary column in the same DataTable
. I'm not concerned with multiple values or multiple matches; the data is organised such that they don't occur anyway, all I want is for it to return the first match if it exists, or nothing if it doesn't.
I'm basing the code on this very simple example :
Private Function TableLookup(dtb As DataTable, lookupFieldName As String, lookupFieldValue As Integer, returnFieldName As String) As String
Dim result As String
Dim matches = From row In dtb Let lookup = row.Field(Of Integer)(lookupFieldName) Where lookup = lookupFieldValue
If matches.Any Then result = matches.First().row.Field(Of String)(returnFieldName)
Return result
End Function
But obviously that only works if the lookupField is an Integer
field and the returnField is a String
field. Because the function needs to handle arbitrary columns, those columns could have arbitrary DataType
s? And the value being returned is also arbitrary (could be an Integer
, could be a String
... etc.)
Obviously I can determine what the DataType
s are for each column easily enough :
Dim lookupFieldType As Type = dtb.Columns("lookupFieldName").DataType
Dim returnFieldType As Type = dtb.Columns("returnFieldName").DataType
But that's still no use as row.Field(Of T)
is strongly-typed; I can't use a variable to specify the DataType
:
Dim matches = From row In dtb Let lookup = row.Field(Of lookupFieldType)(lookupFieldName) Where lookup = lookupFieldValue
If matches.Any Then result = matches.First().row.Field(Of returnFieldType)(returnFieldName)
Have a feeling I'm going about this in completely the wrong way to begin with but it seems like there should be a straightforward way of looking up arbitrary columns in data tables (otherwise what's the point in having them, right?)
Any suggestions?
CodePudding user response:
If you will know what the types of both columns will be when you call the method, you can make it generic like this:
Private Function TableLookup(Of TKey As IEquatable(Of TKey), TResult)(table As DataTable,
keyColumnName As String,
key As TKey,
resultColumnName As String) As TResult
Dim row = table.AsEnumerable().FirstOrDefault(Function(dr) dr.Field(Of TKey)(keyColumnName).Equals(key))
Return If(row Is Nothing, Nothing, row.Field(Of TResult)(resultColumnName))
End Function
If you won't know what the column types are, you could use something like this:
Private Function TableLookup(table As DataTable,
keyColumnName As String,
key As Object,
resultColumnName As String) As Object
Dim keyType = key.GetType()
If keyType IsNot table.Columns(keyColumnName).DataType Then
Return Nothing
End If
Dim filterExpression As String
If keyType Is GetType(String) Then
filterExpression = $"{keyColumnName} = '{key}'"
ElseIf keyType Is GetType(Date) Then
filterExpression = $"{keyColumnName} = #{key:M/dd/yyyy h:mm:ss tt}#"
Else
filterExpression = $"{keyColumnName} = {key}"
End If
Dim row = table.Select(filterExpression).FirstOrDefault()
Return If(row Is Nothing, Nothing, row(resultColumnName))
End Function