[RESOLVED] Help with best practice for loading data

[Chrissy]

Hello!

I am new to VB.NET... trying to convert all my VB6 programs and am trying to update them in the process.

I have a program that is mostly used as an interface to our main accounting system. It pulls data to view and create reports. I have a couple combo boxes that need to populate from the database for selection in the program. My thought is to somehow pull the information in at the start of the program and keep it in a table somewhere so I can just load the comboboxes from the stored data. It doesn't need to be real time, as the lists will not change much.

Currently, I am accessing the DB directly when the form loads to populate the combo box. The database is "in the cloud"... just trying to make it run faster.... is a little slow when accessing the database.

Attached is an image of what I tried to create with the data that I need for two of the comboboxes... I am not certain how to use them or if this is even the correct route.

If you could steer me in the right direction, it would be greatly appreciated.



Thank you in advance!
Chrissy

[dday9]

Since they are essentially static lookup tables, what I would do is create a strongly typed object to represent the table and then store a collection of those objects in the My namespace during your application start.

For example, the classes would look like this (you'll likely need to update the data types):

Public Class Owner
    Public Property ucode As String
    Public Property ulastname As String
End Class

Public Class PtrCode
    Public Property HMY As String
    Public Property SCODE As String
    Public Property SDESC As String
    Public Property RPT_COLUMN As String
    Public Property IMPACT As String
End Class

Then in your ApplicationEvents.vb file, you could read the data and store them in a collection:

Namespace My
    Partial Friend Class MyApplication
        Public Property Owners As List(Of Owner)
        Public Property PtrCodes As List(Of PtrCode)

        Private Sub MyApplication_Startup(sender As Object, e As StartupEventArgs) Handles Me.Startup
            Owners = GetOwners()
            PtrCodes = GetPtrCodes()
        End Sub

        Private Function GetOwners() As List(Of Owner)
            Dim owners = New List(Of Owner)

            Const commandString = "SELECT OWNER.* FROM OWNER;"
            Try
                ' create a new connection
                Using con = New OleDbConnection("My Connection String Here")

                    ' create a new command using the connection and command string
                    Using cmd = New OleDbCommand(commandString, con)
                        con.Open()

                        Dim reader = cmd.ExecuteReader()
                        While reader.Read()
                            owners.Add(New Owner() With {
                                          .ucode = reader("ucode"),
                                          .ulastname = reader("ulastname")})
                        End While
                        reader.Close()

                        con.Close()
                    End Using
                End Using
            Catch ex As Exception
                ' uh oh
                Console.WriteLine(ex.Message)
            End Try

            Return owners
        End Function

        Private Function GetPtrCodes() As List(Of PtrCode)
            Dim ptrCodes = New List(Of PtrCode)

            Const commandString = "SELECT PTRCODE.* FROM PTRCODE;"
            Try
                ' create a new connection
                Using con = New OleDbConnection("My Connection String Here")

                    ' create a new command using the connection and command string
                    Using cmd = New OleDbCommand(commandString, con)
                        con.Open()

                        Dim reader = cmd.ExecuteReader()
                        While reader.Read()
                            ptrCodes.Add(New PtrCode() With {
                                            .HMY = reader("HMY"),
                                            .SCODE = reader("SCODE"),
                                            .SDESC = reader("SDESC"),
                                            .RPT_COLUMN = reader("RPT_COLUMN"),
                                            .IMPACT = reader("IMPACT")})
                        End While
                        reader.Close()

                        con.Close()
                    End Using
                End Using
            Catch ex As Exception
                ' uh oh
                Console.WriteLine(ex.Message)
            End Try

            Return ptrCodes
        End Function

    End Class
End Namespace

Now you can bind your ComboBox controls like this:

With ComboBox1
    .DisplayMember = "ulastname",
    .ValueMember = "ucode",
    .DataSource = My.Application.Owners
End With

Quick side note - If you can fix your naming convention in the database, I would highly encourage you to do so now.

[Chrissy]

That makes sense...Thank you so much!

FYI-My company is not the owner of the database... we only have read access to it, so I cannot change the field names.

Quick side note - If you can fix your naming convention in the database, I would highly encourage you to do so now.

[dday9]

FYI-My company is not the owner of the database... we only have read access to it, so I cannot change the field names.

I feel your pain on this. I am currently on a project with similarly bad naming that we only have read access to.

[Shaggy Hiker]

If it is a bit slow, you might consider WHEN you load the data, as well. You said that you do it on form load, which may be the earliest opportunity to load the data. If that's the case, then so be it. If that's the first form, and you get to loading the data at the first possible opportunity, then there's not much to be done there. You might make it appear a bit nicer by putting up something like a splash screen while you wait, but that's only a cosmetic improvement. You'll still take the same amount of time, you'd just be giving the user something to look at while they wait.

If the form that does the loading isn't the very first thing that you do in the application, then there could be some opportunities for loading the data earlier, or loading in a background process.

If the data is really static, there is also the possibility of caching the data locally and using that while the real data loads in the background. This would get far more complicated pretty quickly, but it's something to consider.

[Chrissy]

If it is a bit slow, you might consider WHEN you load the data, as well. You said that you do it on form load, which may be the earliest opportunity to load the data. If that's the case, then so be it. If that's the first form, and you get to loading the data at the first possible opportunity, then there's not much to be done there. You might make it appear a bit nicer by putting up something like a splash screen while you wait, but that's only a cosmetic improvement. You'll still take the same amount of time, you'd just be giving the user something to look at while they wait.

If the form that does the loading isn't the very first thing that you do in the application, then there could be some opportunities for loading the data earlier, or loading in a background process.

If the data is really static, there is also the possibility of caching the data locally and using that while the real data loads in the background. This would get far more complicated pretty quickly, but it's something to consider.

I have a splash screen and plan to load the data into the lists when the program starts. This way the data will be ready when needed.

Thanks for your input!

[techgnome]

I feel your pain on this. I am currently on a project with similarly bad naming that we only have read access to.

I was recently tangential to a project that not only used Hungarian notation for field names but also, in a penny pinching move, took out all vowels... so the Check For Service Required Flag became bchkfrsvcrqrd ... oh... yeah... and there is no Little Orphan Annie Secret Decoder Ring to go with it either. Either you know to drink your Ovaltine, or you found someone who did.


-tg

[Chrissy]

I was recently tangential to a project that not only used Hungarian notation for field names but also, in a penny pinching move, took out all vowels... so the Check For Service Required Flag became bchkfrsvcrqrd ... oh... yeah... and there is no Little Orphan Annie Secret Decoder Ring to go with it either. Either you know to drink your Ovaltine, or you found someone who did.


-tg

[Chrissy]

I added the code as stated above and it works great for loading my combo boxes. However, I am now having an issue with my old code for looping through the combo box. For example, I have a Statement form where the user can select an owner from the combobox or leave blank to run for all owners.

How do I loop through all the owners in the combobox using this code? I'm sure it is something simple, but for some reason my brain is having trouble making the transition from VB6 to VB.NET!!

Public Sub LoadComboBoxWithPartners(ByRef CmbX As ComboBox)
 
        CmbX.Font = myFont
 
        With CmbX
            .Font = myFont
            .DisplayMember = "fullname"
            .ValueMember = "ucode"
            .DataSource = My.Application.Owners
        End With
        CmbX.SelectedIndex = -1
 
    End Sub

Any guidance is appreciated.
Thank you!
Chrissy

[Shaggy Hiker]

You probably shouldn't be looking through the combobox at all. I'm feeling that I'm misunderstanding something about the design. It sounds like you want to do something for either ALL owners, or for just the selected owner. There are two ways this could be interpreted, so I see two possible designs that don't have all that much in common.

The one is where you are doing something anyways, and you may or may not want to restrict by owner. Kind of like whether or not you add a WHERE clause onto a SELECT query. In that case, you'd use the selected item from the combobox if you wanted to deal with just that one owner, and do nothing otherwise.

The other design would be where you want to do something for each owner in some set of owners, but there always has to be an owner for the action to make sense. In that case, if no owner was selected in the combobox, you'd iterate through all the owners in My.Application.Owners, and if there was an owner selected, then you'd just do the action using the selecteditem from the combobox.

I'm not sure that I described the difference between those two with any clarity.

[Chrissy]

The other design would be where you want to do something for each owner in some set of owners, but there always has to be an owner for the action to make sense. In that case, if no owner was selected in the combobox, you'd iterate through all the owners in My.Application.Owners, and if there was an owner selected, then you'd just do the action using the selecteditem from the combobox.

Yes... this is what I want.

This is what I am going for... basically I am creating a statement for the owner. The same code would be applied either to only one owner. Or, if left blank, it loops through the owners in My.Application.Owners and does the same code for each Owner. Basically creating individual reports for all owners versus just one report for one owner.

Ugh... not sure I explained that well either, but I think you know what i am going for based on the explanation above!

Chrissy

[dday9]

Honestly, that sounds like bad UI design.

If I were you, I would have a CheckBox above the ComboBox. The CheckBox's Text would be "All Owners". The Enabled property of the ComboBox would be based on the Checked property of the ComboBox.

Then when the user would go to generate the report(s), it would look something like:

Dim ucodes = New List(Of String)
If (CheckBoxAllOwners.Checked) Then
    ucodes.AddRange(My.Application.Owners.Select(Function(owner) owner.ucode))
Else
    ucodes.Add(CmbX.SelectedValue)
End If

For Each ucode In ucodes
    ' do something
Next

[Chrissy]

Honestly, that sounds like bad UI design.

If I were you, I would have a CheckBox above the ComboBox. The CheckBox's Text would be "All Owners". The Enabled property of the ComboBox would be based on the Checked property of the ComboBox.

Then when the user would go to generate the report(s), it would look something like:

Dim ucodes = New List(Of String)
If (CheckBoxAllOwners.Checked) Then
    ucodes.AddRange(My.Application.Owners.Select(Function(owner) owner.ucode))
Else
    ucodes.Add(CmbX.SelectedValue)
End If

For Each ucode In ucodes
    ' do something
Next

Thank you for the reply, this helps a lot! The design is mimicked after our main accounting system. There are many fields for the user to filter the data on. Checking a box rather than leaving blank seems like an unnecessary step that I would have to add for all filter items. Basically they select an option if they want to filter the results otherwise leave blank to include all.

Just curious why you think it is a bad design?

[dday9]

There is room ambiguity/confusion.

With the current design, the user has to know ahead of time that an item selected in the ComboBox (the unselected item) behaves differently than others.

[Chrissy]

There is room ambiguity/confusion.

With the current design, the user has to know ahead of time that an item selected in the ComboBox (the unselected item) behaves differently than others.

There is no ambiguity... the instructions literally say... leave blank to run for all partners. This is how all of our reports are. There could be 10 filters on a form... mandatory fields are designated with a blue background. Pretty straightforward from a reporting standpoint... if they include data in the filter, it will filter on that data, otherwise it will pull all.

This is the best way to have one report with multiple options on how the data is to be presented.

[dday9]

There is a level of subjectivity when it comes to UI. Personally, I do not find your design to be intuitive but if it works for you then that is fine.

You can still leverage the "meat" of my code example only getting rid of the CheckBox:

Dim ucodes = New List(Of String)
If (CmbX.SelectedIndex = 0) Then ' assumes top-most is "empty" option
    ucodes.AddRange(My.Application.Owners.Select(Function(owner) owner.ucode))
Else
    ucodes.Add(CmbX.SelectedValue)
End If

For Each ucode In ucodes
    ' do something
Next

[Chrissy]

Yes, I was able to tweak your code and it worked great.

I regards to the UI... I like to keep an open mind and try to see where others are coming from in design. Just trying to understand... you would put a checkbox next to each one of these filter items that says "Run for All" or something to that affect and require the user to check each box every time?

Attachment 179980

[dday9]

No, not in that situation.

I was under the assumption that there was just a single filter (the Owner dropdown). In a situation like in your screenshot, that looks (and presumably behaves) just fine.