Invalid cast exception when reading result from SQLDataReader

Question

My stored procedure:

    @UserName nvarchar(64),

    AS

    BEGIN
    SELECT MPU.UserName, SUM(TS.Monday)as Monday //TS.Monday contains float value
    FROM dbo.MapTask MT JOIN dbo.MapPU MPU
    ON MPU.ID = MT.MPUID
    JOIN dbo.TimeSheet TS
    ON MT.TMSID = TS.ID
    WHERE MT.StartDate = @StartDate_int and MPU.UserName = @UserName
    GROUP BY MPU.UserName
    END

In my C# code

SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            float monday = (float)reader["Monday"]; // Invalid cast exception
        }

Can somebody tell me what I did wrong ? Thank you.

Solution 1

My guess is that the value is being returned as a boxed double instead of float. When you unbox the type has to be exactly right. So assuming I'm right and it's not decimal or something like that, you could use:

float monday = (float) (double) reader["Monday"];

and it would work. That's pretty ugly though. If you use SqlDataReader.GetFloat it should get it right if it's genuinely a single-precision value, and it's clearer (IMO) what's going on.

On the other hand, your data could actually be coming back from the database as a double, in which case you should (IMO) use:

float monday = (float) reader.GetDouble(column);

As an aside, are you sure that float is actually the most appropriate type here in the first place? Often decimal is more appropriate...

Solution 2

A sql float is a .NET Double, see on the msdn. Try casting to a double.

Comments

[Jethro]

You can try Convert.ToFloat(reader["Monday"].ToString());

[Jon Skeet]

@Jethro: That's a pretty horrible way of doing it though. When a conversion fails, replacing it with two conversions (to and from string) is generally not a good idea.

[Jethro]

@Jon Skeet, that does make sense, plus Convert.ToFloat doesn't exist. Need to be more carefull. If Convert.ToFloat did exist would it be better to do this then. Convert.ToFloat(reader["Monday"]); ??

[Jon Skeet]

@Jethro: It would be better to call reader.GetFloat IMO. Give control of the conversion to the reader itself.

[TheGateKeeper]

You can try casting to double and then to float, that is what I do.

[Triynko]

If the datatype in SQL Server is "float", then calling SqlDataReader.GetFloat will unfortunately throw an error, because the naming conventions are completely incompatible. An SQL "float" is actually a double-precision value and therefore maps to a .NET double. An SQL "real" is the single-precision that maps to a .NET float. .NET's names are internally inconsistent, instead of choosing "double" and "single" as keywords, it mixes "float" in, whereas in SQL it's "float" and "real" with no reference to double or single precision.

[Triynko]

This is the correct answer. The cast exception occurs because SQL server uses "float" and "real" to refer to double and single-precision values respectively. I'd imagine it's a common mistake to try to call SqlDataReader.GetFloat on an SQL "float" field, because that would make sense, but alas, you must call SqlDataReader.GetDouble. And if you meant to store a single-precision value, it's back to the database to change the type from "float" to "real". They should have just stuck to "float32", "float64", "int32", int64" instead of all this "tiny" "small" "big" "real" "single" "double" nonsense.