处理Web API返回的大型JSON数据 [英] Dealing with large JSON data returned by Web API
问题描述
我们正在构建一个Web API，该API接收字符串数组作为输入参数，该参数查询oracle数据库并将结果作为JSON文件返回.
We are building an web API that receives the array of strings as input parameter which queries the oracle database and returns the result as a JSON file.
所以代码就像
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;
}
}
}
}
}
}
为API返回的数据采用以下格式
The data returned for the API is in the below format
{"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异常的大量数据时会遇到问题. 建议使用与"data"属性类似的JSON属性:类似于"next_data"，其值需要传递给SQL OFFSET(在MySQL中有效，我不确定这是否适用于oracle)，如果没有剩余数据，则将"next_data"的值设置为0.我不确定如何实现.不确定是否可以实现.任何帮助，我们将不胜感激.
We are sometimes having issue in returning the large amount of data it throws the OutOfMemory Exception. It was suggested to use the JSON property, parallel to the "data" property: like "next_data", with a value of the value you need to pass into the SQL OFFSET (which works in MySQL, I am not sure if this works in oracle),if there no data remaining then set the value of "next_data" to 0.I am not sure how to implement this.Not sure if this can be implemented. Any help with this is greatly appreciated.
推荐答案
您的问题是，您正在运行一个Oracle查询，该查询将返回大量结果，然后将整个结果集加载到内存中，然后进行序列化到HttpResponseMessage.
Your problem is that you are running an Oracle query that is returning a very large number of results, and then loading that entire result set into memory before serializing it out to the HttpResponseMessage.
为减少内存使用量，应查找并消除所有将查询的整个结果集都加载到临时中间表示形式(例如DataTable或JSON字符串)中的情况，而应使用 DataReader .这避免了根据
To reduce your memory usage, you should find and eliminate all cases where the entire set of results from the query is loaded into a temporary intermediate representation (e.g. a DataTable or JSON string), and instead stream the data out using a DataReader. This avoids pulling everything into memory at once according to this answer.
首先，从追溯开始，您似乎已经 启用浏览器链接 已选中.因为这显然试图将整个响应缓存在MemoryStream中，所以您将要禁用它，如
First, from your traceback, it appears you have Enable Browser Link checked. Since this apparently tries to cache the entire response in a MemoryStream, you will want to disable it as explained in FilePathResult thrown an OutOfMemoryException with large file.
接下来，您可以流式传输IDataReader 使用Json.NET和以下类和转换器直接转换为JSON:
Next, you can stream the contents of an IDataReader directly to JSON using Json.NET with following class and converter:
[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();
}
}
然后修改您的代码，使其类似于:
Then modify your code to look something like:
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.
This avoids the in-memory DataSet representation of the results.
顺便说一句，我认为这条线
Incidentally, I reckon the line
if (sb.Length > 0)
sb.Length--;
应该是:
if (inconditions.Length > 0)
sb.Length--;
我相信您正在尝试剥离查询中的尾随逗号，当且仅当inconditions.Length > 0
I believe you're trying to peel off the trailing comma in the query, which will be present if and only if inconditions.Length > 0
请注意-我不是Oracle开发人员，并且没有安装Oracle.为了进行测试，我使用底层的OleDbConnection模拟了OracleClient类，并且效果很好.
Please note - I'm not an Oracle developer and I don't have Oracle installed. For testing I mocked up the OracleClient classes using an underlying OleDbConnection and it worked fine.
这篇关于处理Web API返回的大型JSON数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
