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

TheCoder 91 Reputation points

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


or in a different order

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.

An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,318 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Michael Taylor 41,766 Reputation points

    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  
       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 "  
               joinQuery = True  
           End If  
           Dim vins = group.Select(Function(x) """" + x.VIN + """")  
           query += $"(classCode = ""{group.Key}"" AND vin IN ({String.Join(",", vins)}))"  

    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.

    0 comments No comments

  2. TheCoder 91 Reputation points

    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]

    0 comments No comments