11

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

}
Jon Skeet
  • 1,261,211
  • 792
  • 8,724
  • 8,929
BasakEbru
  • 175
  • 1
  • 1
  • 11

7 Answers7

32

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.

Jeroen Mostert
  • 23,628
  • 1
  • 40
  • 74
  • You can find the table [here](https://msdn.microsoft.com/en-us/library/ms131092(v=sql.130).aspx) ;-) – Tim Schmelter May 20 '16 at 10:44
  • @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. – Jeroen Mostert May 20 '16 at 10:47
4

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
}
Tim Schmelter
  • 411,418
  • 61
  • 614
  • 859
2

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);
Hari Prasad
  • 15,659
  • 4
  • 18
  • 33
0

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();
apomene
  • 13,898
  • 9
  • 41
  • 64
0
 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

René Vogt
  • 40,163
  • 14
  • 65
  • 85
Brendon
  • 194
  • 1
  • 1
  • 12
  • @TimSchmelter explain? – Brendon May 20 '16 at 10:33
  • See for example @Hari Prasad's answer. No need for that work around. – René Vogt May 20 '16 at 10:34
  • 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... – Jon Skeet May 20 '16 at 10:36
  • @RenéVogt and Tim Schmelter Can you explain why it is an unnecessary workaround? – Brendon May 20 '16 at 10:36
  • @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 – Brendon May 20 '16 at 10:38
  • @Brendon: Fundamentally, this is an unnecessary conversion to and then from string. Such conversions should be avoided wherever possible. – Jon Skeet May 20 '16 at 10:39
  • @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 – Brendon May 20 '16 at 10:40
  • @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. – Tim Schmelter May 20 '16 at 10:41
  • Thanks @TimSchmelter – Brendon May 20 '16 at 10:45
0

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

Ranjit Singh
  • 3,597
  • 1
  • 18
  • 35
0

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

Kodre
  • 171
  • 6