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; }

     }

Solution 1

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
}

Solution 2

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

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

Comments

[Wirsing]

@juergend This is not PHP.

[MilkBottle]

This is using SQLite-Net in WinRT.

[Shafik Yaghmour]

I am a little puzzled that a C# question is being closed as a dup of a PHP question.

[MilkBottle]

I got the above error. What I did wrong? I am using SQLite-Net

[LS_ᴅᴇᴠ]

Don't you have to do something like ...ExecuteScalar("...", new [] object)?

[FunksMaName]

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?

[MilkBottle]

@FunksMaName, Thanks it works now.

[MilkBottle]

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?

[Farhan Ghumra]

If you use my solution & var ID = db.Insert(newOrder); then ID will be SId, that's your primary key & auto increment column.

[MilkBottle]

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 :)

[Farhan Ghumra]

Have you changed SQLite.cs according to my solution?

[MilkBottle]

It is Ok to use this Int Id = newOrder.SId Will this be any different than your solution .

[Farhan Ghumra]

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.

[Zany]

@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 ?