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?

You need to return `int?` then... – leppie Aug 18 '14 at 16:50 — leppie, Aug 18 '14 at 16:50

score 5 · Accepted Answer · answered Aug 18 '14 at 16:51

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];
}

score 0 · Answer 2 · answered Aug 18 '14 at 16:54

The code you have in your extension method will always return an integer because you're using the coalesce operator "??" to give the default(int) value. If you want it to return null instead, just remove the default(int) to the right of the operator.

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

Extension method to handle database values

2 Answers2