Dealing with large JSON data returned by Web API
<p>We are building an web API that receives the array of strings as  input parameter which queries the oracle database and returns the result as a JSON file. </p>

<p>So the code is like </p>

<pre><code> namespace PDataController.Controllers
{
  public class ProvantisDataController : ApiController
  {
    public HttpResponseMessage Getdetails([FromUri] string[] id)
    {

       List&lt;OracleParameter&gt; prms = new List&lt;OracleParameter&gt;();
        string connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
        using (OracleConnection dbconn = new OracleConnection(connStr))
        {
            var inconditions = id.Distinct().ToArray();
            var srtcon = string.Join(",", inconditions);
            DataSet userDataset = new DataSet();
            var strQuery = @"SELECT 
                           STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, 
                           STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, 
                           Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, 
                           STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME , 
                           Trunc(STCD_PRIO_CATEGORY_DESCR.END_DATE) AS SESSION_END_DATE, 
                             FROM 
                             STCD_PRIO_CATEGORY_DESCR, 
                             WHERE 
                            STCD_PRIO_CATEGORY_DESCR.STD_REF IN(";
            StringBuilder sb = new StringBuilder(strQuery);
             for(int x = 0; x &lt; inconditions.Length; x++)
                 {
                   sb.Append(":p" + x + ",");
                   OracleParameter p = new OracleParameter(":p" + x,OracleDbType.NVarchar2);
                   p.Value = inconditions[x];
                   prms.Add(p);
                 }
            if(sb.Length &gt; 0) sb.Length--;
            strQuery = sb.ToString() + ")"; 
            using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
              {
               selectCommand.Parameters.AddRange(prms.ToArray());
                 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>

<p>The data returned for the API is in the below format </p>

<pre><code>{"data":[{"CATEGORY":"Internal Study","SESSION_NUMBER":7,"SESSION_START_DATE":"2015-02-13T00:00:00","SESSION_START_TIME":"2015-02-13T10:33:59.288394","SESSION_END_DATE":"2015-02-13T00:00:00"}]}
</code></pre>

<p>We are sometimes having issue in returning the large amount of data it throws the OutOfMemory Exception.
<a href="https://i.stack.imgur.com/m40PN.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/m40PN.jpg" alt="enter image description here"></a>
 It was suggested to use the JSON property, parallel to the “data” property: like “next_data”, with a value of the value you need to pass into the SQL OFFSET (which works in MySQL,  I am not sure if this works in oracle),if there no data remaining then set the value of “next_data” to 0.I am not sure how to implement this.Not sure if this can be implemented. Any help with this is greatly appreciated.
<a href="https://i.stack.imgur.com/0rcOk.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/0rcOk.jpg" alt="enter image description here"></a></p>