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..

@tomfanning I'm hacking an ORM. I want to log anytime someone tries to run a query where a nullable db field maps to a non-nullable value type. — dan
– dan, Commented Apr 15, 2011 at 0:22

luckyluke · Accepted Answer · 2011-04-16 00:49:42Z

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...

answered Apr 16, 2011 at 0:49

luckyluke

1,5639 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve Wellens · Accepted Answer · 2011-04-15 01:21:35Z

1

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'

answered Apr 15, 2011 at 1:21

Steve Wellens

20.6k2 gold badges31 silver badges70 bronze badges

4 Comments

dan Over a year ago

i want to know if a column in the result set can contain nulls, not if the column in the underlying table can contain nulls. In my example, I use an IsNull() in the select statement to turn null values into non-null values. In this case, that result set column cannot contain nulls, even though the underlying table column can.

Steve Wellens Over a year ago

The result set column can still contain nulls, but you've replaced all the nulls with non nulls. So you want to know if the data was 'scrubbed'? You could retrieve the same column twice...one would be raw with nulls and the other would be the scrubbed version of the same column.

dan Over a year ago

If I've replaced all nulls with non nulls, then the result set column doesn't contain nulls. That's what I'm trying to find out, but without having to loop through the rows to look at individual values. Basically I'm trying to validate ad-hoc queries against an object model at runtime. You and I can tell just by looking at a query which result columns may contain nulls, so there should be a way to write a function that can figure that out.

luckyluke Over a year ago

Also in my opinion using isnull IN queries that return null is not the kosher way( though Your milagr may vary) god created nullable types so that the map to nullable result fields and the we call GetValueOrDefault in the property implementation in the poco and everything is clean;-)

Collectives™ on Stack Overflow

SqlDataReader find out if a data field is nullable

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related