title: Dealing with large JSON data returned by Web API
body: <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>