Code on the fly ? RRS feed

  • Question

  • I am writing software for a client to access and filter the CMS (Centers for Medicare & Medicaid Services) NPI (National Provider Identifier) database. The database is a Comma Delimited file with over 5.5 million lines of 300 + fields per line. It is available here (if you really want it)

    I want the user to be able to choose 1 to perhaps 4 criteria to retrieve data that is of interest to them. The form and code below are a crude example for the look, using only a few of the fields.

    The choices in the left comboboxes are such that the field number is the same as the combobox selected index.
    Reading the file is no problem as is splitting each line. What I am hoping for is a method to create actual code from the users selections without a huge number of If .. EndIf statements.

    The code to execute in the example (after splitting each line into an array named "LineData") would be
    If LineData(10).contains("LMFT") and LineData(36).Contains("/2017") then
     'Write the line to the results
    End if

    I hope for two things:
    1 - this makes sense ...
    2 - There is a way to add code and execute it in a running program

    Saturday, August 18, 2018 12:22 AM

All replies

  • Thinking about this always goes back if the option is there to use SQL-Server Express. I've done this type of operation before

    Option 1

    • Use the MERGE statement to import CSV with one command. If data changes down the road MERGE handles this. For a moderate example.
    • Create a generic user control where you can feed in English name for the first ComboBox and a underlying field name, second ComboBox is static e.g. Contains, Starts/End with etc. Same goes for radio-buttons.
    • The User control would have a validate property and a method to constructor the WHERE condition.
    • Construct a User control for each condition.
    • Create a class that is feed in conditions and concatenates the conditions.
    • Run the SELECT with one or more WHERE conditions.

    Option 2

    • Used option 1 bullet 1
    • Download and see if this Query Builder would assist coupled with option 1, bullet 2. I have not looked at the query builder but from glancing at the code it appears to be a builder pattern.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, August 18, 2018 1:59 AM
  • 2 - There is a way to add code and execute it in a running program

    Yeah there is but not with VB.NET or C#.

    Saturday, August 18, 2018 2:22 AM
  • There's no need for dynamic code.  Instead of thinking of it as building one big If....And...Then statement, think of it as building a collection of criteria and then iterating the collection.

    You could make a FilterCriteria class like:

    Public Class FilterCriteria
        Public Property Combinator As CriteriaCombinator
        Public Property FieldIndex As Integer
        Public Property Comparison As CriteriaComparison
        Public Property Value As String
    End Class
    Public Enum CriteriaComparison
    End Enum
    Public Enum CriteriaCombinator
    End Enum

    Then write a matching function to use the criteria:

    Public Function DoesLineMatch(lineData As IEnumerable(Of String), criteria As IEnumerable(Of FilterCriteria)) As Boolean
        Dim finalResult As Boolean
        For Each c In criteria
            Dim value = lineData.ElementAt(c.FieldIndex)
            Dim localResult As Boolean
            Select Case c.Comparison
                Case CriteriaComparison.Equals
                    localResult = value.Equals(c.Value)
                Case CriteriaComparison.Contains
                    localResult = value.Contains(c.Value)
                Case CriteriaComparison.StartsWith
                    localResult = value.StartsWith(c.Value)
                Case CriteriaComparison.EndsWith
                    localResult = value.EndsWith(c.Value)
            End Select
            Select Case c.Combinator
                Case CriteriaCombinator.None
                    finalResult = localResult
                Case CriteriaCombinator.And
                    finalResult = (finalResult And localResult)
                    If Not finalResult Then Return False
                Case CriteriaCombinator.Or
                    finalResult = (finalResult Or localResult)
            End Select
        Return finalResult
    End Function

    Now your GUI can build a List(Of FilterCriteria).  The first one in the list would not have the Combinator property set, but then all subsequent items you add can specify how they combine with the previous criteria.

    This is just one way to do it and it could be designed other ways depending on how complex you want to allow the filter creation to be.  Hopefully this gives you a general idea to work from.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Saturday, August 18, 2018 3:12 PM