How can I safely read string vals from SQL Server that may be null?

Question

When the Address2 column is empty, this line:

string address2 = sqlD8aReader.GetString(ADDRESS2_OFFSET);

...fails with:

  System.Data.SqlTypes.SqlNullValueException was unhandled by user code
  HResult=-2146232015
  Message=Data is Null. This method or property cannot be called on Null values.
  Source=System.Data
  StackTrace:
       at System.Data.SqlClient.SqlBuffer.get_String()
       at System.Data.SqlClient.SqlDataReader.GetString(Int32 i)
       at HandheldServer.Models.SQLServerPOCRepository..ctor() in c:\HandheldServer\HandheldServer\Models\SQLServerPOCRepository.cs:line 58
  InnerException:

How can I safely read strings that might be null? Should it be dealt with in SQL somehow (if so, how?) or should it be dealt with in the reading/C# code?

Answer 1

I don't know how your data is being populated, but often times when we execute a query where I work, if it's imperative to have empty strings instead of null for certain operations, we'll use ISNULL(column, '') on columns that might possibly return null so our application layer won't need to worry about whether or not the value is null, it will just handle it like a string.

Otherwise, if you want to handle it application side you can use this:

if(!sqlD8aReader.IsDBNull(ADDRESS2_OFFSET))
{
    string address2 = sqlD8aReader.GetString(ADDRESS2_OFFSET);
}