sqlite in Xamarin.Froms how to Update individual record in Local DB-CodePudding

I am trying to update individual records in sqlite database. I know how to Insert and delete records. I'd like to Update an individual record in a similar way to how I am deleting an individual record below. This uses a linq statement to get the record by Asset ID. I'd then like to pass my data to this to update.

I've also included how I insert a new record for reference. Does anybody have an example that they could share?

Delete an existing record

using (SQLiteConnection localconn = new SQLiteConnection(App.FilePath))
         {
            localconn.CreateTable<Road_Inspections>();
            localconn.Table<Road_Inspections>().Where(x => x.Unique_ID == unique_ID).Delete();
         }

Insert new record

        Road_Inspections lri = new Road_Inspections()
                {
                    ID = id,
                    Road_ID = Road_ID.Text.ToString(),
                    Asset_ID = Asset_ID.Text.ToString(),
                    Defect_Type = txtDefectType.Text.ToString(),
                    Response = txtResponse.Text.ToString(),
                    Inspection_Date = DateTime.Now,
                    
                };

                using (SQLiteConnection conn = new SQLiteConnection(App.FilePath))
                {
                    conn.CreateTable<Road_Inspections>();
                    int rowsAdded = conn.Insert(lri);
                    await DisplayAlert("Success", "Inspeciton Saved to Device", "OK");
                }

CodePudding user response：

You need a primary Key or Id in your object Announcement to identify your unique object in your database, for example:

[PrimaryKey, AutoIncrement]
public int Id { get; set; }

Since you want to update you have to get the original entry from the database first. And then you can edit and update it. So, you don't need to delete it before you insert a new one.

In xamarin form you can use nuget sqlite-net-pcl to achieve this.

Please refer to the following code:

 public Task<int> SaveItemAsync(TodoItem item)
    {
        if (item.ID != 0)
        {
            return Database.UpdateAsync(item);
        }
        else
        {
            return Database.InsertAsync(item);
        }
    }

For more details,you can check: https://docs.microsoft.com/en-us/xamarin/xamarin-forms/data-cloud/data/databases .

And there is a sample included in above document, you can check it here:https://docs.microsoft.com/en-us/samples/xamarin/xamarin-forms-samples/todo/