C# – How to handle nulls with SqlDataReader

SqlDataReader returns a DBNull object when a column is null. This isn’t the same as a C# null. You can check if the column is null by comparing it with DBNull.Value or by using SqlDataReader.IsDBNull().

Here’s an example showing these two ways of checking if a column is null:

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

//Option 1 - Compare with DBNull.Value
var birthdate = reader["BirthDate"];
if (birthdate != DBNull.Value)
	person.BirthDate = birthdate as DateTime?;

//Option 2 - Check IsDBNull()
if (!reader.IsDBNull("BirthDate"))
	person.BirthDate = reader.GetDateTime("BirthDate");
Code language: C# (cs)

After checking if the column is null, you can do whatever is appropriate in your scenario. For example, if you’re mapping a query to a model, you can set the property to a default value or null. I’d suggest using nullable properties when you have nullable columns, as I’ll discuss later.

When you try to use one of the Get() methods (such as GetString()) on a null column, it throws an exception:

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

To prevent running into this exception, you’ll have to check columns for DBNull as shown above. The problem is this can lead having lots of repetitive code. Either use Dapper to map query results instead, or you can encapsulate this code in a generic extension method, which I’ll show next.

Generic extension method that reads a column and checks if it’s null

The following generic extension method(s) encapsulates checking if a column is null with SqlDataReader.IsDBNull() and uses the generic SqlDataReader.GetFieldValue() to read the value. It returns the object casted to the proper type, or it returns the type’s default value (which is null for nullable types):

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

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

    public static T Get<T>(this SqlDataReader reader, int columnOrdinal)
    {
        if (reader.IsDBNull(columnOrdinal))
            return default;
        return reader.GetFieldValue<T>(columnOrdinal);
    }
}
Code language: C# (cs)

Note: I included two overloads of this generic extension method. One for column names and one for column ordinals. You can keep one or both. In older versions of .NET, you’d have to use the ordinal when using IsDBNull() (which means you’d get a compiler error in the first method shown – just delete that method if you’re using an older version).

Here’s a full example of using this extension method when reading and mapping query results with SqlDataReader:

using System;
using System.Data.SqlClient;

using (var con = new SqlConnection("db connection string"))
{
    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")
        };

        Console.WriteLine($"Name={person.Name} Year of birth={person.BirthDate?.Year}");
    }
}
Code language: C# (cs)

This outputs the following:

Name=Bob BirthYear=
Name=Alice BirthYear=2022Code language: plaintext (plaintext)

Note: Bob’s BirthYear shows a blank because it’s null.

Using nullable value types

When your table has nullable columns, it’s a good idea to use nullable properties in the model class. This simplifies things, because null means “this property was not set to anything” which is exactly correct. If you use a non-nullable property and the column is null, you have to set it to a special value and know what that special value means, which can be a pain. For example, you might use -100, -1, or 0 for integers to mean “this was not set to a value.”

Of course, there can be cases where you can have meaningful default values that make more sense than using null.

Here’s an example of using nullable properties that represent nullable columns defined in the table. First, here’s a table with a mix of nullable and non-nullable columns:

CREATE TABLE [dbo].[People](
	[Name] [nvarchar](50) NOT NULL,
	[BirthDate] [datetime] NULL,
	[FavoriteMovie] [nvarchar](50) NULL,
	[FavoriteNumber] [int] NULL
) ON [PRIMARY]
Code language: SQL (Structured Query Language) (sql)

Here is a model for this table, using nullable properties for the nullable columns:

public class Person
{
	public string Name { get; set; }
	public DateTime? BirthDate { get; set; }
	public string FavoriteMovie { get; set; }
	public int? FavoriteNumber { get; set; }
}
Code language: C# (cs)

BirthDate and FavoriteNumber are nullable value types (nullable DateTime and nullable int respectively). FavoriteMovie is a string, which is nullable (it’s a reference type).