C# REST API GET method: Unable to handle large amount of records

Question

I'm using VS 2017 to rebuild a C# app. However I cannot retrieve more than 3993 records and I need to get ~180K to insert them in a flat table (SQL db)

I think the pagination method was not designed properly and I'm not pretty sure how to fix it, If I add a number greater than 4K it crashes. What could be the best method to fix this?

This is what we have so far:

Helper.cs:

{
    ItemsDto itemsCollection = null;
    HttpClient httpClient = new HttpClient();


    httpClient.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", token);


    var uri = string.Format(@"{0}pageSize={1}&page=1", Constants.REQUEST_ENDPOINT, Constants.MAXVALUE);//Constants.MAXVALUE); 
    uri = uri + "&advancedFilter=__Status__~eq~'Active'";
    uri = uri + @"&dynamicColumns=Name,ID,Date,Status,Version,Name,Division";

    uri = uri + "&orderDirection=DESC";

    HttpResponseMessage response = await httpClient.GetAsync(uri);


    if (response == null)
    {
        throw new Exception("There is no returned request from API");  

    }

    if (response.IsSuccessStatusCode)
    {
        var responseAsString = await response.Content.ReadAsStringAsync();
        itemsCollection = JsonConvert.DeserializeObject<ItemsDto>(responseAsString) ?? new ItemsDto();
    }
    else
    {
        throw new Exception("An error request has ocurred: " + response.RequestMessage);  
    }

    if (itemsCollection.Items == null)
    {
        log.Info("There is no files available");
        itemsCollection.Items = new List<Document>();
    }

    return itemsCollection.Items;

}

App.config:

  <add key="IntMaxValue" value="4000" /> //Change pageSize Here

Constants.cs:

public static readonly int MAXVALUE = int.Parse(ConfigurationManager.AppSettings["IntMaxValue"]);

Maybe you don't have enough memory to process that many at a time? — Steven B., Nov 24 '19 at 04:37
Thanks @Steven B it could be a timeout issue, I'll investigate a little bit to see how can I configure that. — Tana, Nov 24 '19 at 05:39
@Mikael the error is " A task was canceled. Inner Exception" I'll try to debug the code line by line to see if I can see something else. — Tana, Nov 24 '19 at 05:39
Do you have access to the api source code you're consuming? The problem is there, so you probably should call this service the times you need to retrieve the number the total number of registers — , Nov 24 '19 at 05:46
@Nestorzin Unfortunately, I don't have access, it's an external vendor product, but they said that there are other customers that don't face the same issue, so I guess it's something in my code. — Tana, Nov 24 '19 at 05:50
@tana check the answer [here](https://stackoverflow.com/questions/29179848/httpclient-a-task-was-cancelled/29214208#29214208) about that error. It's probably timing out — Mikael, Nov 24 '19 at 05:50

score 0 · Accepted Answer · answered Nov 29 '19 at 20:22

The final solution was to use "for" in order to use the api pagination correctly. In this case, If I want to retrieve 150K records, then page=10 and pageSize=15000 (15k per page)

for(int pagina=1; pagina<=10; pagina++)
            {
                HttpClient httpClient = new HttpClient();
                httpClient.Timeout = TimeSpan.FromMinutes(30);
                httpClient.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", token);

                var uri = string.Format(@"{0}pageSize={1}&page={2}", Constants.REQUEST_ENDPOINT, 15000,pagina);//Constants.MAXVALUE); 
uri = uri + "&advancedFilter=__Status__~eq~'Active'";
uri = uri + @"&dynamicColumns=Name,ID,Date,Status,Version,Name,Division";

                uri = uri + "&orderDirection=DESC";

                HttpResponseMessage response = await httpClient.GetAsync(uri);


                if (response == null)
                {
                    throw new Exception("There is no returned request from TAP API");

                }

                if (response.IsSuccessStatusCode)
                {
                    var responseAsString = await response.Content.ReadAsStringAsync();
                    itemsCollection = JsonConvert.DeserializeObject<ItemsDto>(responseAsString) ?? new ItemsDto();
                    itemsCollectionTotal.Add(itemsCollection);
                }
                else
                {
                    throw new Exception("An error request has ocurred: " + response.RequestMessage);
                }

                if (itemsCollection.Items == null)
                {
                    log.Info("There is no documents available from API");
                    itemsCollection.Items = new List<Document>();
                }

            }

            return itemsCollectionTotal;//itemsCollectionTotal.Item;

        }

C# REST API GET method: Unable to handle large amount of records

1 Answers1