How to safely return a result with a null value from sqlreader to int?

Question

I have a table that contains null values, and I need to get data from the table using SqlDataReader. I cannot figure out how I can use DBNull for int safely.

I am doing it this way at the moment:

... reader = command.ExecuteReader(); while (reader.Read()) { int y = (reader["PublicationYear"] != null) ? Convert.ToInt32(reader["PublicationYear"]) : 0; ... } ... 

but getting Object cannot be cast from DBNull to other types. when PublicationYear is null.

How can I get the value safely?

Thanks.

Answer 1

You should compare reader["PublicationYear"] with DBNull.Value , not null .

Answer 2

DBNull not null . Instead, you should try something like this:

 int y = (reader["PublicationYear"] != DBNull.Value) ? ... 

Answer 3

Change

reader["PublicationYear"] != null

to

reader["PublicationYear"] != DBNull.Value

Answer 4

You must explicitly check if a value of type DBNull

 while (reader.Read()) { int y = (!reader["PublicationYear"] is DBNull) ? Convert.ToInt32(reader["PublicationYear"]) : 0; ... } 

In fact, you can do this comparison by value as well as type:

 reader["PublicationYear"] != DBNull.Value 

In short, you can expect DBNull be returned for zeros from the database, not null.

Answer 5

alternatively you can do the following. when you convert DBNull to 0, change the procedure that makes the selection. so the choice itself returns zero for a null value.

to demonstrate an idea

  SELECT ... ,ISNULL (PublicationYear, 0) as PublicationYear ... FROM sometable 

The advantage of this is that your code does not require additional verification.

Answer 6

What an error: (reader["PublicationYear"] != null) You should check for DBNull.Value ....

Answer 7

 int ord = reader.GetOrdinal("PublicationYear"); int y = reader.IsDBNull(ord) ? 0 : reader.GetInt32(ord); 

Or alternatively:

 object obj = reader["PublicationYear"]; int y = Convert.IsDBNull(obj) ? 0 : (int)obj; 

Answer 8

Change your test from (reader["PublicationYear"] != null) to (reader["PublicationYear"] != DBNull.Value) .

Answer 9

Database null values ​​should be compared with DBNull.Value

reader = command.ExecuteReader(); while (reader.Read()) { int y = (reader["PublicationYear"] != DBNull.Value) ? Convert.ToInt32(reader["PublicationYear"]) : 0; ... }