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

}

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.

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
}