CODEWITHSUNDEEP
c#.net
Rejeev Divakaran
Rejeev Divakaran

Reputation: 4504

SqlDataReader.GetString and sqlnullvalueexception

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?

Upvotes: 31

Views: 22193

Answers (4)

Tommy Carlier
Tommy Carlier

Reputation: 8149

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

public 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.GetStringOrNull(reader.GetOrdinal(columnName));
    }
}

Which you can call like this:

value = reader.GetStringOrNull(n);

Upvotes: 46

drzymala
drzymala

Reputation: 2049

This worked for me:

value = reader.GetValue(n).ToString();

Upvotes: 4

Thomas Levesque
Thomas Levesque

Reputation: 292455

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.

Upvotes: 15

Andrew Hare
Andrew Hare

Reputation: 351526

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.

Upvotes: 3

Related Questions