Need help on Nested Repeater. Need to limit queries.

[SheSaidImaPregy]

Hey, I created a datalist with a nested repeater. In the nested repeater, I need to limit the amount of records shown (only 10 allowed). However, just limiting the second query into the dataset doesn't work, only grabs 10 records. So I need to figure out a way of only showing 10 results for each. Basically, let's say this is my setup:

category1
- 1
- 2
- 3
- 4
...
- 10
- 11
- 12
- 13

category2
- 1
- 2
- 3
- 4
...
- 20
- 21
- 22
- 23

category3
- 1
- 2
- 3
- 4
...
- 16
- 17
- 18
- 19

Now I need to limit each category to 10 rows only. There are about 22 categories and about 10,000 results. Obviously I need to do my best to just limit it to 220 results, not 10,000!! Any help will be grateful. Thank you.

[SheSaidImaPregy]

anyone have any suggestions? I suppose I can put a loop on and trully limit the amount per sub repeater, but that doesn't help the fact that my query will still return 10,000 results. This is especially worse as I am storing it in the cache to use in a dataset.

[SheSaidImaPregy]

anyone? this will help tons.

[shaulf]

anyone? this will help tons.

I read through your question and could not quit understand the problem. How are you getting the data? from a database? why not restrict the number of records there? You could filter the dataset using the "DefaultView" row filter - http://msdn2.microsoft.com/en-us/library/system.data.dataview.rowfilter(VS.71).aspx
. Send me your code and I will take a look.

[SheSaidImaPregy]

It is dealt through a nested repeater. So the first repeater has no limits. It will show all 22-50 results needed. The second query (nested one), I would like to limit to 10 records per repeater. By limiting inside the query using LIMIT 10 or whatever, limits the results to all repeaters so that only 10 records are shown. So basically, this is what I want:

Category 1     Category 2     Category 3
1 result       1 result       1 result
2 result       2 result       2 result
3 result       3 result       3 result
4 result       4 result       4 result
.........
9 result       9 result       9 result
10 result      10 result      10 result

'' However, with retrieving the results it will display all. So with 10,000 results, you can imagine how big of a list it would be. By limiting the inner query to 10, it will bind 4 results to category 1, 3 to category 2, 3 to category 3. I have put a loop on currently that limits the amount of records to 10 per category, but the query will still return 10,000 results, and store all 10,000 in the dataset. If you still need my code, just ask for it once more and I will put it up!

[shaulf]

mmm...why are the nested repeater not restricted? Are you not using the ItemDataBound event to populate it? When doing so you could control how many records are process could you not?

[SheSaidImaPregy]

yes i am using the itemdayabound method. could you explin please, and does it help against quering 10,000 rows when only 220 are needed?

[shaulf]

yes i am using the itemdayabound method. could you explin please, and does it help against quering 10,000 rows when only 220 are needed?

Hi,

displaying any listed data in a web page is a three steps process (basically):

1. Getting the data from a database\source
2. processing the data
3. displaying the data

When you got the data in the first step you should get the minimum amount required. For the performance stand point there is no one answer; is it better to fetch all at once or go back and forth (in the item data bound) fetching the second level groups. It depends on the database size, network\location and number of sub groups queried in the item data bound. As a rule I probably would go with a very quick small query for the categories - something like a hundred results then query the database in the item data bound. Databases are better at querying data than .NET DataSets. However if the database is huge or far away from the web server I would think again.

To cut a long story short query the top level lets say school classes. Bind the DataTable to the list and in the Item Data Bound Query the database for each class students.

Another thing to consider is to show just the top level (classes in my example) and provide a link to the students what I call Card View. This way you do not need to bother with processing records the user might not be interested with (the down side is the need for the user to click twice to get the data).

if you still find it unclear send me your code and I will take a look...

Good Luck

[SheSaidImaPregy]

Nope, makes perfect sense. The server is in the same building as the mysql server, so that is fine. I would just hate to tap the database.. 25 times instead of once.. you know? But if tapping the database 25 times and only retreving a total of 275 records compared to 10,000 (at the moment, but later above 100,000), the difference might be huge. Thanks.

[justapimp]

Are you also getting the category information with your query result? If you are, you could select TOP 10 record of each category and use the foreach method to filter out the categories and populate your nested repeater.

In ASP.NET this seems a bit difficult, but in PHP this problem could be resolved without having to pre-filter your result.

[SheSaidImaPregy]

there are two queries, one pulls the category names and the category ID's. The second query pulls all records that relate to any of those ID's. It seems impossible, at this moment, at least with the current method I am using.

[selenachan]

I have a very similar problem where I have a nested repeater and I want to limit the child items to 5. Following is my code... any help would be much appreciated...

<asp:Repeater id="parentRepeater" runat="server">
				                                <itemtemplate>
					                                <br /><br /><b>
						                                <%#DataBinder.Eval(Container.DataItem, "category")%>
						                                <img src='images/divider/<%#DataBinder.Eval(Container.DataItem, "ID")%>_divider.jpg'  alt="<%#DataBinder.Eval(Container.DataItem, "category")%>"/>
					                                </b>
					                                <br>
					                                <asp:repeater id="childRepeater" runat="server" datasource='<%# Container.DataItem.Row.GetChildRows("myrelation") %>'>
                                                        <itemtemplate>
                                                        
                                                           <a href='products.aspx?catID=<%#container.dataitem("CatID")%>&pID=<%#container.Dataitem("ID")%>' class="categoryLink">
							                               <img src='images/products/<%#Container.DataItem("PNumber")%>t.jpg'alt="<%#Container.DataItem("PName")%>" border="1"  \></a>
						
Private Sub Load_CategoryRepeater()

        Dim CatId As String
        CatId = Request.QueryString("catID")
        Dim cnn As SqlConnection = New SqlConnection("Server=localhost;Database=xxx;Integrated Security=SSPI")
        Dim cmd1 As New SqlDataAdapter("select * from category where parentID = '" _
        & CatId & "'", cnn)


        'Create and fill the DataSet.
        Dim ds As New DataSet()
        cmd1.Fill(ds, "category")

        Dim cmd2 As New SqlDataAdapter _
              ("SELECT Category.Category, Products.PNumber, Products.ID, Products.PName, Products.PPrice, Products.PDescription, Products.CatID, Category.ID AS CategoryID, Category.ParentID FROM Category INNER JOIN Products ON Category.ID = Products.CatID Where Category.ParentID ='" _
        & CatId & "'", cnn)

        cmd2.Fill(ds, "product")

        ds.Relations.Add("myrelation", ds.Tables("category").Columns("ID"), ds.Tables("product").Columns("CatID"))


        parentRepeater.DataSource = ds.Tables("category")
        parentRepeater.DataBind()


        cnn.Close()
    End Sub     
						                                </itemtemplate>
					                                </asp:repeater> 
				                                </itemtemplate>
			                                </asp:Repeater>

Hi,

displaying any listed data in a web page is a three steps process (basically):

1. Getting the data from a database\source
2. processing the data
3. displaying the data

When you got the data in the first step you should get the minimum amount required. For the performance stand point there is no one answer; is it better to fetch all at once or go back and forth (in the item data bound) fetching the second level groups. It depends on the database size, network\location and number of sub groups queried in the item data bound. As a rule I probably would go with a very quick small query for the categories - something like a hundred results then query the database in the item data bound. Databases are better at querying data than .NET DataSets. However if the database is huge or far away from the web server I would think again.

To cut a long story short query the top level lets say school classes. Bind the DataTable to the list and in the Item Data Bound Query the database for each class students.

Another thing to consider is to show just the top level (classes in my example) and provide a link to the students what I call Card View. This way you do not need to bother with processing records the user might not be interested with (the down side is the need for the user to click twice to get the data).

if you still find it unclear send me your code and I will take a look...

Good Luck

[SheSaidImaPregy]

For this, just update your query to select the top 5 results ...

SELECT TOP 5 Category.Cat....

This way it has only 5 records and is limited.