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