nHydrate: persisting data with multi level table relation

Sep 2, 2010 at 2:53 PM

Hi,

I am working on one project where I have to persist data with multi level table relations. Let me simply explain the scenario with table design.

Example:

Customer table [ columns: customer_id (PK) int <Identity>, customer_name varchar(50)]

Orders table [ columns: order_id (PK) int <Identity>, order_desc varchar(100), customer_id (FK)]

OrderItems table [columns: item_id (PK) int <Identity>, item_desc varchar(100), item_price decimal(18,6), order_id (FK)]

See above mentioned 3 tables Customer, Orders and OrderItems along with PK->FK relation. Let me give a simple example. I create Customer then Create an Order (one to many) and for each Order I create Order Items (again one to many)

So here is how I would like to persist my Customer -> Order -> Items info

Customer1 = C1, Order = O1, Item = I1, I2, I3

My code goes like this

           string cnstr = "server=SQL01; database=MyDB; Integrated Security=SSPI;";
            MyComp.CUSTDAL.Business.ConfigurationValues.GetInstance().ConnectionString = cnstr;

            CustomerCollection cc = new CustomerCollection();
            Customer c = cc.NewItem();
            c.customer_name = "C1";
            cc.AddItem(c);

            OrderCollection oc = new OrderCollection();
            Order o = oc.NewItem();
            o.order_desc = "O1";
            oc.AddItem(o);

            ItemCollection ic = new ItemCollection();
            Item itm = ic.NewItem();
            itm.item_name = "I1";
            itm.item_price = 400.00M;
            ic.AddItem(itm);

//-- Add OrderItems to the Order 

o.ItemList.AddRange(ic.ToList<Item>().GetRange(0, ic.Count));

//-- Add Orders to the Customer
            c.OrderList.AddRange(oc.ToList<Order>().GetRange(0, oc.Count));
           
            cc.Persist();
            //cc.SubDomain.Persist();

            //System.Data.DataRelationCollection dr = cc.SubDomain.Relations;

In the above code I am persisting data at Customer leve (The root or first level) but it is persisting only Customer and Item.

If I do perist at Order level (i.e. oo.Persist() or oo.SubDomain.Perisist()) then it persit only Order and its Item.

With this, it looks like nHydrate is not doing data persisting with multi level table relation. Or Am I missing something?

If you have any clue or resolution please help.

 

Thanks

Manish

 

 

Coordinator
Sep 5, 2010 at 7:10 PM
Edited Sep 5, 2010 at 7:12 PM

When you create collection business objects an implicit subdomain is created that is a container used to persist in one database transaction. Like the following code.

CustomerCollection cc = new CustomerCollection();

When you declare the Customer, Orders, and OrderItems collections, you created 3 different containers and you will need to persist all three separately and in different transactions. I think what you want to do is add all objects to 1 container and persist them all at one time in one transaction. You need to modify you code a little to do this. Notice I tied the objects together by actually setting the foreign keys.

			CustomerCollection cc = new CustomerCollection();
Customer c = cc.NewItem();
c.customer_name = "C1";
cc.AddItem(c);

//Now add the order collection to the same container as the customer collection
OrderCollection oc = cc.Subdomain.GetCollection<OrderCollection>();
Order o = oc.NewItem();
o.customer_id = c.customer_id;
o.order_desc = "O1";
oc.AddItem(o);

//Now add the item collection to the same container as the customer collection
ItemCollection ic = cc.Subdomain.GetCollection<ItemCollection>();
Item itm = ic.NewItem();
itm.order_id = o.order_id;
itm.item_name = "I1";
itm.item_price = 400.00M;
ic.AddItem(itm);

//Save all objects in one DB transaction
cc.Persist();