B. Clay Shannon B. Clay Shannon - 10 months ago 51
SQL Question

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

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
Message=Data is Null. This method or property cannot be called on Null values.
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

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?


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:

    string address2 = sqlD8aReader.GetString(ADDRESS2_OFFSET);