help speeding up some code with data access

I could use some guidance on a better way get get my data than I am using. This way is taking nearly 45 seconds to get the data.

here are my methods used to get the data

<code>
1. public List<BOM> GetBomList(string partno)
2. {
3.
4. List<BOM> bomList = new List<BOM>();
5. BOM spaBom = BOMDB.GetSpaBomParent(partno);
6. bomList.Add(spaBom);
7. //get spalevel1
8. List<BOM> level1List = BOMDB.GetSpaBomChild(spaBom.BomPartID);
9. foreach (BOM l1 in level1List)
10. {
11. bomList.Add(l1);
12. //getlevel2
13. List<BOM> level2List = BOMDB.GetSpaBomChild(l1.BomPartID);
14. foreach (BOM l2 in level2List)
15. {
16. bomList.Add(l2);
17. //getlevel 3
18. List<BOM> level3List = BOMDB.GetSpaBomChild(l2.BomPartID);
19. foreach (BOM l3 in level3List)
20. {
21. bomList.Add(l3);
22. //get level 4
23. List<BOM> level4List = BOMDB.GetSpaBomChild(l3.BomPartID);
24. foreach (BOM l4 in level4List)
25. {
26. bomList.Add(l4);
27. }
28. }
29. }
30.
31. }
32.
33. return bomList;
34. }

</code>

then my data access class is:

<code>
1. public static class BOMDB
2. {
3.
4. public static BOM GetSpaBomParent(string partno)
5. {
6. SqlConnection con = new SqlConnection(ConnectionString.getConnectionString());
7. SqlCommand cmd = new SqlCommand("dbo.BOSS3GetSpaBomLevel0", con);
8. cmd.CommandType = CommandType.StoredProcedure;
9. cmd.Parameters.Add("partno", SqlDbType.NVarChar).Value = partno;
10. BOM bom = new BOM();
11. SqlDataReader dr;
12. try
13. {
14. con.Open();
15. dr = cmd.ExecuteReader();
16. while (dr.Read())
17. {
18. if (dr["bompartid"] != DBNull.Value)
19. bom.BomPartID = Convert.ToInt32(dr["bompartid"]);
20. //indent the bomblevel string based on level number
21. if (dr["bomlevel"] != DBNull.Value)
22. bom.BomLevel = dr["bomlevel"].ToString();
23. if (dr["avgcost"] != DBNull.Value)
24. bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
25. if (dr["bomqty"] != DBNull.Value)
26. bom.BomQty = Convert.ToInt32(dr["bomqty"]);
27. if (dr["avgcost"] != DBNull.Value)
28. bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
29. if (dr["stdcost"] != DBNull.Value)
30. bom.StdCost = Convert.ToDouble(dr["stdcost"]);
31. bom.BomUnit = dr["bomunit"].ToString();
32. bom.Description = dr["descript"].ToString();
33. }
34. return bom;
35. }
36. catch (Exception ex)
37. {
38. throw ex;
39. }
40. finally
41. {
42. con.Dispose();
43. }
44.
45. }
46.
47.
48. public static List<BOM> GetSpaBomChild(int ParentID)
49. {
50. SqlConnection con = new SqlConnection(ConnectionString.getConnectionString());
51. SqlCommand cmd = new SqlCommand("dbo.BOSS3_GetSpaBomChild", con);
52. cmd.CommandType = CommandType.StoredProcedure;
53. cmd.Parameters.Add("parentid", SqlDbType.Int).Value = ParentID;
54. List<BOM> bomList = new List<BOM>();
55. SqlDataReader dr;
56. try
57. {
58. con.Open();
59. dr = cmd.ExecuteReader();
60. while (dr.Read())
61. {
62. BOM bom = new BOM();
63. if (dr["bompartid"] != DBNull.Value)
64. bom.BomPartID = Convert.ToInt32(dr["bompartid"]);
65. if (dr["bomlevel"] != DBNull.Value)
66. {
67. string l = dr["bomlevel"].ToString();
68. if (l.Trim().Equals("1"))
69. bom.BomLevel = "-" + l;
70. else if (l.Trim().Equals("2"))
71. bom.BomLevel = "--" + l;
72. else if (l.Trim().Equals("3"))
73. bom.BomLevel = "---" + l;
74. else
75. bom.BomLevel = "----" + l;
76. }
77. if (dr["avgcost"] != DBNull.Value)
78. bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
79. if (dr["bomqty"] != DBNull.Value)
80. bom.BomQty = Convert.ToInt32(dr["bomqty"]);
81. if (dr["avgcost"] != DBNull.Value)
82. bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
83. if (dr["stdcost"] != DBNull.Value)
84. bom.StdCost = Convert.ToDouble(dr["stdcost"]);
85. bom.BomUnit = dr["bomunit"].ToString();
86. bom.Description = dr["descript"].ToString();
87. bomList.Add(bom);
88. }
89.
90. }
91. catch (Exception ex)
92. {
93. throw ex;
94. }
95. finally
96. {
97. con.Dispose();
98. }
99. return bomList;
100. }
101. }


if you think you can help me speed this up please let me know. I can't have it run this slow

Thanks!

</code>

I formated the code incorrectly, my apologies. I will see if this will look better

Code:

---

public List<BOM> GetBomList(string partno)
    {
       
        List<BOM> bomList = new List<BOM>();
        BOM spaBom = BOMDB.GetSpaBomParent(partno);
        bomList.Add(spaBom);
        //get spalevel1
        List<BOM> level1List = BOMDB.GetSpaBomChild(spaBom.BomPartID);
        foreach (BOM l1 in level1List)
        {
            bomList.Add(l1);
            //getlevel2
            List<BOM> level2List = BOMDB.GetSpaBomChild(l1.BomPartID);
            foreach (BOM l2 in level2List)
            {
                bomList.Add(l2);
                //getlevel 3
                List<BOM> level3List = BOMDB.GetSpaBomChild(l2.BomPartID);
                foreach (BOM l3 in level3List)
                {
                    bomList.Add(l3);
                    //get level 4
                    List<BOM> level4List = BOMDB.GetSpaBomChild(l3.BomPartID);
                    foreach (BOM l4 in level4List)
                    {
                        bomList.Add(l4);
                    }
                }
            }

        }
       
        return bomList;
    }

---

Code:

---

public static BOM GetSpaBomParent(string partno)
    {
        SqlConnection con = new SqlConnection(ConnectionString.getConnectionString());
        SqlCommand cmd = new SqlCommand("dbo.BOSS3GetSpaBomLevel0", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("partno", SqlDbType.NVarChar).Value = partno;
        BOM bom = new BOM();
        SqlDataReader dr;
        try
        {
            con.Open();
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (dr["bompartid"] != DBNull.Value)
                    bom.BomPartID = Convert.ToInt32(dr["bompartid"]);
                //indent the bomblevel string based on level number
                if (dr["bomlevel"] != DBNull.Value)
                    bom.BomLevel = dr["bomlevel"].ToString();   
                if (dr["avgcost"] != DBNull.Value)
                    bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
                if (dr["bomqty"] != DBNull.Value)
                    bom.BomQty = Convert.ToInt32(dr["bomqty"]);
                if (dr["avgcost"] != DBNull.Value)
                    bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
                if (dr["stdcost"] != DBNull.Value)
                    bom.StdCost = Convert.ToDouble(dr["stdcost"]);

                bom.Source = dr["source"].ToString();
                bom.Group = dr["componentgroup"].ToString();
                bom.PartNo = dr["partno"].ToString();
                bom.BomUnit = dr["bomunit"].ToString();
                bom.Description = dr["descript"].ToString();
               
            }
            return bom;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Dispose();
        }

    }


    /// <summary>
    ///
    /// </summary>
    /// <param name="ParentID"></param>
    /// <returns></returns>
    public static List<BOM> GetSpaBomChild(int ParentID)
    {
        SqlConnection con = new SqlConnection(ConnectionString.getConnectionString());
        SqlCommand cmd = new SqlCommand("dbo.BOSS3_GetSpaBomChild", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("parentid", SqlDbType.Int).Value = ParentID;
        List<BOM> bomList = new List<BOM>();
        SqlDataReader dr;
        try
        {
            con.Open();
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                BOM bom = new BOM();
                if (dr["bompartid"] != DBNull.Value)
                    bom.BomPartID = Convert.ToInt32(dr["bompartid"]);
                if (dr["bomlevel"] != DBNull.Value)
                {
                    string l = dr["bomlevel"].ToString();
                    if (l.Trim().Equals("1"))
                        bom.BomLevel = "-" + l;
                    else if (l.Trim().Equals("2"))
                        bom.BomLevel = "--" + l;
                    else if (l.Trim().Equals("3"))
                        bom.BomLevel = "---" + l;
                    else
                        bom.BomLevel = "----" + l;
                }
                if (dr["avgcost"] != DBNull.Value)
                    bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
                if (dr["bomqty"] != DBNull.Value)
                    bom.BomQty = Convert.ToInt32(dr["bomqty"]);
                if (dr["avgcost"] != DBNull.Value)
                    bom.AvgCost = Convert.ToDouble(dr["avgcost"]);
                if (dr["stdcost"] != DBNull.Value)
                    bom.StdCost = Convert.ToDouble(dr["stdcost"]);

                bom.Source = dr["source"].ToString();
                bom.Group = dr["componentgroup"].ToString();
                bom.PartNo = dr["partno"].ToString();
                bom.BomUnit = dr["bomunit"].ToString();
                bom.Description = dr["descript"].ToString();
                bomList.Add(bom);
            }
           
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Dispose();
        }
        return bomList;
    }

---

Can you do the join in a sproc and return the data in one result set?