SQL Parameter as DBNull.Value is not working-CodePudding

I'm loading a select box list with a distinct list of values from our database table on CompanyName.

If the value in the column is NULL, I am adding '-- NULL --'

If the value in the column is empty or white space, I'm adding '-- EMPTY / WHITE SPACE --'

Otherwise, it will be the value in the column.

When I am trying to update the parameter to the property value DBNull.value is not working.

Here is my SQL statement:

SELECT Column1
      ,Column2
      ,[CreatedBy]
      ,RestOfColumns...
  FROM [dbo].[CompanyTable]
  where CompanyName = @DataVisParam

Here is my code:

        public DataTable GetDataVisQueryParameterizedResults(string query, string parameterizedSelectedValue, string connectionString)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(ConnectionUtilities.GetWebConfigConnectionString(connectionString)))
            {
                conn.Open();

                using (SqlDataAdapter sda = new SqlDataAdapter(query, conn))
                {
                    sda.SelectCommand.Parameters.Clear();
                    sda.SelectCommand.CommandType = CommandType.Text;
                    SqlParameter param = sda.SelectCommand.Parameters.Add(SharedUtilities.DataVisParameterPlaceholder(), SqlDbType.NVarChar);

                    /*if (parameterizedSelectedValue == SharedUtilities.DataVisParamSelectBoxValueIsNull())
                    {
                        param.Value = DBNull.Value;
                    }
                    else*/
                    if (parameterizedSelectedValue == SharedUtilities.DataVisParamValueSelectBoxIsEmptyOrWhiteSpace())
                    {
                        param.Value = string.Empty;
                    }
                    else
                    {
                        param.Value = parameterizedSelectedValue;
                    }

                    sda.Fill(dt);
                }
            }

            return dt;
        }

        /// <summary>
        /// Data Vis parameterized query placeholder.
        /// </summary>
        /// <returns>Data vis parameterized placeholder text that will be used in the data vis query</returns>
        public static string DataVisParameterPlaceholder()
        {
            return "@DataVisParam";
        }

        /// <summary>
        /// Data vis parameter value is null.
        /// </summary>
        /// <returns>Parameter value for null on the select box</returns>
        public static string DataVisParamSelectBoxValueIsNull()
        {
            return "-- NULL --";
        }

        /// <summary>
        /// Data vis parameter value is empty or white space.
        /// </summary>
        /// <returns>Parameter value for empty or white space on the select box</returns>
        public static string DataVisParamValueSelectBoxIsEmptyOrWhiteSpace()
        {
            return "-- EMPTY / WHITE SPACE --";
        }

I've tried DBNull.Value and not even setting it, but always 0 records are return for NULL. If I do the empty or white space, it works and 2 records are returned. Everything else is working fine.

Thoughts?

CodePudding user response：