首页 文章

如何在将数据插入SQLite表后获取最后一行ID [复制]

提问于
浏览 次
4

这个问题在这里已有答案：

我正在使用SQLite和SQLite-Net Wrapper for WinRT app . 其他平台可能有SQLite，但实现可能会有所不同，例如使用SQLite-Net api .

如何在插入SQLite后立即获取最后一行ID？谢谢

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

     }
c# sqlite windows-phone-7 winrt-xaml

2 回答

  • 1

    在SQLite-net中， Insert 方法返回插入的行数（SQLite.cs） . 因此，如果您希望它返回最后一行ID，您可以更新它以执行此操作 .

    目前的实施 .

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

    更新实施 .

    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
}
    回复于
  • 7

    你的连接上有ExecuteScalar方法吗？然后用

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

相关问题