SqlDataReader.GetString and sqlnullvalueexception

Question

I am new to C#. I was executing some select queries from database tables using System.Data.SqlClient classes. I got sqlnullvalueexception while executing some select query. On googling I come to know that if the value is null in the database, SqlDataReader.GetString (or it's variants) will throw sqlnullvalueexception. What is the best coding practice for this?

if (!sqlDataReader.IsDBNull(n)) value = r.GetString(n);

Any better way of coding?

Answer 1

That really is the best way to go about it if you wish to avoid any exceptions. You need to decide whether or not a null field represents an exceptional situation in your code - if it doesn't then use this method. If it does then I would suggest that you either allow the exception to be thrown or catch the exception and wrap it in a more meaniful exception and throw that one.

But the main thing to know is that this is the standard way to retrieve values from a data reader when a null field does not represent an exceptional situation in the application domain.

Answer 2

The code you posted is fine. You could also do something like that :

value = r[n] as string;

If the value in the database is null, r[n] will return DBNull.Value, and the cast to string will return null.

Answer 3

If you don't want to repeat this a lot, just create a helper function, like this:

static class DataReaderExtensions
{
    public static string GetStringOrNull(this IDataReader reader, int ordinal)
    {
        return reader.IsDBNull(ordinal) ? null : reader.GetString(ordinal);
    }

    public static string GetStringOrNull(this IDataReader reader, string columnName)
    {
        return reader.IsDBNull(columnName) ? null : reader.GetString(columnName);
    }
}

Which you can call like this:

value = reader.GetStringOrNull(n);