Simple.Data Sqlite Insert returns Null

Question

Im using Simple.Data to insert data into an Sqlite database. I read on the wiki that Insert returns the inserted data. I need to get the latest rowID (identity). But I get Null instead.

Using the Stable version from NuGet.

var db = Database.OpenFile("Database.db");
var x = db.Scan.Insert(Name:"Test", Description:"test", CreationDate:DateTime.Now, DocumentDate:DateTime.Now, ModifiedDate:DateTime.Now);

DB schema:

CREATE TABLE Scan ( 
    ID           INTEGER         PRIMARY KEY,
    Name         NVARCHAR( 50 )  NOT NULL,
    Description  TEXT,
    CreationDate DATETIME        NOT NULL,
    DocumentDate DATETIME        NOT NULL,
    ModifiedDate DATETIME        NOT NULL 
);

Does this even work for SQLite? If not whats the best way to retrieve the rowID of the inserted record?

score 3 · Accepted Answer · answered Jul 29 '14 at 13:40

Had the same "issue". The problem lies within your schema.

You have to add identity to your primary key column:

ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,

Then you get back the row and you can i.e. create a new object with it:

var x = db.Scan.Insert(Name:"Test", Description:"test", CreationDate:DateTime.Now, DocumentDate:DateTime.Now, ModifiedDate:DateTime.Now);
return new Scan { ID = (int)x.ID, Name = x.Name, ... }

From the Wiki:

If you have an IDENTITY column defined on your table, then the Insert methods will all return a copy of the record fetched from the database, so all defaulted values are set. In 0.4, support for other ways of fetching the inserted row will be added.

Simple.Data Sqlite Insert returns Null

1 Answers1