Search code examples
c#sql-serverdbnull

Trickly null validation for SQL Query


"AND TT.[_TYPE] = CASE WHEN " + String.IsNullOrEmpty(lstTypeSearch.SelectedItem) ? 
 DBNull.Value} +  
" IS NULL THEN TT.[_TYPE] ELSE " + lstTypeSearch.SelectedItem + " END ";

Above is a pseudo aspect of my query.

I need to do a validation of listbox item for null and then set as DBNull.Value, which is to be passed into CASE WHEN within SQL Query.

Any better way to achieve this? I am getting tons of String to Bool, Null to String conversion errors...

Further, is there anyway to pass DBNull.Value as a Parameter across data access layer?

EDIT: original query is in a Static class.

    public static readonly string SqlGetItemsBy_Number_Capacity_Type =   
    "SELECT TT.[_NUMBER], " +
    "TT.[CAPACITY], " +
    "TT.[_TYPE], " +                  
    "TS.[SESSIONE] " +
    "FROM [ITEMS] AS TT, //some code
    "WHERE //some code
    "AND TT.[_TYPE] = CASE WHEN @Type IS NULL THEN TT.[_TYPE] ELSE @Type END";

If I run the same query in SQL Server, it works fine.

  SELECT //some code
  AND [_TYPE] = CASE WHEN NULL IS NULL THEN [_TYPE] ELSE @TYPE END

Finally: I decided to go with SQL Append and following to validate/set parameter.

    string paramAppend;
    var bld = lstTypeSearch.SelectedItem;

    if (bld != null)
    {
        paramAppend = "AND TT.[_TYPE] = " + lstTypeSearch.SelectedItem.ToString();
    }
    else
        paramAppend = "";

Solution

  • It looks like lstTypeSearch here represents a column name (hence parameterization: not an option), so the first thing I'd say is: make sure you white-list that. Rather than trying to do everything in one go, separate the two cases:

    if(string.IsNullOrEmpty(lstTypeSearch.SelectedItem))
    {
        // nothing to check?
    }
    else
    {
        CheckValidColumn(lstTypeSearch.SelectedItem); // throws if white-list fails
        sql.Append(" AND TT.[_TYPE] = [") // should probably add table alias
           .Append(lstTypeSearch.SelectedItem)
           .Append("]");
    }    
    

    If I have misunderstood, and this isn't a column, then just parameterize:

    if(string.IsNullOrEmpty(lstTypeSearch.SelectedItem))
    {
        // no restriction?
    }
    else
    {
        sql.Append(" AND TT.[_TYPE] = @type");
        cmd.Parameters.AddWithValue("type", lstTypeSearch.SelectedItem);
    }
    // ...
    cmd.CommandText = sql.ToString();