Large query how to return results back in sections C#/ASP.NET MVC 4

Question

I have an application written in ASP.NET MVC 4. I have a requirement to return large results from a table accessed with oledbdatareader.

I am using AJAX to return a JsonResult object that contains a List: List<TableRow>

What I do not understand is, if I am in the DataReader loop

using (OleDbDataReader reader = command.ExecuteReader())
{
   while (reader.Read())
   {
       names.Add(Convert.ToString(reader[0]));
   }
}

Is there a way to periodically send the list object, and then create a new object to pickup and continue on?

What is the requirement for displaying the data? Is there a reason why you are not able to use a table (grid) with paging capabilities so where the user can adjust the amount of data coming back to like 10, 25, 50 or 100 rows and then click arrows to page back and forth? — Flea, Mar 24 '17 at 15:15
I can use a grid, but the issue is, I was looking for a way to send the data in packets sort of other than filling a data structure all at once and then passing it to the client. Haven't really done it before, so i was looking to see what the normal process was for a web application, but I may just lean on SignalR and give that a shot.. — eaglei22, Mar 27 '17 at 13:06

score 0 · Accepted Answer · answered Mar 21 '17 at 19:19

0

Technically the server can only return a single response to every request, so there is not way to do what you want (short of setting up some sort of crazy socket stuff).

I'd flip what you're doing on its head and have your javascript request chunks of the dataset in batches of 1000 (or whatever size), and have it start rendering while requesting the next chunk.

Better yet, you could implement some form of infinite scrolling in your UI so that the next chunk is only requested just in time for it to be displayed, that way you're not sending unneeded data to the client.

answered Mar 21 '17 at 19:19

arychj

711
3
21

As far as the crazy socket stuff, maybe I could use "SignalR" to handle but, I would rather not rely on that just yet, and find an alternative. So for the dataset request in batches, that is tougher as the table is not index (maybe I could ask to have that done) but I wouldn't know how to communicate where to stop and start each new query (if I am understanding correctly) Ultimately I would like to stop the data structure from getting too big. If I could get an id for each row. I would just do ranges that way maybe. – eaglei22 Mar 21 '17 at 19:26

Flea · Answer 2 · 2017-03-27T18:48:34.207

I think you have a few options that are rather common to implement. If you have 10,000 records that you need to give back to the client, you can manage this in your MVC application. If you are using Entity Framework and Linq, you can write your business logic to just send back 100 rows per each time the user clicks the next button. This will keep the transmission to the client small and even keep the call from the web server to the SQL server small.

If you don't want to have the user click a next button (i.e. paging) but want do to an infinite scroll style, then just do the same thing, as the user keeps scrolling, just keep calling the Ajax method to send back each 100 rows at a time.

The web server and database server isn't going to choke on 10,000 records; it will be choking going down to the client. Even if you open a socket in Signal R, I think you should ask yourself do I really need to push 10,000 rows all at once to the client?

Think about the Twitter app on a mobile phone, they are sending the data to you as you scroll, they are not sending it all at once. Does that help any?

Updated based on your comment regarding its straight SQL

Here is an example of doing a simple version of paging in SQL:

DECLARE @intStartRow int;
DECLARE @intEndRow int;

SET @intStartRow = (@intPage -1) * @intPageSize + 1;
SET @intEndRow = @intPage * @intPageSize;    

WITH blogs AS
    (SELECT strBlogName, 
     ROW_NUMBER() OVER(ORDER BY intID DESC) as intRow, 
     COUNT(intID) OVER() AS intTotalHits 
     FROM tblBlog)
SELECT strBlogName, intTotalHits FROM blogs
WHERE intRow BETWEEN @intStartRow AND @intEndRow

Source: http://joelabrahamsson.com/my-favorite-way-to-do-paging-with-t-sql/

thanks for the feed back! I am not using entity framework, I am using OleDbConnection. user: arychj, mentioned the same thing about infinite scrolling. Because I am using OleDbConnection, I am confused on how all of this works. Do you have a simple example to provide, if this is possible with OleDbConnection. — eaglei22, Mar 27 '17 at 15:48
no stored procedures, just a standard sql string with prepared statement. — eaglei22, Mar 27 '17 at 16:48
@eaglei22 - You will have to do something to page the data, so since you are using straight SQL, you will need to add a little business logic and you can implement the example above. — Flea, Mar 27 '17 at 18:50

Large query how to return results back in sections C#/ASP.NET MVC 4

2 Answers2

Linked