Is there a way to check if column supports null values from datareader?

Question

Columns of a table in db can store a null values (as DBNulls). Is there a way I can get this info from IDataReader or DbDataReader?

using (var reader = command.ExecuteReader())
{
    //can I get the column info like if it supports null value if I pass the ordinal?
    reader.CheckIfSupportsNull(someIndex) ???

    while (reader.Read())
    {

    }
}

I do know I can read the cell values and check it against DBNull.Value, but I'm not asking if I can check the read value is null, but if I can check if the column itself supports DBNulls irrespective of the actual values present in table.

I would like to know if this is possible with MySqlDataReader and/or SqlDataReader..

Answer 1

IDataReader.GetSchemaTable allows for that. It returns a DataTable describing the result set. Look into the docs to see what columns it returns (there are many). Not sure how expensive that call is, though. I don't think it does a round-trip to the server.

It returns the properties of the result set, not of some table directly. If you select columns directly from a table, the properties should match, though. This can not work for computed expressions (SomeCol + 1) because SQL Server does not precisely track nullability through expressions.

Answer 2

@usr's answer pointed me in right direction. I got it done like this:

var table = reader.GetSchemaTable();
foreach (DataRow column in table.Rows) //here each row represents a column
{
    var allowsNull = column.Field<bool>("AllowDbNull"); //get it one by one here
    // similarly column.Field<string>("ColumnName") gives the name of the column
}

should work.

Note: As @Edper mentions

var allowsNull = (bool)column["AllowDbNull"];

does the job too. This has an added advantage that you dont have to reference System.Data.DataSetExtensions.dll which is required for Field<T> extension method.