System.DBNull.Value != null

Spent the most of easter digging through heaps of sql. So here’s another “in-stinker” which almost had me fooled again.


Inside of a component I’m not afraid to work directly with sqlcommands. Setting up a datadapter and a dataset would be an overkill when the database will return just a scalar value. The result returned by the ExecuteScalar method can be somewhat misleading at first sight. Take this code


public string CustomerName(int custId)
{
   SqlCommand cmd = new SqlCommand(string.Format(“SELECT Name FROM Customers WHERE idCust = {0}”
, custId), sqlConnectionToSchaakBondDB);
   object
result = cmd.ExecuteScalar();


   if (result == null)
      return “Customer not found”
;
   if
(result == System.DBNull.Value)
      return “Customer found but name is null”
;
   return (string) result;



Testing the result only against null could give a quite misleading result. In the second case the method does return a value but the content of this value is null. Which is not the same as not returning a value at all.


Peter


 

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • http://codebetter.com/members/pvanooijen/default.aspx pvanooijen

    Yup. The _result_ of the story can be collpased into one statement.

  • http://www.cubecreations.com BitMite

    One less line.

    object o = cmd.ExecuteScalar();
    return (o != null && o != DBNull.Value) ? o.ToString() : “”;

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    I’m not sure about a clear answer on this. In my experience it depends on these things
    - DB server brand
    - Is the parameter marked as nullable ?
    - Is the column you are selecting on marked as nullable ?

    You need a couple of clear tests to sort this out

  • Greg W

    I’ve got a stored proceedure:
    Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
    If User.Identity.IsAuthenticated Then
    e.Command.Parameters(“@ownerid”).Value = Membership.GetUser().ProviderUserKey
    Else
    e.Command.Parameters(“@ownerid”).Value = DBNull.Value
    End If
    End Sub

    Stored Procedure finds Albums with OwnerID.
    When a person who is not logged in looks at the page they see all the albums except the private ones as spec’ed.

    /* get the values for the date and row */
    SELECT @rows = (@pageNum-1) * @pageSize
    SET ROWCOUNT @rows
    SELECT @keyid=albumid FROM albums
    WHERE(Albums.private = 0) OR (Albums.private = 1) AND (Albums.ownerid = @ownerid)
    ORDER BY albumid ASC
    END

    My questions is: No ablums should be displayed?

  • http://codebetter.com/blogs/peter.van.ooijen/ pvanooijen

    hi Leon,

    An empty string is not the same as null. Which is not the same as dbnull. What you want do can be done as follows:

    object o = cmd.ExecuteScalar();
    if (o != null && o != DBNull.Value)
    result = Convert.ToString(o);

    Takes an extra step

  • Leon Bemmelmans

    string result = cmd.ExecuteScalar() as string;

    I would really like the result to be an empty string “” if the resulttype is DBNULL Will the above solution do the trick ?

    Leon

  • Peter van Ooijen

    Naveen: The column in the db should have the "allow nulls" flag set to true. I think that’s not the case with your coulmn.you

  • Naveen Katara

    While u try to insert a DBNull.Value in the varchar field of database it inserts the ” which is not NULL

    for EX:-

    iif(txt.Text="", DBNull.Value,txt.Text)

    Result = ”

    That shows System.DBNull.Value != NULL

  • Peter van Ooijen

    Mmm, a gentle typecast. Nice.

    But the reason why the name was not found : "customer does not exist" versus "customer does exist but does not have a name", would get lost. Could be important to know.

  • Scott_NO_@_SPAM_Tripleasp.net (Scott Watermasysk)

    You should also be able to do:

    string result = cmd.ExecuteScalar() as string;

    if(result == null)

    {

    return "NOT FOUND";

    }

    return result;

  • Peter van Ooijen

    Blush.. You’re absolutely right, I updated the sql. It is indeed just some example code, adapted from something I’m working on.

    Throwing an exception would indeed make a lot more sense in a real life situation, these results are not something you would want on a mailing label or the like.

  • Michael Russell

    By the way, you also forgot the closing parenthesis on your WHERE clause.

    By the way, if this was just example code, please ignore this next portion. My question here is why you’re returning strings instead of throwing exceptions. You say that you don’t mind working with SQL inside of components, then you’re obviously assuming that you are going to be getting passed valid information (in this case, a valid custId). If the record doesn’t exist, then either the input was incorrect or you have a data integrity issue. Either way, this is an unexpected event.