SqlDataReader find out if a data field is nullable

Question

Is there a way I can determine in .NET, for any arbitrary SQL Server result set, if a given column in the result can contain nulls?

For example, if I have the statements

Select NullableColumn From MyTable

and

Select IsNull(NullableColumn, '5') as NotNullColumn From MyTable

and I get a datareader like this:

var cmd = new SqlCommand(statement, connection);
var rdr = cmd.ExecuteReader();

can I have a function like this?

bool ColumnMayHaveNullData(SqlDataReader rdr, int ordinal)
{
   //????
}

I want it to return true for the first statement, and false for the second statement.

rdr.GetSchemaTable() doesn't work for this because it returns whether the underlying column can be null, which is not what I want. There are functions on datareader that return the underlying sql type of the field, but none seem to tell me if it can be null..

Answer 1

Unfortunately You can't because SQL server has no way of determining whether a field is nullable or not. You can do arbitral transformations on fields in result set (operators, function calls etc.) and those transformations do not have metadata about them whether they can or can't return null. So You have to figure that out manually or use views with schemabinding...

Answer 2

I'm a bit confused:

"doesn't work for this because it returns whether the underlying column can be null, which is not what I want. "

"but none seem to tell me if it can be null.."

You can query the underlying table to see if a column is null-able (assuming that is what you want).

SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable' AND COLUMN_NAME = 'MyColumn'