Multiple conditional parameters in SQL and C#

Question

Consider the following function which has 2 optional variables

public List<T> SelectSqlItems<T>(
    string settingsgroup = null,
    int? state = null)
{
    SqlCommand selectCommand = null;

    if (settingsgroup == null)
    {
        selectCommand = new SqlCommand(
            "select * from ApplicationSettings ", con);
    }
    else
    {
        selectCommand = new SqlCommand(
            string.Format(
                "select * from ApplicationSettings where settingsgroup='{0}' ",
                settingsgroup),
            con);
    }

    if (state != null)
    {
        selectCommand.CommandText += 
            !selectCommand
                .CommandText
                .ToLower()
                .Contains("where")
            ? string.Format("where state={0}", state)
            : string.Format("and state={0}", state);
    }

    //etc..
}

I have 4 possibilities:

settingsgroup==null && state==null
settingsgroup==null && state!=null
settingsgroup!=null && state==null
settingsgroup!=null && state!=null

From every case above a different SQL command has to be produced. What are the built in functionalities in C# that could help me achieve such things without a lot of conditional statements, and if you were to write the above how would you write it other than having to overload the function 4 times?

Answer 1

This is a common problem in SQL that can be effectively handled in the query itself, thus allowing queries to be created in advance, use parameters, and be accessed through stored procedures.

Use of parameters is an important recommendation and should not be considered optional. SQL Parameters will help prevent SQL injection attacks. For example, imagine if someone were to call your method using the following parameter values:

SelectSqlItems<T>("' OR settingsgroup <> '", null);

Your query would now become:

select * from ApplicationSettings where settingsgroup='' OR settingsgroup<>'' 

This would of course return all rows from the table, and potentially expose private information. Even worse possibilities exist, however, such as inserting a DELETE clause which could delete your whole table, or even drop your entire database (though hopefully your user permissions are configured to at least prevent these worst-case scenarios).

To prevent this, your SelectSqlItems method can be restated to the following:

public List<T> SelectSqlItems<T>(
    string settingsgroup = null,
    int? state = null)
{
    var cmdText = "..."; // See Query Below
    var selectCommand = new SqlCommand(cmdText, con);

    // Set the values of the parameters
    selectCommand.Parameters.AddWithValue("@settingsgroup", settingsgroup);
    selectCommand.Parameters.AddWithValue("@state", state);

    // etc...
}

Your query can now be stated as follows:

SELECT
    *
FROM
    ApplicationSettings
WHERE
    ((@settingsgroup IS NULL) OR (settingsgroup=@settingsgroup))
    AND
    ((@state IS NULL) OR (state=@state)) 

If a parameter value is null, the left side of the conditional statement joined by OR will always have the value TRUE, and therefore all rows will be matched. If, however, the parameter value is not NULL, the left side of the conditional will have the value FALSE and the right side will be inspected. The right side will only have the value TRUE if the row's value matches the parameter value, and therefore only the rows matching the parameter value will be returned. This concept can be repeated with as many parameters as required.

Answer 2

Why not switch to an SQL stored procedure with both parameters being optional and pass the parameters passed to SelectSqlItems directly to it ?