SQLite exception deleting row using sqlite-net

Question

I got tired to search so here it goes my first SO question hoping someone had the same problem and can help me

Goal

I am trying to store my application data with a SQLite database

Application description

Windows 8 app C# XAML with local SQLite database using SQLite for Windows Runtime Extension and sqlite-net library

Table definition

public class Product {
    private int _id;

    [SQLite.PrimaryKey, SQLite.AutoIncrement]
    public int ID
    {
        get { return _id; }
        set { _id = value; }
    }

    private string _date;
    public string DATE
    {
        get { return _date; }
        set { _date = value; }
    }

    private string _desc;
    public string DESC
    {
        get { return _desc; }
        set { _desc = value; }
    }
}

Problem1

public int Insert (object obj) description says the following:

Inserts the given object and retrieves its auto incremented primary key if it has one.

However everytime I insert a row it return 1. I can sucessfully insert with a auto-incremet ID but somehow it does not return me its ID. Why?

Problem 2

I can insert new rows but not delete them

Working around problem 1 to get last row generated ID, I try to delete rows but with no success.

See this example test that always fails:

using (var db = new SQLiteConnection(Path.Combine(_path, _dbname)))
{
    var p1 = new Product() { DESC = "insert1", DATE = DateTime.Now.ToString() };
    db.Insert(p1);
    p1.ID = 1;
    var p2 = new Product() { DESC = "insert2", DATE = DateTime.Now.ToString() };
    // I am sure that this row is getting ID 2, so it will not have a wrong ID
    p2.ID = 2;
    db.Insert(p2);
    var p3 = new Product() { DESC = "insert3", DATE = DateTime.Now.ToString() };
    db.Insert(p3);
    p3.ID = 3;
    db.Delete<Product>(p2);
}

As you can see I try to insert 3 rows and delete the second one. The rows are inserted but I get the following SQLite.SQLiteException exception:

unable to close due to unfinalized statements or unfinished backups

Why? I don't open other connections before and after that.

Thanks in advance

Solved

Problem 1

+1 and thanks for @Bridgey for pointing out that function does not match it description and for the relevant search

The function does not return ID as it says but it defines the object ID. So when I insert a new Product, Product.ID will have last inserted ID.

Problem 2

I changed db.Delete<Product>(p2); to db.Delete(p2); and now it works. SQLite-net correctly identify the row as Product. I still don't know why the unable to close due to unfinalized statements or unfinished backups exception was happening. If someone knows why tell me please.

Answer 1

I think for problem 2, the issue is that you are passing the Product object as the parameter for the Delete method. The documentation says: Deletes the object with the specified primary key. I think the following should work:

db.Delete<Product>(p1.ID);

Regarding problem 1, the code of the Insert method of the sqlite-net package ends:

var count = insertCmd.ExecuteNonQuery (vals);

if (map.HasAutoIncPK) {
  var id = SQLite3.LastInsertRowid (Handle);
  map.SetAutoIncPK (obj, id);
}

return count;

As you can see, count is returned, even if id is set.

EDIT: Actually, according to the author this is deliberate. "Insert returns the number of rows modified. The auto incremented columns are stored in the object. Please see the doc comments." https://github.com/praeclarum/sqlite-net/issues/37