引发了类型'System.OutOfMemoryException'的异常WEB API [英] Exception of type 'System.OutOfMemoryException' was thrown WEB API

问题描述

我们创建了Web API来查询Oracle数据库，并以以下格式以JSON格式返回结果，因此该API将获取输入参数的数组.由于查询结果是如此庞大，就像下面的查询一样，当我们尝试使用SQL Developer时，它将返回313568.

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.

从* STCD_PRIO_CATEGORY中选择*，其中STPR_STUDY.STD_REF IN("BL001，TM002")

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;

当我使用URL https://bhbl.abc.org/api/Sample?id = 'BL001'& id ='TM002' 用于查询数据库为

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

但是当我使用URL > https://bhbl.abc.org/api /Sample?id = "BL001"，它返回的结果仅为41552条记录

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

如何创建将返回大量数据的API.任何帮助将不胜感激

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

推荐答案

这是因为api返回的数据太大，导致产生内存溢出的过程.您需要使用分页sql:

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

客户端处理一次，多次调用api，完成所有数据采集.

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

这篇关于引发了类型'System.OutOfMemoryException'的异常WEB API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！