How to get float value with SqlDataReader?

Question

In my database, I have NextStatDistanceTime value as a float. When "float time = reader.GetFloat(0);" line excecuted, it gives an error of

system invalid cast exception

How can I get float value from sql command in this code?

Here is my code:

using (SqlConnection conn = new SqlConnection(@"<myconnectionstring>"))
{
    float totaltime = 0;
    for (int i = startStationIndex; i < endStationIndex; i++)
    {
        SqlCommand command = new SqlCommand("SELECT NextStatDistanceTime FROM [MetroDatabase].[dbo].[MetroStation] WHERE StationIndex = " + i + "", conn);
        try
        {
            conn.Open();
            command.ExecuteNonQuery();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    float time = reader.GetFloat(0);
                    totaltime = totaltime + time;
                    conn.Close();
                }
            }                        
        }
        catch (Exception ex)
        {
            result = ex.Message;
            Console.WriteLine(ex.Message);
        }
    }

}

Trituration

What is the type in the database? – Jacquiline

@JonSkeet he has mentioned it, its "float" in database. – Photoperiod

@RanjitSingh: Whoops, missed that. In that case I'd really expect it to be fine... My guess is that it isn't really a float in the database. It would be interesting to know the result of calling reader.GetValue(0) - what type is that? – Jacquiline

As an aside, you should immediately start using parameterized SQL. – Jacquiline

Oh, and why not get the database to sum the values for you? It seems odd to fetch all the values, just to add them up... – Jacquiline

Answer 1

It's time for a little table, I think.

T-SQL type name	.NET equivalent	C# type name	DataReader method
FLOAT	System.Double	double	IDataReader.GetDouble()
REAL	System.Single	float	IDataReader.GetFloat()

Note that GetFloat has the wrong name -- it should be GetSingle, because float is a C#-specific name. It makes no sense in VB.NET, for example.

So, if your database column is of type FLOAT, read it using GetDouble, not GetFloat. The data reader methods do not perform conversions; there is a generic GetValue method to get the value as an object that you can then convert further.

Incidentally, this is not the only subtlety -- the .NET floating-point types support denormalized values, whereas the T-SQL types do not, so it is possible to have floating-point numbers in your .NET code that can't be successfully stored in the database, even if the types match.

Balloon

You can find the table here ;-) – Ladybird

@TimSchmelter: that's a subtly different table because it describes the mapping to the ADO.NET SQL-specific types as well. SqlSingle isn't the same as System.Single. I could include it in my table for completeness, but there's rarely a need to use these types unless there's no corresponding .NET system type (like SqlGeography). But, yes, this is another layer of conversions to be aware of. – Balloon

Answer 2

As you can read here a sql-server float maps to a .NET double, so you need to use GetDouble:

double totaltime = 0;  // necessary, double is wider than float
// ...

while (reader.Read())
{
    double time = reader.GetDouble(0);
    totaltime = totaltime + time;
    // conn.Close(); no, not in this loop, should be closed in the finally or via using-statement
}

Ladybird

Answer 3

My guess is that Database is returning double value, try getting it as Double and convert it float (if required).

float time= (float) reader.GetDouble(0);

Plaid

Answer 4

you can try:

float time = float.Parse(reader[0].ToString());

also note (though not related with your Q) that you don't need to run

command.ExecuteNonQuery();

Journal

Answer 5

 while (reader.Read())
 {
     object initialTime = reader["NextStatDistanceTime"];
     float time;
     float.TryParse(initialTime.ToString(), out time);

     totaltime = totaltime + time;
     conn.Close();
 }

Try this, this will get the time from the Database then convert it to a float, you can just put the reader["NextStatDistanceTime] in the tryparse if you want but to make it clearer i have done it like this.

Any issues let me know

Subroutine

@TimSchmelter explain? – Subroutine

See for example @Hari Prasad's answer. No need for that work around. – Cabbage

It's not just unnecessary, but it's potentially dangerous. Suppose it's coming back from the database as a string already, using . as the decimal separator, but the OP is using a culture which uses , as the decimal separator... – Jacquiline

@RenéVogt and Tim Schmelter Can you explain why it is an unnecessary workaround? – Subroutine

@JonSkeet Ok, that makes sense, it wouldnt be able to be converted into a float otherwise, and would probably have to validate it before using it as a float to check if it contains a ',' or something along those lines – Subroutine

@Brendon: Fundamentally, this is an unnecessary conversion to and then from string. Such conversions should be avoided wherever possible. – Jacquiline

@JonSkeet Ok thank you, I am unfamiliar with the .GetDouble they are using which is the reason to the answer i have made... I will do some research on those and use them in my future code – Subroutine

@Brendon: because you accept the fact that OP doesn't know that floats are mapped to double and provide workaround with string conversions. That might work in this case, but could cause other issues (in future). String conversions are always vulnerable to localization issues(f.e. one culture uses colon the other comma as decimal separator). It's also not very efficient. – Ladybird

Thanks @TimSchmelter – Subroutine

Answer 6

Try this

convert.ToSingle(reader["NextStatDistanceTime"])

or do

double value = (double)reader["NextStatDistanceTime"]

Float of sql is equivalent to double of c#, you can see the similar mapping here

Photoperiod

Answer 7

Probably is precision mismatch between database type and c# type. Try cast like (float)reader.GetDouble(0);

Fidelfidela