CODEWITHSUNDEEP
c#asp.netasp.net-mvcasp.net-web-api
trx
trx

Reputation: 2157

Issue returning larget set of data over Web API

We created the WebAPI for querying an Oracle database return them in the JSON format. I am having the below code 

public class PDataController : ApiController
{
 public HttpResponseMessage Getdetails([FromUri] string[] id)
 {
    List<OracleParameter> prms = new List<OracleParameter>();
    string connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
    using (OracleConnection dbconn = new OracleConnection(connStr))
    {
        DataSet userDataset = new DataSet();
        var strQuery = @"SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN(";
         StringBuilder sb = new StringBuilder(strQuery);
         for(int x = 0; x < inconditions.Length; x++)
             {
               sb.Append(":p" + x + ",");
               OracleParameter p = new OracleParameter(":p" + x, OracleType.Int32);
               p.Value = inconditions[x];
               prms.Add(p);
             }
        if(sb.Length > 0) sb.Length--;
        strQuery = 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=PStudyData.json", out contentDisposition))
                {
                    response.Content.Headers.ContentDisposition = contentDisposition;
                }
                return response;
            }
        }

    }
}

The query returns results that are huge, so it sometimes throws OutOfMemoryException. enter image description here Instead of Call the API during each page request.Call API on each initial page request, cache the DB response on the middle-tier and then paginate from that “local” cache.Can we do the above approach. I am kind of really stuck with the memory issue and not able to move any further. As I am very new to ASP.NET and C# coding.Any help with is really appreciated. Thanks

Upvotes: 0

Views: 101

Answers (1)

Roman Royter
Roman Royter

Reputation: 1665

I would recommend to implement paging and return data in chunks. I think it's safe to say that if you're running out of memory on the server, you will also probably have issues with bandwidth as well as memory issues on the receiving end.

Upvotes: 4

Related Questions