CODEWITHSUNDEEP
c#asp.netnullsqldatareaderdbnull
Burjua
Burjua

Reputation: 12726

How to safely cast nullable result from sqlreader to int?

I have a table which contains null values and I need to get data from the table using SqlDataReader. I can't figure out how I can safely cast DBNull to int.

I'm doing it in this way at the moment: 

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

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

How can I get the value safely?

Thanks.

Upvotes: 9

Views: 23385

Answers (9)

Jayeshwaree
Jayeshwaree

Reputation: 66

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

Upvotes: 1

Nocklas
Nocklas

Reputation: 1367

Change

reader["PublicationYear"] != null

to

reader["PublicationYear"] != DBNull.Value

Upvotes: 2

Nika G.
Nika G.

Reputation: 2384

as an alternative you can do the following. as you are converting DBNull to 0, alter the procedure that does the select. so that the select itself returns zero for a null value.

snippet to demonstrate the idea

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

advantage of this is that, no additional checking is needed in your code.

Upvotes: 2

Rob Levine
Rob Levine

Reputation: 41318

You should explicitly check if the value returned is 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 to be returned for nulls from the database, rather than null itself.

Upvotes: 2

Fernando
Fernando

Reputation: 4028

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

Upvotes: 1

LukeH
LukeH

Reputation: 269498

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;

Upvotes: 3

Steve
Steve

Reputation: 216313

That's the error: (reader["PublicationYear"] != null) You should test for DBNull.Value....

Upvotes: 1

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120997

DBNull is not the same as null. You should try something like this instead:

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

Upvotes: 4

Mr Lister
Mr Lister

Reputation: 46589

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

Upvotes: 14

Related Questions