SQLite-net PCL; C# Int64/long to SQLite INTEGER

Question

I am using Xamarin for Visual Studio 2013, and the SQLite-net PCL version 1.0.11 (https://www.nuget.org/packages/sqlite-net-pcl/).

Basically I am interested in storing a numeric value greater than 2,147,483,647 in a SQLite table where the field/column has a [PrimaryKey, AutoIncrement] decoration of a class via ORM.

The below class does create the table, and inserting values (rows) works fine:

public class Patient
{
    [PrimaryKey, AutoIncrement, Column("PATIENT_NBR")]
    public int PatientNumber { get; set; }
    ...
}

The two classes below do not create the table when using "long" or "Int64" in conjunction with PrimaryKey and AutoIncrement:

public class Patient
{
    [PrimaryKey, AutoIncrement, Column("PATIENT_NBR")]
    public long PatientNumber { get; set; }
    ...
}

or

public class Patient
{
    [PrimaryKey, AutoIncrement, Column("PATIENT_NBR")]
    public Int64 PatientNumber { get; set; }
    ...
}

In the last two examples above - when running the CreateTable:

public class CreateDatabase
{
    private readonly SQLiteConnection conn;
    public CreateDatabase()
    {
        try
        {
            conn = DependencyService.Get<ISQLite>().GetConnection(Constants.DatabaseName);
            conn.CreateTable<Patient>();

            if (conn.Table<Patient>().Count() == 0)
            {
                conn.Insert(new Patient { PatientCreateDate = DateTime.UtcNow, PatientFirstName = "John", PatientLastName = "Doe" });
                conn.Insert(new Patient { PatientCreateDate = DateTime.UtcNow, PatientFirstName = "Jane", PatientLastName = "Doe" });
            }
        }
        catch (Exception ex)
        {
            throw new Exception(string.Format("Create SQLite Connection. Error: {0}.", ex.Message));
        }
    }
}

I receive the exception: ex.Message: "AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY"

If I remove AutoIncrement from the decoration of the PatientNumber, it will create the table fine, but then I run into a constraint error as the value being inserted into PatientNumber always 0 (since AutoIncrement is not being used), and the field is defined as a PrimaryKey and has to be unique.

Based on the Issues opened for SQLite-net:

https://github.com/praeclarum/sqlite-net/issues/227

it makes reference to being resoved on the below:

https://github.com/praeclarum/sqlite-net/pull/345

(sorry about the two links above - it wouldn't let me post them)

In the last link above, it mentions a workaround of:

conn.ExtraTypeMappings.Add(typeof(int64), "integer")

But I am not sure of where to add this. I have tried adding this just below where "conn" is being assigned, but am getting compiler errors that: "SQLite.SQLiteConnection does not contain a definition for "ExtraTypeMapping".

My understanding is that although SQLite can store a numeric value up to 9223372036854775807, I would be restricted by the decoration of a field with a C# class of type "Int" or "Int32", as it's maximum value is 2,147,483,647.

Am I missing something, or have I misunderstood something??

Any help would be appreciated.

SushiHangover SushiHangover · Accepted Answer · 2015-07-17T19:54:00

Are you mixing up which PCL you want? SQLite-net vs SQLite.net vs ? There are a ton of sqlite variants out there. ;-)

You are pulling the SQLite-net PC nuget and thus are getting SQLite-net PCL 1.0.11 that was release January 22, 2015.

The patch you reference was merged on March 4, 2015:

praeclarum merged commit 1638293 into praeclarum:master on Mar 4

If that is the library that you want, pull the master branch (head or just up to that commit) and build/use that instead the the Nuget.

Or to use the SqLiteConnection.ExtraTypeMappings, maybe you looking for oysteinkrog/SQLite.Net-PCL version?:

Line 94 of SqLiteConnection.cs

SQLite-net PCL; C# Int64/long to SQLite INTEGER

1 Answers