samedi 23 janvier 2016

How to dynamically switch between "WHERE column-name = value" and "WHERE column-name IS NOT NULL"

I am creating a small Visual Studio Windows Form application in C#, and I am using SQLite as my database. Suppose the db stores data for school club members, and they have a value for the state they live in. I am trying to create a parameterized query string so that if the user selects "All States" in my program, then it will load all members. But if they select a specific state, then it will only show members living in that state. How can I dynamically switch between, for example, WHERE USstate = 'FL' and WHERE USstate IS NOT NULL when using a parameterized query string? I want the value of the state to be dynamically chosen, but if "All States" (a check box) is selected, then all records in the db should show up.

Code example of what is not working for me:

string queryString = @"Select * from Membership WHERE USstate = @state;";
List<SQLiteParameter> paramList = new List<SQLiteParameter>();
paramList = addStateParam(paramList);

private List<SQLiteParameter> addStateParam(List<SQLiteParameter> paramList)
    {
        if (!checkBoxFilterAllStates.Checked)
        {
            string selectedState = comboBoxFilterState.Items[comboBoxFilterState.SelectedIndex].ToString();
            var usState = new SQLiteParameter("@state", selectedState);
            paramList.Add(usState);
        }
        else
        {
            var usState = new SQLiteParameter("@state", "IS NOT NULL");
            paramList.Add(usState);
        }
        return paramList;
    }

I tried changing USstate = @state to just USstate @state and then having @state replaced with either "= " + selectedState or IS NOT NULL, but if there is no equal sign before the @state, an syntax or logic error appears every time. However, if I do keep the equals sign, then the IS NOT NUll code will not work.

How do I dynamically fix this issue? Thanks in advance! And sorry if I was too wordy or redundant.

Aucun commentaire:

Enregistrer un commentaire