C# – Mapping nullable columns with SqlDataReader

When you have a database table with null columns, and you’re using SqlDataReader to map the results, there are a few things to do:

  • Use nullable types in the model class. Example: instead of int use int? so the value can be null.
  • Use SqlDataReader.IsDBNull() to check if you need to set the value to null.

In this article, I’ll show an example of how to map nullable columns. I’ll use an extension method that encapsulates the DBNull check, so that the mapping code is nice and neat, like this:

BirthDate = reader.Get<DateTime?>("BirthDate")
Code language: C# (cs)

Create a model with nullable types

I have a database table with the following definition:

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)

Notice it has three nullable fields.

I have the following model class that represents this table:

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)

When your table has nullable columns, it’s a good idea to use nullable types in the model class.

Let’s say you used int instead of int?. If the value in the table were null, you’d have to pick a special number to represent “has no value.” It’s way better to use int? in this case, because then you don’t need a special number – null means “has no value.”

Add a SqlDataReader extension method that wraps the DBNull check

You must call SqlDataReader.IsDBNull() if you have null columns. Instead of cluttering your code with lots of calls to IsDBNull(), you can use this extension method:

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); } }
Code language: C# (cs)

This prevents a few errors and keeps your code nice and clean.

When you don’t call IsDBNull(), and the value in the column is null, you’ll get the following runtime exception:

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

Let’s say you try to fix this by setting the field to null if IsDBNull() returns true:

FavoriteNumber = reader.IsDBNull("FavoriteNumber") ? null : reader.GetInt32("FavoriteNumber")
Code language: C# (cs)

If you’re not on C# 9, you’ll get a compile-time error, such as the following (I’m on C# 8):

CS8400 Feature ‘target-typed conditional expression’ is not available in C# 8.0. Please use language version 9.0 or greater.

You can either upgrade to .NET 5, or put an explicit cast on the null, like this:

FavoriteNumber = reader.IsDBNull("FavoriteNumber") ? (int?)null : reader.GetInt32("FavoriteNumber")
Code language: C# (cs)

If you use the extension method instead, then you can avoid these problems and declutter your code. The call above looks like this when you use the extension method:

FavoriteNumber = reader.Get<int?>("FavoriteNumber")
Code language: C# (cs)

Execute the query and map all the fields

The following code queries the table, uses the extension method to map all of the columns, and outputs JSON:

var conString = @"Server=<sql instance>;Database=People;Integrated Security=true"; var people = new List<Person>(); using (var con = new SqlConnection(conString)) { con.Open(); using (var cmd = new SqlCommand(@"SELECT * FROM People", con)) { using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { people.Add(new Person() { Name = reader.Get<string>("Name"), BirthDate = reader.Get<DateTime?>("BirthDate"), FavoriteMovie = reader.Get<string>("FavoriteMovie"), FavoriteNumber = reader.Get<int?>("FavoriteNumber") }); } } } } var json = JsonSerializer.Serialize(people, new JsonSerializerOptions() { WriteIndented = true }); Console.WriteLine(json);
Code language: C# (cs)

This outputs the following:

{ "Name": "John Doe", "BirthDate": null, "FavoriteMovie": null, "FavoriteNumber": null }
Code language: JSON / JSON with Comments (json)

2 thoughts on “C# – Mapping nullable columns with SqlDataReader”

  1. For me reader.IsDBNull() did not accept a string for the column name, it wanted an int, so I used reader.GetOrdinal(string name) to get this. I am using .NET core 3.1, I don’t know if that is why it is different.

    I’ve always wanted to reduce the number is IsDbNull checks in my code so thank you for this.

    • I’m glad this helped you out.

      Regarding IsDBNull(string), this is an extension method in System.Data.Common.dll. To use this extension method, add using System.Data;. Note: This is part of the framework (including .NET Core 3.1).

      Your solution to use reader.GetOrdinal(string) is good. Internally, that’s what the IsDBNull(string) extension method is doing.

      By the way, thanks for pointing this out. I forgot to put using System.Data; in the SqlDataReaderExtensions code example.


Leave a Comment