Exception of type 'System.OutOfMemoryException' was thrown WEB API

Question

We created the Web API for querying the Oracle DB returning the result in the JSON in the below format.So the API will be getting the array of input parameters. As the query result is so huge like for the below query it returns 313568 when we try in the SQL Developer.

SELECT * from STCD_PRIO_CATEGORY where STPR_STUDY.STD_REF IN("BL001,TM002")

Below is the code we are using

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;

When I am using the URL https://bhbl.abc.org/api/Sample?id='BL001'&id='TM002' for querying the DB as

But when I am using the URL https://bhbl.abc.org/api/Sample?id='BL001' it returns result which is just 41552 records

How to create API which will return huge amount of data. Any help is greatly appreciated

Community · Accepted Answer · 2016-08-04 06:27:47Z

2

This is because, api returned data is too large, the process to produce memory overflow. You need to use the paging sql:

SELECT *
  FROM (SELECT a.*, ROWNUM rn
          FROM (SELECT *
                  FROM table_name) a
         WHERE ROWNUM <= 40)
 WHERE rn >= 21

Client proccess once, multiple calls api, complete all data acquisition.

The client core code below:

// ids: 'BL001' 'TM002', next_key: paging record index
while (next_key != null)
{
    next_key = GetDetails(ids, next_key);
}

private string GetDetails(stirng[] ids, string next_key)
{
    // call api
    var result = ...;

    // parse api reponse result
    object key = result.next_key;
    if (key == null) return null;
    string s = key.ToString();
    return string.IsNullOrEmpty(s) ? null : s;
}

The server core code below:

public HttpResponseMessage Getdetails([FromUri] string[] id, [FromUri] string next_key)
{
    // use paging sql
    // excute sql return record count
    var count = ...

    // return next_key
    if (count < 20)
    {
        result.next_key = null;
    }
    else
    {
        result.next_key = (int.Parse(next_key) + 20).ToString();
    }

    return result;
}

edited Aug 4, 2016 at 6:27

CommunityBot

11 silver badge

answered Aug 3, 2016 at 1:04

Fan TianYi

4173 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

trx Over a year ago

Can we do this without Paging? Because the client is looking for result at once

Fan TianYi Over a year ago

@trx Client proccess once, multiple calls api, complete all data acquisition.

Collectives™ on Stack Overflow

Exception of type 'System.OutOfMemoryException' was thrown WEB API

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related