question

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.