invalid cast exception when trying to transform SQL int -> c# int?

Question

Hi I'm currently using the following to get the columns of a sql server database

public IDataReader myReader()
{
    DbCommand = new SqlCommand("Select * from Mydatabase");
    SqlConnection con = new SqlConnection(connectionstr);
    con.Open();
    DbCommand.Connection = con
    return command.ExecuteReader(CommandBehavior.closeConnection);
}

IDataReader reader = myReader();
while (reader.Read())
{
    int? a = (int?)reader["myIntColumn"];
}

At other locations (for other tables) the code works fine and also for this table it works fine until I come to the fields that are int in the database. Then I get System.InvalidCastException (in other tables I didn't get that problem).

Is there anything I did wrong? Or that I have to do to make this work? (the int value in question is NULL in the DB)

score 5 · Accepted Answer · answered Jun 15 '14 at 10:23

5

Your code will not work when the result is null, since the value of the column will be DbNull.Value on the .NET side then.

Try this:

int? a = reader["myIntColumn"] as int?;

answered Jun 15 '14 at 10:23

Patrick Hofman

153,850
22
249
325

Functioned that way. That is interesting. Can you tell me why implicit conversion throws the exception but the "as" conversion does notß – Thomas Jun 15 '14 at 10:26
1

Because `DbNull.Value` is not an `int?`. When you try to cast it with `as` it will be null when it cannot cast the value. – Patrick Hofman Jun 15 '14 at 10:27
Quoting: "The problem is that both values returned by the ternary operator must be the same type, or one must be implicitly convertible to the other. In this case, null cannot be implicitly converted to int nor vice-versus, so an explict cast is necessary." – vfioox Jun 15 '14 at 10:28
Yes I got mistaken on a few fields, I'm just bad at this. – vfioox Jun 15 '14 at 10:31
Got a question is there any possibility that when I use multiple " reader ["xyz"] as int?" in a parameterlist that he has troubles doing it correctly? (as soon as I didn't use a single assign like int? a = but instead new myobj(reader["my1"] as int?, reader["my2"] as int?,...) I got the same error message again. – Thomas Jun 16 '14 at 06:13
It is impossible to get this error message on casts using `as`. Any other fields? Also, try to make a break down of your code: initialize all values in a separate variable. This will make diagnosing the problem easier. – Patrick Hofman Jun 16 '14 at 06:18

score 1 · Answer 2 · answered Jun 15 '14 at 10:26

1

You need to explicitly check for DBNull.Value as follows:

while (reader.Read())
{
   var a = reader["myIntColumn"] == DBNull.Value ? null : (int?)reader["myIntColumn"];
}

answered Jun 15 '14 at 10:26

devduder

394
1
10

invalid cast exception when trying to transform SQL int -> c# int?

2 Answers2