What are good ways to get values of type REAL/DOUBLE from a SQLite Database in C#

Question

I'm trying to get the values of a specific column that has the type REAL and want to either save it in a double, or a float.

Problem is, everything I've tried so far gives me either only a single integer back or an error: specified cast is not valid. For instance if the value in the database is 1,99 I get 1. Debugger says it's still of type double though.

What I've tried:

public static void GetDouble()
{
    SQLiteConnection databaseConnection = new SQLiteConnection($"Data Source=.\\val.sqlite;Version=3;");
    SQLiteCommand sqlCommand = databaseConnection.CreateCommand();
    sqlCommand.CommandText = $"SELECT * FROM doubles";
    databaseConnection.Open();

    SQLiteDataReader sqlReader = sqlCommand.ExecuteReader();
    while (sqlReader.Read())
    {
        // expected values: 1,1 | 8,66 | 0,64 | 0,82 | 1,9 | 0,89 | 0,52 | 1,35 | 4,32 | 1,66

        double nbr = (double)System.Convert.ToDouble(sqlReader["KDRatio"]); // gives me: 1 | 8 | 0 | 0 | 1 | 0 | 0 | 1 | 4 | 1

        // double nbr = sqlReader.GetDouble(sqlReader.GetOrdinal("KDRatio")); // specified cast is not valid
        // double nbr = sqlReader.GetDouble(5); // specified cast is not valid
        // double nbr = (double)sqlReader["KDRatio"]; // gives me: 1 | 8 | 0 | 0 | 1 | 0 | 0 | 1 | 4 | 1
        // float nbr = (float)sqlReader["KDRatio"]; // specified cast is not valid
        // float nbr = (float)System.Convert.ToDouble(sqlReader["KDRatio"]); // gives me: 1 | 8 | 0 | 0 | 1 | 0 | 0 | 1 | 4 | 1
        // string nbr = ((double)sqlReader.GetDouble(5)).ToString("#,#", CultureInfo.InvariantCulture); // specified cast is not valid

        System.Console.WriteLine(nbr);
    }
    databaseConnection.Close();
}

I actually tried alot more combinations but none worked. I expected

double nbr = (double)System.Convert.ToDouble(sqlReader["KDRatio"]);

to work, since in other methods I receive different values just fine with e.g

Name = sqlReader["Name"].ToString()
OnlineTimeMinutes = System.Convert.ToInt32(sqlReader["OnlineTimeMinutes"])

but doubles seem to make me alot of trouble.

So how can I receive the correct value with the two decimals?

Answer 1

Alright after two days I figured it out. Yet, I feel like this is not (or should not) be the right approach for this.

This, and only this, returned me the correct value.

sqlReader.GetString(sqlReader.GetOrdinal("KDRatio"));

I double checked once more that the values get written correctly into the database. Passed them in as a float and a double and when I checked for the Type with

System.Console.WriteLine(sqlReader["KDRatio"].GetType().Name);

it's returning "Double" in both cases.

Weird that I cannot just use reader.GetFloat() or reader.GetDouble() like it's intended? Looks like a bug to me that the only way to read a double or float, is to read it as a string. Anyway, it works now.

Ended up using this:

double n = double.Parse(sqlReader.GetString(sqlReader.GetOrdinal("KDRatio")));

EDIT:

Figured something out by a pure accident. I use DB Browser to check the values in my databases, and I noticed if the value in the database got only 00's in the fractional part, so e.g 74.00, then the value is written with a period and I can use GetDouble() just fine. GetString() would then give me an exception again, cast not valid. If it has anything else there, so 74,32, then it's written with a comma and I can only read it as a string.

This is probably where SQLite's Dynamic Typing comes into play. It's maybe messing with the double I pass in and is changing the type or something. Doesn't explain why I still get double returning as a Type when I check though, but interesting behaviour..

So, now I ended up using this:

double n;
try { n = double.Parse(sqlReader.GetString(sqlReader.GetOrdinal("KDRatio"))); }
catch { n = sqlReader.GetDouble(sqlReader.GetOrdinal("KDRatio")); }