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..
GetValue(0) != DbNull. Value) //if value in columnA is not null { //.... } }
Handling Null column values using turnery operator: In this example I used turnery operator with DBNull. Value to check sql data reader columns value is null or not, if found null then column value assigned to some default value.
For working with database ANSI SQL null values, use System. Data. SqlTypes nulls rather than Nullable. For more information on working with CLR value nullable types in Visual Basic see Nullable Value Types, and for C# see Nullable value types.
string ColumnValue; if (dr["ColumnName"] != null) ColumnValue = dr["ColumnName"].
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...
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With