CODEWITHSUNDEEP
c#sqlite
Lorenzo Isidori
Lorenzo Isidori

Reputation: 2047

SQLite C# Insert an unsigned integer store a negative number

I have a this table

CREATE TABLE MyTable (Id NUMERIC NOT NULL,UnsignedValue NUMERIC NOT NULL DEFAULT 0)

I have to save a number as Id and an unsigned integer in the column 'UnsignedValue'. My Insert using C#:

int id = 1;
uint myUnsignedValue = 3990427167;
using (IDbCommand cmd = connection.CreateCommand())
{
    cmd.CommandText = $@"INSERT INTO MyTable (Id, UnsignedValue) VALUES (@id, @value)";

    IDbDataParameter par = cmd.CreateParameter();
    par.ParameterName = "@id";
    par.Value = id;
    cmd.Parameters.Add(par);

    par = cmd.CreateParameter();
    par.ParameterName = "@value";
    par.Value = myUnsignedValue;
    cmd.Parameters.Add(par);

    cmd.ExecuteNonQuery();
}

The insert works but if I look with DB Browser for SQLite, in that column I see a negative value. My concern is about I am using Dapper for mapping data source to my object model. The SELECt query gives exception because the value is too small for an uint type.

I checked the IDbParameter.DbType at runtime and it is UInt32 as expected.

Am I missing something?

EDIT:

As suggested in the comments below I tried to use INTEGER column type instead of NUMERIC. But the result is the same.

I also noticed that the negative value stored is the same of an explicit Int32 cast to myUnsignedValue.

Upvotes: 1

Views: 732

Answers (1)

motosubatsu
motosubatsu

Reputation: 445

I suspect SQLite isn't determining the value correctly - firstly it doesn't have unsigned integers - they're all signed 64bit values, secondly it determines size given the value and I think it's getting that wrong.

From what you describe it sounds like SQLite is "correctly" determining the size of the parameter to be 32bit and then treating it as a signed value (because it doesn't have unsigned integers). So when the value is treated as a signed 32bit integer it gives you a negative value.

Declare the variable as an Int64 instead and it should correctly store the value.

Upvotes: 1

Related Questions