locked
ASP .NET MVC web API service is loading forever RRS feed

  • Question

  • User-362386836 posted

    <div class="post-text" itemprop="text">

    I created the ASP .NET MVC web API service which executes a Oracle query using the just controller.

    But when I try to access the service http://localhost:64614/api/Prnt and it keeps loading for ever and nothing is being returned

    The query it is trying to execute is complex and if I try to give a simple select statement it is returning the result. I tested my complex query in the DB and it works

    How do I check on which APP Pool my application is running?

    Oracle.ManagedDataAccess.Client;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net;
    using System.Net.Http;
    using System.Web.Http;
    using System.Data;
    using Newtonsoft.Json;
    namespace TestingPr.Controllers
    {
    public class PrController : ApiController
    {
        public string Get()
        {
            var strQuery = @" Complex query here  ";
            OracleConnection dbConn = new OracleConnection("DATA SOURCE=ABC;PASSWORD=ABCD;PERSIST SECURITY INFO=True;USER ID=TUV");
            dbConn.Open();
            OracleCommand selectCommand = new OracleCommand(strQuery, dbConn);
            OracleDataAdapter adapter = new OracleDataAdapter(selectCommand);
            DataTable selectResults = new DataTable();
            adapter.Fill(selectResults);
            dbConn.Close();
            return JsonConvert.SerializeObject(selectResults)
        }
    }

    In the place of complex query here , if I put the simple select it works but if I change to complex query it doesnt. When I cheked the query in DB it returns 151 records, so do I need give anything extra in my code? Any help is greatly appreciated

    This is my first application and kind of stuck.

    </div>

    Thursday, July 21, 2016 5:42 AM

Answers

  • User753101303 posted

    Hi,

    So your query returns 151 rows but could it be that it returns blob columns as well for example causing to return a huge payload? Also are you sure that "loading forever" doesn't mean that you have a JavaScript error or that the Ajax query fails (do you have an error function to show something when it happens). You can also use the VS debugger to see what happens on the server side (for example assign JsonConvert.SerializeObject(selectResults) to a variable before returning it and see in the debugger what is the length of this string etc...

    IMO the key point in debugging is making first sure to perfectly understand what happens  It will be then much issuer to fix an error you understodd rather than to wonder what causes an error you didn't understood yet. In my experience "loading forever" doesn't happen or very rarely (so as suggested earlier check the size of the returned string, double check you don"t have a server side or client side error that make things to "stop with an error" rather than to "load forever").

    Not directly related but if used from a browser you usually return a list of object rather than a DataTable. Also the web API does the serialization for you (ie your function just return a list of objects and the "plumbing" is done for you).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 21, 2016 6:41 AM
  • User36583972 posted

    Hi vdha,

    Given the behavior you describe, I suspect your method load a LOT of data from the database, which takes a long time and looks like no response. Maybe you can refer the following suggestions.

    1: You can use Async/Await in your ASP.NET project.

    Async Programming : Introduction to Async/Await on ASP.NET:

    https://msdn.microsoft.com/en-us/magazine/dn802603.aspx

    2: Optimize your SQL statements, Improve performance and reduce time. API calls should be very deterministic in the way they query the database and return strictly defined result sets. Writing a well-performing API is hard  and query performance is critical.

    3: Try to disable the lazy loading in the New constructor of every WebApi repository.

    Best Regards,

    Yohann Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 22, 2016 2:21 AM

All replies

  • User753101303 posted

    Hi,

    So your query returns 151 rows but could it be that it returns blob columns as well for example causing to return a huge payload? Also are you sure that "loading forever" doesn't mean that you have a JavaScript error or that the Ajax query fails (do you have an error function to show something when it happens). You can also use the VS debugger to see what happens on the server side (for example assign JsonConvert.SerializeObject(selectResults) to a variable before returning it and see in the debugger what is the length of this string etc...

    IMO the key point in debugging is making first sure to perfectly understand what happens  It will be then much issuer to fix an error you understodd rather than to wonder what causes an error you didn't understood yet. In my experience "loading forever" doesn't happen or very rarely (so as suggested earlier check the size of the returned string, double check you don"t have a server side or client side error that make things to "stop with an error" rather than to "load forever").

    Not directly related but if used from a browser you usually return a list of object rather than a DataTable. Also the web API does the serialization for you (ie your function just return a list of objects and the "plumbing" is done for you).

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 21, 2016 6:41 AM
  • User36583972 posted

    Hi vdha,

    Given the behavior you describe, I suspect your method load a LOT of data from the database, which takes a long time and looks like no response. Maybe you can refer the following suggestions.

    1: You can use Async/Await in your ASP.NET project.

    Async Programming : Introduction to Async/Await on ASP.NET:

    https://msdn.microsoft.com/en-us/magazine/dn802603.aspx

    2: Optimize your SQL statements, Improve performance and reduce time. API calls should be very deterministic in the way they query the database and return strictly defined result sets. Writing a well-performing API is hard  and query performance is critical.

    3: Try to disable the lazy loading in the New constructor of every WebApi repository.

    Best Regards,

    Yohann Lu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 22, 2016 2:21 AM