creating class structure in mvvm issues

Question

I have the following Classes:
Item

public class Item : INotifyPropertyChanged, IDataErrorInfo
{
    private int? id;
    public int? ID
    {
        get
        { return id; }
        set
        { id = value; }
    }

    private string name;
    public string Name
    {
        get
        { return name; }
        set
        {
            if (value != name)
            {
                ClearError("Name");
                if (string.IsNullOrEmpty(value) || value.Trim() == "")
                    SetError("Name", "Required Value");
                name = value;
            }
        }
    }
    private List<MedicineComposition> medicineCompositions;
    public List<MedicineComposition> MedicineCompositions
    {
        set { medicineCompositions = value; }
        get { return medicineCompositions; }
    }
}

MedicineComposition

public class MedicineComposition : INotifyPropertyChanged, IDataErrorInfo
{
    private int? id;
    public int? ID
    {
        get
        { return id; }
        set
        { id = value; }
    }

    private Item item;
    public Item Item
    {
        get
        { return item; }
        set
        {
            if (item != value)
            {
                ClearError("Item");
                if (value == null)
                    SetError("Item", "Required Value");
                item = value;
            }
        }
    }
    private Component component;
    public Component Component
    {
        get
        { return component; }
        set
        {
            if (component != value)
            {
                ClearError("Component");
                if (value == null)
                    SetError("Component", "Required Value");
                component = value;
            }
        }
    }
}

Component which has only id and Name
and the following functions that bring data from database and make the list of my objects: GetItems in Item Class

public static List<Item> GetAllItems
{
get
{
    List<Item> MyItems = new List<Item>();
    SqlConnection con = new SqlConnection(BaseDataBase.ConnectionString);
    SqlCommand com = new SqlCommand("sp_Get_All_Item", con);
    com.CommandType = System.Data.CommandType.StoredProcedure;
    try
    {
        con.Open();
        SqlDataReader rd = com.ExecuteReader();
        while (rd.Read())
        {
            Item i = new Item();
            if (!(rd["ID"] is DBNull))
                i.ID = System.Int32.Parse(rd["ID"].ToString());
            i.Name = rd["Name"].ToString();
            i.MedicineCompositions = MedicineComposition.GetAllByItem(i);

            MyItems.Add(i);
        }
        rd.Close();
    }
    catch
    {
        MyItems = null;
    }
    finally
    {
        con.Close();
    }
    return MyItems;
}

GetAllByItem in MedicalCompositions

public static List<MedicineComposition> GetAllByItem(Item i)
{
    List<MedicineComposition> MyMedicineCompositions = new List<MedicineComposition>();

    SqlConnection con = new SqlConnection(BaseDataBase.ConnectionString);
    SqlCommand com = new SqlCommand("sp_Get_ByItemID_MedicineComposition", con);
    com.CommandType = System.Data.CommandType.StoredProcedure;
    SqlParameter pr = new SqlParameter("@ID", i.ID);
    com.Parameters.Add(pr);
    try
    {
        con.Open();
        SqlDataReader rd = com.ExecuteReader();
        while (rd.Read())
        {
            MedicineComposition m = new MedicineComposition() { };
            if (!(rd["ID"] is DBNull))
                m.ID = Int32.Parse(rd["ID"].ToString());
            if (!(rd["ComponentID"] is DBNull))
                m.Component = Component.GetByID(Int32.Parse(rd["ComponentID"].ToString()));
            m.Item = i;
            MyMedicineCompositions.Add(m);
        }
        rd.Close();
    }
    catch
    {
        MyMedicineCompositions = null;
    }
    finally
    {
        con.Close();
    }
    return MyMedicineCompositions;
}

it's like to use mvvm because it let you deal with objects instead of datatable, but when i use the previous shape of class structure i have the following problems:

• i have at least 1000 records in Item Table in database so when i call GetAllItems i have slow performance especially when the database in not on local computer.
• i tried to load Items when splash screen on, it takes times but take medium performance
• on each update on Item table i should recall GetAllItems so slow back
  my questions is where is the problem that i have in creating class, and is this the best way to structure the class in mvvm

Answer 1

I dont think your user need to see all the 1000 items at a glance, not even the many thousand of composition and components related.

I situations like this I would:

1. Filter the data. Ask the user for the Item name, category or what else.
2. Delay load. At first load only the (filtered) Items. When the user select an Item switch to an "Item details" View and load the related data (composition and components).