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

_For the SqlDataReader.GetSchemaTable method returns metadata about each column in the following order: ... AllowDBNull ..._ — Konrad Kokosa, Dec 12 '13 at 08:06

score 6 · Answer 1 · answered Dec 12 '13 at 08:06

6

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.

answered Dec 12 '13 at 08:06

usr

168,620
35
240
369

1

The schema table returns one row per column in the result set. But yes, the column is named `AllowDBNull`. Thanks. – usr Dec 12 '13 at 08:09
Thanks for the heads up, gave me the direction. +1-ed. For completeness I will accept my answer. Btw, I prefer to get the column info of the result set, not the table itself, so no issues there. I do think the column info in case of expressions are more or less accurate as far as nullability goes, but the trivial rare cases are not a big issue. – nawfal Dec 12 '13 at 08:52

nawfal · Accepted Answer · 2013-12-12T11:16:12.500

4

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

edited Dec 12 '13 at 11:16

answered Dec 12 '13 at 08:48

nawfal

70,104
56
326
368

1

You could do it like this also `column["AllowDBNull"]`. – Edper Dec 12 '13 at 08:55
@Edper yes that too, just that you have to cast to boolean back. – nawfal Dec 12 '13 at 11:13
Yes, where you able to get what you want in your code by the way? Or where you able to solve it now? – Edper Dec 12 '13 at 11:14
1

@Edper I think dont get your question. This was the problem I had in hand, and yes it was solved. If you meant had I needed your second alternative to fix the problem, no the earlier one was quite enough. Nevertheless I love the second one more. Big thanks for that! – nawfal Dec 12 '13 at 11:18
Good to hear that you have it solve @nawfal. In my case I call a method that would return a `List` that contains all the column names that allows NULL. And put it before the loop so that I don't have to call it time and time again. And then just using `if (columnAllowNull.Contains(rdr.GetName(i)))` to check if a given column name allows null or not. – Edper Dec 12 '13 at 11:28
@Edper indeed I wont run it inside the loop. **Dealing with table schema is darn expensive!!** In ur case I can suggest something faster. `HashSet nullColumns` where int refers to field ordinal. So now you can do your checking lookup like `nullColumns.Contains(i)`. This is faster for two reasons, one, a set based lookup is faster, two, you don't need to get the name of the column based on ordinal, ie `rdr.GetName(i)`. In case you want both ordinal and column name of null columns, you can turn your `HashSet` to `Dictionary` where int is ordinal and string is column name – nawfal Dec 12 '13 at 11:36
Thanks @nawfal. Actually you could use also `List` in my case and would have to cal it `nullColumns.Contains(i)` as well. – Edper Dec 12 '13 at 11:48
1

It depends on the number of items in your `HashSet` and `List` if it is less than 5 or even 5, `List` is faster after that `HashSet` beats out `List`. See the discussion [here](http://stackoverflow.com/questions/150750/hashset-vs-list-performance) – Edper Dec 12 '13 at 12:08

score 1 · Answer 3 · answered Oct 17 '15 at 01:21

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.

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

3 Answers3