How to get float value with SqlDataReader?

BasakEbru picture BasakEbru · May 20, 2016 · Viewed 25.7k times · Source

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

Jeroen Mostert picture Jeroen Mostert · May 20, 2016

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.