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

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

Answer 2

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 3

Since you are reading data (using a DbDataReader), then actually no (despite the other answers), not reliably: the column can have previously allowed and stored nulls, and later forbidden nulls to be stored without removing the already stored nulls. This practice is sometimes used since checking database constraints (like NOT NULLABLE) makes inserting data much slower.

In essence, AllowDBNull is only useful when reading data if you already know that the column has had it's NOT NULLABLE constraint either permanently enforced or checked after any inserts.

From MSDN:

AllowDBNull: Set if the consumer can set the column to a null value or if the provider cannot determine whether the consumer can set the column to a null value. Otherwise, not set. A column may contain null values, even if it cannot be set to a null value.