Safe DataReader in .NET

Posted by Joe Enos on June 06, 2021 · 9 mins read

If you’re using ADO.NET out of the box with a DataReader, null-checking is just a bit obnoxious. Normally in a real project, you’ll have a real framework around your data access, whether that’s Entity Framework, some third party tool, or your own home-grown repositories. But if you’re doing a small project and just using a DataReader, then at least do this much:

using System;
using System.Data;

namespace MyNameSpace {
public class DataReaderEx {
private readonly IDataReader _dataReader;

public DataReaderEx(IDataReader dataReader) {
_dataReader = dataReader;
}
public string GetString(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? null : _dataReader.GetString(i);
}
public int GetInt32(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? 0 : _dataReader.GetInt32(i);
}
public int? GetInt32Nullable(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? (int?)null : _dataReader.GetInt32(i);
}
public long GetInt64(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? 0 : _dataReader.GetInt64(i);
}
public long? GetInt64Nullable(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? (long?)null : _dataReader.GetInt64(i);
}
public bool GetBoolean(string name) {
int i = _dataReader.GetOrdinal(name);
return !_dataReader.IsDBNull(i) && _dataReader.GetBoolean(i);
}
public bool? GetBooleanNullable(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? (bool?)null : _dataReader.GetBoolean(i);
}
public DateTime GetDateTime(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? DateTime.MinValue : _dataReader.GetDateTime(i);
}
public DateTime? GetDateTimeNullable(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? (DateTime?)null : _dataReader.GetDateTime(i);
}
public Guid GetGuid(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? Guid.Empty : _dataReader.GetGuid(i);
}
public Guid? GetGuidNullable(string name) {
int i = _dataReader.GetOrdinal(name);
return _dataReader.IsDBNull(i) ? (Guid?)null : _dataReader.GetGuid(i);
}
}
}

This gives you the ability to retrieve nullable values, and properly map them to null. It also converts nulls to the default value if you’re pulling a nullable column into a non-nullable variable.

// Old way:
int idx = rdr.GetOrdinal("Name");
string name = rdr.IsDBNull(idx) ? (string)null : rdr.GetString(idx);
idx = rdr.GetOrdinal("NumYears");
int numYears = rdr.IsDBNull(idx) ? 0 : rdr.GetInt32(idx);

// Alternative old way, but it just feels dirty:
string name = rdr["Name"] as string;
int numYears = (rdr["NumYears"] as int?) ?? 0;

// New way:
using (var rdr = comm.ExecuteReader()) {
var rdrEx = new DataReaderEx(rdr);
string name = rdrEx.GetString("Name");
int numYears = rdrEx.GetInt32("NumYears");
}