CODEWITHSUNDEEP
c#
Anonymous
Anonymous

Reputation: 1979

Unexpected behavior between String.IsNullOrEmpty and DBNull.Value

I have the following query:

public static string GetCustomerName(string customerNo)
{
   string query = "query to get customer";
   var myConn= new MYConnection();
   using (SqlConnection con = new SqlConnection(myConn.MYConnectionString))
   {
       con.Open();
       SqlCommand cmd = new SqlCommand(query, con);
       cmd.Parameters.Add("@customerNo", SqlDbType.NVarChar).Value = customerNo;
       object result = cmd.ExecuteScalar();
       return result == DBNull.Value ? String.Empty : (string)result;
   }

}

I'm calling the method above like this:

string customerName = GetCustomerName(CustomerID);

if (customerName.Contains(Constants.Company.CompanyName))
{

    Additional Logic...
}

However, I'm getting a Object Reference Null error if my method doesn't return a customer name. I would think that the GetCustomer method would return an empty string.

If I change the call to get the CustomerName to below, it works perfectly.

string customerName = GetCustomerName(emailAndSTCodeInfo.CustomerID);
if (String.IsNullOrEmpty(customerName))
{
    customerName = "";
}
if (customerName.Contains(Constants.Chase.ACCOUNT_NAME))
{
    Additional Logic
}

So, my question is, what would be the proper way of handling this if my GetCustomer method doesn't find a record and returns null. I'm currently using the working code above but it seems like a hack or something.

Any help would be greatly appreciated.

Upvotes: 0

Views: 1102

Answers (2)

Michael Liu
Michael Liu

Reputation: 55389

ExecuteScalar returns null if no record is returned.

To guarantee that GetCustomerName never returns null, you could change the last line to

return Convert.ToString(result);

Convert.ToString(object) returns an empty string if the argument is either null or DBNull.Value.

Upvotes: 2

to StackOverflow
to StackOverflow

Reputation: 124696

If a query returns no rows, then executing it with ExecuteScalar will return null, not DBNull.Value.

So your GetCustomerName method needs to check for a null return value as well as DBNull.Value.

Upvotes: 2

Related Questions