OutOfMemoryException returns a large dataset from the Web API

Question

OutOfMemoryException returns a large dataset from the Web API

We have created a web API that queries the Oracle database and returns the result in JSON format. If the returned data is very large, it throws an exception from the exception. Therefore, in another question, it was suggested that the problem is that loading the entire data set into memory before it is serialized to HttpResponseMessage.

So the classes below are created

[JsonConverter(typeof(OracleDataTableJsonResponseConverter))]
public sealed class OracleDataTableJsonResponse
{
public string ConnectionString { get; private set; }
public string QueryString { get; private set; }
public OracleParameter[] Parameters { get; private set; }

public OracleDataTableJsonResponse(string connStr, string strQuery, OracleParameter[] prms)
{
    this.ConnectionString = connStr;
    this.QueryString = strQuery;
    this.Parameters = prms;
}
}

class OracleDataTableJsonResponseConverter : JsonConverter
 {
public override bool CanConvert(Type objectType)
{
    return objectType == typeof(OracleDataTableJsonResponse);
}

public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
    throw new NotImplementedException("OracleDataTableJsonResponse is only for writing JSON.  To read, deserialize into a DataTable");
}

public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
    var response = (OracleDataTableJsonResponse)value;

    using (var dbconn = new OracleConnection(response.ConnectionString))
    {
        using (var selectCommand = new OracleCommand(response.QueryString, dbconn))
        {
            if (response.Parameters != null)
                selectCommand.Parameters.AddRange(response.Parameters);
            using (var reader = selectCommand.ExecuteReader())
            {
                writer.WriteDataTable(reader, serializer);
            }
        }
    }
}
}

public static class JsonExtensions
{
public static void WriteDataTable(this JsonWriter writer, IDataReader reader, JsonSerializer serializer)
{
    if (writer == null || reader == null || serializer == null)
        throw new ArgumentNullException();
    writer.WriteStartArray();
    while (reader.Read())
    {
        writer.WriteStartObject();
        for (int i = 0; i < reader.FieldCount; i++)
        {
            writer.WritePropertyName(reader.GetName(i));
            serializer.Serialize(writer, reader[i]);
        }
        writer.WriteEndObject();
    }
    writer.WriteEndArray();
}
}

And the API controller is like

public HttpResponseMessage Getdetails([FromUri] string[] id)
{
    var prms = new List<OracleParameter>();
    var connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
    var inconditions = id.Distinct().ToArray();
    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(";
    var sb = new StringBuilder(strQuery);
    for (int x = 0; x < inconditions.Length; x++)
    {
        sb.Append(":p" + x + ",");
        var p = new OracleParameter(":p" + x, OracleDbType.NVarchar2);
        p.Value = inconditions[x];
        prms.Add(p);
    }
    if (sb.Length > 0)// Should this be inconditions.Length > 0  ?
        sb.Length--;
    strQuery = sb.Append(")").ToString();

    var returnObject = new { data = new OracleDataTableJsonResponse(connStr, strQuery, prms.ToArray()) };
    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;
 }