Click here to Skip to main content
15,883,705 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Using an ajax call I get data by'CreateReport' method.

Here is my ajax call:

JavaScript
$.ajax({
      type: "POST",
      url: "Service.asmx/CreateReport",
      data: "{'reportname':'" + ReportID + "','datasourceid':'" + DataSourceId[i] + "','parameter':'" + Parameter + "'}",
      contentType: "application/json; charset=utf-8",
      dataType: "json",
      async: false,
      index: i,

      success: function (response) {
          numberofquery = numberofquery + 1;
          var Query = response.d;
          //alert(Query);

          store_data(Query, this.index);

          function store_data(Query, i) {
              try {
                  if (Query != null || Query != "") {

                      Query = JSON.parse(Query);


                      window[QueryName[i]] = [];

                      window[QueryName[i]] = Query;

                  }

              }

              catch (err) { }
          }


      },

      async: true,


      error: function (msg)
      {
          numberofquery = numberofquery + 1;
          CustomAlertShow("Error");
      },

      complete: function () {

          //GenerateAllReports();
          //console.log("numq=" + numberofquery + ",totalq=" + totalquery);
          if (numberofquery == totalquery) GenerateWithReloadedData();
      },

  });


And here is my method in service.


C#
[WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public string CreateReport(string reportname, string datasourceid, string parameter)
    {

        try
        {
            DataSet dataSet1 = new DataSet();
            SqlConnection cn1 = new SqlConnection(ConStr);

            string queryString = "Select ReportID, DataSourceID from dbo.ReportDataSourceDataModel " 

            SqlDataAdapter daGlobalClass = new SqlDataAdapter(queryString, cn1);

            cn1.Open();


            daGlobalClass.Fill(dataSet1, "myDataTable");

            cn1.Close();

            return JsonConvert.SerializeObject(dataSet1.Tables[0]);


        }
        catch (SqlException ex)
        {

            return "-ERR: " + DisplaySqlErrors(ex);

        }
    }



In general it works fine when dataset1 has around 100000 data. But this service doesn't work for large amount of data.

How can I get over million data through this webservice anad using this ajax call ?
Posted
Updated 25-May-15 23:40pm
v3
Comments
Mehdi Gholam 26-May-15 5:50am    
Why transfer that much data?
[no name] 26-May-15 7:29am    
S
vinayvraman 26-May-15 6:14am    
What do you mean when you say doesn't work? Does it timeout? Does it throw any error?
sachi Dash 26-May-15 9:19am    
Thanks a lot for reply.

If I select Top 10000 data then it works. For the following query it works fine:
"SELECT TOP 10000 T0.url AS '[Q1].[Site]' FROM user_url T0 "

But If I select all data like: "SELECT T0.url AS '[Q1].[Site]' FROM user_url T0 ". It does not works. It shows a error list like that:

System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.
at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(String value)
at System.IO.StringWriter.Write(String value)
at Newtonsoft.Json.JsonTextWriter.WriteEnd(JsonToken token)
at Newtonsoft.Json.JsonWriter.AutoCompleteClose(JsonContainerType type)
at Newtonsoft.Json.JsonWriter.InternalWriteEnd(JsonContainerType container)
at Newtonsoft.Json.JsonWriter.WriteEndObject()
at Newtonsoft.Json.JsonWriter.WriteEnd(JsonContainerType type)
at Newtonsoft.Json.JsonWriter.WriteEnd()
at Newtonsoft.Json.JsonWriter.AutoCompleteAll()
at Newtonsoft.Json.JsonWriter.Close()
at Newtonsoft.Json.JsonTextWriter.Close()
at Newtonsoft.Json.JsonWriter.Dispose(Boolean disposing)
at Newtonsoft.Json.JsonWriter.System.IDisposable.Dispose()
at Newtonsoft.Json.JsonConvert.SerializeObjectInternal(Object value, Type type, JsonSerializer jsonSerializer)
at Newtonsoft.Json.JsonConvert.SerializeObject(Object value, Type type, JsonSerializerSettings settings)
at Newtonsoft.Json.JsonConvert.SerializeObject(Object value)
at Service.CreateReport(String reportname, String datasourceid, String parameter) in d:\BI-FERRET\App_Code\Service.cs:line 133
F-ES Sitecore 26-May-15 10:53am    
One thing you can do is to not return the table object but instead build a collection of classes that has just the data you need to return and return that instead. No guarantee that it will work but it has a better chance. Ultimately if you need to pass so much data to the client there is something wrong with your interface or your architecture.

Thats interesting! Looking at the exception stack I see that the error is not on the client side but on the server while converting your data from datatable to json string. This means "JsonConvert.SerializeObject(dataSet1.Tables[0]);" is throwing the error.

There are quite a few options you can choose to get rid of this error:

1. Save the data as a file and allow the user to download the file into his local machine. This will allow him to see the data in his machine.
2. Implement pagination so that at a time only a small set of information is exchanged between client and server. (E.g. gmail uses pagination.)
3. Stream the data (check Buffered response)

See the below mentioned links for more information:

https://msdn.microsoft.com/en-in/library/aa528818.aspx[^]

http://www.drdobbs.com/windows/parsing-big-records-with-jsonnet/240165316?pgno=2[^]

http://www.newtonsoft.com/json/help/html/Performance.htm[^]
 
Share this answer
 
As specified in Solution 3, those are your only options.

What's happening is that the object you're converting to JSON is being serialized into a string that is way too big for .NET to hold. The largest any object (including a string) can be is 2GB in size.

You're simply returning too much data to be serialized into a string and returned to the client.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900