0

I am creating a asp.net Web API service end point which return a bulk data from the oracle database. I am converting the returned data in the JSON format. It was working fine, but suddenly I am getting an error saying Out of Memory exception in the string result. 

  public HttpResponseMessage Getdetails([FromUri] string[] id)
    {
        using (OracleConnection dbconn = new OracleConnection("DATA SOURCE=J;PASSWORD=C;PERSIST SECURITY INFO=True;USER ID=T"))
        {
            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 + ")";
            OracleCommand selectCommand = new OracleCommand(strQuery, dbconn);
            OracleDataAdapter adapter = new OracleDataAdapter(selectCommand);
            DataTable selectResults = new DataTable();
            adapter.Fill(selectResults);
            string result = JsonConvert.SerializeObject(selectResults);
            string contentDisposition = "inline; filename=ProvantisStudyData.json";
            //byte[] byteInfo = Encoding.ASCII.GetBytes(result);
            HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.OK, result, MediaTypeHeaderValue.Parse("application/json"));
            response.Content.Headers.ContentDisposition = ContentDispositionHeaderValue.Parse(contentDisposition);
            //response.Content.Headers.ContentLength = byteInfo.Length;
            return response;
   }
 }

Above the code I am using. I am not sure why I am geting the exeception suddelny and why sometimes it is working. Any help is greatly appreciated.

trx
  • 2,077
  • 9
  • 48
  • 97
  • Because sometimes the runtime can allocate a contiguous piece of memory to contain the string, and sometimes it can't. Or because sometimes `id` contains ids yielding fewer, smaller results, and sometimes it contains more, longer results. Or perhaps because some objects have circular references, which JSON.NET _should_ detect. – CodeCaster Jul 28 '16 at 15:13
  • 2
    How much data are you trying to convert at once? How are you processing/displaying the data? Is your application running as a 32 or 64 bit application? If the purpose is for displaying data, then you should page the results. Also, get rid of the select *. You never know when someone will add a blob field into a table and suddenly your results go from 12KB to 6GB. – DVK Jul 28 '16 at 15:14
  • http://stackoverflow.com/questions/8630736/getting-an-outofmemoryexception-while-serialising-to-json – Danny Cullen Jul 28 '16 at 15:14
  • I should be returning a file. I gave select * in the question becaus the select statement was longer and didnt want put the entire column names. But in my application I am giving the column names explicitly. For example sometimes the query returns over 50,000 records – trx Jul 28 '16 at 15:23
  • _"sometimes the query returns over 50,000 records"_ - and you wonder why this throws an OutOfMemoryException? Implement paging. – CodeCaster Jul 28 '16 at 15:27
  • @CodeCaster with the same filter for id sometime it returns the result sometimes it throws exception. The same filter returns over 50,000 records – trx Jul 28 '16 at 15:28
  • @CodeCaster I am new to .C# and .NET and not sure what is paging. Do I import it on to application – trx Jul 28 '16 at 15:30
  • Well you didn't mention that. But like I said in my previous comment, that exception occurs when the runtime can't allocate a contiguous piece of memory. The exact stack trace (which you omitted) would really help only a little bit (I guess it's at a `StringBuilder.ToString()` inside JSON.NET), because the exception proves you're simply trying to stuff too large a response in memory at once. – CodeCaster Jul 28 '16 at 15:31
  • No, paging as in limiting the number of records that you return from this API at once. So clients request record 1-50 at the first request, 51-100 at the second and so on. – CodeCaster Jul 28 '16 at 15:31
  • @CodeCaster But we will need all the records in the response. The response is going to be used in other application. Is there any other way of handling this exception. Like last 4 times I tried to run the application it returns the result. EXceptionally few times it fails – trx Jul 28 '16 at 15:33
  • 1
    You're going to have trouble handling very large responses at each step of your application -- this is only the first error you'll get. As CC recommended paging is the way this is generally handled. Usually if a service is going to have to handle a response that is very large, or one with many records, it will only give you x number of records at a time, with an indicator that there are more available. – mikey Jul 28 '16 at 15:36
  • 1
    To implement this pattern, you'll need to architect your service such that you only return a maximum of x records, with an indicator that there are more. You hold the result in memory on the server side, and if the client requests the additional records you then return those. On the client side the consumption can be a while loop. – mikey Jul 28 '16 at 15:36
  • I _really_ beg to differ that you need all records in one call from the client. If you think you really do, and you know for sure that the client can handle it, you can run this application in/as a 64-bit process so it can access more memory, and thus has more chance of allocating a contiguous piece of memory big enough to hold the output string. – CodeCaster Jul 28 '16 at 15:37
  • How do I check if my application is 64 or 32 bit – trx Jul 28 '16 at 15:39
  • @CodeCaster can I convert them directly? Or do I have to change the entire application. I am very new to .NET applications – trx Jul 28 '16 at 15:47

0 Answers0