SqlDataReader finds out if a data field is valid

Question

Is there a way I can define in .NET for any arbitrary SQL Server result set if this column can contain zeros as a result?

For example, if I have statements

Select NullableColumn From MyTable 

and

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

and I get datareader as follows:

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

Can I have such a function?

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

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

rdr.GetSchemaTable() does not work for this because it returns if the base column can be null, which I don't want. There are functions in the datareader that return the base type of the sql field, but no one tells me if it can be null.

Answer 1

Unfortunately, you cannot, because the SQL server has no way to determine if a field is null or not. You can do arbitration transformations in the fields in the result set (operators, function calls, etc.), and these transformations do not have metadata about them, whether they can or cannot return null. So you have to figure it out manually or use views with schemabinding ...

Answer 2

I'm a little confused:

"does not work for this, because it returns whether the base column can be null, which is not what I want.

"but no one tells me if it can be zero.

You can query the base table to see if the column is null (assuming this is what you want).

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