Crosslight SQLite Insert

yudi — Wed, 02 Mar 2016 02:31:53 GMT

Deeply apologize for the delay in sending this.

I’m currently working on this ticket and need more time to provide you with solution, suggestion, or sample.

I’ll get back to you as soon as possible.

Edited on March 6, 2016 11:30 PM
Reason: Update recent progress

When I insert an object of this type with a synchronous connection the Insert method always returns 1. This is definitely not the primary key. It looks like it returns the number of affected records...

Yes, you are correct. The Insert() method returns the number of inserted rows. For example, in the following snippet code, the value of count will be 2.

List<Item> InsertNewItems = new List<Item>();

InsertNewItems.Add(
    new ITemplate()
    {
        ItemId = -1,
        CategoryID = 2,
        Name = "New Item 1",
        Quantity = 3,
        Price = 400,
        Description = "Description of New Item 1"
    }
);

InsertNewItems.Add(
    new ITemplate()
    {
        ItemId = -1,
        CategoryID = 2,
        Name = "New Item 2",
        Quantity = 4,
        Price = 500,
        Description = "Description of New Item 2"
    }
);

int count = this.DB.InsertAll(InsertNewItems);

How can I retrieve the primary key of a newly inserted record?...

After invoke Insert(object item); or InsertAll(IEnumerable items), the primary key of newly inserted record will be automatically updated. For example:

Items newItem = new Item()
{
    ItemId = -1,
    CategoryId = 2,
    Name = "New Item",
    Quantity = 3,
    Price = 100,
    Description = "New Item Description"
};

this.SyncDB.Insert(newItem);

Debug.Writeline("Record with Id : " + newItem.ItemId.ToString() + " inserted.");

What about InsertAsync? How does it work here?...

You can invoke InsertAsync(object item) to insert the specified item asynchronously. Please note that the AsyncDb property is of ISQLiteAsyncConnection such as shown in the following:

#region Fields

private ISQLiteAsyncConnection _asyncDB;

#endregion

#region Properties

protected ISQLiteAsyncConnection AsyncDB
{
    get { return _asyncDB; }
    set
    {
        if (_asyncDB != value)
        {
            _asyncDB = value;
            this.OnPropertyChanged("AsyncDB");
        }
    }
}

#endregion

#region Constructors

public SimpleViewModel()
{
    string dbName = "SQLiteBasic.db3";
            
    ILocalStorageService storageService = ServiceProvider.GetService<ILocalStorageService>();
    IActivatorService activatorService = ServiceProvider.GetService<IActivatorService>();
    var factory = activatorService.CreateInstance<Func<string, ISQLiteAsyncConnection>>();

    this.AsyncDB = factory(storageService.GetFilePath(dbName, LocalFolderKind.Data));
}

#endregion

#region Methods

private async void ExecuteInsert(object parameter)
{
    Item item = new Item();
    item.ItemId = -100;
    item.CategoryId = (this.Items.Count % 3)+1;
    item.Name = "Item " + Convert.ToString(this.Items.Count + 1);
    item.Quantity = 100;
    item.Price = 100000;
    item.Description = "Description Item " + Convert.ToString(this.Items.Count + 1);

    await this.Db.InsertAsync(item);
    this.LoadItem();
    this.ToastPresenter.Show("Record "+item.Name.ToString()+" Inserted");

    Debug.WriteLine("Record "+item.Name.ToString()+" Inserted");
}

#endregion

For more detail, please kindly check Crosslight SQLiteBasic sample.

Hope this helps.

Crosslight SQLite Insert

thomas.albert@tea-net.ch — Thu, 25 Feb 2016 08:14:39 GMT

Any feedback?

Crosslight SQLite Insert

thomas.albert@tea-net.ch — Sat, 13 Feb 2016 09:59:15 GMT

Hello Crosslight Support,

I have a question about the Insert method of SQLite. The belonging documentation says "Inserts the given item and retrieves its auto incremented primary key if it has one.".

I have a model with a property Id of type int. The property has the attributes [PrimaryKey] and [AutoIncrement]. When I insert an object of this type with a synchronous connection the Insert method always returns 1. This is definitely not the primary key. It looks like it returns the number of affected records.

How can I retrieve the primary key of a newly inserted record?

What if I run the insert within a transaction? How can I retrieve the primary key of a newly inserted record in this scenario?

What about InsertAsync? How does it work here?

Thanks for clarifying.

Best regards,

Thomas

Intersoft Community - Crosslight - Crosslight SQLite Insert

Crosslight SQLite Insert

Crosslight SQLite Insert

Crosslight SQLite Insert