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

MilkBottle Published at Dev

MilkBottle

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

     }

Farhan Ghumra

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
}

Collected from the Internet

Please contact [email protected] to delete if infringement.