Running into OutOfMemoryException when serializing List of Objects which needs to be ported as JSON to a DataTable

Question

I have a very large List of Objects (Totaling 186799) which I am attempting to port to a DataTable in JSON format. The total length of the Serialized list of objects is 62553299. How can I port this data from a webservice in JSON format to a DataTable in a aspx file.

public void GetData()
{
    DataTable dt;
    string connectionString = "----";
    string selectCommand = "SELECT -----";
    using (AdomdConnection conn = new AdomdConnection(connectionString))
    {
        conn.Open();
        using (AdomdDataAdapter adapter = new AdomdDataAdapter (selectCommand, conn))
        {
            dt = new DataTable();
            adapter.Fill(dt);
            List<ResourceData> ResourceInfo = new List<ResourceData>();
            ResourceData ResourceInfoRow = null;
            foreach (DataRow dr in dt.Rows)
            {
                ResourceInfoRow = new ResourceData();
                ResourceInfoRow.SourceProject = dr.ItemArray[0].ToString();
                ResourceInfoRow.SourceFile= dr.ItemArray[1].ToString();
                ResourceInfoRow.Project = dr.ItemArray[2].ToString();
                ResourceInfoRow.File = dr.ItemArray[3].ToString();
                ResourceInfoRow.Parent = dr.ItemArray[4].ToString();
                ResourceInfoRow.Id = dr.ItemArray[5].ToString();
                ResourceInfo.Add(ResourceInfoRow);
            }
            JavaScriptSerializer js = new JavaScriptSerializer();
            js.MaxJsonLength = 2147483647;
            Context.Response.Write(js.Serialize(ResourceInfo)); //This is where I hit the OutOfMemoryException
        }
        conn.Close();
    }
}

I have to port the data back in json format since I am using a DataTable plugin.

Thank you for your responses.

Suppose, you have a *StartOffset* and *Length* parameters and you return only limited set of result to the user, like many public APIs do(facebook, instagram, twitter etc.) Wouldn't it be better? — Eser, Apr 08 '16 at 20:16
Obvious thing to try would be to stream to `Context.Response.OutputStream`, but [`JavaScriptSerializer`](https://msdn.microsoft.com/en-us/library/system.web.script.serialization.javascriptserializer.aspx) apparently has no methods to write to a stream so that's out. You might consider switching to [tag:json.net] and streaming directly along the lines of [JSON.net serialize directly from oledbconnection](https://stackoverflow.com/questions/33835729/json-net-serialize-directly-from-oledbconnection). — dbc, Apr 08 '16 at 20:34

score 1 · Accepted Answer · answered Apr 08 '16 at 20:40

1

It is very likely your string is to big for your memory or the string it self it to big (2gb). You try to serialize a lot of objects. So probably the best way is to split the objects into multiple chucks for serialization. Or you can try another converter like: Newtonsoft. The Newtonsoft serializer is a fast and probably the most used serializer out there. The Newtonsoft serializer is also able to convert to json using a stream: Serialize to stream. Which can be directly written to the Response object.

answered Apr 08 '16 at 20:40

Peter

27,590
8
64
84

Thanks Peter. Just realized that the point of failure occurs when attempting to write the serialized data. – JayHawk Apr 08 '16 at 22:41
As you suggested Peter, I split the data. In my aspx page, I used $.when(x,y).done(function(a,b){} to get the two json data sets and then used $.merge to combine the data. – JayHawk Apr 13 '16 at 17:46

score 0 · Answer 2 · answered Apr 08 '16 at 23:42

You apparently are running out of memory trying to serialize to a very large string, then write that string to a Context.Response stream. One solution would be to use json.net and do something along the lines of JSON.net serialize directly from oledbconnection, which streams directly from an IDataReader without ever loading the entire contents of the query into memory. This might work with an AdomdDataReader, though I've not tried it.

You, however, are using JavaScriptSerializer, which has no methods to serialize directly to a stream. Still, since what you are serializing is an IEnumerable<T>, it should be possible to serialize each item individually, then write each individually, manually enclosing the whole in brackets as required for a JSON array:

public static class JavaScriptSerializerExtensions
{
    public static void SerializeToStream<T>(this JavaScriptSerializer serializer, IEnumerable<T> collection, Stream stream)
    {
        if (serializer == null || collection == null || stream == null)
            throw new ArgumentNullException();
        var writer = new StreamWriter(stream, new UTF8Encoding(false));  // DO NOT DISPOSE!
        writer.Write("[");
        long count = 0;
        var sb = new StringBuilder();
        var buffer = new char[4000];
        foreach (var item in collection)
        {
            if (count > 0)
                writer.Write(",");
            sb.Length = 0;
            serializer.Serialize(item, sb);
            writer.Write(sb, buffer);
            count++;
        }
        writer.Write("]");
        writer.Flush();
    }
}

public static class TextWriterExtensions
{
    public static void Write(this TextWriter writer, StringBuilder sb, char[] buffer = null)
    {
        if (sb == null || writer == null)
            throw new ArgumentNullException();
        if (buffer != null && buffer.Length <= 0)
            throw new ArgumentException("buffer != null && buffer.Length <= 0");
        var length = sb.Length;
        if (length <= 0)
            return;
        buffer = buffer ?? new char[Math.Min(4000, length)];
        for (int index = 0; index < length; index += buffer.Length)
        {
            var count = Math.Min(length - index, buffer.Length);
            sb.CopyTo(index, buffer, 0, count);
            writer.Write(buffer, 0, count);
        }
    }
}

And then do:

        var stream = Context.Response.OutputStream;

        var query = from dr in dt.Rows.Cast<DataRow>()
                    select new
                    {
                        SourceProject = dr.ItemArray[0].ToString(),
                        SourceFile = dr.ItemArray[1].ToString(),
                        Project = dr.ItemArray[2].ToString(),
                        File = dr.ItemArray[3].ToString(),
                        Parent = dr.ItemArray[4].ToString(),
                        Id = dr.ItemArray[5].ToString(),
                    };

        var js = new JavaScriptSerializer();
        js.MaxJsonLength = 2147483647;

        js.SerializeToStream(query, stream);

score 0 · Answer 3 · answered Dec 07 '18 at 11:21

In short JavascriptSerializer has several overloads. If you use the one that returns a string you will be limited to StringBuilder max .ToString() capacity of just over 100.000.000 chars (string length limit thing in .net).

    //.Net source
    internal string Serialize(object obj, SerializationFormat serializationFormat)
    {
        StringBuilder sb = new StringBuilder();
        Serialize(obj, sb, serializationFormat);
        return sb.ToString();
    }

So instead, use the overload where you supply the StringBuilder. Then after serialization fetch chunks of strings at a time with .ToString(index, length) not exceeding that max size of 100.000.000 or so chars.

    public void Serialize(object obj, StringBuilder output)
    {
        Serialize(obj, output, SerializationFormat.JSON);
    }

    //usage afterwards
    sb.ToString(0, 100000);

See StringBuilder.ToString() throws OutOfMemoryException

Running into OutOfMemoryException when serializing List of Objects which needs to be ported as JSON to a DataTable

3 Answers3