How to delete a list of Item in and Item SQLite Xamarin Forms

Question

Hi Xamarin Community,

I have a question about SQLite, I have a list of item, and inside each item I have a list of items, my question is when I delete the main item that contains a list of item, the items inside that items doesnt deleted,

I need help with the process of deletingwithchildre or something like that, there is a pdf with picture explaining a little more

Thanks
Regards

125726-prubea.pdf

Answer 1

Hello,​

Welcome to our Microsoft Q&A platform!

If you want to achieve the cascade add, insert and delete. sqlite-net-pcl do not provide this function.

You can search xamarin forms SQLite Extensions keywords, you will find a nuget package support cascade operations.

For example, I have One to many relationships table(One Customer have serveral Orders) like following code. I add CascadeOperations = CascadeOperation.CascadeInsert| CascadeOperation.CascadeRead | CascadeOperation.CascadeDelete property above the Orders.

   public class Customer  
       {  
           [PrimaryKey, AutoIncrement]  
           public int Id { get; set; }  
     
           public string Name { get; set; }  
     
           [OneToMany(CascadeOperations = CascadeOperation.CascadeInsert| CascadeOperation.CascadeRead | CascadeOperation.CascadeDelete)]  
           public Order[] Orders { get; set; }  
       }  
     
     
       public class Order  
       {  
           [PrimaryKey, AutoIncrement]  
           public int Id { get; set; }  
     
           public float Amount { get; set; }  
     
           public DateTime Date { get; set; }  
     
           [ForeignKey(typeof(Customer))]  
           public int CustomerId { get; set; }  
       }  

If I create two tables and records to the SQLite DB in OnAppearing() method. Button_Clicked method to get the first customer record. Button_Clicked_1 delete the customer record (have cascade delete operation). Button_Clicked_2 check if have customer 1's record when I execute the cascade delete operation.

   namespace SQLiteCascadeDelete  
   {  
       public partial class MainPage : ContentPage  
       {  
           public MainPage()  
           {  
               InitializeComponent();  
           }  
           SQLiteConnection db;  
           protected override void OnAppearing()  
           {  
               base.OnAppearing();  
                db = Utils.CreateConnection();  
     
     
     
     
               db.CreateTable<Customer>();  
               db.CreateTable<Order>();  
     
               var customer1 = new Customer()  
               {  
                   Name = "Test1",  
                   Orders = new[]  
                   {  
                       new Order { Amount = 25.7f, Date = new DateTime(2014, 5, 15, 11, 30, 15) },  
                       new Order { Amount = 15.2f, Date = new DateTime(2014, 3, 7, 13, 59, 1) },  
                       new Order { Amount = 0.5f, Date = new DateTime(2014, 4, 5, 7, 3, 0) },  
                       new Order { Amount = 106.6f, Date = new DateTime(2014, 7, 20, 21, 20, 24) },  
                       new Order { Amount = 98f, Date = new DateTime(2014, 02, 1, 22, 31, 7) }  
                   }  
               };  
     
     
     
               var customer2 = new Customer()  
               {  
                   Name = "Test2",  
                   Orders = new[]  
                  {  
     
                       new Order { Amount = 110.5f, Date = new DateTime(2014, 4, 5, 7, 3, 0) },  
                       new Order { Amount = 102226.6f, Date = new DateTime(2014, 7, 20, 21, 20, 24) },  
     
                   }  
               };  
     
     
     
     
               db.InsertWithChildren(customer1, recursive: true);  
     
               db.InsertWithChildren(customer2, recursive: true);  
           }  
     
           private void Button_Clicked(object sender, EventArgs e)  
           {  
              // var db = Utils.CreateConnection();  
     
               
     
               Customer customer= db.GetWithChildren<Customer>("1", recursive: true);  
     
               var name=customer.Name;  
               var ordd = customer.Orders;  
               string ordersting = "";  
               foreach (Order item in ordd)  
               {  
                   ordersting += "Amount " + item.Amount + "Date: " + item.Date+ "\n";  
               }  
     
                 
               res.Text = "Name:" + name + "\n Orders \n" + ordersting;  
           }  
     
           private void Button_Clicked_1(object sender, EventArgs e)  
           {  
     
               Customer customer = db.GetWithChildren<Customer>("1", recursive: true);  
     
                
               db.Delete(customer, recursive: true);  
           }  
     
           private void Button_Clicked_2(object sender, EventArgs e)  
           {  
               // var db = Utils.CreateConnection();  
               Order res=new Order();  
               try  
               {  
                    res = db.Get<Order>(1);  
               }  
               catch (Exception)  
               {  
     
                  //it will have exception, because no this order.  
               }  
               finally  
               {  
                   if (res.Id>0)  
                   {  
                       OrderRes.Text = "Amount " + res.Amount + "Date: " + res.Date + "\n"; ;  
                   }  
                   else  
                   {  
                       OrderRes.Text = "";  
                   }  
     
               }  
                 
             
                
           }  
       }  
      
   }  

Here is xaml layout.

   <?xml version="1.0" encoding="utf-8" ?>  
   <ContentPage xmlns="http://xamarin.com/schemas/2014/forms"  
                xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"  
                x:Class="SQLiteCascadeDelete.MainPage">  
     
       <StackLayout>  
           <Button Text="readSpecific Items" Clicked="Button_Clicked"></Button>  
           <Label Text="" x:Name="res"></Label>  
     
           <Button Text="delete" Clicked="Button_Clicked_1"></Button>  
     
           <Button Text="readOrder Item" Clicked="Button_Clicked_2"></Button>  
           <Label Text="" x:Name="OrderRes"></Label>  
       </StackLayout>  
     
   </ContentPage>  

Here is Utils.cs.

   public class Utils  
       {  
           /// <summary>  
           /// Returns the proper database file path to initialize the SQLite connection.   
           /// </summary>  
       
     
           public const string DatabaseFilename = "TodoSQLite7.db3";  
     
           public const SQLite.SQLiteOpenFlags Flags =  
               // open the database in read/write mode  
               SQLite.SQLiteOpenFlags.ReadWrite |  
               // create the database if it doesn't exist  
               SQLite.SQLiteOpenFlags.Create |  
               // enable multi-threaded database access  
               SQLite.SQLiteOpenFlags.SharedCache;  
     
           public static string DatabaseFilePath  
           {  
               get  
               {  
                   var basePath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);  
                   return Path.Combine(basePath, DatabaseFilename);  
               }  
           }  
     
     
           public static SQLiteConnection CreateConnection()  
           {  
               #if PCL  
                           return new SQLiteConnection(new SQLitePlatformIOS(), DatabaseFilePath);  
               #else  
                           return new SQLiteConnection(DatabaseFilePath);  
               #endif  
           }  
       }  

Here is running screenshot.

Delete Before(click the readOrder Item button, there are a record, if the order table have this order):

Deleted(click the readOrder Item button,No record,No this record will have a exception. So I set a empty result. ):

Best Regards,

Leon Lu

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.