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

Reputation: 1043

Handling large amount of data -Web API

The Web API we created queries the OracleDB and returns a huge set of records and we will have to convert it in to the JSON

Below is the actual Controller 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;

The issue is if we have huge number of rows like 300,000 it throws Out of memory exception. I read in other forums that this issue can be solved by pagination. But I am looking to get entire set of records at once, as the client application requires entire result when the Web API is called. I am not sure how would I handle this. any help is greatly appreciated.

Upvotes: 1

Views: 8531

Answers (1)

Rob
Rob

Reputation: 11788

I had a similar issue and solved it by adding some parameters to my web.config:

<requestLimits maxAllowedContentLength="2147483647" />

(this is placed inside <system.webServer><security><requestFiltering>)

and

<httpRuntime targetFramework="4.6.1" maxRequestLength="2147483647" executionTimeout="1600" requestLengthDiskThreshold="2147483647" />

(this has to be in the section <system.web>)

Upvotes: 1

Related Questions