0

I'm starting to program with ASP-NET MVC an application with Angular for the front-end and SQL Sever to the database. In some cases, I have complex query than I have to use and I cannot modify because of a restriction business. I am using a structure similar to this one: using simple queries in ASP.NET MVC but I don´t know which is the correct way to handle a lot of data and show in the front-end.

I have a ViewModel with the data structure of the results query, a DomainModel where the query is located and the Controller to communicate with the front-end.

My problem is that I don´t know which would be the way to develop what I am trying. Now I´m trying to create as many objects in a list object as rows in my query, but when this method is running my computer gets blocked with no error showed (I can guess it is because it is using the whole memory).

Note that the table in the front has to show only 25 results per page, maybe I can execute the query always when the user choose a diferente page of the table, getting a different lots of results. I didn´t try this choice yet.

This is part of the DomainModel:

public IEnumerable<OperationView> GetOperations()
{
    List<OperationView> Operationslist = new List<OperationView>();
    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand("", connection))
    {
        command.CommandText = /*Query joining 8-10 tables*/;
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            var OperationView = new OperationView();
            OperationView.IdOperacion = reader["ID_OPERACION"].ToString();
            //Loading here some other variables of OperationView
            Operationslist.Add(OperationView);
        }
        connection.Close();
    }
    return Operationslist;
}

This is part of the Controller:

public IEnumerable<OperationView> GetOperaciones()
{
    var Operation = new OperationPDomainModel();
    return Operation.GetOperations();
}

I think that my front and ViewModel are not importants for this problem, but I can include them if needed.

Currently, if I try to execute the computer shuts down unexpectely...

Tobias Tengler
  • 6,848
  • 4
  • 20
  • 34
Jose Antonio
  • 123
  • 1
  • 1
  • 7
  • you can import PagedList class to help if you go that route: https://www.nuget.org/packages/PagedList/ – pcalkins Jun 24 '19 at 20:44
  • What's with the `List`? Ditch it, and use [`yield return`](https://stackoverflow.com/questions/410026/proper-use-of-yield-return). You're creating a completely unnecessary layer of in-memory caching. – John Wu Jun 25 '19 at 06:34
  • you need to query those tables first on which all the filters are applied (if there are any filters) and then paginate it.The result data should be joined with other tables and then read by your `GetOperations()` method – Ali Umair Jun 25 '19 at 06:48
  • Cool!!! I will try PagedList and yield return. Thanks for the advices. – Jose Antonio Jul 17 '19 at 22:50

3 Answers3

1

There are a few improvements you could make.

Make the call async

The operation would hang as it blocks the main thread. If possible try this operation async. Use task-based programming to run the operation on a different thread. That should make things a little better not improve that significantly.

Use pagination

Get only the number of records that you need to display on the page. This should be the best improvement based on the code you have. It would also be better to have some more filters if possible. But getting only 25 records if you need only 25 should be the way to go. It would also help if you could use modern programming techniques like EF and LINQ instead of traditional ADO.Net

Use Ajax

Such large processing should be done using AJAX calls. If you do not want the user to wait for the data to be loaded, you can load the page and make the data retrieval a part of a separate AJAX call.

Shahzad
  • 1,677
  • 1
  • 12
  • 25
1

As your system is going out of memory, you need to have pagination.

This paging is should be done in the database side. UI just need to pass the page index and number of records displayed per page.

So your query should be something as below

Select a,b,c, ROW_NUMBER() OVER(ORDER BY a) rnum from foo where rnum between (25 * Page_Index) + 1 and (25 * Page_Index) + 25

mip
  • 51
  • 3
0

check this View Millions Of Records

https://www.c-sharpcorner.com/article/how-to-scroll-and-view-millions-of-records/

Sanjay Bharga
  • 36
  • 2
  • It is not advisable to post just links to another website. the link can go dead. You should post a full solution here with links just for reference. In case the link goes dead, the post should even then serve as an answer. – Shahzad Jun 25 '19 at 07:05