views:

137

answers:

2

I am getting a system.outofmemory exception in my code:

While r1.Read()
                    menu1id = r1("id")
                    db.AddInParameter(command2, "@menu1id", DbType.Int32, menu1id)
                    r2 = db.ExecuteReader(command2)
                    command2.Parameters.Clear()
                    menu1heading = r1("Heading")
                    If r1("url") IsNot Nothing And r1("moduleid") = 0 Then
                        url = r1("url").ToString

                        If InStr(url, "file:///") > 0 Then
                            Dim builder As New StringBuilder
                            builder.Append("<a href=")
                            builder.Append(r1("url"))
                            builder.Append(" target=")
                            builder.Append(r1("urltarget"))
                            builder.Append(">")
                            builder.Append(menu1heading)
                            builder.Append("</a>")
                            level1.Add(builder.ToString)
                        Else
                            Dim builder As New StringBuilder
                            builder.Append("<a href=http://")
                            builder.Append(r1("url"))
                            builder.Append(" target=")
                            builder.Append(r1("urltarget"))
                            builder.Append(">")
                            builder.Append(menu1heading)
                            builder.Append("</a>")
                            level1.Add(builder.ToString)
                        End If
                    Else
                        Dim builder As New StringBuilder
                        builder.Append("<a href=~/Default.aspx?id=")
                        builder.Append(r1("id"))
                        builder.Append(">")
                        builder.Append(menu1heading)
                        builder.Append("</a>")
                        level1.Add(builder.ToString)
                    End If


                    While r2.Read
                        menu2id = r2("id")
                        db.AddInParameter(command3, "@menu2id", DbType.Int32, menu2id)
                        r3 = db.ExecuteReader(command3)
                        command3.Parameters.Clear()
                        menu2heading = r2("Heading")

                        If r2("url") IsNot Nothing And r2("moduleid") = 0 Then
                            Dim builder As New StringBuilder
                            builder.Append("<a href=http://")
                            builder.Append(r2("url"))
                            builder.Append(" target=")
                            builder.Append(r2("urltarget"))
                            builder.Append(menu2heading)
                            builder.Append("</a>")
                            level2.Add(builder.ToString)
                        Else
                            Dim builder As New StringBuilder
                            builder.Append("<a href=~/Default.aspx?id=")
                            builder.Append(r2("id"))
                            builder.Append(">")
                            builder.Append(menu2heading)
                            builder.Append("</a>")
                            level2.Add(builder.ToString)
                        End If


                        While r3.Read
                            menu3heading = r3("Heading")
                            menu3id = r3("id")
                            If r3("url") IsNot Nothing And r3("moduleid") = 0 Then
                                Dim builder As New StringBuilder
                                builder.Append("<a href=http://")
                                builder.Append(r3("url"))
                                builder.Append(" target=")
                                builder.Append(r3("urltarget"))
                                builder.Append(">")
                                builder.Append(menu3heading)
                                builder.Append("</a>")
                                level3.Add(builder.ToString)
                            Else
                                Dim builder As New StringBuilder
                                builder.Append("<a href=~/Default.aspx?id=")
                                builder.Append(r3("id"))
                                builder.Append(">")
                                builder.Append(menu3heading)
                                builder.Append("</a>")
                                level3.Add(builder.ToString)
                            End If

                        End While

                        r3.Close()
                    End While

                    r2.Close()
                End While

                r1.Close()
            End While
            r0.Close()

Please can you tell me how I go about diagnosing and fixing this exception? thanks a lot.

+4  A: 

The method uses a lot of string concatenations, each of them creating lots of new temporary string objects which use up your heap space. This can be avoided by using a StringBuilder to create the concatenated string. The StringBuilder works not only more memory-efficient when concatenating lots of strings, it will also be much faster:

Dim builder As New StringBuilder()
builder.Append("<a href=")
builder.Append("r0("url"))
builder.Append("target=")
builder.Append(r0("urltarget"))
builder.Append(menu0heading)
builder.Append("</a>")

Dim str as String
str = builder.ToString()

Note: As pointed out by David Neale in the comments, in this context it would be better to use a TagBuilder (or an XmlWriter) to create the HTML/XML document tree.

0xA3
The OP hasn't mentioned using MVC so I'll just post as a comment. The [TagBuilder](http://msdn.microsoft.com/en-us/library/system.web.mvc.tagbuilder.aspx) class can further simplify this sort of function.
David Neale
+3  A: 

You would be a lot better off changing your logic to a single sql query that returns all of your menu items in one go and then iterating this dataset to build your menu.

You could just try

SELECT id, DepartmentID, GroupingID, Heading, OrderID, 
Publish, moduleid, url, urltarget
FROM Grouping 
WHERE (DepartmentID = 0 AND Publish <> 0)
ORDER BY OrderID

This returns all of the data that the above queries return, including the GroupingID which determines the tree structure. You should be able to load up the results into a collection of objects and then query them using LINQ to build your menu.

Copy your data into the following class annd then use LINQ on a list of them:

public class DataClass
{
public string Id { get; set; }
public string DepartmentID { get; set; }
public string GroupingID { get; set; }
public string Heading { get; set; }
public string OrderID { get; set; }
public string Publish { get; set; }
public string Moduleid { get; set; }
public string Url { get; set; }
public string Urltarget { get; set; }

public List<DataClass> Children { get; set; }

public DataClass(string id, string departmentID, string groupingID, string heading, string orderID, string publish, string moduleid, string url, string urltarget)
{
    Id = id;
    DepartmentID = departmentID; 
    GroupingID = groupingID;
    Heading = heading;
    OrderID = orderID; 
    Publish = publish;
    Moduleid = moduleid;
    Url = url;
    Urltarget = urltarget;
}
}
Daniel Dyson
The problem I have is that each record uses the id from the previous record to determin its parent in the menu each time the loops run. Is it possible to achieve this result with a dataset? . I'm new with using datasets / linq
Phil
Absolutely. You have the GroupingID in your dataset, so each record is "aware" of which parent it belongs to. I have just added a Children property to the DataClass. Just an idea, but it might get you thinking about how this might work. Think about the posibiilty of a Method that renders the Anchor tag, or maybe a separate converter class that takes your dataclasses and generates the menu
Daniel Dyson
Sorry, I just noticed that you example is in VB and my answer is in C#. Shouldn't be too difficult for you to convert??
Daniel Dyson
You would read all of your data into a list of instances of the class. At this point you are then able to iterate the list, building a new FinalList of the same class. Where you find a top level item if (GroupingID == 0), then you add it to the final list. Then you loop through again, looking for objects with a GroupingID in the Final List and add then to the appropriate object's children. And then again until you have none left. This doesn't limit you to 4 levels and will be easier to debug
Daniel Dyson