CODEWITHSUNDEEP
c#sqlitedoublesqldatareader
Eddd
Eddd

Reputation: 69

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

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?

Upvotes: 2

Views: 1474

Answers (2)

user700390
user700390

Reputation: 2339

This seems like some combination of SQLite's features - namely type affinity, flexible typing, and its treatment of numeric literals (which ignores culture and requires '.' for decimal separator character).

https://sqlite.org/datatype3.html#type_affinity

https://sqlite.org/quirks.html#flexible_typing

https://sqlite.org/lang_expr.html#literal_values_constants_

Your columns probably have text affinity. The SQLite.NET library is probably performing truncation when you try to read them as numerics and encounters the "," characters.

To avoid your current workaround, I would try to redefine the column in SQLite to ensure it is NUMERIC or REAL affinity, and use the "." instead of "," for your decimal separator.

Upvotes: 1

Eddd
Eddd

Reputation: 69

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

Upvotes: 2

Related Questions