Alvaro Oliveira Alvaro Oliveira - 27 days ago 5
C# Question

How do I get Dapper.Rainbow to insert to a table with AutoIncrement on SQLite?

I created a sample table on SQLite that has an Id column that is auto increment.

CREATE TABLE "ESVLIntegration" ("Id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "ProcessId" TEXT NOT NULL , "UserId" INTEGER NOT NULL , "Status" TEXT NOT NULL , "StartDate" DATETIME NOT NULL , "EndDate" DATETIME, "Operation" TEXT NOT NULL , "SNEquip" TEXT NOT NULL , "CardName" TEXT NOT NULL , "FilePath" TEXT NOT NULL , "Processed" BOOL NOT NULL )


But when I try to insert for the second time, I get the following error:


Abort due to constraint violation PRIMARY KEY must be unique


This is my code

public class ESVLIntegration
{
public long Id { get; set; }
public String ProcessId { get; set; }
public long UserId { get; set; }
public String Status { get; set; }
public DateTime StartDate { get; set; }
public DateTime EndDate { get; set; }
public String Operation { get; set; }
public String SNEquip { get; set; }
public String CardName { get; set; }
public String FilePath { get; set; }
public Boolean Processed { get; set; }
}

public class Sample : Database<Sample>
{
public Table<ESVLIntegration> ESVLIntegration { get; set; }
}

private void WriteParameters()
{
"Writing sample parameters to SQLite DB".LogDebug();
var pars = new ESVLIntegration();
pars.ProcessId = Guid.NewGuid().ToString();
pars.CardName = "gpp3";
pars.StartDate = DateTime.Now;
pars.Status = "Start";
pars.Operation = VerifyStatus;
pars.SNEquip = "12345";
pars.FilePath = @"C:\Folder\FilePath";
pars.Processed = false;
using (var conn = new SQLiteConnection(connStr))
{
conn.Open();
var db = Sample.Init(conn, 2);
db.ESVLIntegration.Insert(pars);
}
}


Any ideas on what I'm doing wrong here ?

EDIT

INTEGER columns on SQlite are of type int64(long)

Answer

From the SQLite FAQ I found :

With this table, the statement

INSERT INTO t1 VALUES(NULL,123);

is logically equivalent to saying:

INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);

So I just changed my class Id to be nullable

public class ESVLIntegration
{
    public long? Id { get; set; }
    public String ProcessId { get; set; }
    public long UserId { get; set; }
    public String Status { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public String Operation { get; set; }
    public String SNEquip { get; set; }
    public String CardName { get; set; }
    public String FilePath { get; set; }
    public Boolean Processed { get; set; }
}

Now it works great!