locked
How to transfer large data through webservice

    Question

  • Hi everyone,
    I'm working with webservice on Net 2.0. I have a mysql database whose size is about 200MB. I want to make a copy of this database and send it to client through webservice in XML format.
    I try to do this in some ways. I divide and read small parts of the database then save in a XML file on Webserver machine so that i will compress it  before sending to client. However, when webserver is reading data from database, it is broken. Maybe I didn't code it well.
    Could someone give a solution for this? Please give me a code example if possible. I stress with this problem.
    Any idea is highly appreciated. Sorry for my poor english.
    Minh Chieu
    Wednesday, June 27, 2007 10:20 AM

Answers

  •  

    Possible cause of failure in your case could be an executionTimeout.

     

    One of the best way to transfer the entire database through webservice is you can dump the entire db into a file with mysql dump/backup utilities and then stream the file using webservice streaming.

     

    Check the Bulk Data Transfer in webservice methods in: Improving Web Service performance improvement.

     

    Also check this article on streaming large data in MSDN for a sample.

     

    For any MySql related queries please use appropriate forums for it. Thanks!

    Wednesday, June 27, 2007 5:21 PM

All replies

  • if i understand correctly this is failing when querying the database?

    if so let me know and I'll move the thread to the ".NET Framework Data Access and Storage" where you will reach the experts of the area.

     

    in the meantime, in order to get the necessary help, could you send all the details of the failure you're seeing? exception messages, stack traces, the more the better.

     

    Wednesday, June 27, 2007 3:44 PM
  •  

    Possible cause of failure in your case could be an executionTimeout.

     

    One of the best way to transfer the entire database through webservice is you can dump the entire db into a file with mysql dump/backup utilities and then stream the file using webservice streaming.

     

    Check the Bulk Data Transfer in webservice methods in: Improving Web Service performance improvement.

     

    Also check this article on streaming large data in MSDN for a sample.

     

    For any MySql related queries please use appropriate forums for it. Thanks!

    Wednesday, June 27, 2007 5:21 PM
  • Hi all,
    Firstly, thanks very much for your immediate reply,
    Actually, I want webservice get a datatable and save it to XML file.The datatable is about 500.000 rows or more. This is my code.

    [WebMethod]
        public void GetandSaveDataToXML()
        {
            bool notExitsXmlFile = true;
            OdbcConnection con = new OdbcConnection(constr);
            con.Open();
            for (int j = 0; j < 500; j++)
            {
                OdbcDataAdapter daplumst;
                DataTable dtplumst = new DataTable("Plumst");
                int startpoint = j * 1000;
                int endPoint = startpoint + 1000;
               
                string selectPlumst = "SELECT * FROM plumst LIMIT " + startpoint + "," + endPoint;
                daplumst = new OdbcDataAdapter(selectPlumst, con);
                daplumst.Fill(dtplumst);

                if (notExitsXmlFile )
                {
                    dtplumst.WriteXml("C:/Inetpub/wwwroot/MyWebservice/Plumst.xml");
                    notExitsXmlFile = false;
                }
                else
                {
                    XmlDocument doc = new XmlDocument();
                    doc.Load("C:/Inetpub/wwwroot/MyWebservice/Plumst.xml");

                    for (int i = 0; i < dtplumst.Rows.Count; i++)
                    {
                        XmlElement xmlEl = CreateAppendElement(doc.DocumentElement, "Plumst", null);
                        CreateAppendElement(xmlEl, "JANCODE", dtplumst.RowsIdea["JANCODE"].ToString());
                        CreateAppendElement(xmlEl, "HINSYU", dtplumst.RowsIdea["HINSYU"].ToString());
                        CreateAppendElement(xmlEl, "HATMKR", dtplumst.RowsIdea["HATMKR"].ToString());
                        CreateAppendElement(xmlEl, "HINBAN", dtplumst.RowsIdea["HINBAN"].ToString());
                        CreateAppendElement(xmlEl, "ARTCD", dtplumst.RowsIdea["ARTCD"].ToString());
                        CreateAppendElement(xmlEl, "UPDYMD", dtplumst.RowsIdea["UPDYMD"].ToString());
                    }
                    doc.Save("C:/Inetpub/wwwroot/MyWebservice/Plumst.xml");
                }

                daplumst.Dispose();
                dtplumst.Dispose();
            }
            con.Close();
        }

    and this is error message.


    Server Application Unavailable
    The web application you are attempting to access on this web server is currently unavailable.  Please hit the "Refresh" button in your web browser to retry your request.
    Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.

     

    The error happens when XML file is about over 100MB. This is just a my solution and maybe it is not correct.  By the way, after creating the XML, i send it to client so that the client can work offline. If someone have another way for all these, please help me.

    Thanks,
    PS: To ranamauro : Please help me to move the topic to "NET Framework Data Access and Storage".

    Monday, July 02, 2007 10:27 AM
  • from the additional details you sent, it does look like the problem is not in the data layer, so no need to move the thread at this point.

     

    could you follow these instructions and send us the relevant pieces?

    "An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur. "

     

    note on the design: I'm not sure I understand why you're saving to a file, what's the reasoning behind it? what happens if you have multiple callers to GetandSaveDataToXML()?
    Tuesday, July 03, 2007 3:50 AM
  • Please be aware that by using XmlDocument, you are forcing the entire xml document into memory.   Given that you are building the XmlDocument using a DataSet, you probably have 2 copies of your database in memory. 

     

    If the webservice consumer is also a .NET application, you can skip a few steps and just return the dataset from the webservice.  I would not recommend it as best practice, but I think in your case it might be what is most practical.

     

    You can also move your con.Close() up to just after you call DataAdapter.Fill.

     

    If you really want to write the xml file to disk, I would suggest using XmlWriter (assuming this is .NET 2.0).  It would drasticly reduce memory usage.

     

    XmlWriter xml = XmlWriter.Create(file);

    xml.WriteStartElement("Plumst");

    etc..

     

    http://msdn2.microsoft.com/en-us/library/system.xml.xmlwriter.aspx

     

    If you can retrieve the error message from the event log, we can help you further.

     

    Given that you are trying to build this to enable offline usage, you are likely to be better off if you go in a different direction.  Although I can't say without knowing more about your application and its requirements.

    Wednesday, July 04, 2007 5:50 PM
  • Very thank to Evan H.

    I have checked my code, and seen that i had some stupid mistakes.

    This is my old code:

    int startpoint = j * 1000;
    int endPoint = startpoint + 1000;
    string selectPlumst = "SELECT * FROM plumst LIMIT " + startpoint + "," + endPoint;

    The LIMIT clause in a select string, whose two numberic arguments are startpoint and the number of rows to return, will return rows from startpoint to starpoint + number of rows to return, not endPoint.
    The code should be changed to:

    int startpoint = j * 1000;
    int numberOfRowsToReturn = 1000;
    string selectPlumst = "SELECT * FROM plumst LIMIT " + startpoint + "," + numberOfRowsToReturn;

    Next, as Evan H said, using XML document will get the entire XML file to memory. In my current solution, i make a xml file for each step of for loop, then compress all these xml files to one zip file and send to client. The client will decompress zip file, read all xml files and merge them in the one datatable.

     The reason that i want to make XML file in webservice machine is I don't need to connect to database to get data for the next webservice calls. I think sending a database in zip file is better. I can not also return a dataset in webmethod because the dataset is too large in this case. If my understand if this is wrong, please tell me.
    Again, thank you very much.

    Thursday, July 05, 2007 4:28 AM