Converting SqlDataReader’s value to C# types

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.

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.

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.

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.

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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s