SQL Data Reader - Handling Null Column Values

SQL Data Reader - handling Null column values

You need to check for IsDBNull:

if(!SqlReader.IsDBNull(indexFirstName))
{
  employee.FirstName = sqlreader.GetString(indexFirstName);
}

That's your only reliable way to detect and handle this situation.

I wrapped those things into extension methods and tend to return a default value if the column is indeed null:

public static string SafeGetString(this SqlDataReader reader, int colIndex)
{
   if(!reader.IsDBNull(colIndex))
       return reader.GetString(colIndex);
   return string.Empty;
}

Now you can call it like this:

employee.FirstName = SqlReader.SafeGetString(indexFirstName);

and you'll never have to worry about an exception or a null value again.

Handle NULL in Datareader

You can use SqlDataReader.IsDBNull to check for null values out of a data reader. C# null and DBNull are different.

 c.ActualWeight = 
     dr.IsDBNull(0) 
     ? default(float) 
     : float.Parse(dr[0].ToString().Trim());

data is null when using SqlDataReader

You need to check the nullable fields using the SqlDataReader.IsDBNull method:

int statusIndex = Reader.GetOrdinal("status");
string sstatus = Reader.IsDBNull(statusIndex) ? null : Reader.GetString(statusIndex);

SQLDataReader how to check for null column values?

You can use IsDBNull

for(int i=0; i<myarray.Length;i++){
    if(reader.IsDBNull(i)){
        myarray[i] = "";
    }
    else
    {
        myarray[i] = reader[i];

    }
}

SqlDataReader Best way to check for null values -sqlDataReader.IsDBNull vs DBNull.Value

I would not get too caught up in the which method is better, because both work and I have used both in code before.

For instance, here is a utility function I dug up from one of my old projects:

/// <summary>
/// Helper class for SqlDataReader, which allows for the calling code to retrieve a value in a generic fashion.
/// </summary>
public static class SqlReaderHelper
{
    private static bool IsNullableType(Type theValueType)
    {
        return (theValueType.IsGenericType && theValueType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
    }

    /// <summary>
    /// Returns the value, of type T, from the SqlDataReader, accounting for both generic and non-generic types.
    /// </summary>
    /// <typeparam name="T">T, type applied</typeparam>
    /// <param name="theReader">The SqlDataReader object that queried the database</param>
    /// <param name="theColumnName">The column of data to retrieve a value from</param>
    /// <returns>T, type applied; default value of type if database value is null</returns>
    public static T GetValue<T>(this SqlDataReader theReader, string theColumnName)
    {
        // Read the value out of the reader by string (column name); returns object
        object theValue = theReader[theColumnName];

        // Cast to the generic type applied to this method (i.e. int?)
        Type theValueType = typeof(T);

        // Check for null value from the database
        if (DBNull.Value != theValue)
        {
            // We have a null, do we have a nullable type for T?
            if (!IsNullableType(theValueType))
            {
                // No, this is not a nullable type so just change the value's type from object to T
                return (T)Convert.ChangeType(theValue, theValueType);
            }
            else
            {
                // Yes, this is a nullable type so change the value's type from object to the underlying type of T
                NullableConverter theNullableConverter = new NullableConverter(theValueType);

                return (T)Convert.ChangeType(theValue, theNullableConverter.UnderlyingType);
            }
        }

        // The value was null in the database, so return the default value for T; this will vary based on what T is (i.e. int has a default of 0)
        return default(T);
    }
}

Usage:

yourSqlReaderObject.GetValue<int?>("SOME_ID_COLUMN");
yourSqlReaderObject.GetValue<string>("SOME_VALUE_COLUMN");

how to check if a datareader is null or empty

if (myReader["Additional"] != DBNull.Value)
{
    ltlAdditional.Text = "contains data";
}
else
{
     ltlAdditional.Text = "is null";
}

How to deal with SqlDataReader null values in VB.net

You have opened the reader, but have not asked it to actually read anything.

After this line:

Dim reader As SqlDataReader = command.ExecuteReader()

add

If reader.Read() Then

and wrap the result reading into this if statement, i.e.

If reader.Read() Then
    Dim index As Integer = reader.GetOrdinal("RESULT1")
    If reader.IsDBNull(index) Then
        RESULT1 = String.Empty
    Else
        RESULT1 = reader(index)
    End If
End If

Note that this works because your SQL should only return a single record. In the event that you were reading multiple records, you would need to call the Read statement in a loop until there were no more records, i.e.

Do While reader.Read()

Loop

---

Related Topics