Unexpected behavior between String.IsNullOrEmpty and DBNull.Value

Question

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.

When you debug, what is the value of result prior to your return statement? — Dave Zych
– Dave Zych, Commented Apr 30, 2013 at 18:01
Please make more effort when formatting your question. There's no need to have the code starting a third of the way across the window. — Jon Skeet
– Jon Skeet, Commented Apr 30, 2013 at 18:01
Do you ever expect GetCustomerName() to return null? If not and if your logic depends on CustomerName, then I'd say throwing exception and catching it on the upper layer (caller method) would be the best way to handle this. — Arian Motamedi
– Arian Motamedi, Commented Apr 30, 2013 at 18:02
this may sound funny, but I can't actually 'debug' this. It's a windows service that is very large. For some reason, corporate has some odd email setup that blocks this program from sending emails from my development machine. I know, it makes it a nightmare. It's suppose to be fixed soon...Not soon enough though. — Anonymous
– Anonymous, Commented Apr 30, 2013 at 18:03

Michael Liu · Accepted Answer · 2013-04-30 18:01:41Z

2

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.

answered Apr 30, 2013 at 18:01

Michael Liu

55.8k14 gold badges126 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

to StackOverflow · Accepted Answer · 2013-04-30 18:01:25Z

2

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.

answered Apr 30, 2013 at 18:01

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

Collectives™ on Stack Overflow

Unexpected behavior between String.IsNullOrEmpty and DBNull.Value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related