Get certain values out of an dynamic array using VB.NET

Question

I have an array that is created from a web page. The values can be like:

HT_998899
LT_58212
MT_332233
HT_00001

or in a different order
MT_332233
HT_998899
MT_00001
LT_5812

and so on, it can be in any order and any number of inputs. Based on each prefix I have to do a different SQL Where clause. So for example,
I want to grab everything that begins with HT and so I can create a dynamic where clause in my SQL so for example:

select * from classification where classCode = 'HT' and vin in ('998899') or classCode = 'MT' and vin in ('332233','00001')

and so on. The where clause will be dynamic.

Answer 1

So you need to parse a series of strings into a code and number and then query the DB based upon it. There are a variety of ways to do this but since your format seems a little flexible let's assume that everything on the left of the underscore is the code and everything on the right is the VIN. To make this easier we'll use a simple wrapper type.

   Class VehicleData  
       Public Property Code As String  
     
       Public Property VIN As String  
     
   End Class  
     
   Iterator Function ParseData(values As IEnumerable(Of String)) As IEnumerable(Of VehicleData)  
       For Each value In values  
           Dim tokens = value.Split("_", 2)  
           If tokens.Length = 2 Then  
               Yield New VehicleData() With {.Code = tokens(0), .VIN = tokens(1)}  
           End If  
       Next  
   End Function  

Given a string array we return back the equivalent VehicleData with the values parsed out. From there you can do whatever you want.

   Dim tests() As String = New String() {  
           "HT_998899", "LT_58212", "MT_332233", "HT_00001"  
       }  
     
   Dim data = ParseData(tests)  

In your specific case you wanted to group the values by code and then add them to a query dynamically. Let's build that specific case up.

   Dim groups = data.GroupBy(Function(x) x.Code)  
     
     
   Dim query = "SELECT * FROM Classification WHERE "  
   Dim joinQuery = False  
   For Each group In groups  
       If joinQuery Then  
           query += " OR "  
       Else  
           joinQuery = True  
       End If  
     
       Dim vins = group.Select(Function(x) """" + x.VIN + """")  
       query += $"(classCode = ""{group.Key}"" AND vin IN ({String.Join(",", vins)}))"  
   Next  

Note that we are making an assumption that you validate the string input you are getting to avoid SQL injection attacks. Also note that if you're dealing with a lot of values then the query may be too big so don't try this on 1000 of strings. If you need that kind of functionality then you'll need to partition your data and/or consider moving some of that logic into the database.

Answer 2

So, with the data coming from a textbox, how would your last example work? what is this line referring to?

Dim groups = data.GroupBy(Function(x) x.Code)

and yes, the data can have those prefixes and will be 3 characters then an underscore, then the numbers

and the data is being passed in as: [ separated by semi colon]
HT_998899;LT_58212;MT_332233;HT_00001