Question Dynamically Query By Stored Preferences

[zoomzoom02]

I have a database that I am querying that has 48 columns. These columns mean various things to people that will be using them. Some people only need to see 6, while others need almost all of them.

What I want to do is create a 'preferences' form. Here, they can click a checkbox of each column they want to see. Then when they click the button to query the database they see only these things. I want this to also be stored to where if they close and then reopen the application, their preferences are saved.

As it stands now, I am querying an Access database and using a dataadapter/dataset to populate the datagrid to display my information.

First off, is there an easy way to allow for custom columns to pull into the query? All I can think of is doing a complicated web of if-then or case statements. I am a complete noob when it comes to datagrids.

Secondly, what is the easiest way to store 'preferences'?

 

[jmcilhinney]

This advice assumes that storing the preferences on a per-Windows-user basis is OK. If not then you'd have to write much more custom code.

Create the preferences form with all the CheckBoxes.
Select a CheckBox and open the Properties window.
Expand the (ApplicationSettings) node.
Select the Checked child node and click the drop-down arrow.
Click (New...).
Set an appropriate name, e.g. "IncludeDateOfBirthColumn" for a column named "DateOfBirth".
Click OK.
Repeat for each CheckBox.

You have now created and bound a setting for the Checked property of each CheckBox. These settings will be automatically loaded at startup, edited when you click a CheckBox and saved at shutdown.

In code, you can write a method to get the list of included columns like so:

Private Function GetIncludedColumnList() As IEnumerable(Of String)
    Dim includedColumns As New List(Of String)

    If My.Settings.IncludeDateOfBirthColumn Then
        includedColumns.Add("DateOfBirth")
    End If

    'etc.

    Return includedColumns
End Function

You can then use that method like this:

Dim sql = String.Format("SELECT {0} FROM MyTable", String.Join(", ", Me.GetIncludedColumnList()))

 

[zoomzoom02]

Ah, now that I see it, it makes perfect sense. I will give that a shot, but that looks like exactly what I need. Thank you!