Ross Coded Classes

March 1, 2010

Typesafe access to nullable DataColumns

Filed under: .NET Tips — Tags: , — Ross Bradbury @ 3:26 pm

I recommend using these standards when reading values from a DataRow.

  • Prefer not to use the as operator with the DataRow indexer because it hides type conversion errors and it is slower than other options.
  • Use the Field extension method from System.Data.DataSetExtensions.dll for value-type columns that allow DBNull.
  • Use casting for columns that do not allow DBNull.
  • Prefer checking DataRow.IsNull and casting for nullable reference types

The extension methods Field and SetField are in System.DataSetExtensions.dll (System.Data namespace).

// Instead of using the as operator to access a DateTime? type column...
var entryDate = row["entry_date"] as DateTime?;
// Use the Field extension method instead.
var entryDate = row.Field<DateTime?>("entry_date");

All the signatures that the DataRow indexer accepts are accepted by the Field method.

It really is more of a type-safety and understanding/clarity issue than performance. Timings for 50 million calls (I recommend uses in blue) :

DateTime, AllowDBNull = true, not-null:

 row.Field<DateTime?>(column)                        4172 ms Type-safe and fastest
 row.IsNull(column) ? null : (DateTime?)row[column]  9722 ms
 row[column] as DateTime?                            9025 ms Hides InvalidCastException

DateTime, AllowDBNull = true, null:

 row.Field<DateTime?>(column)                        3935 ms Type-safe and pretty fast
 row.IsNull(column) ? null : (DateTime?)row[column]  3080 ms
 row[column] as DateTime?                            5429 ms Hides InvalidCastException

DateTime, AllowDBNull = false, not-null:

 row.Field<DateTime>(column)                         4639 ms
 (DateTime) row[column]                              2708 ms Type-safe and fast

String, AllowDBNull = false, not-null:

 row.Field<string>(column)                           3362 ms
 (string) row[column]                                1317 ms Type-safe and fast

String, AllowDBNull = true, not-null:

 row.Field<string>(column)                           3467 ms less to type, typesafe, slower.
 row.IsNull(column) ? null : (string) row[column]    2106 ms A lot to type, but fast
 row[column] as string                               1320 ms Hides InvalidCastException

String, AllowDBNull = true, null:

 row.Field<string>(column)                           2857 ms less to type, typesafe, slower.
 row.IsNull(column) ? null : (string) row[column]    1182 ms A lot to type, but fast
 row[column] as string                               1279 ms Hides InvalidCastException

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Blog at WordPress.com.

%d bloggers like this: