Converting SqlDataReader’s value to C# types

less than 1 minute read

I spent another day working with SqlDataReader, and actually I enjoyed it. It's pure geek joy to learn a new thing a day.

With SqlDataReader, you get the value and convert it in order to assign it to C# variable.

[sourcecode language="csharp"]
person.FirstName = reader[colFirstName];
person.Age = reader[colAge];

What if the column is nullable. You will get an exception.

You can check if the value is null like this.

[sourcecode language="csharp"]
person.FirstName = reader[colFirstName] == DBNull.Value ? null : (string)reader[colFirstName];
person.Age = reader[colAge] == DBNull.Value ? 0 : (int)reader[colAge];

It doesn't look very graceful. You read the value, validate it, and read it again to assign it. By using SqlReader.IsDBNull(), you can make it a little simpler.

[sourcecode language="csharp"]
person.FirstName = reader.IsDBNull(colFirstName) ? null : (string)reader[colFirstName];
person.Age = reader.IsDBNull(colAge) ? 0 : (int)reader[colAge];

Some argue that IsDBNull() is actually slower than comparing to DBNull.Value

And finally, you can use C#'s "as" operator with the "??" to cast the value.

[sourcecode language="csharp"]
person.FirstName = reader[colFirstName] as string;
person.Age = reader[colAge] as int? ?? 0;

It is elegant. Only one catch is that person.Age must be a nullable int. "as" only works for reference type.