Unable to handle System.Data.SqlTypes.SqlNullValueException

Question

I have the following code:

public string getFinalCon(string desid) {
    string finalConId = null;
    try
    {
        query = "select finalConID from discussions where desid=@did";
        com = new SqlCommand(query, con);
        com.Parameters.AddWithValue("@did", desid);
        con.Open();
        sdr = com.ExecuteReader();
        while (sdr.Read())
        {
            if (sdr.GetString(0).Equals("none") == false && sdr.GetString(0)!=null)
            {
                finalConId = sdr.GetString(0);
                break;
            }
        }
        con.Close();
    }
    catch (Exception)
    {
    }
    return finalConId;
}

As you can see I am catching the "Exception", the global exception. But the problem is that whenever this line finalConId=sdr.GetString(0) is executed, the system throws a System.Data.SqlTypes.SqlNullValueException.

Yes it will surely throw it whenever there is NULL value in the database in the corresponding field. But what I want is that this exception should be caught by the catch block and the function should return the default value of finalConId that is NULL as declared in starting of the function. But this is not happening instead it shows up my error page. I am calling this function like this:

string conid = getFinalCon(Request["id"].ToString());
                                    
if (conid == null)
{ /*---some code---*/}
else
{/*---some code---*}

How can I handle this exception?

Answer 1

Don't catch exceptions when you don't need to. The proper way to do this is to test sdr.IsDBNull(0) before calling sdr.GetString(0). If IsDBNull() returns true, then GetString() will throw an exception and you should not call it.

It's also very poor practice to swallow all exceptions without indicating some sort of error. catch { } or catch (Exception) { } should be avoided in almost all cases. If something catastrophic happens (the DB connection goes down, for example) you want to allow that exception to propagate. Otherwise, how is the caller to distinguish between the cases "null value in that column" and "database connection died?"