Scenario: I have a WebApi that runs a method on my service layer that executes a query either returns 500k result lines, containing 90 columns.
Method that queries and traverses results:
using (var command = Session.Connection.CreateCommand())
{
command.CommandText = dataSource.Query;
var name = "";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
}
}
}
Today to try to build a JSON I use the JsonTextWriter class:
StringBuilder sb = new StringBuilder(int.MaxValue);
StringWriter sw = new StringWriter(sb);
using (JsonWriter jrWriter = new JsonTextWriter(sw))
{
using (var command = Session.Connection.CreateCommand())
{
command.CommandText = dataSource.Query;
var name = "";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
int fields = reader.FieldCount;
jrWriter.WriteStartObject();
for (int i = 0; i < fields; i++)
{
jrWriter.WritePropertyName(name);
name = reader.GetName(i);
if (reader[i] == null)
{
jrWriter.WriteValue("");
}
else if (reader[i] is string)
{
jrWriter.WriteValue(reader[i].ToString().Trim());
}
else if (reader[i] is bool)
{
var value = (bool)reader[i];
if (value)
jrWriter.WriteValue("true");
else
{
jrWriter.WriteValue("false");
}
else if (reader[i] is DateTime)
{
jrWriter.WriteValue(DateTime.Parse(reader[i].ToString()));
}
else
{
jrWriter.WriteValue(reader[i].ToString().Trim().Replace(",", "."));
}
jrWriter.WriteEndObject();
}
}
}
}
Problem:
After iterating 150k records, SystemOutMemoryException is generated.
I have tried several other solutions to try to send this mass of data to the front, but always crash in the same exception.
I have tried to convert each of my records to an array of bytes and include each record in an Array of byte array, so I even managed to iterate through all records, but at the time of returning to the front, the SystemOutMemoryExeption .
If someone has already had to send a lot of data through the webApi, I'm open to receiving tips to do this better, because I believe the problem is always having to convert everything to string.