Paging the huge data that is returned by the Web API

Question

We created the WebAPI for querying an Oracle database. The query returns results that are huge, so it sometimes throws OutOfMemoryException.

The recommendation was to use the Paging concept. I don't understand how the client application will know how many times the API has to be called to get the entire set of the result. Also do I need to create a separate class for the paging or can I operate it in my API controller.

Can anyone please help me with this as this is my first Web API. We cannot create stored procedures for this because we just have read access on the database

public HttpResponseMessage Getdetails([FromUri] string[] id)
{
    string connStr = ConfigurationManager.ConnectionStrings["ProDataConnection"].ConnectionString;
    using (OracleConnection dbconn = new OracleConnection(connStr))
    {
         var inconditions = id.Distinct().ToArray();
         var srtcon = string.Join(",", inconditions);
         DataSet userDataset = new DataSet();
         var strQuery = @"SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN(" + srtcon + ")";
         using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
         {
              using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
             {
                 DataTable selectResults = new DataTable();
                 adapter.Fill(selectResults);
                 var returnObject = new { data = selectResults };
                 var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
                 ContentDispositionHeaderValue contentDisposition = null;

                 if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
                {
                     response.Content.Headers.ContentDisposition = contentDisposition;
                }

                return response;
            }
        }
    }
}

Embank

Your code has a sql-injection issue. Read how to use commandparameters. – Humbuggery

Answer 1

The general idea behind paging through the API is that the client will pass the "page" of data they want and the "amount" of records they want.

From there you can structure your query something to the effect of

Select all records, but skip ((Page - 1) * amount) of records and take (amount) of records.

If you use LINQ to SQL there are Take() and Skip() methods that help make this a lot easier to write on code side. If you aren't using LINQ to SQL you'll need to find something Oracle specific.

Final note, since a good API is designed to be "stateless" it will be the requirement of the client to maintain which page they are on when handling previous/next page queries. Typically the page and amount variables are kept in Javascript or even something as simple as hidden variables and can be used to calculate how many pages are available and the like

Here's a basic sample of a WebAPI call that I make that does paging. You may need to modify it a little to support getting all records and potentially anything Oracle specific if LINQ to SQL / EF doesn't support it:

public IActionResult GetProducts(int? page, int? count)
        {
            var takePage = page ?? 1;
            var takeCount = count ?? DefaultPageRecordCount;

            var calls = context.Products
                            .Skip((takePage - 1) * takeCount)
                            .Take(takeCount)
                            .ToList();

            return Json(calls);
        }

Uranium

Thanks Dillie-O but what if the client wants the entire set of records at once. Is that possible. – Embank

@Embank - If the user wants all of the records at once, then you can put something into your API to ignore take/skip if they have specified a page value of -99 or simply null, though typically when a null is encountered you have a default fallback. – Uranium

Thanks Dillie-O so I will have to change my select statement right. Are there any simple samples I can refer to – Embank

@Embank - I updated my answer with a simple code sample from a project I worked on. You may need to tweak it a bit to meet your needs – Uranium

Answer 2

IQueryable<ShowMedicineViewModel> query;
List<ShowMedicineViewModel> medic = new List<ShowMedicineViewModel>();
var medicineInfo = _dbContext.medicine_details.Where(m => (m.Medicine_name.StartsWith(medicinename)) && (m.Medicine_type == medicinetype)).ToList();   

List<string> TotalMedicine = new List<string>();

var results = (medicineInfo.OrderBy(x => x.id)
              .Skip((pages - 1) * 2)
              .Take(2));


Parallel.ForEach(results, item =>
{
    var temp = Mapper.DynamicMap<medicine_details, ShowMedicineViewModel>(item);

    medic.Add(temp);
});

Dictionary<string, int> dictionary2 = new Dictionary<string, int>();
dictionary2.Add("CurrentPage", pages);
dictionary2.Add("TotalPages", medicineInfo.Count() / 2 < 1 ? 1 : medicineInfo.Count());

Dictionary<string, object> dictionary = new Dictionary<string, object>();
dictionary.Add("Data", medic);
dictionary.Add("Page", dictionary2);

return dictionary;

Potash