System.Data.SQLite

When having a table, with a column of datatype DECIMAL(38,9) (or just NUMERIC), data gets read wrong, when using SQLiteDataAdapter.Fill(DataTable dt).

I have value 123456789123456780 stored in DB and it gets read as 123456789123457000M.

My workaroud: 

new public int Fill(DataTable dataTable)
{
	var rowsAddedToDataSet = 0;

	var reader = _command.ExecuteReader();
	var dataSet = dataTable.DataSet;

	for (var i = 0; i < reader.FieldCount; i++)
		dataTable.Columns.Add(reader.GetOriginalName(i), reader.GetFieldType(i));

	while (reader.Read())
	{
		rowsAddedToDataSet++;

		var row = dataTable.NewRow();

		for (var i = 0; i < reader.FieldCount; i++)
		{
			if (reader.GetFieldType(i) == typeof(Decimal))
				row[i] = reader.GetDecimal(i);
			else
				row[i] = reader.GetValue(i);
		}

		dataTable.Rows.Add(row);
	}

	reader.Close();

	return rowsAddedToDataSet;
}

Feel free to contact me for more info.

Is the value inserted into the database as a string?  Do you have a code snippet
that shows how this is done?

Also, what is the schema of the table involved in this?

I've been able to recreate the symptoms described in this ticket.  It boils down
to the fact that the Decimal type internally maps to the Double type affinity.

The only viable alternative would be to map it to the Text affinity; however,
that would break backwards compatibility.

Your workaround seems like the correct way to deal with this situation.  The
GetValue method uses automatic type detection that relies upon the affinity
mappings; hence, it will not produce the desired result in this case.

This issue has existed since the start of the project in 2005.

[/artifact?ln=430-431&name=a746c57897de5d6d]

I'm adding a new connection flag (for use in the connection string) that will
force the Decimal columns to be treated as text.

The new connection flag is "GetDecimalAsText".  It can be activated for a specific
connection by adding the following property to the connection string:

    Flags=GetDecimalAsText;

When doing this, be sure there is a semicolon after the previous property, e.g.:

    "Data Source=test.db;Flags=GetDecimalAsText;"

Alternatively, it can be activated for the process by using the associated
environment variable, e.g.:

    Environment.SetEnvironmentVariable("DefaultFlags_SQLiteConnection",
        "GetDecimalAsText");

Fixed on trunk via check-in [59d87c8bce].

Can you compile the code on trunk and let us know if this change works for you?

    class Program
    {
        static void Main(string[] args)
        {
            var toSave = 12345678912345.1556346M;

            Console.WriteLine("Testing problem A - english culture");
            ProblemA(toSave);

            Console.WriteLine("-----------------------------------");

            Console.WriteLine("Testing problem B - danish culture");
            ProblemB(toSave);

            Console.ReadKey();            
        }

        static void ProblemA(decimal toSave)
        {
            System.Globalization.CultureInfo.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-US");
            System.Globalization.CultureInfo.CurrentUICulture = System.Globalization.CultureInfo.GetCultureInfo("en-US");

            Problem("Data Source=testProblemA.db;Flags=GetDecimalAsText;", toSave);
        }

        static void ProblemB(decimal toSave)
        {
            System.Globalization.CultureInfo.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("da-DK");
            System.Globalization.CultureInfo.CurrentUICulture = System.Globalization.CultureInfo.GetCultureInfo("da-DK");

            Problem("Data Source=testProblemB.db;Flags=GetDecimalAsText;", toSave);
        }

        static void Problem(string connnectionString, decimal toSave)
        {
            using (var connection = new SQLiteConnection(connnectionString))
            {
                connection.Open();

                using (var cmd = new SQLiteCommand(connection))
                {
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS Test (Id INTEGER PRIMARY KEY AUTOINCREMENT, Num NUMERIC);";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = $"INSERT INTO Test (Num) VALUES (@Num)";
                    cmd.Parameters.AddWithValue("@Num", toSave);
                    cmd.ExecuteNonQuery();
                }
            }

            Console.WriteLine($"Saved {toSave}");

            var dt = new DataTable();

            using (var connection = new SQLiteConnection(connnectionString))
            {
                connection.Open();

                using (var cmd = new SQLiteCommand(connection))
                {
                    cmd.CommandText = "SELECT * FROM Test";

                    var adapter = new SQLiteDataAdapter(cmd);

                    adapter.Fill(dt);
                }
            }

            foreach (DataRow row in dt.Rows)
                Console.WriteLine($"Read ID: {row.Field<long>("Id")} Num: {row.Field<decimal>("Num")}");
        }
    }

Looks like there needs to be a new parameter binding flag too.

The new connection flags are now "GetDecimalAsText" and "BindDecimalAsText".
They can be activated for a specific connection by adding the following property
to the connection string:

    Flags=GetDecimalAsText,BindDecimalAsText;

When doing this, be sure there is a semicolon after the previous property, e.g.:

    "Data Source=test.db;Flags=GetDecimalAsText,BindDecimalAsText;"

Alternatively, they can be activated for the process by using the associated
environment variable, e.g.:

    Environment.SetEnvironmentVariable("DefaultFlags_SQLiteConnection",
        "GetDecimalAsText,BindDecimalAsText");

It should also be noted that in order to work around automatic type affinity
detection provided by the SQLite core library, the column types should be
"DECIMALTEXT" and "NUMERICTEXT" instead of "DECIMAL" and "NUMERIC".

With the latest changes on trunk, I'm unable to reproduce the issue if the missing
decimal separators.