How to delete data from SQLite database in Xamarin

Question

This is my DisplayData.xaml:


   <ContentPage.Content>
        <StackLayout>
            <ListView x:Name="RecordList"
                      ItemSelected="OnItemSelected">
                <ListView.ItemTemplate>
                    <DataTemplate>
                        <ViewCell>
                            <StackLayout Orientation="Horizontal" 
                                         HorizontalOptions="FillAndExpand"
                                         Margin="20, 10, 20, 0" >
                                <Label Text="{Binding Text}" 
                                       HorizontalOptions="StartAndExpand" 
                                       TextColor="WhiteSmoke"/>                                                                                                           
                            </StackLayout>
                        </ViewCell>
                    </DataTemplate>
                </ListView.ItemTemplate>
            </ListView>
            <Entry x:Name="TextEntry"
                   Placeholder="hehe"
                   IsVisible="False"/>
            <Entry x:Name="IDEntry"
                   Placeholder="hehe"
                   IsVisible="False"/>
        </StackLayout>
    </ContentPage.Content>

This is my DisplayData.xaml.cs:


    public partial class DisplayData : ContentPage
    {
        string _dbPath = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "myDatabase.db3");
        SpeechRecTable _speech = new SpeechRecTable();

        public DisplayData()
        {
            InitializeComponent();           
        }

        protected override void OnAppearing()
        {
            base.OnAppearing();
            var db = new SQLiteConnection(_dbPath);
            RecordList.ItemsSource = db.Table<SpeechRecTable>().OrderBy(x => x.Text).ToList();
        }

        private void OnItemSelected(object sender, SelectedItemChangedEventArgs e)
        {
            _speech = (SpeechRecTable)e.SelectedItem;
            TextEntry.Text = _speech.Text;
            IDEntry.Text = _speech.Id.ToString();

            Device.BeginInvokeOnMainThread(async () =>
            {
                await Navigation.PushAsync(new RecordContent(TextEntry.Text, IDEntry.Text));
            });            
        }
    }

When a listview Item is selected it looks like this on my RecordContent.xaml:

When I click my delete button it does not delete that record, this the code for delete:


        private async void BtnDelete_Clicked(object sender, EventArgs e)
        {
            var db = new SQLiteConnection(_dbPath);            
            db.Table<SpeechRecTable>().Delete(x => x.Id == _speech.Id);
            await Navigation.PopAsync();            
        }

even if I use:


db.Delete<SpeechRecTable>(Id);

instead of:


db.Table<SpeechRecTable>().Delete(x => x.Id == _speech.Id);

The Item will still not be deleted, and most of the tutorials that or guides that I have encountered has only one view page with the add,delete, and update button. I am trying to separate the delete and update button, my update button works just fine, but the delete is not working, it just immediately execute this: await Navigation.PopAsync(); and then nothing happens.

You are not using Async methods, which means you need to commit your changes before you make other changes. Switch to use SQLiteAsyncConnection and call async functions each time you update data. SQLite package nice handles commits etc when you use async methods, otherwise you have to do it manually. Also have a look at the logs, cuz you might be getting exception when you run Delete. This could narrrow it down more to what is your problem. — Woj

Hacki Hacki · Accepted Answer · 2020-01-28T00:33:22

I have already solved my problem, by using this:


         private void BtnDelete_Clicked(object sender, EventArgs e)
         {
            Device.BeginInvokeOnMainThread(async () =>
            {
                var result = await this.DisplayAlert("Delete Item", "Are you sure that you want to DELETE this record?", "Yes", "No");

                if (result)
                {
                    var db = new SQLiteConnection(_dbPath);
                    _speech.Id = Convert.ToInt32(DataIdEntry.Text);
                    db.Table<SpeechRecTable>().Delete(x => x.Id == _speech.Id);
                    await Navigation.PopAsync();
                }

                else
                {

                }
            });                      
        }

How to delete data from SQLite database in Xamarin

1 Answers