CODEWITHSUNDEEP
c#sql-serverdbnull
bonCodigo
bonCodigo

Reputation: 14361

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 = "";

Upvotes: 0

Views: 283

Answers (4)

ta.speot.is
ta.speot.is

Reputation: 27214

If null then feed that into the case when within SQL, so it will return all the data.

I'm interpreting this to mean that if there's no value specified then return all the rows, otherwise filter by that value.

I'm assuming lstTypeSearch.SelectedItem is your value and it's a string. If not, cast it or dig out the string value.

command.CommandText =
    "SELECT * FROM TT WHERE @SelectedItem IS NULL OR TT.[_TYPE] = @SelectedItem";

var selectedItem = (string) lstTypeSearch.SelectedItem;

command.Parameters.AddWithValue("@SelectedItem",
    String.IsNullOrEmpty(selectedItem) ? (object) DBNull.Value : selectedItem);

using (var dataReader = command.ExecuteReader())
{
    ...
}

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1064114

With the edit, you should be able to replace the last line with:

AND (@Type IS NULL OR TT.[_TYPE] = @Type)

far clearer, although frankly it doesn't make for great query cache plan usage or optimization; it would still, IMO, be better to just compose the correct SQL.

As for passing in the value:

object value = string.IsNullOrEmpty(lstTypeSearch.SelectedItem)
       ? (object)DBNull.Value : (object)lstTypeSearch.SelectedItem;
// ...
cmd.Parameters.AddWithValue("Type", value);

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82524

First, you should work with parameters, unless want to be exposed to potential sql injection threats.

Second, if you are building the sql dynamically in the code, a better approach would be to add the sql condition only if lstTypeSearch.SelectedItem is not an empty string or null. something like that:

sSql = "your sql query";
if(!String.IsNullOrEmpty(lstTypeSearch.SelectedItem)) {
    sSql += "TT.[_TYPE] = '" + lstTypeSearch.SelectedItem + "'";
}
sSql += ";"

btw, what if the SelectedItem has a string that only contains white spaces? consider replacing the String.IsNullOrEmpty to String.IsNullOrWhiteSpace.

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1064114

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();

Upvotes: 3

Related Questions