Extension method to handle database values

Question

I find myself reading data from various SQL objects (DataTable, SqlReader)) and assigning to my own custom objects. Often times I cannot be sure if the data I am retrieving from the database is null or contains valid data. Though I make my object properties nullable I still can't assign an object value to an integer property.

public int? ClientId{ get; set; }
this.ClientId = clientTable.Rows[0]["ID"];

In the above case I cannot cast the clientTable.Rows[0]["ID"] as int because the value may be null.

this.ClientId = (int)clientTable.Rows[0]["ID"]; // WARNING! value could be null

So I thought an Extension method would be a good idea (I got this idea from this SO answer) ....

public static int GetIntFromDB(this DataRow row, string columnName)
{
  return row[columnName] as int? ?? default(int);
}

The extension method is called using ...

this.ClientId = clientTable.Rows[0].GetIntFromDB("ID");

The problem is the Extension method always returns an integer. Is there a way to return back a NULL value to the object property?

Answer 1

Sure, just make your method return int? instead of int. Heck, then it can be even simpler:

public static int? GetIntFromDB(this DataRow row, string columnName)
{
    return row[columnName] as int?;
}

I'd personally do it slightly differently though, to avoid masking places where you're asking for an int from a different field type:

public static int? GetInt32FromDB(this DataRow row, string columnName)
{
    return row.IsNull(columnName) ? (int?) null : (int) row[columnName];
}