Difference between NULL and DBNull
I have always misunderstood the difference between NULL and DBNULL until recently while I was working on ASP.NET web application, and was talking to a Webservice and I found an exception coming from one of the web methods inside the Webservice which is using the SqlCommand.ExecuteScalar() method.
What happens when the ExecuteScalar() method is used? Usually, the ExecuteScalar() method returns the First Column of the First Row returned by the database stored procedure or SQL query.
Suppose for example you are searching for the CustomerPhone of the customer named "Bilal Haidar", and that customer didn't have any record in the database, so a query such as:
public string Get_Customer_ID(string UserName)
{
// Prepare database connection
SqlConnection conn = new SqlConnection("..."); // Prepare command to execute
SqlCommand cmd = new SqlCommand("SELECT CustomerPhone FROM Custoers WHERE CustomerName = 'Bilal Haidar'", conn);
// Execute the command and store result in an object
object objResult = cmd.ExecuteScalar();
if (objResult == null)
return "Customer not found in our database";
if (objResult == System.DBNull.Value)
return "Customer found but his/her phone number is null";
return (string) objResult;
}
So now here how it goes. The query will search for the Customer Phone. We are returning the result and storing it in the objResult object.
If the result object is null, then there is no record in the database with the username specified.
If the result value is not null, we need to check if the Cutomer Phone field has a value or not, how do you do so? Using the DBNull.Value would check for you if the field returned is null or not.
So, comparing the ExecuteScalar() to null means, we are checking if there is a record returned, and checking if value of the first column of the first row is null or not, means we need to check against DBNul..Value
Hope you have now a better understanding of the difference between NULL and DBNull
Regards