How to get the last row Id after insert data into table for SQLite

Question

I am using SQLite and SQLite-Net Wrapper for WinRT app. Other platform may have SQLite, but the implementation may be different such as using SQLite-Net api.

How do I get the last row Id immediately after insert for SQLite? Thanks


 using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var newOrder = new SalesOrder()
                {
                    CustId = g_intCustId,
                    Customer_No = txtBlkCustomer.Text.Trim(),
                    Order_Date = DateTime.Today                    
                };

                db.Insert(newOrder);

      }

--1--- Update : I am using SQLite-Net Wrapper. I am not using SQLite -WInRT


I get the following error :

The type arguments for method 'SQLite.SQLiteConnection.ExecuteScalar(string, params object[])' 

cannot be inferred from the usage. Try specifying the type arguments explicitly.    

db.Insert(newOrder);
var key = db.ExecuteScalar("SELECT last_insert_rowid()");


---2-- Update 


This is the class :

My problem is : How to get the SId immediately after inserting a record using above code.

 class SalesOrder
    {
        [PrimaryKey, AutoIncrement]
        public int SId { get; set; }  

        public int CustId { get; set; }
        public string Customer_No { get; set; }
        public DateTime  Order_Date { get; set; }

     }

I am a little puzzled that a *C#* question is being closed as a dup of a *PHP* question. — Shafik Yaghmour, Oct 16 '13 at 03:17
@MilkBottle I have the same question as you had , but im not able to figure out how to change the code ? where am i supposed to find the SQLite.cs file i am using nuget. — Zany, Sep 16 '17 at 11:16

FunksMaName · Answer 1 · 2013-10-09T09:01:45.917

7

do you have the ExecuteScalar method on your connection? then use

var key = db.ExecuteScalar<int>("SELECT last_insert_rowid()");

edited Oct 09 '13 at 09:01

answered Oct 09 '13 at 07:51

FunksMaName

2,101
1
15
17

1

I got the above error. What I did wrong? I am using SQLite-Net – MilkBottle Oct 09 '13 at 08:02
Don't you have to do something like `...ExecuteScalar("...", new [] object)`? – LS_ᴅᴇᴠ Oct 09 '13 at 08:18
I just installed the package, and it worked. Also, what does id in "var id = db.Insert(newOrder);" return? It seems to return the identity of the inserted record. Is your identity column an int type? – FunksMaName Oct 09 '13 at 08:19

score 1 · Accepted Answer · answered Oct 09 '13 at 08:46

In SQLite-net, Insert method returns the number of row inserted (SQLite.cs). So if you want it to return the last row ID you can update it to do like that.

Current implementation.

public int Insert (object obj, string extra, Type objType)
{
    if (obj == null || objType == null) {
        return 0;
    }


    var map = GetMapping (objType);

    #if NETFX_CORE
    if (map.PK != null && map.PK.IsAutoGuid)
    {
        // no GetProperty so search our way up the inheritance chain till we find it
        PropertyInfo prop;
        while (objType != null)
        {
            var info = objType.GetTypeInfo();
            prop = info.GetDeclaredProperty(map.PK.PropertyName);
            if (prop != null) 
            {
                if (prop.GetValue(obj, null).Equals(Guid.Empty))
                {
                    prop.SetValue(obj, Guid.NewGuid(), null);
                }
                break; 
            }

            objType = info.BaseType;
        }
    }
    #else
    if (map.PK != null && map.PK.IsAutoGuid) {
        var prop = objType.GetProperty(map.PK.PropertyName);
        if (prop != null) {
            if (prop.GetValue(obj, null).Equals(Guid.Empty)) {
                prop.SetValue(obj, Guid.NewGuid(), null);
            }
        }
    }
    #endif


    var replacing = string.Compare (extra, "OR REPLACE", StringComparison.OrdinalIgnoreCase) == 0;

    var cols = replacing ? map.InsertOrReplaceColumns : map.InsertColumns;
    var vals = new object[cols.Length];
    for (var i = 0; i < vals.Length; i++) {
        vals [i] = cols [i].GetValue (obj);
    }

    var insertCmd = map.GetInsertCommand (this, extra);
    var count = insertCmd.ExecuteNonQuery (vals);

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

    return count;
}

Updated implementation.

public int Insert (object obj, string extra, Type objType)
{
    if (obj == null || objType == null) {
        return 0;
    }


    var map = GetMapping (objType);

    #if NETFX_CORE
    if (map.PK != null && map.PK.IsAutoGuid)
    {
        // no GetProperty so search our way up the inheritance chain till we find it
        PropertyInfo prop;
        while (objType != null)
        {
            var info = objType.GetTypeInfo();
            prop = info.GetDeclaredProperty(map.PK.PropertyName);
            if (prop != null) 
            {
                if (prop.GetValue(obj, null).Equals(Guid.Empty))
                {
                    prop.SetValue(obj, Guid.NewGuid(), null);
                }
                break; 
            }

            objType = info.BaseType;
        }
    }
    #else
    if (map.PK != null && map.PK.IsAutoGuid) {
        var prop = objType.GetProperty(map.PK.PropertyName);
        if (prop != null) {
            if (prop.GetValue(obj, null).Equals(Guid.Empty)) {
                prop.SetValue(obj, Guid.NewGuid(), null);
            }
        }
    }
    #endif


    var replacing = string.Compare (extra, "OR REPLACE", StringComparison.OrdinalIgnoreCase) == 0;

    var cols = replacing ? map.InsertOrReplaceColumns : map.InsertColumns;
    var vals = new object[cols.Length];
    for (var i = 0; i < vals.Length; i++) {
        vals [i] = cols [i].GetValue (obj);
    }

    var insertCmd = map.GetInsertCommand (this, extra);
    var count = insertCmd.ExecuteNonQuery (vals);
    long id = 0;    //New line
    if (map.HasAutoIncPK)
    {
        id = SQLite3.LastInsertRowid (Handle);  //Updated line
        map.SetAutoIncPK (obj, id);
    }

    //Updated lines
    //return count; //count is row affected, id is primary key
    return (int)id;
    //Updated lines
}

I need to do this: Insert a record in a table and get the Primary Key of the table IMMEDIATELY for this record. Above code, I use db.Insert(newOrder), then Int Id = newOrder.SId where SId is the primary Key in that table, is this method correct? — MilkBottle, Oct 09 '13 at 09:39
If you use my solution & `var ID = db.Insert(newOrder);` then ID will be `SId`, that's your primary key & auto increment column. — Farhan Ghumra, Oct 09 '13 at 10:00
I think the ID in var ID = db.Insert(newOrder) is Not the Primary Key as it always return 1. I tried this Int Id = newOrder.SId, where Id is different each time I create a new record. Can you confirm this? Thanks :) — MilkBottle, Oct 09 '13 at 10:50
It is Ok to use this Int Id = newOrder.SId Will this be any different than your solution . — MilkBottle, Oct 10 '13 at 08:19
No it's same, but if you use my solution then you don't have to write line `Id = newOrder.SId` because it will return the ID. — Farhan Ghumra, Oct 10 '13 at 08:28
@Xyroid i am not able to find any procedure on how to edit nuget packages /libraries , simple right clicking and goto implementation is not briniging up the metheods only properties.Where to find SQLite.cs file ? — Zany, Sep 16 '17 at 07:00

How to get the last row Id after insert data into table for SQLite

2 Answers2