R – How to use “one to many” relations in SubSonic

foreign-key-relationshipsubsonic

What are the ways to tell SubSonic about the relationship (only foreign keys? Or other methods too)?
If I have (for example) a team object with related team members

** how do I access and update the team members from the team

** how do I update the team members? Does saving the team object saves the team members changes

** How do I add members to the team? Do I just create a new member, assign the team ID to the foreign key and save? Or is there a more object oriented way (e.g. team.Add(teamMember))

Best Answer

Subsonic code generation will read the foreign key relationships in the tables and create the required helper methods in the table classes. The Northwind Product class has a PrimaryKey relationship to the OrderDetail class. Subsonic generates the method

public Northwind.OrderDetailCollection OrderDetails()

to get the OrderDetail rows as an OrderDetailCollection. This is a BindingList that you can change as needed, and call SaveAll() to save the list. There is no deep saving, so saving the Product won't save related OrderDetail rows.

[Test]
public void Demo_Product_OrderDetails()
{
    Product product = new Product(3); // Read an existing row.
    OrderDetailCollection orderDetails = product.OrderDetails();
    Assert.IsTrue(orderDetails.Count == 12);
    foreach(OrderDetail orderDetail in orderDetails)
    {
        orderDetail.Discount -= 0; // Do something meaningful.
    }
    OrderDetail newDetail = new OrderDetail();
    newDetail.ProductID = 3;
    newDetail.OrderID = 10248;
    newDetail.UnitPrice = 7.00m;
    newDetail.Discount = 0.10f;
    newDetail.Quantity = 12;
    orderDetails.Add(newDetail);
    orderDetails.SaveAll();

    orderDetails = product.OrderDetails();
    Assert.IsTrue(orderDetails.Count == 13);

    OrderDetail.Destroy(newDetail.OrderID);

    orderDetails = product.OrderDetails();
    Assert.IsTrue(orderDetails.Count == 12);

}

Related Solutions

Saving related objects in SubSonic

(Order and OrderItem generated by SubSonic).
Order order = new Order();
//populate order details.


OrderItem item = new OrderItem();
//populate orderItem details.

item.Order = order;   //THIS LINE SETS THE PARENT OBJECT TO ABOVE ORDER

Just remember to wrap up in a transaction and call the save methods to commit this info to your database. You will have add a reference to the System.Transactions namespace in your project and then reference in your class.

e.g

   using (TransactionScope scope = new TransactionScope())
    {
        try
        {
            Order order = new Order();
            //populate order details.
            order.Save(); //Commit to DB


            OrderItem item = new OrderItem();
            //populate orderItem details.

            item.Order = order;   //THIS LINE SETS THE PARENT OBJECT TO ABOVE ORDER

            item.Save();  //Commit to DB

            //complete you transaction
            scope.Complete();

        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            throw ex;
        }
    }

Sql – When to use “ON UPDATE CASCADE”

It's true that if your primary key is just a identity value auto incremented, you would have no real use for ON UPDATE CASCADE.

However, let's say that your primary key is a 10 digit UPC bar code and because of expansion, you need to change it to a 13-digit UPC bar code. In that case, ON UPDATE CASCADE would allow you to change the primary key value and any tables that have foreign key references to the value will be changed accordingly.

In reference to #4, if you change the child ID to something that doesn't exist in the parent table (and you have referential integrity), you should get a foreign key error.

Best Answer

Related Solutions

Saving related objects in SubSonic

Sql – When to use “ON UPDATE CASCADE”

Related Topic