Swapping huge data returned by the web API

Question

We created the WebAPI to query the Oracle database. The query returns huge results, so it sometimes throws an OutOfMemoryException .

The recommendation was to use the concept of paging. I don’t understand how the client application will know how many times the API must be called in order to get the whole set of results. I also need to create a separate swap class, or I can manage it in my API controller.

Can anyone help me with this since this is my first web API. We cannot create stored procedures for this, because we just have read access in 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; } } } } 

Answer 1

The general idea of ​​swapping through the API is that the client passes the "page" of the required data and the "number" of required records.

From there you can structure your query with the effect

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 the code side. If you are not using LINQ to SQL, you need to find something specific Oracle.

Final note: since a good API is designed as "stateless", the client will need to support which page they are working on when accessing previous / next pages. Typically, page variables and quantities are stored in Javascript, or even something as simple as hidden variables, and can be used to calculate the number of pages available, etc.

Here is a basic sample WebAPI call that I make that makes a search call. You may need to modify it a bit to support retrieving all records, and maybe something specific to Oracle if LINQ to SQL / EF does not 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); } 

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;