-1

I've got this code, which works as long as there is a matching value in the database:

public String GetDeptNameForDeptId(int deptId)
{
    String deptName;
    const string qry = "SELECT Name FROM Department WHERE Id = @deptID";

    try
    {
        using (SQLiteConnection con = new SQLiteConnection(HHSUtils.GetDBConnection()))
        {
            con.Open();
            SQLiteCommand cmd = new SQLiteCommand(qry, con);
            cmd.Parameters.Add(new SQLiteParameter("deptID", deptId));
            deptName = cmd.ExecuteScalar().ToString();
        }
    }
    catch (Exception ex)
    {
        String msgInnerExAndStackTrace = String.Format(
            "{0}; Inner Ex: {1}; Stack Trace: {2}", ex.Message, ex.InnerException, ex.StackTrace);
        ExceptionLoggingService.Instance.WriteLog(String.Format("From Platypus.GetDeptNameForDeptId: {0}", msgInnerExAndStackTrace));
        return "No matching value found"; //String.Empty;
    }
    return deptName;
}

However, if there is no match (the Id val passed in as deptId does not exist in the table), an NRE occurs. Is there an alternative to ExecuteScalar() that will fail gracefully? Such as, simply return an empty string?

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862
  • 1
    This question seems like it's a bit to easy for someone with your amount of reputation. :-s What's wrong with checking the result of ExecuteScalar for null? – Patrick Feb 10 '15 at 17:45
  • This *can* be a dupe of http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it – Patrick Feb 10 '15 at 17:48
  • In the same way that Huck Finn can be a dupe of Don Quixote. IOW, "yeah, but..." – B. Clay Shannon-B. Crow Raven Feb 10 '15 at 17:53
  • @GrantWinney: Of course, that why I put emphasize on "can". The thing is, if the OP knows that ExecuteScalar can produce a null result, why use `ToString` on the result *immediately*, instead of storing it in a local variable first? To me it's very strange to ask "I know this causes a NullReferenceException, but I won't read the API or solve it using something simple like checking for null, give me something simple", especially when the answer is so simple. – Patrick Feb 10 '15 at 23:37
  • @Patrick: You seem to be assuming that I knew "Convert.ToString()" is significantly different than ".ToString()" I didn't; I thought they were a "six of one, half a dozen of the other" type of thing (two ways to skin a cat that were synonymous). – B. Clay Shannon-B. Crow Raven Feb 10 '15 at 23:40
  • Well, `Convert.ToString(arg)` is the class `Convert` that takes an argument to its method `ToString`, and `.ToString()` is dereferencing the variable to call the method. Calling a method with an argument is very different from derefencing a variable.. This is basically covered in a beginners class (or should be) of object oriented programming, and is also explained in the other question I linked to. – Patrick Feb 10 '15 at 23:45
  • @Patrick: In both cases, the value being acted on is being converted to a string representation, right? On the surface, it seems reasonable to assume they do the same thing. What is different, it seems, is that one way is safe from NREs and the other isn't. Which raises the question in my mind: what is .ToString()'s raison d'etre? Why does it exist if Convert.ToString() is always better (which, to me, it seems it is). It's a case of an embarrassment of riches leading to wringing of hands rather than clapping of hands. Less is (often) more. – B. Clay Shannon-B. Crow Raven Feb 10 '15 at 23:51

2 Answers2

4

Instead of calling ToString use Convert.ToString method like:

 deptName = Convert.ToString(cmd.ExecuteScalar());

this will return empty string in case of null or DBNull.Value being returned.

Habib
  • 219,104
  • 29
  • 407
  • 436
2

You can just check the result:

var result = cmd.ExecuteScalar();
deptName = result == null ? "" : result.ToString();
Selman Genç
  • 100,147
  • 13
  • 119
  • 184