Xamarin.Android SQLite SQLiteConnection.Insert does not return inserted object's ID

Question

I'm very new to Xamarin and C# and trying to insert some sample data to my simple recipe database for testing purpourses.

However, when inserting a new recipe, the SQLiteConnection.Insert(data) does not return the ID of the inserted record as it should (see here), but instead returns 1 every time.

My insert data method:

public static int insertUpdate(Object data) {
        string path = DB.pathToDatabase ();
        DB.createDatabase ();
        try {
            var db = new SQLiteConnection(path);
            int inserted = db.Insert(data);
            if (inserted != 0)
                inserted = db.Update(data);
            return inserted;
        }
        catch (SQLiteException ex) {
            return -1;
        }
    }

Inserting the sample data:

        int stand = insertUpdate (new Recipe {
            Name = "Standard",
            Author = "Aeropress Nerd",
            Description = "The perfect recipe for your first cup",
            Type = "Default"
        });


        insertUpdate (new Step { Action = "Pour", Duration = 10, Recipe = stand });
        insertUpdate (new Step { Action = "Stir", Duration = 20, Recipe = stand });
        insertUpdate (new Step { Action = "Steep", Duration = 15, Recipe = stand });

        int inv = insertUpdate (new Recipe {
            Name = "Inverted",
            Author = "Aeropress Nerd",
            Description = "A more advanced brew using the inverted method.",
            Type = "Default"
        });

I cannot figure out what I am doing wrong. Thanks in advance for any help and sorry for the (probably) stupid question.

this is what the `Insert` Method signature looks like `public long insert (String table, String nullColumnHack, ContentValues values)` accourding to developer.android.com returns a long not an int — MethodMan, Jan 11 '16 at 19:06

dsnunez · Accepted Answer · 2016-01-12T16:52:01.257

However, when inserting a new recipe, the SQLiteConnection.Insert(data) does not return the ID of the inserted record as it should (see here), but instead returns 1 every time.

I am pretty sure you downloaded some nuget package or component to include SQLite functionality to your Xamarin.Android App and it may be slightly different from the native implementation. Then, you should refer to the specific documentation for whatever it is that you're using on Xamarin.

My wild guess is that you are using this component. If I'm wrong, please comment to correct my answer. If I'm right, you should try this:

The object you want to insert

class Row
{
   public int Id { get; set; }
}

class Recipe : Row
{
    //Recipe's properties
}

class Step : Row
{
    //Step's properties
}

The insertUpdate method definition

public static int insertUpdate(Row data) {
    string path = DB.pathToDatabase ();
    DB.createDatabase ();
    try {
        var db = new SQLiteConnection(path);
        int inserted = db.Insert(data); //will be 1 if successful
        if (inserted > 0)
            return data.Id; //Acording to the documentation for the SQLIte component, the Insert method updates the id by reference
        return inserted;
    }
    catch (SQLiteException ex) {
        return -1;
    }
}

The insertUpdate method usage

 //As Step and Recipe both are derived classed from Row, you should be able to use insertUpdate indistinctively and without casting

    insertUpdate (new Step { Action = "Steep", Duration = 15, Recipe = stand });

    int inv = insertUpdate (new Recipe {
        Name = "Inverted",
        Author = "Aeropress Nerd",
        Description = "A more advanced brew using the inverted method.",
        Type = "Default"
    });

Hi, thanks so much for your help! Your code worked great, except I had to cast the data Object to a Recipe object so I could access the ID. Thanks again :) — Ben Stobbs, Jan 12 '16 at 16:00
You're right! I missed that deatil. I will update my answer to include that in some generic way. — dsnunez, Jan 12 '16 at 16:42

score 0 · Answer 2 · answered Jan 11 '16 at 19:08

0

why after inserting data to your db, you starting to update it by the same object right away. This code most likely redundant.

if (inserted != 0)
  inserted = db.Update(data);
return inserted;

answered Jan 11 '16 at 19:08

N. Chebotarev

143
7

1

i am not very familiar with xamarin but update methods more often returns the number or rows been updated, so its not a surprese you getting 1 every time – N. Chebotarev Jan 11 '16 at 19:11

Xamarin.Android SQLite SQLiteConnection.Insert does not return inserted object's ID

2 Answers2