处理Web API返回的大型JSON数据

Question

我们正在构建一个web API，它接收字符串数组作为输入参数，查询oracle数据库并以JSON文件的形式返回结果。

所以代码是这样的

 namespace PDataController.Controllers
{
  public class ProvantisDataController : 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))
        {
            var inconditions = id.Distinct().ToArray();
            var srtcon = string.Join(",", inconditions);
            DataSet userDataset = new DataSet();
            var strQuery = @"SELECT 
                           STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, 
                           STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, 
                           Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, 
                           STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME , 
                           Trunc(STCD_PRIO_CATEGORY_DESCR.END_DATE) AS SESSION_END_DATE, 
                             FROM 
                             STCD_PRIO_CATEGORY_DESCR, 
                             WHERE 
                            STCD_PRIO_CATEGORY_DESCR.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,OracleDbType.NVarchar2);
                   p.Value = inconditions[x];
                   prms.Add(p);
                 }
            if(sb.Length > 0) sb.Length--;
            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=ProvantisStudyData.json", out contentDisposition))
                    {
                        response.Content.Headers.ContentDisposition = contentDisposition;
                    }
                    return response;
                }
            }

        }
    }
}
}

接口返回数据格式如下

{"data":[{"CATEGORY":"Internal Study","SESSION_NUMBER":7,"SESSION_START_DATE":"2015-02-13T00:00:00","SESSION_START_TIME":"2015-02-13T10:33:59.288394","SESSION_END_DATE":"2015-02-13T00:00:00"}]}

我们有时会在返回大量数据时遇到问题，因为它会抛出OutOfMemory异常。

建议使用JSON属性，与“data”属性并行:如“next_data”，带有需要传递到SQL偏移量中的值(在MySQL中有效，我不确定这在oracle中是否有效)，如果没有剩余数据，则将“next_data”的值设置为0。我不确定如何实现this.Not确定这是否可以实现。这方面的任何帮助都是非常感谢的。

​

Answer 1

您的问题是，您正在运行一个返回大量结果的Oracle查询，然后在将结果集序列化到HttpResponseMessage之前将整个结果集加载到内存中。

为了减少内存使用，您应该找到并消除所有查询结果集被加载到临时中间表示(例如，DataTable或JSON字符串)中的情况，而不是使用DataReader将数据流出。根据this answer的说法，这避免了一次将所有内容都放到内存中。

首先，从您的回溯来看，您似乎已经检查了。由于这显然是试图在MemoryStream中缓存整个响应，因此您需要按照FilePathResult thrown an OutOfMemoryException with large file中的说明禁用它。

接下来，您可以使用带有以下类和转换器的Json.NET将IDataReader的内容直接流式传输到JSON：

[JsonConverter(typeof(OracleDataTableJsonResponseConverter))]
public sealed class OracleDataTableJsonResponse
{
    public string ConnectionString { get; private set; }
    public string QueryString { get; private set; }
    public OracleParameter[] Parameters { get; private set; }

    public OracleDataTableJsonResponse(string connStr, string strQuery, OracleParameter[] prms)
    {
        this.ConnectionString = connStr;
        this.QueryString = strQuery;
        this.Parameters = prms;
    }
}

class OracleDataTableJsonResponseConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(OracleDataTableJsonResponse);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        throw new NotImplementedException("OracleDataTableJsonResponse is only for writing JSON.  To read, deserialize into a DataTable");
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        var response = (OracleDataTableJsonResponse)value;

        using (var dbconn = new OracleConnection(response.ConnectionString))
        {
            dbconn.Open();
            using (var selectCommand = new OracleCommand(response.QueryString, dbconn))
            {
                if (response.Parameters != null)
                    selectCommand.Parameters.AddRange(response.Parameters);
                using (var reader = selectCommand.ExecuteReader())
                {
                    writer.WriteDataTable(reader, serializer);
                }
            }
        }
    }
}

public static class JsonExtensions
{
    public static void WriteDataTable(this JsonWriter writer, IDataReader reader, JsonSerializer serializer)
    {
        if (writer == null || reader == null || serializer == null)
            throw new ArgumentNullException();
        writer.WriteStartArray();
        while (reader.Read())
        {
            writer.WriteStartObject();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                writer.WritePropertyName(reader.GetName(i));
                serializer.Serialize(writer, reader[i]);
            }
            writer.WriteEndObject();
        }
        writer.WriteEndArray();
    }
}

然后修改您的代码，使其如下所示：

    public HttpResponseMessage Getdetails([FromUri] string[] id)
    {
        var prms = new List<OracleParameter>();
        var connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
        var inconditions = id.Distinct().ToArray();
        var strQuery = @"SELECT 
                       STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, 
                       STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER, 
                       Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE, 
                       STCD_PRIO_CATEGORY_DESCR.START_DATE AS SESSION_START_TIME , 
                       Trunc(STCD_PRIO_CATEGORY_DESCR.END_DATE) AS SESSION_END_DATE, 
                         FROM 
                         STCD_PRIO_CATEGORY_DESCR, 
                         WHERE 
                        STCD_PRIO_CATEGORY_DESCR.STD_REF IN(";
        var sb = new StringBuilder(strQuery);
        for (int x = 0; x < inconditions.Length; x++)
        {
            sb.Append(":p" + x + ",");
            var p = new OracleParameter(":p" + x, OracleDbType.NVarchar2);
            p.Value = inconditions[x];
            prms.Add(p);
        }
        if (sb.Length > 0)// Should this be inconditions.Length > 0  ?
            sb.Length--;
        strQuery = sb.Append(")").ToString();

        var returnObject = new { data = new OracleDataTableJsonResponse(connStr, strQuery, prms.ToArray()) };
        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;
    }

这避免了结果在内存中的DataSet表示。

顺便说一句，我估计这条线

        if (sb.Length > 0)
            sb.Length--;

相反，应该是：

        if (inconditions.Length > 0)
            sb.Length--;

我相信您正在尝试去掉查询中的尾随逗号，当且仅当inconditions.Length > 0时才会出现

请注意-我不是Oracle开发人员，也没有安装Oracle。为了测试，我使用底层OleDbConnection模拟了OracleClient类，它工作得很好。

Answer 2

你被允许改变你的方法来获取数据吗？我的意思是，如果您正在使用RESTful服务，那么在单个请求中传输如此多的数据并不是一个好主意。也许为了这个目的而下载一个文件，或者通过分页获得数据。

您还可以尝试更改最大请求长度，如下所示：Change max request lenght

但同样，web应用程序一次传输和/或处理如此多的数据并不是一件好事。