CODEWITHSUNDEEP
asp.netjsonasp.net-mvcasp.net-web-api
trx
trx

Reputation: 2167

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

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 enter image description here

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

Upvotes: 0

Views: 3002

Answers (1)

Fan TianYi
Fan TianYi

Reputation: 417

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;
}

Upvotes: 2

Related Questions