Paging the huge data that is returned by the Web API
<p>We created the WebAPI for querying an Oracle database. The query returns results that are huge, so it sometimes throws <code>OutOfMemoryException</code>.</p>

<p>The recommendation was to use the <em>Paging</em> 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. </p>

<p>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</p>

<pre><code>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;
            }
        }
    }
}
</code></pre>