Object Oriented Architecture .... Lazy loading the child objects in VS 2008 using C#

Hi,

I have a "Person" class with "Orders" as the child object. Now "Orders" represents which is of type "List<OrderType>". When page is loading then i am loading ALL the child objects for each parent object. Here is the sample code:

    /// <summary>
    /// Load all records from database
    /// </summary>
    /// <returns></returns>
    public List<Person> Load()
    {
        List<Person> listPerson = new List<Person>();

        SqlConnection connPerson = new SqlConnection(connStr);
        connPerson.Open();
        SqlCommand cmdPerson = new SqlCommand("LoadPerson", connPerson);
        cmdPerson.CommandType = CommandType.StoredProcedure;

        try
        {
            SqlDataReader drPerson = cmdPerson.ExecuteReader(CommandBehavior.CloseConnection);

            while (drPerson.Read())
            {
                Person p = new Person();

                p.PersonID = Convert.ToInt32(drPerson["PersonID"]);
                p.FirstName = Convert.ToString(drPerson["FirstName"]);
                p.LastName = Convert.ToString(drPerson["LastName"]);
                p.Age = Convert.ToInt32(drPerson["Age"]);

                List<OrderType> listOrder = new List<OrderType>();

                SqlConnection connOrder = new SqlConnection(connStr);
                connOrder.Open();
                SqlCommand cmdOrder = new SqlCommand("LoadOrder", connOrder);
                cmdOrder.CommandType = CommandType.StoredProcedure;

                try
                {
                    cmdOrder.Parameters.AddWithValue("@personID", p.PersonID);

                    SqlDataReader drOrder = cmdOrder.ExecuteReader(CommandBehavior.CloseConnection);

                    while (drOrder.Read())
                    {
                        OrderType o = new OrderType();

                        o.OrderID = Convert.ToInt32(drOrder["OrderID"]);
                        o.PersonID = Convert.ToInt32(drOrder["PersonID"]);
                        o.OrderName = Convert.ToString(drOrder["OrderName"]);

                        listOrder.Add(o);
                    }
                }
                catch
                {
                    throw;
                }
                finally
                {
                    cmdOrder.Dispose();
                    connOrder.Close();
                    connOrder.Dispose();
                }
               
                p.Orders = listOrder;

                listPerson.Add(p);
            }
        }
        catch
        {
            throw;
        }
        finally
        {
            cmdPerson.Dispose();
            connPerson.Close();
            connPerson.Dispose();
        }

        return listPerson;
    }

When there are too many obejcts then i am getting very slow response onto UI as it has to loop through ALL the parent and child objects even though they are not necessary for the first time. For the first time i need ONLY the parent objects. So i want to do LAZY loading of the "Orders" which is of type "List<OrderType>".

Can you please let me know how can i do it ? Some how i need to load the objects when i require them but not at the page load. Otherwise my page will not respond if there are too may objects to load. Can you please provide me the code in C# ?

Thanks
milani_lucieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mez4343Commented:
If you only want to load the Orders data based on a selection of 1 Parent object then I would recommend a Master/Detail type of page design. In this example, the Products would be the parent data grid which would equate to Person object in your application.

http://www.asp.net/data-access/tutorials/master-detail-using-a-selectable-master-gridview-with-a-details-detailview-cs
chandra_darbhaCommented:
In the definition of Orders Property in the Person class should look like this

 
public class Person
    {
        private List<OrderType> _orders = null;

        public List<OrderType> Orders
        {
            get
            {
                if (_orders == null)
                {

                    SqlConnection connOrder = new SqlConnection(connStr);
                    connOrder.Open();
                    SqlCommand cmdOrder = new SqlCommand("LoadOrder", connOrder);
                    cmdOrder.CommandType = CommandType.StoredProcedure;

                    try
                    {
                        cmdOrder.Parameters.AddWithValue("@personID", p.PersonID);

                        SqlDataReader drOrder = cmdOrder.ExecuteReader(CommandBehavior.CloseConnection);

                        while (drOrder.Read())
                        {
                            OrderType o = new OrderType();

                            o.OrderID = Convert.ToInt32(drOrder["OrderID"]);
                            o.PersonID = Convert.ToInt32(drOrder["PersonID"]);
                            o.OrderName = Convert.ToString(drOrder["OrderName"]);

                            _orders.Add(o);
                        }
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        cmdOrder.Dispose();
                        connOrder.Close();
                        connOrder.Dispose();
                    }
                }
                else
                {
                    return _orders;
                }
            }
        }
    }

Open in new window


and you need not initialize p.Orders in the above code. The p.Orders will get populated the first time it is called.

Thanks,
Chandra
chandra_darbhaCommented:
Sorry the else condition was a type... here is the correct code..

 
public class Person
    {
        private List<OrderType> _orders = null;

        public List<OrderType> Orders
        {
            get
            {
                if (_orders == null)
                {

                    SqlConnection connOrder = new SqlConnection(connStr);
                    connOrder.Open();
                    SqlCommand cmdOrder = new SqlCommand("LoadOrder", connOrder);
                    cmdOrder.CommandType = CommandType.StoredProcedure;

                    try
                    {
                        cmdOrder.Parameters.AddWithValue("@personID", p.PersonID);

                        SqlDataReader drOrder = cmdOrder.ExecuteReader(CommandBehavior.CloseConnection);

                        while (drOrder.Read())
                        {
                            OrderType o = new OrderType();

                            o.OrderID = Convert.ToInt32(drOrder["OrderID"]);
                            o.PersonID = Convert.ToInt32(drOrder["PersonID"]);
                            o.OrderName = Convert.ToString(drOrder["OrderName"]);

                            _orders.Add(o);
                        }
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        cmdOrder.Dispose();
                        connOrder.Close();
                        connOrder.Dispose();
                    }
                }
                
                return _orders;                
            }
        }
    }

Open in new window


Thanks,
Chandra

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chandra_darbhaCommented:
Missed again :-). Add "_orders = new List<OrderType>();" as first statement in the if condition.

Thanks,
Chandra
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.