This project is read-only.

Insert, delete and update using associative table

Mar 10, 2010 at 3:36 AM
Edited Mar 10, 2010 at 3:38 AM

What would be the best approach to insert, delete or update when using associative tables? i.e when we have a Member table, Address table and Member_Address associative table. I am using the below code to persist the object

 

            // Member item
            MemberCollection mcoll = new MemberCollection();
            Member mom = mcoll.NewItem();
            mom.Firstname = "Susheela";
            mom.Lastname = "Girigowda";
            mom.Email = "sgirigowda@hotmail.com";
            mcoll.AddItem(mom);
            // Member address
            // MemberAddressCollection nm = (MemberAddressCollection)mom.ParentCollection.SubDomain[NHydrate.Sirrius.Business.Collections.MemberAddressCollection];
            MemberAddressCollection nm = new MemberAddressCollection();
            MemberAddress momMac = nm.NewItem();
            // Address item
            // AddressCollection momCollAdd = (AddressCollection)momMac.ParentCollection.SubDomain[NHydrate.Sirrius.Business.Collections.AddressCollection];
            AddressCollection momCollAdd = new AddressCollection();
            Address momAddress = momCollAdd.NewItem();
            momAddress.Addressline = "16971 Point Pleasant Ln";
            momAddress.City = "Dumfries";
            momAddress.State = "VA";
            momAddress.Zipcode = "22026";
            momCollAdd.AddItem(momAddress);
            momMac.MemberItem = mom;
            momMac.AddressItem = momAddress;

 

            // Member item

            MemberCollection mcoll = new MemberCollection();

            Member mom = mcoll.NewItem();

            mom.Firstname = "XXXX";

            mom.Lastname = "VVVVVV";

            mom.Email = "AACCC@CCCCSSSS.com";

            mcoll.AddItem(mom);

 

            // Member address

            // MemberAddressCollection nm = (MemberAddressCollection)mom.ParentCollection.SubDomain[NHydrate.Sirrius.Business.Collections.MemberAddressCollection];

            MemberAddressCollection nm = new MemberAddressCollection();

            MemberAddress momMac = nm.NewItem();

 

            // Address item

            // AddressCollection momCollAdd = (AddressCollection)momMac.ParentCollection.SubDomain[NHydrate.Sirrius.Business.Collections.AddressCollection];

            AddressCollection momCollAdd = new AddressCollection();

            Address momAddress = momCollAdd.NewItem();

            momAddress.Addressline = "AAAAADDDDDFFFF";

            momAddress.City = "DFDDFDD";

            momAddress.State = "FF";

            momAddress.Zipcode = "12312";

            momCollAdd.AddItem(momAddress);

 

            momMac.MemberItem = mom;

            momMac.AddressItem = momAddress;

 

nm.AddItem(momMac);

            nm.Persist();

 

 

The above works but is there a simple syntax to it. How would one go about delete the above object graph without have to parse the child objects and deleting then and then delete the parent object.

 

Mar 11, 2010 at 8:14 PM

The above code can reduce to this.

 

// Member item
MemberCollection memberCollection = new MemberCollection();
Member member = mcoll.NewItem();
member.Firstname = "XXXX";
member.Lastname = "VVVVVV";
member.Email = "AACCC@CCCCSSSS.com";
memberCollection.AddItem(member);
// Address item
AddressCollection addressCollection = memberCollection.SubDomain.GetCollection<AddressCollection>();
Address address = momCollAdd.NewItem();
address.Addressline = "AAAAADDDDDFFFF";
address.City = "DFDDFDD";
address.State = "FF";
address.Zipcode = "12312";
addressCollection.AddItem(address);
member.AddressItem = address;
memberCollection.Persist();

// Member item
MemberCollection memberCollection = new MemberCollection();

Member member = mcoll.NewItem();
member.Firstname = "XXXX";
member.Lastname = "VVVVVV";
member.Email = "AACCC@CCCCSSSS.com";
memberCollection.AddItem(member);

// Address item
AddressCollection addressCollection = memberCollection.SubDomain.GetCollection<AddressCollection>();
Address address = momCollAdd.NewItem();
address.Addressline = "AAAAADDDDDFFFF";
address.City = "DFDDFDD";
address.State = "FF";
address.Zipcode = "12312";
addressCollection.AddItem(address);

//Link the two objects
member.AddressItem = address;
memberCollection.Persist();

You should be able to delete a connection by setting the property to null. Below I load a member and set its AddressItem to null and save. This does not remove either the member or address item just the connection between them. If you wish to remove the actual object, call its "Delete" method.
Member member = Member.SelectUsingPK(1);
member.AddressItem = null;
member.ParentCollection.Persist();

 

Mar 13, 2010 at 3:08 AM

Thanks for the report, I seem to have trouble deleting though. In the above example MemberAddress is the join table. The member object holds a reference to a list of address object, so I can's seem to set member.addressitem = null? is there something wrong I am doing? Also how would I delete the object graph completely i.e. if I call the delete on the member object doesn't seem to delete the related objects. Any help will be appreaciated.

 

Mar 13, 2010 at 3:09 AM

Is there a clean way to delete the child dependencies.

So with the above example, is there a way I could do

Member member = Member.SelectUsingPK(1);

member.Delete

And then the delete would know to go and remove the address as well? 

Mar 13, 2010 at 9:52 PM

You must call the Delete method on an object to remove it from the database. When you set "member.AddressItem = null" you are really removing the relation not the object. This assumes that the foreign key "Member.address_id" in the Member table can be null. So when you change the field "Member.address_id" or the object reference "Member.AddressItem" and then save it like "member.Persist()" it will break the relation between the two objects in the database but will NOT remove the address row from the address table. You must call the Delete method on the Address item to remove it. You could do this.

member.AddressItem.Delete();
member.ParentCollection.Persist();

The reason Persist is called on the parent collection (and not the member object) is that you are saving changing to more than one object (Member and Address) so you must really save the whole subdomain. These lines are functionality equivalent. 

member.ParentCollection.Persist();
member.ParentCollection.Subdomain.Persist(); 

There are only 2 kinds of saves: an object save and a subdomain save.  If you call "object.Persist()" it will save just that one object to the database. You can get errors if you use this the wrong way of course like making many dependent changes in a subdomain and then saving just the one object when there are dependent objects on it. When you call the Persist method on a subdomain the dependencies are calculated and persisted in order.

Mar 14, 2010 at 2:49 AM

I ended up doing this  and it worked. It would be great to have some explanation on ParentCollection and Subdomain they are a little confusing. Thanks for your time.

 

// Delete
            var dMc = MemberCollection.SelectUsingPK(int.Parse(txtFName.Text));
            var dM = dMc[0];
            foreach (Address a in dM.AddressList)
            {
                a.Delete();
            }
            dM.Delete();
            dM.ParentCollection.Persist();

// Delete

            var dMc = MemberCollection.SelectUsingPK(int.Parse(txtFName.Text));

            var dM = dMc[0];

            foreach (Address a in dM.AddressList)

            {

                a.Delete();

            }

            dM.Delete();

            dM.ParentCollection.Persist();

 

Mar 14, 2010 at 4:50 AM

I am sorry it is a bit confusing. I have added this WIKI page to clarify.

http://nhydrate.codeplex.com/wikipage?title=Subdomains%20Explained

Mar 29, 2010 at 8:55 AM
Edited Mar 29, 2010 at 10:20 AM

What is the

momMac.MemberItem = mom;
momMac.AddressItem = momAddress;

MemberItem and the AddressItem? Are this columns in the tables?

In my code, i can't call it!

Mar 29, 2010 at 1:29 PM

MemberItem is the member table

AddressItem is the address table

MemberAddressItem - Associative table (Join table)