How to handle nulls with SqlDataReader in C#

In SQL, NULL represents the absence of any value, whereas in C#, null represents the lack of an object reference. When reading data from a database, SqlDataReader returns DBNull to indicate that the value of a column is NULL. This is an important distinction because DBNull is not the same as C#'s null.

If you try to read a column's value without checking for DBNull, and the column contains a NULL value, you will encounter an exception:

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

You can check if a column’s value is DBNull by comparing the value returned by SqlDataReader with DBNull.Value.

using System.Data.SqlClient;
using System.Data;

// Example with DBNull.Value comparison
var birthdate = reader["BirthDate"];
if (birthdate != DBNull.Value)
{
    person.BirthDate = birthdate as DateTime?;
}

Here, we check if the column BirthDate contains a NULL value by comparing it to DBNull.Value. If it’s not DBNull, we can safely cast it to a DateTime? (nullable DateTime).

Another option is to use the IsDBNull() method of SqlDataReader, which checks if a column contains a NULL value.

if (!reader.IsDBNull(reader.GetOrdinal("BirthDate")))
{
    person.BirthDate = reader.GetDateTime(reader.GetOrdinal("BirthDate"));
}

The IsDBNull() method checks whether the specified column (by either its name or ordinal position) contains NULL. This approach is less prone to errors and is more readable in some cases.

If you try to use methods like GetString(), GetDateTime(), or any other Get method on a NULL column, you will encounter an exception. To prevent this, it’s crucial to always check for DBNull before calling the Get methods.

You can create a generic extension method for SqlDataReader that automatically checks if a column is NULL and returns a default value if it is. This method can be used for both column names and ordinals.

using System.Data.SqlClient;
using System.Data;

public static class SqlDataReaderExtensions
{
    public static T Get<T>(this SqlDataReader reader, string columnName)
    {
        if (reader.IsDBNull(reader.GetOrdinal(columnName)))
            return default;
        return reader.GetFieldValue<T>(reader.GetOrdinal(columnName));
    }

    public static T Get<T>(this SqlDataReader reader, int columnOrdinal)
    {
        if (reader.IsDBNull(columnOrdinal))
            return default;
        return reader.GetFieldValue<T>(columnOrdinal);
    }
}

Here, we define two overloads of the Get<T> method. One accepts the column name, and the other accepts the column ordinal. The method checks if the value is NULL using IsDBNull(), and if so, returns the default value for the type (default for reference types is null, and for value types, it’s the type's default value like 0, false, or DateTime.MinValue).

Now you can use the Get<T> method to read values from your SqlDataReader without having to manually check for DBNull.

using System;
using System.Data.SqlClient;

public class Person
{
    public string Name { get; set; }
    public DateTime? BirthDate { get; set; }
}

using (var con = new SqlConnection("YourConnectionString"))
{
    con.Open();

    using var cmd = new SqlCommand("SELECT Name, BirthDate FROM People", con);
    using var reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        var person = new Person()
        {
            Name = reader.Get<string>("Name"),
            BirthDate = reader.Get<DateTime?>("BirthDate") // Nullable DateTime
        };

        Console.WriteLine($"Name={person.Name} Year of Birth={person.BirthDate?.Year}");
    }
}

In this example, Get<T> will automatically handle DBNull values, so you don’t need to check for DBNull manually.

When dealing with nullable columns in your database, it’s best practice to use nullable types in your C# model class. This avoids the need for special "default" values to indicate nullability, such as -1, 0, or an empty string.

For example, if the database column BirthDate can be NULL, you should use a nullable DateTime (DateTime?) in your C# class.

public class Person
{
    public string Name { get; set; }
    public DateTime? BirthDate { get; set; }
    public string FavoriteMovie { get; set; }
    public int? FavoriteNumber { get; set; }
}

Handling NULL values from SQL Server with SqlDataReader requires a good understanding of the distinction between DBNull (SQL) and null (C#). By using methods like IsDBNull() or comparing with DBNull.Value, you can safely handle NULL values and avoid exceptions.