CODEWITHSUNDEEP
c#sqlcastingreader
basakes
basakes

Reputation: 235

How to get float value with SqlDataReader?

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

}

Upvotes: 13

Views: 34349

Answers (7)

Jeroen Mostert
Jeroen Mostert

Reputation: 28779

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.

Upvotes: 36

Tim Schmelter
Tim Schmelter

Reputation: 460108

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
}

Upvotes: 4

Kodre
Kodre

Reputation: 181

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

Upvotes: 0

Brendon
Brendon

Reputation: 334

 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

Upvotes: 0

Hari Prasad
Hari Prasad

Reputation: 16956

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

Upvotes: 2

Ranjit Singh
Ranjit Singh

Reputation: 3735

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

Upvotes: 0

apomene
apomene

Reputation: 14389

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

Upvotes: 0

Related Questions